topic Re: Multiple fields in fact table referring to one dimension in QlikView

Multiple fields in fact table referring to one dimension

Tue, 03 Jun 2014 08:12:07 GMT

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?

Re: Multiple fields in fact table referring to one dimension

johanlindell — Tue, 03 Jun 2014 08:20:31 GMT

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

Johan

Re: Multiple fields in fact table referring to one dimension

Gysbert_Wassenaar — Tue, 03 Jun 2014 08:23:48 GMT

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.

Re: Multiple fields in fact table referring to one dimension

Tue, 03 Jun 2014 08:31:47 GMT

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

Re: Multiple fields in fact table referring to one dimension

rbecher — Tue, 03 Jun 2014 08:42:35 GMT

Hi,

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

- Ralf

Re: Multiple fields in fact table referring to one dimension

simondachstr — Tue, 03 Jun 2014 09:00:48 GMT

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.

Re: Multiple fields in fact table referring to one dimension

rbecher — Tue, 03 Jun 2014 09:04:29 GMT

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

Re: Multiple fields in fact table referring to one dimension

Tue, 03 Jun 2014 09:07:49 GMT

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

Re: Multiple fields in fact table referring to one dimension

Tue, 03 Jun 2014 11:29:36 GMT

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.

Re: Multiple fields in fact table referring to one dimension

Tue, 03 Jun 2014 11:31:42 GMT

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.

Re: Multiple fields in fact table referring to one dimension

Tue, 03 Jun 2014 11:35:52 GMT

Thanks for your reply, this might be a solution, but as you said it is not a elegant solution.

With that approach you need to add different roles in your data model - it is not possible to list different responsible employees in one record (e.g. article, project, etc.).

It seems, that that issue is quite complicated.