There is count measure in the cube. I need to create a dimension that should list the range of values based on that count values:
and so on.
User wants to pick the any range level in the dimension and cube should show corresponding cell values of count measure which would fall into range of the dimension level value.
if user picks up level 100-199 then cube would filter cell values of measure that has a value between 100 and 199 within 12 month range. So, it would be year to date value of the measure. My problem is how go about creating Dimension that based on measure in the cube and dimension should have level with range of measure values.
Any hint would be appreciated greatly.
Well, it would be best solution. But, in my case won’t work.
Measure in fact table will be aggregated over 12 month period in the Cube.
So, in your example Range Id = 1 of 104.56
Might be in the cube something like that:
So, 313.68 is no longer Range id 1
Point here is over period of time. Not fact value of that measure and that most of the cases will be aggregations of those facts (104.56).
Maybe you could create another few snapshot tables. Weekly snapshot, Monthly snapshot, Quarter snapshot, Yearly snapshot. Set up Range ID's in those tables and use them for reporting. Another option could be solve this somehow on reports level. It's hard to say how, it depends on your business intelligence tool. BTW what tool do you use? Or how you report your data?
We use MS Analysis.
Doing something like that in report is easy. But, users want it on the cube. I believe it should be done dynamically with MDX. I thought maybe somebody else already done it then I don’t have to invent the wheel.
Thanks for response anyway.