Fact Table - Relationship

[manickam]

I came across an article in the kimball forum regarding the data quality architecture. I noticed a model wherein the FACT table having 1 to many relationship with child table (FACT DETAIL).

In general we often use to say that FACT table should not have any relationship with child table to have the charterstics of the dimensioal model and also to distinguish from ER model.

The intention and reason for this disuccsion is to understand where and when can we use these type of designs.

[gvarga]

It is quite common to have dimensional models where one fact table is based upon a master table from the OLTP system and another fact table is based on its detail table, as you mentioned.

Of course there is a logical „relationship” between the 2 fact tables, mostly expressed by „conformed degenerate dimension” (e.g. invoice number, order number etc., which are in both tables)

But if you although define a physical (Foreign key) relationship in your database, you will not import it to the BI tool and so you will have in the admin layer just 2 dimensional models. As to the database model it is a little bit „hibrid” .

If you are analyzing MASTER fact data on elementary level and there is a need to show the DETAIL rows as well, you will have to navigate to another page, that means you have to store somehow the relationship between MASTER and DETAIL fact tables.

[ngalemmo]

A fact table is made up of dimensions (references and degenerate) and measures. The measures are bound by the dimensions, so the table needs all dimensions appropriate for the measures.

In a header/detail situation, the only reason to create a header fact table is if there are useful measures that are bound by the dimensions of the header (or to serve as an aggregation of the detail). The detail fact should contain all dimensions that bound the measures, which includes those dimensions from the header.

Wither you have one fact table or two depends on the measures. If you have two facts you cannot join them directly because the measures are at different grains. The detail measures must be summarized to conform to dimensions in common with the header fact.

If you have a header fact solely to hold dimensional information, you are essentially creating a snowflake of the worst kind. Performance is much better if all dimensions are in a single fact.

Creating a header fact simply as an aggregation should not require the need to join with the detail. The decision to create such a table should depend on frequent use cases that could benefit from queries against a smaller table.

[manickam]

Many thanks for the inputs and now am getting better idea.

As per that model FACT1 and FACT2 holds different grains, but the FACT2 table is child of the FACT1 (have one to many relationship).

Primary key of the FACT1 table will be used as the foreign key in the FACT2 table.

[Jeff Smith]

You can't join Fact Tables together. The only things on Fact Tables are Measures and Dimension Keys.

The primary key of Fact Table 1 (which is unnecessary and will probably cause other issues) should not be a foreign key on Fact Table 2.

Fact table should be designed so that they are independent of other Fact Tables. That's the whole point of a Star.

[manickam]

Please go through this link.

http://www.kimballgroup.com/wp-content/uploads/2007/10/An-Architecture-for-Data-Quality.pdf

[Jeff Smith]

Which page does the link discuss fact table to fact table joins?

[manickam]

Please refer to 10th page

[BoxesAndLines]

You really shouldn't use this type of design.  The error event fact is simply an aggregate of the error event detail.  Kimball needed a common degenerate dimension in order to roll up the all of the detail errors to error event fact.  All he had available was the parent fact primary key.  As an aside, I use this design to capture data quality errors with the FK defined and I don't have any problems with performance.

[Jeff Smith]

It says that this design is more like a production transaction system. He's using the star schema to capture information about processes occurring during the ETL process. It's the exception to the rule that makes the rule true.

[manickam]

Many thanks folks to make me understand...

[Sponsored content]