Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
hic
Former Employee
Former Employee

 

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
Not applicable

Henric, can you please provide an example showing budget vs actual over time using this model?

0 Likes
25,424 Views
hic
Former Employee
Former Employee

I have now uploaded an example on http://community.qlik.com/docs/DOC-3451

HIC

25,424 Views
alexis
Partner - Specialist
Partner - Specialist

The link provided is crashing - I was so looking forward to seeing yoru example - can you resubmit valid URL?

Thanx

Alexis

0 Likes
25,424 Views
alexis
Partner - Specialist
Partner - Specialist

I better clarify - the above link takes you to a page that has 2 URLS one for the pdf and one for the zip file - it is those links that crash not the above

BR
alexis

0 Likes
25,424 Views
alexis
Partner - Specialist
Partner - Specialist

Thanx Henric - it seems everything is there in the ZIP file except for the QVW

0 Likes
25,424 Views
alexis
Partner - Specialist
Partner - Specialist

Applying the good old RTFM principle I have just noticed that you included text files containing the code!

Apologies and thanx in that order!

Alexis

0 Likes
25,424 Views
hic
Former Employee
Former Employee

I know what it's like - RTFM is something I do when everything else fails...

Let me know if there are any questions.

HIC

0 Likes
21,082 Views
Not applicable

Great article, I was just struggling with how to solve this problem. I just have one question. One of my requirements is to follow the factor of <actual daily outcome> / <monthly budget>, on a daily basis. The actual outcome comes each day, but the budget only on month level.

I tried to solve it your way, and it seems to work well, except for this. Should it be possible with your model? Perhaps I am missing something...? My problem seems to be that when a "Date" is selected (or if you have a Straight Table with Date as Dimension), all "Monthly" data is excluded/unavailable. Is there a smart way around this?

(The best practice solution to this seems to be to break the Monthly data out in a Linked Table fashion. But I thought I was going to give this a try.)

Mathias

21,082 Views
hic
Former Employee
Former Employee

No, this is a deliberate choice. If you want to show the Monthly budget numbers when you have a date selected you should use Inclusive symbols ('<ALL>') instead of Exclusive symbols ('null()'). Then you will get the budget numbers just like you want them.

See more about Inclusive and Exclusive symbols in http://community.qlik.com/docs/DOC-3451.

You can also look at http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/02/complex-authorization where inclusive keys are used.

HIC

0 Likes
21,082 Views
Not applicable

Ah! I went back to reading your documentation in detail, and now I think I finally got the hang of it. Thanks for sharing this, much appreciated!

Mathias

0 Likes
21,082 Views