Many to many dimensions: querying intersecting sets?
3 posters
Page 1 of 1
Many to many dimensions: querying intersecting sets?
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:
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:
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?
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:
Type | Sales |
Shirts | 10 |
Ties | 15 |
Pants | 17 |
Jackets | 20 |
Shoes | 13 |
TOTAL | 50 |
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:
Type | Sales |
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
Re: Many to many dimensions: querying intersecting sets?
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...
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
Re: Many to many dimensions: querying intersecting sets?
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!
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!
Similar topics
» Bridge Table and Customer Hierarchy
» Querying Large dimension
» SCD Type 2 dimension and fact table being the same table
» Schema Roadblock: Dimension vs Fact [Dimensions w/FKs to other Dimensions?]
» Question about Dimensions with SKeys to join with other dimensions.
» Querying Large dimension
» SCD Type 2 dimension and fact table being the same table
» Schema Roadblock: Dimension vs Fact [Dimensions w/FKs to other Dimensions?]
» Question about Dimensions with SKeys to join with other dimensions.
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum