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

View previous topic View next topic Go down

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

Post  ranish.ravindran81 on 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

View user profile

Back to top Go down

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

Post  ngalemmo on Fri Jan 20, 2012 8:46 pm

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

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

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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