Need help with dimension design
3 posters
Page 1 of 1
Need help with dimension design
This is for an online retail application.
An order line can be associated to a promotion, so i created a promotion dimension whose natural key is the promotion id.
The order fact table contains the promotion key.
Now, the promotion itself can be associated with one or more trigger types, such as:
-- minimum order amount $100
-- minimum order quantity 5
-- must purchase warranty
Additionally, promotions can be associated with customer groups, such as:
-- men
-- women
-- high spender
For example, i might create a promo that says $25 off your order if you spend over $100, buy a warranty, and are part of the "men" and "high spender" groups.
That is, all 4 conditions (2 order conditions + 2 group conditions) must be met to get the $25 off.
The only way i could think to model this is to create 3 dimensions:
-- promotion dimension
-- promotion trigger dimension
-- customer group dimension (actually this already exists)
Then create 2 factless facts:
-- associate the promotion dim to the promotion trigger dim
-- associate the promotion dim to the customer group dim
But this seems very messy, but i cant think of a better way.
The order will contain the promotion id so its quite easy to connect the order fact to the promotion dimension, but its just modeling the details of the promotion itself that is complex.
Any help would be appreciated, thanks!
An order line can be associated to a promotion, so i created a promotion dimension whose natural key is the promotion id.
The order fact table contains the promotion key.
Now, the promotion itself can be associated with one or more trigger types, such as:
-- minimum order amount $100
-- minimum order quantity 5
-- must purchase warranty
Additionally, promotions can be associated with customer groups, such as:
-- men
-- women
-- high spender
For example, i might create a promo that says $25 off your order if you spend over $100, buy a warranty, and are part of the "men" and "high spender" groups.
That is, all 4 conditions (2 order conditions + 2 group conditions) must be met to get the $25 off.
The only way i could think to model this is to create 3 dimensions:
-- promotion dimension
-- promotion trigger dimension
-- customer group dimension (actually this already exists)
Then create 2 factless facts:
-- associate the promotion dim to the promotion trigger dim
-- associate the promotion dim to the customer group dim
But this seems very messy, but i cant think of a better way.
The order will contain the promotion id so its quite easy to connect the order fact to the promotion dimension, but its just modeling the details of the promotion itself that is complex.
Any help would be appreciated, thanks!
topcat- Posts : 19
Join date : 2012-08-09
Re: Need help with dimension design
The thing is, you don't need to implement the promotion, just report on it. So, action on the trigger is a function of the POS system or clerk, not the DW. Couldn't the terms/conditions of the promotion simply be attributes of the promotion?
Re: Re: Need help with dimension design
Actually the requirements are to report on all aspects of the promotion, and of the promotion as it is tied to orders.
Sample business questions:
-- provide a list of all promotions with trigger "spend over $100"
-- provide a list of all promotions associated with the "big spender" customer group
-- provide a list of all promotions associated with the "big spender" customer group, group by month and sum total order amount
So essentially we want to browse the dimensions for some reports, then run typical analytic queries grouping by various attributes of the promotion.
Sample business questions:
-- provide a list of all promotions with trigger "spend over $100"
-- provide a list of all promotions associated with the "big spender" customer group
-- provide a list of all promotions associated with the "big spender" customer group, group by month and sum total order amount
So essentially we want to browse the dimensions for some reports, then run typical analytic queries grouping by various attributes of the promotion.
topcat- Posts : 19
Join date : 2012-08-09
Re: Need help with dimension design
Yeah, you can have three dimensions if you want. The customer segment can be implemented as a dimension or an attribute of customer if the latter is implemented as type 2. But, do promotion triggers have their own natural key or is it based on the promotion? If the natural key is just the promotion, it doesn't necessarily make sense to make it its own dimension. If it has its own unique identifier coming from the sales system then there is no problem as its own dimension.
Re: Need help with dimension design
The promotion "trigger types" in the transactional system are presented as a list object, so i do suspect that these types are stored in a table somewhere and have a unique identifier, likely just a primary key. we receive a promotion feed that just includes the promotion name. its an xml feed that has a section called triggers and in that section there can be 1 or more trigger names, such as "Minimum cart amount $100". My real question is since i want to connect my order directly to the promotion (based on natural key promotion id), then how do i handle the fact that the promotion may have more than one trigger type? if there was just one, i would stamp the promotion dimension with the trigger type and be done with it. but since there can be multiple triggers per promotion, ie free shipping if "Minimum cart amount $100" or "Cart contains a warranty product", how do i model that? I could create a separate promotion dimension record for each trigger. for example the promotion is called "FreeShip2013" and there are the 2 triggers mentioned above. Then i could create 2 separate promotion dim records both with promotion name "FreeShip2013" and one with trigger "Minimum cart amount $100" and one with "Cart contains a warranty product". then when i get an order, i associate it to one or the other. Problem is, on the order we dont know what triggered the promotion, so i want to connect the order to "FreeShip2013", but nothing else. then we want to be able to see that "FreeShip2013" is also associated with the 2 triggers. again, its just the many to 1 relationship between trigger types and promotions that causes me pain. but when i created the factless fact, it just looks like its modeled like a transactional system and not analytic.
Another example of this would be the book/author model. We have a book dimension and an author dimension. then since books can have multiple authors we would associate the 2 using a factless fact. then if we place an order, the order fact will contain a fk to the book dimension. seems like a similar pattern. so i guess the question is if there is an easier way to do this.
Thanks for all your help.
Another example of this would be the book/author model. We have a book dimension and an author dimension. then since books can have multiple authors we would associate the 2 using a factless fact. then if we place an order, the order fact will contain a fk to the book dimension. seems like a similar pattern. so i guess the question is if there is an easier way to do this.
Thanks for all your help.
topcat- Posts : 19
Join date : 2012-08-09
Re: Need help with dimension design
If you want to know what triggered the sale, you need the trigger dimension and a Fk off the fact. If you want to know what triggers a promotion has, you need a bridge table between the promotion and the trigger dimension. However, such a bridge would have little value in reporting sales as it could only tell you what the potential triggers are, not what actually triggered the promotion.
Re: Need help with dimension design
Since a factless fact table is being used to report on Promos and triggers, aren't there 2 possible solutions? A fact table bridge table with the bridge table containing all of the triggers associated with a promo, couldn't the bridge table be dispensed by adding the trigger dimension directly to the factless fact table.
Jeff Smith- Posts : 471
Join date : 2009-02-03
Similar topics
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Dimension Design with intermediate tables between fact and dimension
» Dimension Design
» Dimension design
» Dimension Design Question
» Dimension Design with intermediate tables between fact and dimension
» Dimension Design
» Dimension design
» Dimension Design Question
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum