Question about Aggragation in Measure Group?

Hello,

I am working on a project using SQL Server BI Developement Studio 2005. While designing a cube, I would like create a Measure Group that shows me a count of students grouped by department or school. However, I am not able to see an aggragate function under Measure Group's properties that would take care of this? For a while I was thinking about using "ByAccount" function but my dimension doesn't have anything called Account. My dimension has following columns: studentid, term, majorid, [dept id], [dept name], [school id], [school name]. Is there a way to get a count of students grouped by dept or school under a Measure Group?

Thanks,

Below, you can see functions that I am able to see when designing Meaure Groups.

Sum

Count

Min

Max

DistinctCount

None

ByAccount

AverageOfChildren

FirstChild

LastChild

FirstNonEmpty

LastNonEmpty

Hi,

I suspect that you might didn't understand the concept of OLAP yet (sorry if I'm wrong with that assumption).

You first have to think about your data... what are facts, what are dimensions. Facts are the base of your measures. Dimensions are your grouping creterias.

In your case the fact table and the dimension table might be the same. You define a "count" measure on any of your fields.

You also define one or more dimensions with your grouping creterias (department, school or whatever you want)...

When you open your cube you will see the count of records you processed. You then can drilldown on each dimension you defined to group your count...

Sorry, this is only a very brief hint how you might start. I really can't give you a complete training on OLAP in this forum...

|||

Thank you Thomas. I am new to Olap and still struggling a little bit to get the concepts right. My understading of a Fact table was that it was the only place where you have columns that can be summed, addded ,aggragated and so on. However, it looks like it is not the case. Do you know where I could learn more about BI in SQL 2005? Is there a website or something with some info?

|||

Take a look for Mosha & Teo Lachev on Google.

Mosha has some good resources listed on his web site. Teo has a great book out.