the master detail tables facts

[samimusleh]

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

[ngalemmo]

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.

[samimusleh]

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

[eddymoussallem]

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

[mostafa_mahrous75]

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

[BoxesAndLines]

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

[Sponsored content]