SQL Server Performance

Totals for a columns and rows

Discussion in 'SQL Server 2008 Analysis Services' started by IrishPirate, Mar 17, 2011.

  1. IrishPirate New Member

    Hi,
    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.
    Thanks,
    Terry
  2. IrishPirate New Member

    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.

Share This Page