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.

[rubenmarin · Accepted solution]

Hi, I would try to go with 1.

In example if you have plants, lines and production orders and line incidents. production orders and line incidents will go concatenated in the fact table.

Try to rename field to be named the same if possible. If in example you have [Start production date] and [Incident notification date], just rename both to [Start date]. If you have Producer and Repairman rename them as Employee or Person.

Other fields that might have use for only one entity (production or Incident) may go in auxiliary tables related to main fact table.

[rubenmarin]

Also sometimes you will also need link tables, like if one record might be counted by different dimensions, like [Start date] and [End date] or [Origin] and [Destination].

I would definitely try to avoid 3 as much as possible, doesn't matter if there are 50 dimensions or just a couple, having different names for the same dimensions usually only creates confusion and difficulties on analysis.

[user467341]

Hi Ruben, thanks for your input.

I am not sure about 1. As mentioned, how should I tackle a distinctly different fact tables where there is no relationship? The example you gave is once again related. What if I have a production and sales fact tables? Production is not related to sale since it has the manufacturing measures and sales is not related to production which it's the measures of items sold.

In this case, I don't see how I can concatenate.

Is option 2 possible?

[rubenmarin]

Hi, if your issue is 'dimensions across multiple fact tables' there should be something in common, at least those dimensions.

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.

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.

[rubenmarin]

Following the previous, if you add financial data, the date could go the main fact, you may also add account or other dimensions that are only related to the financial table. Other dimensions like product will be empty in this fact type.

[user467341]

Hi Ruben Marin. Some fact tables for example have employee fields. An employee can be a common dimension that link back to these fact tables. However, these fact tables can be distinctly different and not related other than sharing the same employee ID to refer to the employee information in the dimension table.

This is similar to date, which does not mean the facts have some kind of common attributes.

I can't really visualize your inputs. Do you have an example diagram to show this?

Thanks!

[rubenmarin]

Hi, in that case I don't see the employee as a fact, fact could be the hiring date, or extra hours worked, absenteeism, that would go in the fact table, with date dimension shared with other facts... but the employee information would be an additional table, related to the fact table by employee Id.

I don't have any diagram I can share. You can do a copy of your app and try to concat facts in the same table to see the result. For each fact type add a fixed field so you can use in set analysis, like  'Sales' as FactType.

As I said with the financial example there is no need to have the same fields in facts, if there is no relation they will just load nulls in the fields of other facts.

[user467341]

My previous post, I mentioned employee is a dimension table. Not a fact table...

[rubenmarin]

That's why I said it "would be an additional table, related to the fact table by employee Id"