Many to many dimensions: querying intersecting sets?

View previous topic View next topic Go down

Many to many dimensions: querying intersecting sets?

Post  sushi on Thu Mar 07, 2013 12:17 am

With many to many dimensions it seems natural that the question would arise: "how many had both?" Ie, the intersection of sets.
For example, let's say a clothing store has an analysis cube of customers and sales. A many to many dimension exists for the type of clothing purchased, and gives results like:

TypeSales
Shirts 10
Ties 15
Pants 17
Jackets 20
Shoes13
TOTAL50

But then the question arises: how many customers bought a shirt and a tie? I don't know of any tool that will do this (other than writing a query), so you have to calculate it manually by filtering on shirts and ties to get something like:

TypeSales
Shirts 10
Ties 15
TOTAL 17

Then you can determine the intersection of the two sets: (10+15)-17 = 8, so 2 bought just a shirt, 7 bought just a tie, and 8 bought both. Of course then it gets exponentially complicated if you want to determine who bought 3 particular things together, etc.

Is there a better way to model this so that these sorts of questions can be answered?



sushi

Posts : 1
Join date : 2013-03-06

View user profile

Back to top Go down

Re: Many to many dimensions: querying intersecting sets?

Post  MikeK on Thu Mar 07, 2013 12:13 pm

I think this is similar to what I am trying to figure out (I am new to this, so please bear with me).

Using the 'Beep/Sales' concept.

I have a fact table with each scan... Recording Product, Receipt Number, Product ID etc..
I have a fact table also for each receipt (which is a group of scans - a different grain with some extra info).
Product would be a dimension, with attributes for grouping them.

So - say I want to get a count of all receipts where the customer bought Bread, but I want to group them by what kind of milk they bought.

So, say 100 receipts contained Bread, so I want a query to produce
No milk - 30
1% milk - 20
2% milk - 40
Whole Milk - 60

The queries I am doing keep cancelling each other out, since a scan will be either bread, or milk, but not both.. I figure there is something simple I am missing...

MikeK

Posts : 1
Join date : 2013-03-07

View user profile

Back to top Go down

Re: Many to many dimensions: querying intersecting sets?

Post  Mike Honey on Mon Mar 11, 2013 1:27 am

Hi sushi and Mike,

I think your requirement could be met by the "Survey" pattern in the SQL BI Many-to-Many Revolution paper:

http://www.sqlbi.com/articles/many2many/

Essentially you would add aliases for your facts for as many combinations as you need, with separate relationships defined.

With that in place, any query tool will give you the right answers e.g. Excel Pivot Tables.

Good luck!
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Many to many dimensions: querying intersecting sets?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum