MDX Script Distinct Count

Question

I have a data warehouse in star schema.  My FactActivities table has a PersonKey and an SurveyCount field.  The PersonKey is the FK to the Person dimension.  The SurveyCount field is an int field limited to 0 or 1.  The Person dimension has one record per person.  FactActivities has a record per activity, so zero or more per person and each record could have SurveyCount as 0 or 1.  There are, of course, other fields, but they are not relevant.

I need to display the unique count of Persons who have a true (1) SurveyCount value.

I've attempted to use DISTINCT and DISTINCTCOUNT with negative results.  I'm sure this is due to my lack of understandng MDX scripting, so while I keep trying, I thought I would ask here as well.  This script is for use in a calculation in a cube, so I don't need the whole Select .... on 0... etc..., just the calc.

Thanks in advance for your thoughts.

Aaron

Answer 1

Hi there,

Create another measure group in your cube based on a named query which provides distinct counts of persons who have true survey count. 

Further info on this and alternate examples here.

http://www.microsoft.com/en-ca/download/details.aspx?id=891

Depending on the size of data, you may be able to get away with using DISTINCTCOUNT.

http://www.mdxpert.com/Functions/MDXFunction.aspx?f=71

cheers,

Andrew

---

Andrew Sears, T4G Limited, http://www.performancepointing.com

Answer 2

I found a work around that achieved the desired results, albeit breaking Kimbal's rules.  I created a dimension based off of FactActivities and added SurveyCount as an attribute.  This allowed me to use it in a MDX calc as so:

([Measures].[Unduplicated Activities Count],[Activities].[Survey Count].&[1])

Unduplicated Activities Count is a distinct count on the PersonKey.

Validation was achieved via querying the data warehouse.  

I'll have to review Andrews's solution as mine break's the Kimbal model and in probably less efficient.

Answer 3

Thanks Andrew!  I wish I had time to try this out right now.  Since I have a working solution, I must move forward.  But, when I have a chance to review and try this, I'll respond more.  -Aaron