Using NOT in a Slice

  • Thread starter Thread starter Aaron Relph
  • Start date Start date
A

Aaron Relph

Hi,

I want to be able to answer the question:

Show me all the customers who bought Product A but didn't buy Product B.

How do I model this in AS2K? My fact table is at transaction level, and has
a customer and a product dimension. I want to be able to answer this
question using a pivot table - not an MDX Query.

Thanks
 
Aaron,

If you want to use a Pivot Table to answer this question, then you will need
to use a helper column in your data source to calculate the status.

The formula would likely be something like

=IF(AND(SUMPRODUCT( )>0,SUMPRODUCT( )=0),"Bought A but Not B","Didn't
buy A or bought B")

The terms in the ( )'s of the SumProduct functions will depend on your
table structure, but the first could be like
SUMPRODUCT(($A$1:$A$100=A1)*($B$1:$B$100="A")*($C$1:$C$100))>0
where A has customer names, B has the products, and C has the count of
products.

And the second could be
SUMPRODUCT(($A$1:$A$100=A1)*($B$1:$B$100="B")*($C$1:$C$100))=0

HTH,
Bernie
MS Excel MVP
 
You could use the approach outlined in
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnolap/html/distinct2.asp.
It would require you to create a transaction dimension or a customer
dimension, and the reality is that the larger that dimension gets, the
slower your queries are going to be against it. This would not be something
that you would expect a user to browse in a "session". You'd want to pose
these queries in some sort of batch process and just query for the results
when the user's session begins.


However, OLAP is probably not the best approach for solving this type of
problem. It sounds like what you really want is to do association analysis.
You can accomplish this using data mining techniques. In the 2000 product
line you could do this using decision trees, and in Yukon there will be
association analysis built in.
 
Further to my last post, Sean is right that this is the way to go if
you want to see counts of customers rather than a filtered list. With
a NOT Product dimension and some fancy MDX you would be able to do
what you want.

You may also want to read the following thread on this subject:
http://groups.google.com/groups?hl=...selm=2655635.1047982589%40dbforums.com&rnum=9

It contains a great trick for splitting up a big transaction dimension
into several small ones, and I know from some testing I've done it
makes a significant difference to query performance if you do this. No
guarantees that this means you'll get acceptable query performance
though (queries could still end up running for minutes or even hours),
but it's worth trying.

Regards,

Chris
 
Back
Top