1. Registered User
Join Date
Dec 2005
Posts
4

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:
Exp:

0-99
100-199
200-399
...
....
..
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.
For example:
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.
Thank You.

2. Registered User
Join Date
Feb 2004
Location
Dublin, Ireland
Posts
212
you need your Range lookup table

Range_lkp
---------
Range_ID (PK)
Range_Desc

containing values

1 0-99
2 100-199
3 200-399

in your Fact table data would look like:

... Measure ...... RangeID (FK)
-----------------------------
... 104.56 ...... 1
... 95.47 ...... 3
... 345.77 ...... 3

the point is you have to populate your RangeID's to your Fact table during ETL

1) populate table with RangeID = NULL
2) update table set RangeID = ... use procedure (I don't think you'll handle it by one update statement)

3. Registered User
Join Date
Dec 2005
Posts
4

## Dimenstion on Measuer

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:

313.68
Sum(
104.56
104.56
104. 56
)

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).

4. Registered User
Join Date
Feb 2004
Location
Dublin, Ireland
Posts
212
OK I know what you mean...

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?

5. Registered User
Join Date
Dec 2005
Posts
4

## Dimension on measuer

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•