Reporting from two fact tables in one quey

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

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.

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

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.

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?

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