Fact Table - Relationship

View previous topic View next topic Go down

Fact Table - Relationship

Post  manickam on Tue Apr 08, 2014 2:20 am

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.


manickam

Posts : 27
Join date : 2013-04-26

View user profile

Back to top Go down

Re: Fact Table - Relationship

Post  gvarga on Tue Apr 08, 2014 12:26 pm

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.

gvarga

Posts : 43
Join date : 2010-12-15

View user profile

Back to top Go down

Re: Fact Table - Relationship

Post  ngalemmo on Tue Apr 08, 2014 2:01 pm

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Fact Table - Relationship

Post  manickam on Wed Apr 09, 2014 8:42 am

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.






manickam

Posts : 27
Join date : 2013-04-26

View user profile

Back to top Go down

Re: Fact Table - Relationship

Post  Jeff Smith on Wed Apr 09, 2014 9:21 am

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Fact Table - Relationship

Post  manickam on Thu Apr 10, 2014 12:32 am

Please go through this link.

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



manickam

Posts : 27
Join date : 2013-04-26

View user profile

Back to top Go down

Re: Fact Table - Relationship

Post  Jeff Smith on Thu Apr 10, 2014 8:50 am

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

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Fact Table - Relationship

Post  manickam on Fri Apr 11, 2014 1:49 am

Please refer to 10th page

manickam

Posts : 27
Join date : 2013-04-26

View user profile

Back to top Go down

Re: Fact Table - Relationship

Post  BoxesAndLines on Fri Apr 11, 2014 7:54 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Fact Table - Relationship

Post  Jeff Smith on Fri Apr 11, 2014 9:29 am

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Fact Table - Relationship

Post  manickam on Tue Apr 15, 2014 2:38 am

Many thanks folks to make me understand...

manickam

Posts : 27
Join date : 2013-04-26

View user profile

Back to top Go down

Re: Fact Table - Relationship

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