Multiple fields in fact table referring to one dimension

[#1]

Dear Qlik-Community,

I have the following situation and don't know how to handle this issue. I hope you know how to solve solve this problem.

I have a fact table with two different fields, which represent employees, and want to connect them to one dimension.

Here an example:

Fact-Table:

Article, ResponsiblePersonPurchasing, ResponsiblePersonSales

1, 20, 30,

2, 10, 30

3, 10, 20

etc.

DiM_Employees:

10, Mr. Maier

20, Mrs. Mueller

30, Mr. Max

Now i want to link the fields ResponsiblePersonPurchasing and ResponsiblePesonSales with DIM_Employee without using two dimensional tables.

Any ideas?

[johanlindell]

This is not an easy one but you can create a "link table". Is is not a elegant solution but it works.

Johan

[Gysbert_Wassenaar]

The alternative is splitting the records of your fact table into something like this:

Article, EmployeeID, Role

1, 20, Purchaser

1, 30, Seller

2, 10, Purchaser

3, 30, Seller

...etc

That will double the size of your fact table. I don't recommend it. Use two dimension tables instead.

[#4]

Hi,

in fact there is difficult to recomend with no knowledgo of entire data model, analytical needs etc.

One of possible ways is:

table1:

Article_id, Article_name, Article_Group

1, A, G1

2,B,G1

3,C,G2

table2:

Article_id,Person_id,Person_type

1,20,Purchasing

1, 30,Sales

2, 10,Purchasing

2,30,Sales

3, 10,Purchasing

3,20,Sales

DiM_Employees:

Person_id,Name

10, Mr. Maier

20, Mrs. Mueller

30, Mr. Max

regards

Darek

[rbecher]

Hi,

following Ralph Kimball's approach of role-playing dimensions you would need to have two employee dimension tables in your case...

- Ralf

[simondachstr]

Ever thought of using ApplyMaps? This way you can translate the IDs into Employee names fairly easy without adding any additional table to the data model.

[rbecher]

.. but then you will still have two employee fields, means two dimensions.

[#8]

In "one field dimension" it will be ofcourse better to put names into fact table. But life is not that simple. I am sure, that there may be more complicated employee dimension, with some hierarchy, department info etc

regards

D

[#9]

I've used ApplyMaps, with that function you are not able to select one Employee and show all articles, which belongs to him, either it belongs to purchasing or to sales.

[#10]

So it is not possible to link two fields in the fact table to one dimension?

I think, it is only possible with two identic dimension tables.