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.