Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
Henric_Cronström

 

A common situation when modeling the data for a Qlik application is that you have several fact tables and the fact tables have mixed granularity. An example is budget vs. actual numbers, where the budget typically is made on a higher level than the actual numbers, e.g. the budget has regions instead of specific customers and months or quarters instead of specific dates.

It could also be that you want to have different granularity in a mixture, e.g. you want full details for the numbers for the current year, but – due to the amounts of data – you want to compare these to aggregated numbers from previous years.

In a Qlik data model, it is possible and not very difficult to use a fact table with mixed granularity. Say for instance that you have a detailed fact table with the numbers for current year:

Original fact tables.png

In addition, you have an aggregated fact table for previous years: Instead of CustomerID, ProductID and OrderDate, you have Country, CategoryID and OrderMonth as foreign keys.

The solution is to concatenate these two tables into one common fact table and use generic keys for the three dimensions.

Consolidated fact table.png

The generic keys contain information about both the higher and lower levels of the dimensional hierarchy and can be used for either the higher level only or for both levels. This way, the detailed records of the fact table link to customer, product, and date, while the records with aggregated numbers link to country, but not to customer; to product category but not to individual products; and to order month but not to individual dates.

It can sometimes be tricky to create the generic keys because the detailed fact table lacks direct information about the higher levels in the dimensional hierarchies, e.g. country and product category. But this can be solved using the function Applymap. For the detailed part of the fact table, the generic keys can be defined as:

     Applymap('MapCustomerToCountry',CustomerID) & '|' & CustomerID as %CustomerID,
     Applymap('MapProductToCategory',ProductID) & '|' & ProductID as %ProductID,
     'Date:' & Num(OrderDate)) as %OrderDate

And in the aggregated part of the fact table, the corresponding definitions could be:

     Country & '|' & Null() as %CustomerID,
     CategoryID & '|' & Null() as %ProductID,
     'Month:' & Num(MonthStart(OrderMonth)) as %OrderDate

The generic keys must be mapped to the real keys using a dimensional link table, but once this is done the application will work like a normal QlikView app.

Dimensional link table.png

This method can be used in a number of cases where you want to define keys that link to several values simultaneously, the most common one being comparison of actual numbers with budget.

Read more about generic keys in the Technical brief on Generic Keys.

HIC

33 Comments
patricesalem
Creator II
Creator II

Hello Henric

A community member has sent me a link to your white page concerning generric keys.

I have tried to understand how to apply it to the target follow-up report I've been trying to put together for 3 days now (budget per salesman/country/ product range vs sales per customer/product/country...).

I'm pretty sure your mapping solution could solve my issue but I'm stuck...

maybe would you accept to give me some tips Left outer join when loading tables / Qlik desktop - script issue

thanks in advance

pat

0 Likes
533 Views
ansarikashif2017

Hi @Henric_Cronström 

This will always be the case that we will have multiple fact tables with different granularity in almost all of medium to high complexity apps. 

Does that mean we should always think of combining by Concatenation or Link table?

If yes, what if we have like more than 6 fact tables in our Model. Wouldn't combining 6 fact tables into one be a performance penalty and look weird in terms of data model? Probably in that case Linked Table should make more sense?

Regards,

Kashif

 

395 Views
ctran2
Contributor
Contributor

Thanks a lot Henric.

My situation perfectly fits your demo.

I've been struggling for many days.

0 Likes
99 Views