Calculated distinct count measures in SSAS

Distinct count measures are fact-of-life for an SSAS developer. No matter how much we try to avoid them they are always asked for and we have to deal with them somehow. Another painful fact is, as you might already know, we cannot create multiple distinct count measures in the same measure group. So each distinct count measure has to sit in its own measure group which,IMO, does not look right when browsing the cube. In this post, I want to show a method of creating distinct count calculated measures which I found on Richard Lees blog here with slight modification.

Using Adventure Works, let’s say the end users want to know the distinct count of customers who have placed orders on the internet. I can add a calculation like this in the cube

CREATE MEMBER CURRENTCUBE.[Measures].[Unique Customers]
AS COUNT(NONEMPTY([Customer].[Customer].[Customer].members,
[Measures].[Internet Order Count])),

This is all fine and dandy, however, as soon as I added any attribute from customer dimension on the rows or filters, the results were showing incorrect values i.e. the same count of customers was repeated for all records.

The solution is to count the number of unique customers in the context of current attributes of customer dimension. For examples sake, lets take Customer City attribute. I tweaked the calculation like below to count customers only in the context of current members in City attributes and it started working as expected when Customer City attribute is used in rows, columns or filters.

CREATE MEMBER CURRENTCUBE.[Measures].[Unique Customers]
),[Measures].[Internet Order Count])),

Of course, you will have to add all the dimension attributes in the CROSSJOIN but ultimately a calculated, though complex, distinct count measure is better than having a number of physical distinct count measures IMHO.


Exporting MDX queries from SQL Server profiler

I was working on a project to load test and benchmark SQL Server Analysis Services(SSAS) database (blog post to follow soon  a brief about which is here). As part of the exercise, I needed to capture MDX queries running on the given SSAS Cube.  SQL Server profiler makes this very simple. Here are the steps.

1. Start SQL Server profiler and connect to the SSAS server. If you want to you can configure the trace properties and filter for one database. Start the trace.

2. Open Excel and connect to SSAS cube.

3. Build your pivot table (or drag and drop stuff around to get a range of queries.)

4. Go back to profiler and Stop the trace.

5. Click File–>Export–>Extract SQL Server Analysis Services Event–>Extract MDX Events.

6. Save the file as MDX.

A slight issues is that this will store all the MDX queries in one mdx file. If you need to have each query in a separate file, I would suggest using notepad++ and creating a small macro do this. Works like a charm!!

Another approach to get the queries is to use OLAP Pivot Table Extension where you build the pivot table and copy the MDX from the extension. The steps are better explained on codeplex.