Unusual(?) parent/child Fact table design issue

View previous topic View next topic Go down

Unusual(?) parent/child Fact table design issue

Post  Jo_D on Thu Jul 03, 2014 6:48 am

I've searched exstensively for an answer to this but am coming up blank.

Currently, I have a Fact table which has invoice header and invoice line information on, designed as is suggested by the articles on this site about parent/child data. All of the information is as child (invoice line) level, with the invoice number repeated on each line.

However, these invoices are for an energy retail company, and as a result they contain a fact which isn't always held on other invoices - the volume of energy which has been consumed.

The volume measure is relevant at both invoice header and invoice line levels, but in different ways. The users want it at invoice line level because multiple charges - so multiple invoice lines - will be based on the same volume. They want to be able to use it for calculations at this level, in which case it would need to be non-additive. The volume for each line is exactly the same as the volume at invoice header level - it's not like an invoice header level discount that could be allocated out. Each line's value has been calculated using the full volume figure. Here's an example:

Invoice NumberLine NamePriceVolumeValue
188741Charge 10.301000300.00
188741Charge 20.201000200.00
188741Charge 30.101000100.00

However, the volume is also relevant at invoice header level, from which point it should be additive. The users might want to find out the volume for all of a customer's invoices, for instance.

Whoever originally created the schema worked around this by including the volume on only one invoice line per each invoice, with it set to 0 on other lines. This is messy, and also doesn't satisfy the users' need to use this measure alongside other invoice lines' values. One option is to include the volume on every invoice line, then do something in the cube that sits above this schema to prevent the duplicate values from being summed up - but is there a way of managing this in the star schema, so the fix doesn't seem replicating if other cubes or reports are produced from it?

Jo_D

Posts : 2
Join date : 2014-07-03

View user profile

Back to top Go down

Re: Unusual(?) parent/child Fact table design issue

Post  BoxesAndLines on Thu Jul 03, 2014 10:02 am

The facts live at different grains. The original solution is the most common "hack" for this problem, that is, put the metric on only 1 of the line items. The volume metric needs to live at the header level where it becomes additive. The solution is to aggregate the lower grained fact table to the header level where all the metrics become additive. This is a simple drill across query across the two facts on a common dimension.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Unusual(?) parent/child Fact table design issue

Post  Jo_D on Thu Jul 03, 2014 10:48 am

That was one of the solutions I'd considered, but pretty much every article I could find on parent/child table situations was strongly against having two fact tables. I guess that doesn't apply when you have a situation like this where some measures really *must* be at the grain of the parent?

Thanks for the quick response!

Jo_D

Posts : 2
Join date : 2014-07-03

View user profile

Back to top Go down

Re: Unusual(?) parent/child Fact table design issue

Post  BoxesAndLines on Thu Jul 03, 2014 10:57 am

The ideal solution is to bring the header information down to the detail level. Since that is not an option, you end up with two fact tables.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Unusual(?) parent/child Fact table design issue

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