Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Fact table design: Sales Transaction with multiple Discount rows

2 posters

Go down

Fact table design: Sales Transaction with multiple Discount rows Empty Fact table design: Sales Transaction with multiple Discount rows

Post  Laszlo Wed Dec 08, 2010 9:18 pm

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

Laszlo

Posts : 2
Join date : 2010-12-08
Location : Florida, USA

Back to top Go down

Fact table design: Sales Transaction with multiple Discount rows Empty Re: Fact table design: Sales Transaction with multiple Discount rows

Post  ngalemmo Thu Dec 09, 2010 7:16 pm

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.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Fact table design: Sales Transaction with multiple Discount rows Empty Re: Fact table design: Sales Transaction with multiple Discount rows

Post  Laszlo Fri Dec 10, 2010 9:35 am

Excellent!
Thanks a lot!

Laszlo

Posts : 2
Join date : 2010-12-08
Location : Florida, USA

Back to top Go down

Fact table design: Sales Transaction with multiple Discount rows Empty Re: Fact table design: Sales Transaction with multiple Discount rows

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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