Hierarchy in the fact table

[markp201]

I have measures at the child, parent and grandparent

The child has only measures and the grain is at the parent with measures that are fully additive.

The grandparent also has its own set of measures.  They are additive but they're not a summary of parent measures.

I don't see this as multiple grains because they're related through common dimensions.

Can this be a single concatenated fact table?

How can this be done without overstating the parent or grandparent measures?

Let's say the parent has 400,000 rows, the grandparent 2000 and the child has 700,000.

I'm reviewing generic keys but is there a solution without this option?  Looking for a yes.

[markp201]

oops.  The child has only dimensions - no measures.

[Sergey_Shuklin]

Hello, Mark!

On words it's difficult to understand the final result of your request (at least for me).

First idea that comes to me is to use a hierarchy() function, but you have a grandparents also, so there is all not so simple.

Can you share a little example of what you have for now and what hinders you to achieve the result? If you also will post a result (output table) it would be more than needed.

Look forward to hear from you!

[markp201]

Does this help?