Count Distinct in SSAS Cube

Hi Gurus, i found some threads with a similar question, but couldn't find a easy solution for this. 

Background: I have a Huge fact dimension table(table has both measures and dimensions) that i am using to build a SSAS Cube. 

The table didnot have a unique identifier, so the database team added ROW NUM as a column to the table which i am using as a PrimaryKey in my Cube build. I was able to create a cube successfully with it without any issues.

Problem: Now customers are asking for a 'Claim Count calculation' which shows the Distinct Claim Count. 

Its defined as below :

Count(Distinct Claim_Number || Claim Year || Claim Month)

. All the 3 columns are available in table, but when i am trying to create this Count Distinct Object in the DSV the Cube processing time increased by 5 times, as now i have to use a GroupBy function in my SQL.(There are around 30 columns to group by). 

Is there a better way to achieve this Count(Distinct Claim_Number || Claim Year || Claim Month) without using groupBy in DSV SQL logic? I cant seem to find any Count(Distinct) function in the Cube Calculation functions?

Please advise,

Thanks,
Kon

Environment: SSAS 2012 Multidimensional Model

September 8th, 2015 3:02am

Hi,

U try to use the DistinctCount Aggregate-Function for measures?

Alos looked at: https://www.mssqltips.com/sqlservertip/3043/different-options-for-creating-a-distinct-count-measure-in-ssas/
  • Edited by yger 26 minutes ago
Free Windows Admin Tool Kit Click here and download it now
September 8th, 2015 3:15am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics