Fact table design: Sales Transaction with multiple Discount rows
2 posters
Page 1 of 1
Fact table design: Sales Transaction with multiple Discount rows
Hi,
I am designing a Sales DW where we can have multiple discounts on a given transaction line.
I created a SalesTransaction (ST) fact table and a SalesDiscount (SD) fact table.
In the current design the ST table has its own key that is a bigint (ST.ST_KEY). I am linking the two fact tables together using ST.ST_KEY = SD.ST_KEY.
I am open for alternatives as I have doubts that this is the most efficient way to implement multi discounting. Currently, I am out of ideas and that is why I am asking for expert advice.
Am I doing the right thing?
Is there a better way?
Is there any known negative side effect or impact on reporting, cube implementation (SQL Server Analysis Services) or on anything/anywhere else?
Thanks in advance!
Laszlo
I am designing a Sales DW where we can have multiple discounts on a given transaction line.
I created a SalesTransaction (ST) fact table and a SalesDiscount (SD) fact table.
In the current design the ST table has its own key that is a bigint (ST.ST_KEY). I am linking the two fact tables together using ST.ST_KEY = SD.ST_KEY.
I am open for alternatives as I have doubts that this is the most efficient way to implement multi discounting. Currently, I am out of ideas and that is why I am asking for expert advice.
Am I doing the right thing?
Is there a better way?
Is there any known negative side effect or impact on reporting, cube implementation (SQL Server Analysis Services) or on anything/anywhere else?
Thanks in advance!
Laszlo
Laszlo- Posts : 2
Join date : 2010-12-08
Location : Florida, USA
Re: Fact table design: Sales Transaction with multiple Discount rows
I had a similar situation for a large CPG manufacturer using SAP (without BW!).
We implemented two fact tables for sales. One was the traditional sales line fact with net amounts relating to the sale. The second fact was the pricing components relating to the line. This broke out, in great detail, how the net price was calculated. The second fact contained all the pertinent dimensions from the sales line fact as well as dimensions for the price component, promotions and accounting information.
The two fact tables were complete and independent of each other and used for different purposes. There was never a need to 'join' the two tables.
We implemented two fact tables for sales. One was the traditional sales line fact with net amounts relating to the sale. The second fact was the pricing components relating to the line. This broke out, in great detail, how the net price was calculated. The second fact contained all the pertinent dimensions from the sales line fact as well as dimensions for the price component, promotions and accounting information.
The two fact tables were complete and independent of each other and used for different purposes. There was never a need to 'join' the two tables.
Re: Fact table design: Sales Transaction with multiple Discount rows
Excellent!
Thanks a lot!
Thanks a lot!
Laszlo- Posts : 2
Join date : 2010-12-08
Location : Florida, USA
Similar topics
» Is it possible to get a distinct order count with a transaction line sales fact table?
» Fact table design based on sales appointments
» Question on Multiple fact table design
» Design Fact Table in Dimensional Modeling with Multiple Grain
» Guidelines for FACT Table Design for High-Transaction Volume and High # of Measures ...
» Fact table design based on sales appointments
» Question on Multiple fact table design
» Design Fact Table in Dimensional Modeling with Multiple Grain
» Guidelines for FACT Table Design for High-Transaction Volume and High # of Measures ...
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum