×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Help In Data Modelling Semi Additive Measures

Help In Data Modelling Semi Additive Measures

Help In Data Modelling Semi Additive Measures

(OP)
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
 

RE: Help In Data Modelling Semi Additive Measures

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.

RE: Help In Data Modelling Semi Additive Measures

(OP)
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

 

RE: Help In Data Modelling Semi Additive Measures

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close