Wednesday, March 28, 2012

Record Count in SSAS Fact Table

We may need to take the record count for a fact table values in SSAS cube. As an example we may need to take the item count for a category. For that we do not need to write any MDX queries or create any Calculated Members.
If you just added the fact table by using New Measure Group, you can see a measure as FactTableNameCount, which was not in the data warehouse. We can use this measure to take the item count for the above example.
But sometimes when we create the fact table, initially we may not add that measure and later we may want it. Then we can add it to the relevant fact table by right clicking the fact table and selecting New Measure.
In the New Measure window default the Usage value is Sum. Click the down arrow and select Count of rows as shown in below figure.

Then click Ok and FactTableNameCount measure will be added. Use that measure to get the record count.

1 comment: