Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Modeling Help: Semi-Additive Measures?

2 posters

Go down

Modeling Help: Semi-Additive Measures? Empty Modeling Help: Semi-Additive Measures?

Post  dbrowning Wed Oct 17, 2012 1:36 pm

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

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

Fact - WbsPlannedValues (Contains one row per day, per project, per Wbs item)
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



Posts : 1
Join date : 2012-10-17

Back to top Go down

Modeling Help: Semi-Additive Measures? Empty Re: Modeling Help: Semi-Additive Measures?

Post  Mike Honey Mon Oct 22, 2012 2:58 am

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 Honey
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

Back to top Go down

Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum