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

... and your question is?

The detailed fact should contain the dimensions of the header. Each fact table would be used independently depending on the nature of the analysis.

Yes. You need two fact tables.

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

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.

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 counting invoices is the only 'header' requirement, have one detailed fact table with the invoice # as a degenerate dimension and do a count distinct.

Thank you all , Now I have a clear good idea