Hello all,

Firstly ,Happy New Year 2012 🙂 to all .!!!

In order to start this year blogging, I would like to post another blog which might be helpful to the one who works in SSAS.

Last time, I was just trying for some trick or some queries in order to quick find the distinct count of all the Hierarchy and attribute members from all the dimensions .

For Example, If we have ‘ PaidDate ‘ dim having Hierarchy members as PaidYear ,PaidMonth, PaidDay then we need to calculate the Distinct count of all those members and similarly for all the others dimension .Then I simply used the

SQL query method as:

Select count(Distinct(PaidYear,PaidMonth,PaidDay)) from PaidDate and similar for others but all I want  is some Quick methods or some DMV queries to find out my solution.

After some research and through MDSN, I finally got my solution .It’s just a simple DMV query as

select * from SYSTEMRESTRICTSCHEMA($SYSTEM.DISCOVER_DIMENSION_STAT,DIMENSION_NAME='Dimension', DATABASE_NAME = 'Database')

--Dimension -replace with the Dimension you want to find Distinct count and Database -replace with your DB catalog.

In the returned tabular result, the ATTRIBUTE_COUNT column is to describe the count that I was looking for.

Is there any alternative solution for this kind of issue .Hope someone have some idea regarding on this.

Hope this post will be useful for all of us in the field of BI mainly SSAS.

Regards,

Anil Maharjan