In real-world scenarios, it is not always possible to have only one fact table in the data model; there will be cases when you need to include multiple fact tables. The immediate solution which we can think of is that we concatenate both the fact tables, but this approach works well only when the granularity of the facts is similar. When the granularity of the tables is different, it is little complicated to use the concatenation of tables.

Let us understand the problem using the following example, where two fact tables need to be linked with each other:

You can see that every record of the Sales table contains the sales details for each product sold, such as sales amount, date, and salesman who sold it. On the other hand, the budget table contains the budget amount for each product category, salesman, and month. So you can see that these two tables have different levels of information. If you concatenate these tables, you will not be able to get the SalesAmt and BugdetAmt comparison, because there are no product and date fields in the budget table, and similarly, no product category and month fields in the Sales table.

So how do we link them together? Before we look at the example and the possible ways to solve this scenario, it is important that we understand the concept of granularity of data.

For example, for the fact table shown earlier, namely the sales table, the granularity is at the salesman, product, and salesdate level, because these fields define what level of data has been stored in the fact table. Similarly, for a dimension table like an employee table, the least level of detail can be an employee name, and for a product table, it can be product name with product variant.

Granularity plays an important role in dimensional modeling. If the granularity is defined properly for the fact tables, it becomes easier to get the proper analysis of the data. For a Qlik Sense consultant, it is very important to find the granularity of the data before starting actual data modeling. This helps to find the right associations amongst the data and provide a valuable insight to business.

Now that you have a fair idea about data granularity, let us have a look at the example of multiple fact table with different granularities. There are multiple ways you can link them together, but we will see the basic two ways of handling these kind of scenarios.