Linking two Fact tables with different grain through a hierarchy dimension

Hi,
While I found some similar topics discussing the issue I'm having, I wasn't able to get a clear answer to my specific problem.

I've got the following structure (simplified) that contains data around students for universities:

Facts:
FactActuals (lowest grain: students)
FactTargets (lowest grain: university, age bands)

Dimensions:
DimUniversity
DimAge
DimStudent

So my two fact tables sits on different grains, Actuals being down to students while the targets are set on a university level and based on age band (e.g. 25-30).
The requirement is for the users to be able to compare Actuals and Targets in the same cube (University completion rate etc.). The actuals fact table needs to go down to student level as it's already being used by other cubes in the business.
I'm having problems figuring out how the DimAge dimension fits in to the model. On the FactActual table I can link it directly to the fact as I can get the student age (e.g. 25), where as the target fact table needs to be set on the DimAge attribute "age band", which is a rollup of the age.

I've had some ideas around creating a view over DimAge that rolls it up to a DimAgeBand level, or even materialising it into a new dimension table. But it I would then lose the ability to link the facts via the dimension in a cube?

Very thankful for any response or guidence in this, and very interested in what is considered best practice in this scenario.

Please let me know if clarification is required.

Regards,
Johan Karlsson

Create two dimensions. One for age band, one for age. Altough I would store birthdate and calculate age.

Thanks for your reply.

So just to confirm:
- I will end up with two dimensions, age and age band (age is a hierarchy already used in the business so it will contain age band as well).
- My FactActuals will have two dim keys, one for each of the above dimensions.
- FactTargets will have a single "age band" dim key.

Many thanks,
Johan

With 2 dimensions, if you have an additional Age Band attribute in the DimAge, DimAgeBand is a natural shrunken dimension or a roll up dimension of DimAge, so you only need one AgeKey in the actual fact table.

Thanks.

Sorry if I'm being thick, but I think I need both keys in my actual?
The DimAge has attributes needed that sits on a lower grain than DimAgeBand, so will need to be linked to the fact.
The DimAgeBand requires a link to both Facts in order for the users to do comparisons and cross analysis (target vs actuals).
Unless I link the two dimensions on the Age Band level somehow and get the facts to link that way?

In order to link your actual with target, you should be using 2 queries, with the results linked by the common attributes.

If Age_band is an attribute on your age dimension, then you should not require the age_band dimension on you actual fact.

You could do it one dim as well. Either way will work. To make it easier for the users, you could create two views, one for age, one for age band, to remove non-applicable hierarchy columns. Think of it like your date dimension. If you want to roll up to the month level you use the month level columns and not the day level columns.

Thanks, the main goal here is to make it easy for the users but also a solution that's easy to maintain.
I went down the view route as well, but couldn't quite figure out how my surrogate key would link to the target fact table. (as it sits on a different grain).

It appears that the summary here is to create a new DimAgeBand dimension and linking that to the DimAge dimension on the Age Band attribute in queries / semantic layers, which will enable the users to do analysis across the facts.

Thanks for the help on this, much appreciated.

If you understand what I mean by shrunken dimension, you might rethink about the two keys in actual fact table.

I would not snowflake DimAge like that as it's straightforward hierarchical relationship between Age and Age Band and they are tiny dimensions. In dimensional thinking, you denormalise them into single dimension and the hierarchy is established within the dimension, not through snowflake or even the fact table. You also aggregate the actuals on Age Band through the same dimension entry AgeKey instead of anything else.

It would be unnecessary to have an extra highly repeated keys in the actual fact table, although it may not be a big deal for small project. It is about building best practice habit to take on big fact tables and monster dimensions. It’s also confusing to users if there are two keys in your fact table while having AgeBand attribute in Age dimension. Users would guess which path they should take to navigate the hierarchy.

For comparison between actuals and budgets, it is the common attribute values that provide the common ground (conformance).

Thanks Hang, I absolutely agree with everything above, FactActuals is not the problem.
I'm still not clear on the best practice around how to link DimAge to FactTargets?
The dimension surrogate key is down to the age level so linking FactTargets to DimAge on "age band" would not work as it would create a many to many relation, due to the dimension being on a lower grain than the fact table.

Link FactTargets to the shrunken dimension DimAgeBand which coexists with DimAge. The point is even if you create the DimAgeBand out of DimAge, you still need the AgeBand attribute in DimAge for the purpose of dimension conformance.

Or you can do as B&L suggested if you don't want a physical DimAgeBand, create an AgeBand view on top of DimAge so it will have perfect 1-m relationship between AgeBand view and Targets. Both approaches work.

Thanks, that makes sense and I'm happy to start implementing my solution.

I assume this is quite a common scenario, (for example: Product - ProductGroup or Date - Month), and good to know what the best practice is.

Many thanks for your help.

Just remember that when querying across fact tables you always join based on conformed dimension attributes (in this case AgeBand).

What are your thoughts around duplicating the Actual figures inside FactActual into FactTarget, aggregating the Actuals to the same level as Targets? (and renaming factTarget to something more appropriate).
So I basically end up with a lower grained FactActual containing actual figures, and the "FactTarget" will have both actuals and targets on the same grain.

This way I wouldn't have to query across Facts.

Sounds good!

Your "Targets" determine the lowest level of granularity for which you would be able to compare ("drill across") targets and actuals, so aggregating the actuals to this level and then putting them in the same fact table makes sense to me.