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

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 23 hours 51 minutes ago

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 Tuesday, September 08, 2015 7:14 AM

once read on it , 

http://www.sqlbi.com/wp-content/uploads/The_Many-to-Many_Revolution_2.0.pdf

I hope this might help, there is a section on distinct count 

Hi konquistador2002,

In this scenario, I suggest you create one more separate dimension using this fact dimension table. Create an attribute using the three columns as key columns. Then we can directly using DistinctCount() function for this set of members in MDX query. Or we can create a measure and set DisctincCount in Usage

Also please refer to a white paper below and optimize the DistinctCount measure.

Analysis Services Distinct Count Optimization

Regards,