Modeling Help: Semi-Additive Measures?

Hi all, I have a modeling problem that I need some help with. The essence of the problem is that I have measures in a fact table that are recorded independently at each level of the hierarchy in one dimension. These measures are non-additive within the hierarchy of that dimension, but are additive across another dimension.

Other measures of the fact table are recorded only at the leaf nodes of the dimension hierarchy and therefore rollup correctly.

My intial thought was to move the semi-additive measures to attributes on the dimension, but because they are recorded by day, this didn't seem to make sense.

I need to be able to produce reports that summarize each measure by Project and each Wbs Level by Day, Week, Month, and Year. The PercentComplete and PlannedQuantity measures are non-additive up the Wbs Hierarchy and as such are recorded as fact rows for each level independently. These measures are also recorded independently for each Project by Day. PercentComplete is included as a measure and calculated during the ETL (rather than being calculated at runtime) because there is significant logic that applies when calculating it at each level of the Wbs.

Below is the structure of what I currently have. Any help would be greatly appreciated. I am open to completely redesigning the model as necessary.


Dimension - Project
ProjectID
Type
Location
etc...

Dimension - WBS (Work Breakdown Structure - represents a hierarchical tree structure of work items that roll up to a single root node)
WbsID
Description
Wbs Level 1
Wbs Level 2
Wbs Level 3
Wbs Level 4
etc...

Fact - WbsPlannedValues (Contains one row per day, per project, per Wbs item)
DateID
ProjectID
WbsID
PlannedCost - Only recorded at leaf Wbs Items, zero otherwise
PlannedManHours - Only recorded at leaf Wbs Items, zero otherwise
PlannedQuantity - Recorded independently for every level of WBS item
PercentComplete - Recorded independently for every level of WBS item


Thanks!

Hi dbrowning

I think you should probably split your fact table to separate the semi-additive measures - its usually confusing and a bit dangerous to mix granularities in one fact table.

So in the new "non-additive" fact table, I think if you include the relevant Level as a new attribute, and you add the Level to your dimension, you can then join the new "non-additive" fact table using WbsID and Level and get the result you are after.

What reporting tool are you using?

Good luck!
Mike