Transaction fact table and Transaction line item fact table

View previous topic View next topic Go down

Transaction fact table and Transaction line item fact table

Post  boernard on Mon Mar 05, 2012 11:05 am

Hello,

i am building a data warehouse for an ecommerce shop. My first idea was to follow the best practices to make a fact table on line item level. But my boss doesn't want the costs for coupons, vouchers, etc allocated on item level. That is why I also have to make a transaction fact table. In order to keep all the information on line item level the dimensions for vouchers and promotions are also available on line item level.

So the only difference between the dimensions of the to fact tables is, that the transaction fact table doesn't have a product dimension. How should I build the DWH now? I thought about linking the transaction fact table to views of the dimensions of the line item fact table.

is this approach ok? any disadvantages?

boernard

Posts : 13
Join date : 2012-01-19

View user profile

Back to top Go down

Re: Transaction fact table and Transaction line item fact table

Post  ngalemmo on Mon Mar 05, 2012 4:21 pm

The two facts should share dimensions as needed, that is what conformance is all about. You don't really need to create views, it all depends on how people will be using it (Are they writing SQL or using a BI tool with an abstraction layer? Will the views make things more understandable?). The transaction fact will have fewer dimensions than the line level, as it is an aggregate of the line. The transaction layer should contain measure totals from the line level to minimize the need to combine the two facts.

You mentioned product is not a dimension on the transaction fact. I would have assumed that would be the case. But, since you mentioned it, are there coupons/vouchers that are product specific? You may want to consider carrying that information in the line table if that is the case.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Transaction fact table and Transaction line item fact table

Post  boernard on Tue Mar 06, 2012 6:35 am

Thank you for your detailed reply!
The users will use a BI Tool (qlikview). I thought about the views because of this pdf Best Practices.pdf On the fourth to the last page it is described that it is better for the BI tool if there are link tables between fact tables and dimensions. I thought I could also resolve this by making views (so that there are no circular references as described in this pdf)

Yes, there are coupons/vouchers which are product specific and I will also keep this information on line item level.
Would you make a bridge table to the product dimension on the transaction fact table? What is the benefit? I thought that all the product specific analysis will be done in the line item fact table and the high level analysis like profit / revenues etc. will be done on the transaction fact table. There should be rarely the need of joining the two fact tables i hope

boernard

Posts : 13
Join date : 2012-01-19

View user profile

Back to top Go down

Re: Transaction fact table and Transaction line item fact table

Post  ngalemmo on Tue Mar 06, 2012 7:10 am

Not familiar with qlikview, but if it has a robust abstraction layer, you usually define dimension aliases there rather than defining views or synonyms in the database. Generally speaking, it is easier to do it there, depending on how your organization deals with production database changes. BI layer changes usually are not subject to as much scrutiny (i.e. paperwork) as a database change.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Transaction fact table and Transaction line item fact table

Post  Vishy on Thu Mar 08, 2012 3:16 am

You can also join 2 fact as order ID or Bill ID must be there right under which you line items are lined ??

Vishy

Posts : 75
Join date : 2012-02-28
Location : India

View user profile

Back to top Go down

Re: Transaction fact table and Transaction line item fact table

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