Reporting from two fact tables in one quey

View previous topic View next topic Go down

Reporting from two fact tables in one quey

Post  bjf on Mon Sep 17, 2012 4:10 pm

We have two fact tables, they model two distinct steps in trhe same business process. The data structures in the source database are very different which contributes to the view they should be seperate facts, there are also a lot of data quality issues with the source systems.

There is a one to one relationship between the two fact tables there is a little duplication in the second that can be removed if we apply an islatestrow flag on the join, there are unmatched rows in both facts which may or may not need to be reported depending on the business need.

I could do a drill across query but the reporting requirement is to report at the granular level, so the aggregation would just be a safety net in case of duplication I guess.

My view is that a couple of extra columns from the second fact table should be added to the first and so the fact tables are merged into one, possibly adding it as rows rather than columns is more correct but it makes reporting more complicated.

What is the correct approach for this sitauation?

Thanks

Bruce

bjf

Posts : 3
Join date : 2012-09-17

View user profile

Back to top Go down

Re: Reporting from two fact tables in one quey

Post  ngalemmo on Mon Sep 17, 2012 4:46 pm

There is a basic pattern to combine two fact tables: aggregate each fact along common dimensions and join or union the two sets. Most BI tools will do this for you.

You can consider building an aggregate fact, but not at the cost of dropping the original fact 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: Reporting from two fact tables in one quey

Post  bjf on Mon Sep 17, 2012 5:01 pm

Thanks, but I need to report this data at the most granular level, so aggregation won't solve the problem for me.

I guess I could create a common dimension containing just the business key that I need to join with but that just means two one to one joins instead of one.

Bruce

bjf

Posts : 3
Join date : 2012-09-17

View user profile

Back to top Go down

Re: Reporting from two fact tables in one quey

Post  ngalemmo on Mon Sep 17, 2012 7:02 pm

You can only combine facts at the common grain of the facts. For example, if one fact has order date, customer, product and order #, and the other has ship date, customer, product, order #, and shipment #, you can only report the two together at customer, product, and order #. If that is not what you want, you need to carry additional dimensions on the dependent fact, such as including the order date in the shipment fact.

You want to design fact tables so they are self sufficient and not rely on other facts for dimensionality. So, for example, in a shipment fact, it is common to include many of the dimensions from the order fact so one can do analysis of shipments as they relate to the order without having to include the order fact itself.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Reporting from two fact tables in one quey

Post  bjf on Mon Sep 17, 2012 7:21 pm

So reading your second paragraph it sounds like I'm correct, if there are two steps in a business process they should both be included as dimensions of the same fact table not as two seperate fact tables, so the design as we currently have it is wrong. Correct?

bjf

Posts : 3
Join date : 2012-09-17

View user profile

Back to top Go down

Re: Reporting from two fact tables in one quey

Post  ngalemmo on Mon Sep 17, 2012 10:59 pm

No, just the opposite. Orders & shipping orders, two steps in the business process, two different grains, two separate fact tables. Orders knows nothing about shipping, but shipping needs some retrospective context, i.e. dimensions relating to the order. When you build a shipment fact you include dimensions of the order to provide additional context (what was ordered, when, what order, who ordered it, etc...).
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Reporting from two fact tables in one quey

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