Fact with multiple grain statements

Last Post 20 Nov 2009 05:04 AM by kalpna. 0 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
New Member
New Member

20 Nov 2009 05:04 AM
HI Everyone,

Can someone suggest a better solution for the following scenario,

The simplified version is...

I have Account, Scenario and Property dimensions.

Property dimension has the hierarchy of Site <- Building <- Floor ..

The fact contains value of an account for different scenarios and buildings..

But,..some of the accounts hold the value at Building level..some at floor level.

I can't take them as seperate dimensions as I need the drill down functionality.

BUt,...How do I store the Building level values in fact??

I have a building B1 with B-F1, B-F2

Accounts A1(building level account), A2(floor level account)..

so,..actual values are...
A1, B1, 100
A2, B-F1, 150
A2, B-F2, 20

In this case....If I make my grain at floor level,..I need to store the data....as
A1, B-F1, 100
A1, B-F2, 100
A2, B-F1, 150
A2, B-F2, 20

But, When I do...the aggregation.... for an account A1...it gives me 200...But the actual value is 100..

In the cube, I can manually overwrite the building level value as its child's value...and freeze it..
But,..not sure how it aggregates these values at across different dimensions!!

But, is this the only way to solve the problem..??

Please Suggest!!..

You are not authorized to post a reply.

Acceptable Use Policy