Friday, March 30, 2012

OLAP 2005 Calculated Measure using Field/Dimension

I am building an OLAP Cube off someone else's data model, so i am a bit stuck trying to decision off model decisions already made and being used.

They have a field for Satisfaction which has the value Yes, No, and Blank when no survey result was received.

I need to make a satisfaction percent based on (count of Yes) / (count of Yes + count of No). So i do not count blank when no result was received.

I need the caculated measure to work against all of the dimensions, so i do not want to build such a specific MDX code that it only works with Time, for example.

Help will be extremely Appreciated

*A

If there is a Satisfaction dimension/attribute, with the members above: Yes, No, and Blank, and a cube "count" measure like SurveyCount, then does something like this work:

([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[Yes])/

(([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[Yes])

+ ([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[No]))

|||WORKED GREAT AND WAS REALLY EASY TO UNDERSTAND AND CHANGE FOR OTHER USES!!!!!!!sql

No comments:

Post a Comment