Relating two dimension in SSAS cube

Question

I have two dimension, say "Dimension1" and "Dimension2" These two dimension should be related, like Dimension1 can have one or more records in Dimension2.

To relate, I have an intermediate table, say "Dimension12"

Sample values are as below.

Dimension1

Column 1
A
B
C

Dimension2

Column2
X
Y
Z

Dimension12

Column1 Column2
A       X
A       Y
B       X
C       Y
C       Z

In DSV I related these 2 tables using “Dimension12” table. Created two new dimension, Dimension1 and Dimension2. Deployed and processed cube.

Now when I query, I used Column1 from Dimension1 and Column2 from Dimension2, this is not giving me required result, I want to see X and Y under A , instead it gives me all like X, Y, Z under A and X, Y, Z under B and X, Y, Z under C. Relational between dimension is not happening. I do not want them to relate through facts.

Can you please help what I am missing here. Or is it possible to do such.

I wonder why you haven't build a hierarchy with 2 levels under a single dimension. — ebayindir

sandeep rawat sandeep rawat · Accepted Answer · 2016-07-13T05:57:13

Issue is with Dimension12 .

It should be generated using full outer join if (there is any join key .)

Else it should be created as cross join of Dimension1 and Dimension 2 in case there is no relation .

you Dimension12 should have below data

Column1  Column2
A        x
B        x
C        x
A        y
B        y
C        y

Edit after comment.

In case we donot have relation in Dimension1 and Dimension2

step 1 > Create a table Dimension12

   Create  table Dimension12 
( id int identity (1,1),-- as i would required a surrogate key for referencing in fact.

Column1 varchar(50),

Column2 varchar(50))


    insert into Dimension12 (Column1,Column2)
     select d1.Column1,d2.Column2 from Dimension1 d1,Dimension2 d2

step 2> One time populate data

insert into Dimension12 (Column1,Column2)
select d1.Column1,d2.Column2 from Dimension1 d1,Dimension2 d2

step 3 > make a Updateinsert ETL to populate Dimension12 tbale.

step 4 > use Dimension12 (table or view on top of this table )in DSV.

Now you are good to go..

Relating two dimension in SSAS cube

1 Answers