Using a dimension table across multiple fact tables

[user467341]

Hi Qlik community,

Been researching this for many hours and have read almost all the top searched results in search engine, including the community posts and links.  I still do not understand the correct way to tackle this issue in Qlik - a single dimension table used across multiple fact tables.

From what I researched, there are two ways to do this:

1. Concatenate all the fact tables into one
2. Create a link table
3. Duplicate dimensions

For 1, I didn't see any posts on how to tackle multiple fact tables when it is distinctly different (do not have any common fields to concatenate). How do one even approach such data modelling?

For 2, I am not too sure if this is a viable solution but I think it make the most sense logically for any end-user once they see the diagram. Again, I do not really see how to implement this.

For 3, duplicate dimensions is just a no go if I have around 15+ fact tables sharing dimensions. The amount of extra dimensions will balloon.

What is the most effective and elegant way to handle a single dimension usage on multiple fact tables? Does Qlik ideal data modelling is meant to be a single, super wide and long, fact table with dimensions connecting to it to form the star?

Appreciate all your advice! Thanks.

[user467341]

Thanks Rubenmarin for your patience. My fact tables are huge (each with around 20 to 40 fields). Concatenating them into one (say like 5 of such tables) will be a massive wide table. 

In your earlier post you mentioned about the wide table too. Do you mean to say concatenate only relevant fields that is needed in the fact table?

Appreciate if other Qlik members will provide their inputs too. 🙂

[rubenmarin]

Hi, yes, you don't need to concatenate all fields, as said in previous posts: "you can also concatenate all fields, but it will end in a very width table, so just concatenate fields that are in at least 2 facts."

The other fields, that are related only to one fact would be in other tables, related to the main fact table, this is just to solve the need for dimensions that are used for different facts.

Btw, not much people will go to a threads with many answers, if you want a different opinion I think it would be better to create another thread.

[deepanshuSh]

As Ruben Already suggested, I would most probably go through that process and in addition to that A table consisting of 30-40 fields have most of the fields as directly not needed in the final KPI's building, I would remove those redundant fields and thereby optimizing the data storage and space occupied by the application. Also, it would reduce the clutter in final model building. 

A better way would be to get inputs from data management or data science team, if you have that available to you, to create a data dictionary for the fields and understand the links between the different tables in a better way. 

[user467341]

Thanks.

Sales and production will have product, some dates, quantities... those will be in the main fact table where you have the issue with the 'dimensions across multiple fact tables', the other fields can go to related tables.

---

Wish to clarify when you mentioned related tables, do this related tables means dimension tables?

For concatenate, the end result should normally look like this?

Also I understand the general community go-to approach is concatenation. However, I am really interested if link table is possible too? Ignoring performance and simplicity.

[vinieme12]

Build a central Bridge Table 

https://community.qlik.com/t5/Design/Canonical-Date/ba-p/1463578

https://community.qlik.com/t5/Design/Concatenate-vs-Link-Table/ba-p/1467569

https://community.qlik.com/t5/QlikView-Documents/Generic-keys/ta-p/1496560

[user467341]

Hi Vinieme. For this method, does that mean there will only be one link master table in the entire data model? Will this work if both fact tables has multiple date fields respectively?

[vinieme12]

 does that mean there will only be one link master table in the entire data model? 

YES, one central bridge table that associates to all facts & dimensions

Will this work if both fact tables has multiple date fields respectively?

The bridge table becomes as union of Link Table + Canonical Date table

[user467341]

Hi Vinieme.

I have read the "Concatenate VS Link table" article and downloaded the Qlik technical paper. The technical paper show a link table that is able to link using common fields because it is related.

How should I approach the link table if it is a different fact table eg fact table WITHOUT any common dimensions.

[rubenmarin]

Hi, "fact table WITHOUT any common dimensions" doesn't needs to solve a "dimension table across multiple fact tables". Fact tables without common dimensions can go anywhere on the data model.