DISTINCT COUNT basket analysis between multiple measures with shared dimension

1
votes

Let's say I have a cube with two different distinct count measures, call them Measure1 and Measure2. Both of these measures contain a common dimension, Dimension1, which is counted by both measures.

What I need to do is return a distinct count of Dimension1 members that exist in both Measure1 and Measure2, after appropriate filtering on each measure as required.

I can define MDX queries for both Measure1 and Measure2 individually and get distinct counts, but I need to be able to "overlap" the result to avoid double-counting the members that exist in both sets.

Note: in the actual scenario, there are more than 2 measures involved, and all MDX queries will be dynamically constructed (the user defines which measures and dimension criteria are included).

Can this be done in SSAS/MDX? If not, is there another Microsoft tool/feature that can? The minimum requirement for the system is SQL Server 2008 R2 Standard Edition.

Honestly I have no idea where to start. Google turned up nothing like this (I saw some basket analysis stuff involving a single measure, but I'm unsure if or how to apply that to my scenario). I'm not an SSAS/MDX/BI expert by any means.

There are two alternatives that I can think of:

  1. Use DRILLTHROUGH using the individual MDX queries and (essentially) COUNT DISTINCT the results.
  2. Use T-SQL on the data warehouse source database. (May be difficult to account for all scenarios efficiently.)

We do have a requirement to also be able to drillthrough, so I'll probably have to implement solution #1 anyway, but it would be nice to have a more efficient way to obtain just the counts, as counts will be needed far more frequently.

4
sql-serverssasmdx
Does this work, as another measure: SUM(CASE WHEN duplicate THEN 1 ELSE 0 END) as OverlapCount – Pieter Geerkens
Sorry, @Pieter, I don't understand what you mean. – Jon Seigel
Can rows self-identify whether or not they are duplicates? If yes, then add the formula above as an additional measure in your pivot tabel, and when adding Measure1 to Measure2 also subtract the value of the new measure (the number od duplicates). – Pieter Geerkens
@Pieter: No, rows can't self-identify if they are duplicated, because it depends on what the user wants (Measure1 and Measure2, or Measure2 and Measure3, etc.). It also depends on the dimension filters that get applied. As I said, I can define MDX queries to get the set of dimension members for each measure, but then I need to DISTINCT COUNT on the combination of the results for all the measures required. – Jon Seigel

4 Answers

0
votes

I would add a Distinct Count measure based on the Dimension1 Key attribute. I would present it in Excel 2010+ using the Sets MDX feature to filter on Measure1, 2 etc.

0
votes

I never did find an MDX solution for this.

I went ahead with a solution that queries the data warehouse directly, and it's working pretty well so far after some performance tweaks. This approach may not be suitable for all applications, but it looks like it will work for our particular scenario.

0
votes

I would recomend union function either on a SQL Server side (create view froom two tables) or at a SSAS side (create a single measure but with diferent partitions from diferent sources (for example, Partition1 - for Credits, Partition2 - for Deposits).

For second way, initialy over this "monstrous" decision you need make simple Measure1 using SUM function. And after, ckick on Measure1 and choose "Create new measure" using DistionctCount function. So SSAS would make a separate new Measure Group with Measure2 using DistionctCount function.

It must work perfectly.

-1
votes

Lets simplify the problem statement. You want the count of customers who bought both bread and eggs or who have a toyota and honda. I faced this issue a long time back and came up with a query design. The performance for these queries was not good. By the nature of these queries they are opening the fact to grain level. Hence all aggreagtion benifits lost.

Here is the code, I am counting the customers based on their names, who ordered ClassicVestS or HLMountainTire and other products 

with  
member [Measures].[CustomersWhoBoughtClassicVestS] as 
count(
intersect(
{nonempty(
existing ([Customer].[Customer].children),[Measures].[Internet Order Count]
)},
{extract(nonempty( ([Customer].[Customer].children* [Product].[Product].&[471]),[Measures].[Internet Order Count]),[Customer].[Customer])}
)
) 
member [Measures].[CustomersWhoBoughtHLMountainTire] as 
count(
intersect(
{nonempty(
existing ([Customer].[Customer].children),[Measures].[Internet Order Count]
)},
{extract(nonempty( ([Customer].[Customer].children* [Product].[Product].&[537]),[Measures].[Internet Order Count]),[Customer].[Customer])}
)
) 
Select {[Measures].[CustomersWhoBoughtClassicVestS],[Measures].[CustomersWhoBoughtHLMountainTire] 
} on columns , 
{ nonempty( [Product].[Product].children 
,[Measures].[Internet Order Count]) } 
on rows 
from [Adventure Works]

Result