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

Which is the Best model to support measures with custom roll up formulaes in a ROLAP environment?

2 posters

Go down

Which is the Best model to support measures with custom roll up formulaes in a ROLAP environment? Empty Which is the Best model to support measures with custom roll up formulaes in a ROLAP environment?

Post  ranish.ravindran81 Thu Jan 19, 2012 11:52 pm

Hi Friends
Please find the details of the requirement
The issue had come out of the business requirement,for which Iam not able to come up with an effective datamodel using which the OLAP model can effectively answer to the business question.The requirement is that a measure should support having different formulae at different levels of 2 dimensions(namely region and time).I think the right term for this is custom roll ups at different levels.

The different formulaes for the measure are
1.The base formulae for that measure is sum(Numerator)/Sum(Denominator),this formulae is valid only at the month level and only when a user selects any level within the region dimension.
2.For all the higher levels of time(Year,Quarter) when used with region dimension ,should give an average of the month.
3. For all other cases which doesnt uses region dimension the formulae should return a weighted average .

I had written an MDX function which does this switching in the OLAP layer based on the dimension level the user selects,but the issue here is that the MDX formulae is complex and we have a ROLAP environment,and so the performance goes down.


The second approach I could think of is having the roll up values stored in the same Fact table rather than computing it on the fly in the OLAP layer,but this makes the table deviate from standard starschema approch and the table becomes complex and not very much analytical.


I could only think of this 2 approaches .How can this be handled in the best possible way?

ranish.ravindran81

Posts : 1
Join date : 2012-01-19

Back to top Go down

Which is the Best model to support measures with custom roll up formulaes in a ROLAP environment? Empty Re: Which is the Best model to support measures with custom roll up formulaes in a ROLAP environment?

Post  ngalemmo Fri Jan 20, 2012 8:46 pm

Create an aggregate fact table with the appropriate calculations.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Back to top

- Similar topics

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