Fact with more than one surrogate key to the same Dimension table

[amirkachlon]

Hi all,

I have a general question:

assume that I have one Fact table with 3 fields of item hierarchy, as follow

Grand Father Item | Father Item | Item

123                         | 12               | 1

now I want to connect each one of them to Dim Item.

I see two options:

1. duplicate dim item 3 times

2. transpose the table so each row of data will have 3 repetitions, for each item in hierarchy, of course I will add another column to handle the hierarchy

both of the solutions seems wasteful

anyone has a better suggestion ?

Thanks

Amir

[girirajsinh]

Hi Amir

Why do you think the first option is wasteful?

You may create one intermediate table loading unique hierarchies( if possible) .

Fact would connect to intermediate table on Item Key.

Also you need to create three dimension table like Grand_Father_Item_table, father_item_table and Item_table.

These three would connect to different field on Intermediate Table.

But Of course, the data modelling would depend on what you are trying to achieve out of it.

let me know if  that helps you in anyway.

Br

Giri

[vinieme12]

Hi,

Can you upload a excel file with sample data in each tab to replicate the datasets that you are working with

I would never choose to multiply the number of rows for each dim