Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
amirkachlon
Contributor III
Contributor III

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

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

2 Replies
girirajsinh
Creator III
Creator III

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
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.