Linking all dimensions to a link table

[sudhakar_budde]

This Question is similar to the link below, but need some additional clarification and understanding please?

Re: Linking 2 Dimension Tables with Link Table

Hi Gurus,

I have similar situation related to this post:Re: Linking 2 Dimension Tables with Link Table , at a client that I recently started work. I found that user was asking a developer to  just  add tables one after the other int the data model without having specific reporting requirement. Once the data model was built they were started self-service reporting on Qlik Sense for KPIs and dashboards. The developer created a 'link table' as exactly as you mentioned in the post Re: Linking 2 Dimension Tables with Link Table.

Please see the attached app as per this post. The problem is, the same user started raising bugs that they see duplicate records with some null attributes as shown in my example! After investigating the data model, I thought this approach doesn't look right. I may prefer to create the data model as per the requirement of reports or at least identify the fact and appropriate association between the dimensions. If the link table is unavoidable, then create composite keys where appropriate for the implementation of link table solution.

Also, there are tables hanging to the link table without an established relationship to any other table in that model or to another key in the link table. E.g. I have included here a 'sales' table in the file attached!

The user don't want to apply no null value filter on the fields on the chart objects either!

Can you share your thoughts suggestions where to start on to solve this issue please ?

Thanks for your help in advance.

Sudhakar

[jaibau1993 · Accepted solution]

Hi Sudhakar:

Maybe I am wromg but I understand that a link table links fact tables with dimension tables. In your example, it links sales with master item and master customer tables. You should create your link table from your fact tables and then link with dimension tables. You see douplicated records because they are explicitly douplicated in your link table since you build it not only using the sales table but also using your dimension ones.

On the other hand, your sales table seems a bit weird: it has an ID and an amount but you usually need information about the product sold, the customer etc in order to properly link to your dimension (master) tables. As you can see in your LinkTable, both sales rows (ID 10 and 11) have no data except in ID column so they are linked to nothing.

Since it is a bit complicated to be explained (at least for me) I prefer to attach a QV file so you can see how I think a link table must work.

If you have any doubts just say. Bests,

Jaime.

[sudhakar_budde]

Thanks Jaime,

Well explained the rules around creating the link tables in the data model !

My app was a similar example of a data model that was built according to user instructions without specific reporting requirements. Unfortunately that can't be re-engineered as they have been using it for some time and due to another program going to replace it in few months, but a challenge in front of me to fix it somehow for time being!

Hope this post helps other community as well 🙂

Thanks once again.

- Sudhakar