0

I'm new to designing cubes with SSAS.

In my simple cube, I have one fact table with 3 dimension tables, as below. The fact table (table1) contains a list of client IDs and other columns linking to the 3 dimensions. This all works fine.

table1
client_id | dimension_link_1 | dimension_link_2 | dimension_link_3
AAAAA     | xxx              |zzz               |bbb
BBBBB     | yyy              |aaa               |ccc

I have another table (table2) that contains three columns - Client ID, Classification Type and Classification Name. A client may have 1-n classifications recorded against them (i.e. ethnicity, religion, allergies etc) so the Client ID may appear on multiple rows in table2. e.g.

table2
client_id | classification_type | classification_name
AAAAA     | Ethnicity           | Japanese
AAAAA     | Allergy             | Hayfever
AAAAA     | Nationality         | Russian
BBBBB     | Ethnicity           | Spanish
BBBBB     | Allergy             | Aspirin
BBBBB     | Nationality         | Spanish
BBBBB     | Physical Support    | Yes

I want to add table2 into my cube so that I can aggregate the list of client IDs by the existing fact table (table1) by Classification Type and Classification Name in table2.

However, I'm not sure what the correct approach for doing this is? I tried joining table2 to the fact table (table1) as a dimension linked on Client ID but I think this only joined the two objects together using the first occurrence of the Client ID in table2.

Help! :)

Thanks,

Hologram

Josh Gallagher
  • 5,211
  • 2
  • 33
  • 60
Hologram
  • 1
  • 2

1 Answers1

0

Import table2 as both a fact table and a dimension. Then in the cube designer in the dimension usage tab, when specifying the relationship between the measure group formed from table1 and the dimension formed from table2, choose "Many to Many" as the relationship type and ensure the "intermediate measure group" is the one you formed from table2.

Josh Gallagher
  • 5,211
  • 2
  • 33
  • 60