confusion about Dimensional Models For Parent-Child
5 posters
Page 1 of 1
confusion about Dimensional Models For Parent-Child
Dear All
I'd like to ask a question regarding to ( Design Tip #25: Designing Dimensional Models For Parent-Child Applications ) if you please can help me
It is about having one fact table for both invoice header (the parent ) and details (the children )
suppose we have a the following information in the invoice header
1- Invoice type ( CASH/CREDIT )
2- Payment status (PAID/PARTIAL PAID/ NONE PAID)
3- Paid duration (days ).
and we need the following analysis :
1- Counting sales invoices per Customs or region
2- Counting CASH and CREDIT invoice
3- Paid duration per customer
so all these analysis are related to header not the line item (grain )
Thank you
I'd like to ask a question regarding to ( Design Tip #25: Designing Dimensional Models For Parent-Child Applications ) if you please can help me
It is about having one fact table for both invoice header (the parent ) and details (the children )
suppose we have a the following information in the invoice header
1- Invoice type ( CASH/CREDIT )
2- Payment status (PAID/PARTIAL PAID/ NONE PAID)
3- Paid duration (days ).
and we need the following analysis :
1- Counting sales invoices per Customs or region
2- Counting CASH and CREDIT invoice
3- Paid duration per customer
so all these analysis are related to header not the line item (grain )
Thank you
samimusleh- Posts : 23
Join date : 2013-03-03
Age : 58
Location : Saudi Arabia
Re: confusion about Dimensional Models For Parent-Child
... and your question is?
nick_white- Posts : 364
Join date : 2014-01-06
Location : London
Re: confusion about Dimensional Models For Parent-Child
The detailed fact should contain the dimensions of the header. Each fact table would be used independently depending on the nature of the analysis.
Re: confusion about Dimensional Models For Parent-Child
Yes. You need two fact tables.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: confusion about Dimensional Models For Parent-Child
nick_white wrote:... and your question is?
when we count CASH/CREDIT invoice for each customer per month , the values will be number of invoices multiplied by invoice lines .
so for BI analysis do I have to use one of the following :
1- create a view that select distinct invoice number ( a degenerate dimension in the fact )
2- create a summary (Materialized view ) for only distinct record from the fact table .
3- go to solution of 2 fact tables .
I feel option 2 is better
samimusleh- Posts : 23
Join date : 2013-03-03
Age : 58
Location : Saudi Arabia
Re: confusion about Dimensional Models For Parent-Child
2 table options is more flexible. You do need to include header invoice # into your line item fact table. If volume is high in your header fact table, then I suggest creating a
monthly snapshot Fact table for your counts.
monthly snapshot Fact table for your counts.
zoom- Posts : 97
Join date : 2010-08-23
Location : Chicago
Re: confusion about Dimensional Models For Parent-Child
It depends how much analysis you want to be able to do at the invoice level as opposed to the invoice line level.
If you want to do analysis at both levels then create two tables - one at the line level grain and one at the header level.
If the only header level analysis you need is to count the number of invoices then I would do it in your BI tool, write SQL that does it or implement the logic in a view - rather than build a new fact table just to answer this one question.
Another option that may work (but probably only if you know the number of invoice lines for an invoice at the point you create the invoice line facts) is to add an invoice count measure with a value of (1/no. of lines) - which you can then just sum up. However, care would be needed with any queries that used this measure as it would be easy to end up with a confusing value if, for example, you applied a filter to the query that excluded some, but not all, lines for an invoice
If you want to do analysis at both levels then create two tables - one at the line level grain and one at the header level.
If the only header level analysis you need is to count the number of invoices then I would do it in your BI tool, write SQL that does it or implement the logic in a view - rather than build a new fact table just to answer this one question.
Another option that may work (but probably only if you know the number of invoice lines for an invoice at the point you create the invoice line facts) is to add an invoice count measure with a value of (1/no. of lines) - which you can then just sum up. However, care would be needed with any queries that used this measure as it would be easy to end up with a confusing value if, for example, you applied a filter to the query that excluded some, but not all, lines for an invoice
nick_white- Posts : 364
Join date : 2014-01-06
Location : London
Re: confusion about Dimensional Models For Parent-Child
If counting invoices is the only 'header' requirement, have one detailed fact table with the invoice # as a degenerate dimension and do a count distinct.
Re: confusion about Dimensional Models For Parent-Child
Thank you all , Now I have a clear good idea
samimusleh- Posts : 23
Join date : 2013-03-03
Age : 58
Location : Saudi Arabia
Similar topics
» From Enterprise Models to Dimensional Models. Can a single Dimension table be referenced multiple times in a fact table?
» Pre-Built Dimensional Models
» Dimensional models for K-12 education
» Granularity In two different Dimensional Models
» Example of a business process with more than 1 fact table
» Pre-Built Dimensional Models
» Dimensional models for K-12 education
» Granularity In two different Dimensional Models
» Example of a business process with more than 1 fact table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum