data model for 2 fact tables (Header / Detail scenario)

[zlbb]

Hello,
I want to verify that I have this data model correct to present as the high level design.

Requirement:
2 facts to be available in one cube.
Facts must be separate physically (regulatory) and it is a header / detail scenario so we do not want to spike up the rows in the header fact with our detail fact (we do not want to combine).
There is 1 dimension which is not related to the header.

Here is an example of the entities:
Dimensions: Account Dim, Product Dim, Invoice Line Dim, Invoice Line Detail Dim
Facts: Invoice Line Fact, Invoice Line Detail Fact
Fact #1 Relationships: Account Dim, Product Dim, and Invoice Line Dim are all related to Invoice Line Fact
Fact #2 Relationships: Invoice Line Detail Dim is related to Invoice Line Detail Fact

So the two facts will be joined by Invoice Line Detail Dim
Invoice Line FACT -> Invoice Line Detail Dim <- Invoice Line Detail Fact

Tables would look like this:
Invoice Line Dim: Invoice Line ID, Date
Invoice Line Fact: Invoice Line ID, Product ID, Account ID, Quantity
Invoice Line Detail Fact: Invoice Line Detail ID, Quantity
Invoice Line Detail Dim: Invoice Line Detail ID, Invoice Line ID, Lot #

Example report: Invoice Line.Date, Invoice Line Detail.Lot #, Invoice Line Detail.Quantity

The query that is generated will query the Invoice Line Detail Fact and then join to the Invoice Line Detail Dim for the Lot # and Invoice Line ID. Then it can join to the Invoice Line Fact with Invoice Line ID and finally join to Invoice Line Dim for the Date. The sum of Invoice Line Detail Fact.Quantity is presented in the report.

Any thoughts on this to avoid a Fact to Fact join, since that is not a best practice?

[zlbb]

In doing a mini-POC of this, I find that I cannot report on Product, Invoice Date, Lot #, Quantity, because the Invoice Line Detail Fact does not have a key to Product as stated in my example above. This means that the foreign key has to be stamped for every dimension on the Invoice Line Detail Fact as well, if we want to infer properties from the header level. Is this a best practice?

[zlbb]

ok so i solved it by creating a view which combines the two facts then made a straightforward star schema

im sure there are examples out there however of modeling for a mixture of conformed and non-conformed dimensions with multiple facts and a logical fact table that sources multiple physical fact tables

it has been fun talking to myself

[Jeff Smith]

In a cube, measures can come from different Fact Tables.

Why wouldn't you aggregate the measures at the detail level up to the header level and put them into the header fact table? You would still have the 2 fact tables, but by aggregating the detail up to the header level, you wouldn't need to join them.

It's one thing to join fact tables during the load process, but joining fact tables by end users is not a good idea. And the only time I would create a view involving 2 fact tables would be with union views when table partitioning was not an option.

[zlbb]

Hi Jeff,

Thank you for your interest.

I do not understand your suggestion, would you be able to give the header fact table using the data tables I provided as examples? Or point me to a resource of someone having done it this way.

Here is a sample report I achieved earlier today:

Invoice Date	Account	Product	Invoice Line Qty	Lot #	Invoice Line Detail Qty
1-15-2011	XYZ Corp	6" Ruler	10	A1B1	6
				A1B2	4

Before the two facts were joined to create the 1 view I had this as my physical source:
Invoice Line Fact: Invoice Line Id, Account Id, Product Id, Quantity
Invoice Line Detail Fact: Invoice Line Detail Id, Invoice Line Id, Quantity

Using the foreign key that is in bold above I could denormalize the two into 1 view. Is this not a best practice?

[Jeff Smith]

Your Invoice Line detail dimension doesn't make sense.

Either the Invoice Line Detail rolls up to the Invoice Line and is part of a hierarchy and should be in 1 dimension - the Invoice Line can have it's own surrogate Key within the dimension and would be a rollup point for a fact table. You would create a view from the Invoice Line Detail Dimension of the Invoice Line attributes (selecting distinct) and use it as a mini-dimension to join to the aggregate fact table.

Or you need a bridge table with the Invoice Line Detail ID and the Invoice Line ID.

Or, on the Invoice Line Detail Fact, you woukld have the Invoice Line Detail ID and the Invoice Line ID. You can aggregate the measures from the Invoice Line Detail Fact up to the Invoice Line (by removing the Invoice Line Detail ID and grouping by the Invoice Line ID). You can combine the aggregated information from the Invoice Line Detail Fact with the information that is going into the Invoice Line fact to create a single Invoice Line Fact or you can create a seperate Aggregate table that is at the same grain as the Invoice Line fact which can be joined (by it's probably better to have 1 Invoice Line fact).

It generally not a good ideal to join 2 fact tables through a dimension table. It's OK to aggregate facts up to the same grain and then join them, but not through a dimension table.

If Invoice Line was a rollup point on the Invoice Line Detail, then you aggregate the Invoice Line Detail Fact information up to the Invoice Line and then join to then Invoice Line. But if you needed to do this on a regular basis, then it's better to it in the load process and structure a fact table to do it rather than pushing a complex process to a report or to an end user.

[BoxesAndLines]

Your Invoice Line detail dimension doesn't make sense.

...It generally not a good ideal to join 2 fact tables through a dimension table. It's OK to aggregate facts up to the same grain and then join them, but not through a dimension table...

Actually, that is the preferred method. When you do, your dimensions are conformed.

[VHF]

If your report required joining on two fact tables it is probably not the best dimensional model from a Kimball-method perspective. Header-detail relationships are a relational database concept and should be factored out of a dimensional model as much as possible.

Joining two fact tables (through conformed dimension attributes as B&L said) is acceptable, but should generally be used for drill-across (such as sales vs. budget) rather than header-detail.

A single fact table at the Invoice Line Detail level would allow you to produce the report you showed. Invoice Line Detail Qty would aggregate (sum) to produce the Invoice Line Qty. If you do need to store Invoice Line level facts separatley for regulatory reasons, then you would need a separate fact table at the Invoice Line level, although I would try to minimize reporting that spans the the two fact tables.

Also, having dimensions for Invoice Line or Invoice Line Detail triggers a red flag--these dimensions would be growing quickly along with the fact table. This might be needed in your case, but identifiers such as these are frequently treated as degenerate dimenssions (DD) and just stored in the fact table.

[Sponsored content]