Totals for a columns and rows | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
Totals for a columns and rowsHi,
I have what should be a simple problem to set up but the results aren’t what I was expecting. I have a fact table of college students organized by student id and a second item classifying them as Undergrad(1) or Graduate(2). I’m trying to do a head count by this level using one student in particular – he has 18 records under Undergrad, 2 as Grad so the cube should 18 and 2 respectively. However, my results are 20 and 20. Along with the Fact table and the 20 records, there are 2 dim tables – one for unique student IDs and the other for the Undergrad/Grad classification. At first I was hoping to use a composite key of the two fields but don’t find information for this type of technique available for the Analysis Services. This is why I have the two dim tables. I’m new at this – Analysis Services and Cubes – so I’m hoping it’s a simple newbie issue. Any help would be greatly appreciated.
And the answer is "New Named Calculation", new named calculation is the answer we were looking for. This is created in the fact table from the data source view designer. Right click on the fact table, select "New Named Calculation" and build the compound key by entering each name followed with a + sign. Then create the measure in the cube. In my original question this new compound key of campus code, student id, year and student level, selected with distinct count will return the head count.