Actual and Plan Facts at different granularity - one conformed dimension?

We have a Resource dimension with a Resource_Skey and attributes of Department, Grade and Resource Type.

We have 2 fact tables in our design:

F_Actual_Headcount
Week_SKey
Resource_SKey
Actual FTE
Actual Hours

F_Plan_Headcount
Week_SKey
Department
Grade
Resource Type
Plan FTE
Plan Hours

We want to report on actual and plan in the same report at Department, Grade and Resource Type level. Question is how do we join the plan fact table to the conformed Resource dimension so that both plan and actual can be joined together at the common granularity?

My thought was to add SKeys for the Department, Grade and Resource Type attributes into the Resource dimension, put SKeys for these into the F_Plan_Headcount table and then use the BI tool (Cognos) to do the work and group the dimension to the correct granularity when it generates the SQL.

Another view was to create 3 'shrunken' dimensions for Department, Grade and Resource Type and then add these as additional skeys into the F_Actual_Headcount table so that both facts can be joined together via these new 'common' dimensions as follows:

F_Actual_Headcount
Week_SKey
Resource_SKey
Department_SKey
Grade_SKey
ResourceType_SKey
Actual FTE
Actual Hours

However this would mean that the F_Actual_Headcount table would have not only a Resource_Skey but also Skeys for Department, Grade and Resource Type which are essentially attributes of resource. This seems wrong to me as we would be denormalising attributes of resource into what would become a bloated fact table. But maybe I'm wrong!

Another matter to consider is there may be plan data for, say, a Resource Type for which there are currently no resources in the Resource dimension, i.e. there is no such instance of that Resource Type in the Resource dimension even though there may be plan for it. This would mean there will be nothing to join it to unless we generate a dummy resource to cater for all possible Resource Types?

Which is the best approach? Or is there a better approach??

Hi

Yes, there is certainly a number of ways to solve this.
The simplest would be to create a resource dimension with all possibilities.




So all records where ResourceType = "A" will be available via a simple query using your Cube software. However you will have to cater for those where the other resource attributes are unknown for this to work.Even if your planned headcount does not have Department etc. attributes.

Hi Ian

Maybe I should have made clearer that plan data is only available at Department, Grade and Resource Type level - we don't plan down to Resource. So it wouldn't make sense to include resource in the plan fact table. Although we can achieve this easily enough in a Cognos cube using a single resource dimension (as the cube will take care of the mixed granularity) we would like to model it for relational, SQL type reporting as well.

Thx
JPB

I think it depends on the realtionship between Resource and Resource type. If Resource rolls up to Resource Type, then create a dimension key within the Resource dimension based on Resource Type. Resource Type becomes a mini-dimension. Create a view of the distinct rows from the resource Dimension that make up the Resource type.

The link between Actual and Plan is the Resource Type Key. Create an aggregate that rolls Actual to the Resource Type Key.

You could even put the Plan figures on the Aggregate table so that the table has 2 measures - Plan and Actual. This could be useful in situations where the Actual amount is 0 and the plan amount is > 0 and would certainly simplify reporting the 2 measures.

Hi Jeff

Yes - resource type, grade and department are all attributes of resource so they do roll up. This is the level they plan at for FTE headcount and hours.

So in our case, it sounds like you would add dimension/surrogate keys for these 3 attributes into the Resource dimension and implement these as shrunken dimensions rather than add them as keys into the Actuals Fact table - correct? Are there any negatives to adding these as keys into the Actual Fact? It sounds as if to enable reporting of actuals vs plan you will need to aggregate the actuals to the same level as plan - makes sense to put these in the same table as the primary key will be identical.

Only problem will be the situation in the last point of my original post: there may be plan data for, say, a Resource Type for which there are currently no resources in the Resource dimension, i.e. there is no such instance of that Resource Type in the Resource dimension so a select distinct on the resource dimension would not return that resource type. It will only be available as master data in the source system - this means we will have to build the mini/shrunken dimension from the source system rather than the conformed dimension which strictly speaking means it will no longer be conformed.

Create 2 dimensions - Resource and Resource Type. Incorporate the elements of the Resource Type Dimension into the Resource Dimension. In other words, The Resource Type dimension looks and acts like a mini dimension of Resource. Except instead of it being a view from the resource Dimension, it's a table with a few additional rows that aren't in the Resource Dimension.

I have a report that displays cost savings from the processing of claims. Well there are many different types of cost savings from the claim. Some of the savings occurs when the claim is denied and the savings is "explained' in the processing policy codes that are on the claim. This would be the lowest level of 1 type of cost savings.

But there are other types of cost savings. There is a cost savings from when the claimant uses an In Network Provider instead of an out of network provider. This is a calculated savings and can differ for the different networks and products.

So I want a cost savings dimension that includes everything. I want to tie the Processing Policy Codes to the Cost Savings dimension because, the Processing related cost savings is simply a roll up. But if I combine the Processing Policy Codes with the Cost Savings Dimension, then I end up with Cost Savings categories with no corresponding processing Policy.

My solution was to create 2 dimensions = one for processing policy and the other for the cost savings. I denormalized the Cost Savings columns into the Processing Policy dimension. I'm not sure if it's kosher or not - I don't like having the same attribute in more than one dimension table - but it prevents snow flaking and reduced the number of dimension Keys on the Claim Fact table by 1.

I would have two dimensions similar to Jeff's model, a ResourceDim with all the attributes denormalised in it and a mini-dimension with Resource Type, Department and Grade in it, also denormalised for any repeating groups. Like Jeff said, the mini dimension is not built from the base dimension ResourceDim, but from the Plan fact source data. So you have one dimension key ResourceKey in Actual fact and one ResourceMiniKey in the Plan fact table.

When you consolidate the two fact tables into a single view, you need to aggregate the actual fact on three higher level attributes above the Resource so that you set the actual fact to the same grain as the plan fact. You would use plan fact table as the driving table and left join the aggregated actual fact table and use COALESCE function to cater for those plans without actuals (Resource).

One of the key points that differentiate dimensional modeling from relational modeling is to reasonably denormalise same set of attributes in different dimension tables to avoid snowflake and minimise dimension entries in the fact table and always do the aggregation on dimension attributes or NK, instead of SK, for consolidation purpose.