Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005

    Unanswered: Dimenstion on measure

    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.
    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. #2
    Join Date
    Feb 2004
    Dublin, Ireland
    you need your Range lookup table

    Range_ID (PK)

    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. #3
    Join Date
    Dec 2005

    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:

    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. #4
    Join Date
    Feb 2004
    Dublin, Ireland
    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. #5
    Join Date
    Dec 2005

    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