Fact table design: Sales Transaction with multiple Discount rows

View previous topic View next topic Go down

Fact table design: Sales Transaction with multiple Discount rows

Post  Laszlo on 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

View user profile

Back to top Go down

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

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

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

Post  Laszlo on Fri Dec 10, 2010 9:35 am

Excellent!
Thanks a lot!

Laszlo

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

View user profile

Back to top Go down

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

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