## Puzzling OLAP problem - help !!!

### Puzzling OLAP problem - help !!!

I need some help  regarding MDX and Analysis Services.

My requirement is something like this ... I need to develop a
report similar to the following example

|    Perc   |   Score   |     Rating
________________________________________

A   |         90 %  |     23      |      Above Average
________________________________________

B   |     64 % |      7       |       Below Average
________________________________________

and so on.

The field Perc is a Calculated Member and can be assumed
something like this ( [A] , [Time].[2003].[Jan] )

I now have a table in my Database called as DIM_SCORE

Eg.

Key    |     Start    |      End     |    Score   |  Rating
___________________________________________________

1      |      91      |     100        |    25
|   Excellent
___________________________________________________

2      |      81      |     90          |   23        |
Above Average
___________________________________________________

. .. ...
. .. ...
. .. ...
___________________________________________________

3      |      61       |    70          |    7         |
Below Average
___________________________________________________

The puzzling part is that I need to figure out which range ( Start : End
) , the Perc value falls in and need to display Score and Desc
correspondingly.

i.e as the Perc value 90 falls in the range 81:90 , it must display a
score of 23 and Rating as ABove Average. The values must be fetched
dynamically from the table.

Of COurse, this can be hardcoded in the Calc Member, but is there anyway
to do it dynamically.

--
Posted via http://dbforums.com

### Puzzling OLAP problem - help !!!

Kokank, for a dynamic data-driven solution I suggest the following approach,

1) Build a separate cube to contain the threshholds.
2) If the threshholds are global, and constant over time, this cube will
have one Grade dimension, with the members {A, B, C, etc.}
3) The Grade dim will have a Rating member property that contains the Rating
descriptors
4) One measure will define the min score neccesary to achieve the grade
5) Combine the cubes together in a virtual cube (or else use LookupCube)

www.tomchester.net

Quote:

> I need some help  regarding MDX and Analysis Services.

>         My requirement is something like this ... I need to develop a
>         report similar to the following example

>                |    Perc   |   Score   |     Rating
>         ________________________________________

>           A   |         90 %  |     23      |      Above Average
>         ________________________________________

>           B   |     64 % |      7       |       Below Average
>         ________________________________________

>         and so on.

>         The field Perc is a Calculated Member and can be assumed
> something like this ( [A] , [Time].[2003].[Jan] )

>         I now have a table in my Database called as DIM_SCORE

> Eg.

>             Key    |     Start    |      End     |    Score   |  Rating
>         ___________________________________________________

>               1      |      91      |     100        |    25
>               |   Excellent
>             ___________________________________________________

>           2      |      81      |     90          |   23        |
>           Above Average
>         ___________________________________________________

>             . .. ...
>             . .. ...
>             . .. ...
>         ___________________________________________________

>          3      |      61       |    70          |    7         |
>          Below Average
>             ___________________________________________________

> The puzzling part is that I need to figure out which range ( Start : End
> ) , the Perc value falls in and need to display Score and Desc
> correspondingly.

> i.e as the Perc value 90 falls in the range 81:90 , it must display a
>   score of 23 and Rating as ABove Average. The values must be fetched
>   dynamically from the table.

> Of COurse, this can be hardcoded in the Calc Member, but is there anyway
> to do it dynamically.

> --
> Posted via http://dbforums.com

### Puzzling OLAP problem - help !!!

Just to add my 2c here. Lookupcube hurts performance. Virtual cubes are
generally preferable.

Regards,
Richard Tkachuk

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

> Kokank, for a dynamic data-driven solution I suggest the following
approach,

> 1) Build a separate cube to contain the threshholds.
> 2) If the threshholds are global, and constant over time, this cube will
> have one Grade dimension, with the members {A, B, C, etc.}
> 3) The Grade dim will have a Rating member property that contains the
Rating
> descriptors
> 4) One measure will define the min score neccesary to achieve the grade
> 5) Combine the cubes together in a virtual cube (or else use LookupCube)

>    www.tomchester.net

> > I need some help  regarding MDX and Analysis Services.

> >         My requirement is something like this ... I need to develop a
> >         report similar to the following example

> >                |    Perc   |   Score   |     Rating
> >         ________________________________________

> >           A   |         90 %  |     23      |      Above Average
> >         ________________________________________

> >           B   |     64 % |      7       |       Below Average
> >         ________________________________________

> >         and so on.

> >         The field Perc is a Calculated Member and can be assumed
> > something like this ( [A] , [Time].[2003].[Jan] )

> >         I now have a table in my Database called as DIM_SCORE

> > Eg.

> >             Key    |     Start    |      End     |    Score   |  Rating
> >         ___________________________________________________

> >               1      |      91      |     100        |    25
> >               |   Excellent
> >             ___________________________________________________

> >           2      |      81      |     90          |   23        |
> >           Above Average
> >         ___________________________________________________

> >             . .. ...
> >             . .. ...
> >             . .. ...
> >         ___________________________________________________

> >          3      |      61       |    70          |    7         |
> >          Below Average
> >             ___________________________________________________

> > The puzzling part is that I need to figure out which range ( Start : End
> > ) , the Perc value falls in and need to display Score and Desc
> > correspondingly.

> > i.e as the Perc value 90 falls in the range 81:90 , it must display a
> >   score of 23 and Rating as ABove Average. The values must be fetched
> >   dynamically from the table.

> > Of COurse, this can be hardcoded in the Calc Member, but is there anyway
> > to do it dynamically.

> > --
> > Posted via http://dbforums.com

I need some help  regarding MDX and Analysis Services.

My requirement is something like this ... I need to develop a
report similar to the following example

|    Perc   |   Score   |     Rating
___________________________________

A   |         90 %  |     23      |      Above Average
___________________________________

B   |     64 % |      7       |       Below Average
___________________________________

and so on.

The field Perc is a Calculated Member and can be assumed
something like this ( [A] , [Time].[2003].[Jan] )

I now have a table in my Database called as DIM_SCORE

Eg.

Key    |     Start    |      End     |    Score   |  Rating
_________________________________________________

1      |      91      |     100        |    25
|   Excellent
__________________________________________________

2      |      81      |     90          |   23        |
Above Average
___________________________________________________

. .. ...
. .. ...
. .. ...
___________________________________________________

3      |      61       |    70          |    7         |
Below Average
___________________________________________________

The puzzling part is that I need to figure out which range ( Start : End
) , the Perc value falls in and need to display Score and Desc
correspondingly.

i.e as the Perc value 90 falls in the range 81:90 , it must display a
score of 23 and Rating as ABove Average. The values must be fetched
dynamically from the table.

Of COurse, this can be hardcoded in the Calc Member, but is there anyway
to do it dynamically

--
Posted via http://dbforums.com