Fact tables at different grains with measures in each

Hi,

I am working on a project that requires two fact tables at different grains. The second fact table is 1:n and is a Type II SCD on about four dimension columns.  There are measures in both fact tables that the business wants to report on.  If these two fact tables are joined, the table with the lower granularity will end up double counting, triple counting, etc.  This is also a reporting warehouse so many dimensions are denormalized back into the fact tables.

How can I avoid this while still being able to pull measures from both facts.  Here is an example of my issue:

Code:

Fact 1 - Well

    Well Nbr   Expected Oil Prod
    ----------------------------
    100        50
    200        50

Fact 2 - Completions

    Well Nbr   Completion Nbr   Eff Date     Prod Method   Actual Oil Prod
    ----------------------------------------------------------------------
    100        10               03/01/2014   Natural       18
    100        10               04/01/2014   Pump Unit     30
    200        15               03/01/2014   Pump Unit     20
    200        30               04/01/2014   Pump Unit     20

One suggestion I found was to allocate the measures in Fact 1 across the data in Fact 2.  In other words, both values of 50 in Fact 1 would split out to two 25s each, and the two tables would be joined:

Code:

Well Nbr   Completion Nbr   Eff Date     Prod Method   Expected Oil Prod   Actual Oil Prod
------------------------------------------------------------------------------------------
100        10               03/01/2014   Natural       25                  18
100        10               04/01/2014   Pump Unit     25                  30
200        15               03/01/2014   Pump Unit     25                  20
200        30               04/01/2014   Pump Unit     25                  20

Is this practice okay to do, or is there something I am missing that needs to be done instead?

Thank you all very much for your help.

- Joe

Hi,
you can allocate allocate the measures in Fact 1 across the data in Fact 2 (and therefore probably drop fact 1) - but only if you can allocate them in a way that makes business sense. Taking your example, if the Expected Oil Prod can be split equally between each Completion (or you can come up with another business rule to define the allocation) then this is fine - however if this just an arbitrary allocation that you are making then it's probably not such as good idea as it will just cause confusion.

However, isn't this just a query issue and not a design issue? Just query each fact table independently and then join the 2 result sets together to display the information? If you don't think this is a solution then can you give an example of the type of report that the business wants to see that contains information from both fact tables?

Just as an aside, I'm intrigued by your statement "This is also a reporting warehouse so many dimensions are denormalized back into the fact tables." - what does this mean? All Warehouses are reporting warehouses and dimensions and facts are completely different types of tables so denormalizing dimensions back into facts makes no sense - at least not to me.

Regards,