Which is the Best model to support measures with custom roll up formulaes in a ROLAP environment?
2 posters
Page 1 of 1
Which is the Best model to support measures with custom roll up formulaes in a ROLAP environment?
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?
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
Re: Which is the Best model to support measures with custom roll up formulaes in a ROLAP environment?
Create an aggregate fact table with the appropriate calculations.
Similar topics
» Dimensional model for support ticketing process
» Integrating Hadoop environment with a DW environment
» Roll up dimension question
» Customer addresses in a high volume retail environment
» Question about ROLAP and MOLAP
» Integrating Hadoop environment with a DW environment
» Roll up dimension question
» Customer addresses in a high volume retail environment
» Question about ROLAP and MOLAP
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|