confusion about Dimensional Models For Parent-Child

View previous topic View next topic Go down

confusion about Dimensional Models For Parent-Child

Post  samimusleh on Thu Nov 05, 2015 12:14 pm

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

samimusleh

Posts : 23
Join date : 2013-03-03
Age : 51
Location : Saudi Arabia

View user profile

Back to top Go down

Re: confusion about Dimensional Models For Parent-Child

Post  nick_white on Thu Nov 05, 2015 12:24 pm

... and your question is?

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: confusion about Dimensional Models For Parent-Child

Post  ngalemmo on Thu Nov 05, 2015 12:43 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: confusion about Dimensional Models For Parent-Child

Post  BoxesAndLines on Thu Nov 05, 2015 2:12 pm

Yes. You need two fact tables.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: confusion about Dimensional Models For Parent-Child

Post  samimusleh on Thu Nov 05, 2015 2:37 pm

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 : 51
Location : Saudi Arabia

View user profile

Back to top Go down

Re: confusion about Dimensional Models For Parent-Child

Post  zoom on Thu Nov 05, 2015 3:52 pm

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.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re: confusion about Dimensional Models For Parent-Child

Post  nick_white on Fri Nov 06, 2015 7:35 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: confusion about Dimensional Models For Parent-Child

Post  ngalemmo on Fri Nov 06, 2015 4:44 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: confusion about Dimensional Models For Parent-Child

Post  samimusleh on Sat Nov 07, 2015 11:30 am

Thank you all , Now I have a clear good idea

samimusleh

Posts : 23
Join date : 2013-03-03
Age : 51
Location : Saudi Arabia

View user profile

Back to top Go down

Re: confusion about Dimensional Models For Parent-Child

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