the master detail tables facts

View previous topic View next topic Go down

the master detail tables facts

Post  samimusleh on Sun Mar 03, 2013 6:21 am

I have the case of the master -detail tables (e.g. the invoice header and details ) to be designed as a fact table .
The master table contains the value (sales charges ) which represents an extra amount added to the total value of invoice items , so my solution for the fact table is to contain a record for the master table with the value of this (sales charges) as a fact and 0 values for all line amounts (facts) , and we distinguishes this record by the value 0 for the line id column, and for each record in the invoice lines (details) we have a record with the value 0 for this (sales charges) as a fact and the actual values for all line amounts (facts) , and we distinguishes this record by the actual value for the line id column .
so each record will have the main invoice header id (invoice number) and the line item number from details for each line except the first record will have value 0 for line number .

suppose the invoice number =500 , the sales charg= 354 and it has to lines
1 total value = 120 , qty = 30
2 total value = 200 , qty = 5

Inv no , Line no , Sales charge , Line item value , Line item qty
500 , 0 , 354 , 0 , 0
500 , 1 , 0 , 120 , 30
500 , 2 , 0 , 200 , 5



thanks

sami musleh

samimusleh

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

View user profile

Back to top Go down

Re: the master detail tables facts

Post  ngalemmo on Mon Mar 04, 2013 3:22 pm

You can do it that way. But your example is missing the product dimension (which I assume you left out for brevity). The thing is, you should populate a key for your sales charges lines as well. You would need to create a product row to accommodate this.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: the master detail tables facts

Post  samimusleh on Tue Mar 05, 2013 12:54 am

Thank you for your advice, yes as you assumed the dimension was left for brevity . Also I have considered the keys . For further explanation , this is the same case for PARENT-CHILD concept where the invoice header have some values (facts) like the sales charge or delivery charges , which can not be implemented as "Allocated fact" since it is not possible to have a formula for this ,for example the value may reflect number of vehicles used for delivery , so it is not convenient to allocate a value for each line item in the invoice.
So my suggestion is to have one fact table which contains a row for the invoice header where we keep such facts (no. of vehicles,sales charge) and a value of 0 for the line_no column indicating a header row , and for each invoice line we have a row where the value for such facts (no. of vehicles,sales charge) = 0 and the line_no column have the corresponding invoice line no (line key ). and we add the key for both invoice and each line .
In this case if we accumulate facts per invoice the value of (sales charge) will be correct since all rows for the line items have the value (0) .
we can also create a view upon this fact table to only display the rows of each invoice header .


Thanks

samimusleh

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

View user profile

Back to top Go down

NULL instead of Zero

Post  eddymoussallem on Tue Mar 05, 2013 10:28 am

I was wondering in this case whether to populate NULL instead of Zeros (0).

eddymoussallem

Posts : 1
Join date : 2013-03-05

View user profile

Back to top Go down

In Case that Allocation is impossible - as mentioned; would it be more proper to split it into 2 Fact Tables

Post  mostafa_mahrous75 on Wed Mar 06, 2013 8:27 am

Thanks ngalemmo for your reply. I've some concerns related to the suggested design where both of Master and Child measures are combined into a Single Fact despite that there's no formula to allocate Master measures at Child Level:

1- Granularity of the Single Fact table can't be well determined

2- We will always need to embed extra filter to distinguish between Master and Child records, which will decrease the data retrieval performance and may cause confusion to end user in case he missed to add that filter

3- Measures of the Single Fact table will be grouped into 2 categories (Master Measures and Child Measures). For each Fact record, only one group will have values and the other group are NULL which will result in unjustifiable increase of Fact record length and consequently increased no. of I/O's

4- We may end up with creating 2 views over Single Fact table:
  • Master View which selects only records and columns related to master table
  • Child View which selects only records and columns related to master table

Again, retrieving data from physical tables is faster than views


I think that the suggested design contradicted with your reply here

mostafa_mahrous75

Posts : 8
Join date : 2011-11-20

View user profile

Back to top Go down

Re: the master detail tables facts

Post  BoxesAndLines on Wed Mar 06, 2013 10:05 am

What you are doing is mixing grains in the fact table. This is never a good idea.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: the master detail tables facts

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