Help In Data Modelling Semi Additive Measures

[tyadukishore]

I Need some Help In Datamodelling few of the Non Additive Measures.

Requirement is that I have some reports which compares the Golas with the Actual Booking amount at various levels of geography hirearchy. One example is Terriotry-->Region-->Area-->BU.Quota Amount changes quarter over quarter and the Region level data is different from the sum of the Territory Quota levels.

Could you please let me know how best this can be modelled.

Sample Data

Territory Period Quota
T1-->Q1-->$30M
T2-->Q1--> $40M

Region Period Quota

R1--->Q1--->$70M

Required:

Region Quota Booking Amount Difference

 

[Hans63]

Is your sample really in line with your described case? you say that the Region level data is different from the sum of the territory quota levels.
Your sample data shows that the region data is the sum of the region data.

Can you give a more detailed description of the non-additive measures.

One way I have solved the problem of non-additive (and semi-additive) facts before is the use of pre-calculated aggregates.

 

[tyadukishore]

Sorry. My bad.

My Meaning of Non Additive Measures is

a) As described above Region Level Quota may or may not be equal to sum of lowerlevel quotas (Territory).
b) Like Wise Quota will change quarter over quarter.
c) Analysis of Quota Comparison with Actuals need to be done at different levels in different hirerachies.
d) My Transactional data is available at the day level

It should have been some thing other than 70M.

Please help me on the design

 

[Hans63]

So, you have a really semi-additive fact. The quota are additive over some dimensions, but not over other (like the hierarchy and (possibly) time).

What I have done is to handle this, is to create an aggregate fact-table, with the correct aggregates (in this case Quota).

So you have a fact table:
Territory - Quarter - Quota budget - other dimensions
T1 Q1 100
T2 Q1 140
T3 Q1 130
T1 Q2 120
T2 Q2 100
T3 Q2 155
...

And you build a aggregate fact table
Region - Quarter - Quota budget - other dimensions
R1 Q1 175
R2 Q1 200
R1 Q2 180
R2 Q2 190
...

You then have to build an additional piece of ETL software to correctly fill the aggregate.

Hope this helps a bit.

Semi-additive facts are NOT nice.