roll up of values in a dimension

building semantic layer
1

Hi

I am facing a problem with the roll up of values in a dimension.

I have a dimension with 3 values at universe level.
Letters
A
B
C

Now I want to add one more value to this dimension, which is D, but it should be sum of A+B. So list of values for new dimension should be:
Letters
A
B
C
D

But when I am using case statement as follows:
case when Letters In (‘A’,’B’) then ‘D’ else Letters end

Then the Values for A & B getting disappeared.
I am getting result set like this:
Letters
C
D

How can I get all the 4 letters to be displayed (i.e. results in 2nd case above) ?

Any help is appreciated.

Regards
Vikram

vikramvasisht (BOB member since 2008-02-18)

2

What database are you using? Have you tried running the query generated in a different query tool? What happens when you run below query:

Select Letters, Case Statement, sum (kpi)
from Table
group by Letters, case Statement

BOB_DW (BOB member since 2004-08-26)

3

Thanks for the reply.

I am using SQL server 2005.

I want to do it at Universe level within the dimension. So would like to avoid any “group by” conditions.

vikramvasisht (BOB member since 2008-02-18)

4

You need a UNION query…

SELECT Table1.Col1
FROM Table1
WHERE Table1.Col1 in ('A','B','C')
UNION
SELECT (Table4.A + Table4.B) AS D
FROM
     (SELECT Table2.Col1
       FROM Table2
       WHERE Table2.Col1='A'
       UNION
       SELECT Table3.Col1
       FROM Table3
       WHERE Table3.Col1='B')Table4

You should get:

A
B
C
D

BO_Chief :us: (BOB member since 2004-06-06)