Qlik Community

Qlik Design Blog

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

Employee
Employee

Fact Table with Mixed Granularity

A common situation when modeling the data for a QlikView 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 QlikView, 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

31 Comments
Not applicable

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

0 Likes
1,977 Views
Employee
Employee

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

HIC

1,977 Views
alexis
Contributor III

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

Thanx

Alexis

0 Likes
1,977 Views
alexis
Contributor III

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
1,977 Views
alexis
Contributor III

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

0 Likes
1,977 Views
alexis
Contributor III

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
1,977 Views
Employee
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
1,977 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

0 Likes
1,977 Views
Employee
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
1,977 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
1,977 Views
Not applicable

With this method, how do you accommodate slowly changing dimensions (SCDs)?  I cannot see how ApplyMap() could be used with that in mind.  Maybe I am missing something.

0 Likes
1,977 Views
Employee
Employee

I must admit that I have never done that. But I don't think it is impossible. You just need to create the keys using IntervalMatch instead. (and maybe nested ApplyMaps(), so it might be complicated scripting...)

HIC

0 Likes
1,977 Views
Not applicable

I have a post at:

http://community.qlik.com/thread/105050

which proposes never to use concatenate.  It is based on true dimensional modeling.  However, maybe my logic is flawed.  I need to test out with your scenario to see if the numbers add up correctly at the different levels.  They should  - famous last words

1,977 Views
Employee
Employee

In my experience, you should concatenate. To keep the original tables as they are, will create circular references. See more in my reply to your post: Re: I see no need to ever use concatenate, or to use link tables..

HIC

1,977 Views
christian77
Valued Contributor

Real data and Budget are same nature events. I always concatenate them.

If budget comes by the month, I can place a monthstart(Date) to see -month to date- comparisons.

Sometimes I divide monthly budget by the days on each month. Then I get a Budget by the date. Doing that you can convert months to weeks, or weeks to months.

I like to cancat all fact tables that I can. That is simplifying.

0 Likes
1,977 Views
Not applicable

For the budget fact,  do you code to the first day of the month? 

From my Android phone on T-Mobile. The first nationwide 4G network.

0 Likes
1,977 Views
Not applicable

Thx I am a bit confused about mixed granularity and generic keys vs hierarchies?

0 Likes
1,977 Views
Employee
Employee

The two concepts are similar, yes.

One difference is that with Generic keys you can define keys with a symbolic meaning, like "all values" or "no values". This is not possible with Hierachies - or rather, it is not as easy.

I use Hierarchy Load if there already is an Adjacent nodes table defined. Otherwise I (usually) use Generic keys.

HIC

1,977 Views
Not applicable

Hi Henric,

Replying to a very old post but, need to clear some doubts. Aren't we required to have mapping tables for mapping aggregated dimensions to detailed dimensions?

Thanks,

Madhura

0 Likes
1,977 Views
Employee
Employee

No, I don't see why you would need that.

HIC

0 Likes
1,977 Views
Not applicable

Hi Henric,

Apologies for my silly doubts but, I am confused about the fields 'MapCustomerToCountry' and 'MapProductToCategory'.

I didn't understand which table they belong to?

0 Likes
1,977 Views
Employee
Employee

Oh, these aren't fields. They are mapping tables. I just use Applymap() to get the fields Country and Category into the the fact table so I can create the correct keys. It doesn't say, but the blog post assumes that you have created these mapping tables already.

See more on Don't join - use Applymap instead

HIC

0 Likes
1,977 Views
Not applicable

Yes, right. That is where I was stuck. Anyway, thanks a lot for clearing this thing! Feeling familiar to generic keys now .

Looking forward for more such information.

Regards

0 Likes
1,977 Views
Chanty4u
Esteemed Contributor III

Hi hic

i need some guidelines[12:20]: How to Delink the datamodel ?

Means I have data model mixup of two diff datasources. so now I want to segregate each

ex: I hve customers and orders datamodel two fact and related dimension's    so  user need to display the datamodel of only customers


Note: right now am using snow flake schema user need to seperate both datamodel  it wil b star schema.


What are the steps to do for this?


Thanks.

Chanty

0 Likes
1,977 Views
yurgelmartina
New Contributor

Hi Henry,

What are the consequences of working with data like that?

What do you recomend?

Capturar.PNG

0 Likes
1,977 Views
datanibbler
Esteemed Contributor

Hi Henric,

this is quite old, I know, but I am facing this issue now - budget vs. actual figures. I want to show both in one chart using the same dimension, so they have to be in one table, else I'd get a loop-link.

I don't have a product_category into which I could subsume the ProductIDs, but I would get around that somehow. But I need another table then to map my generic keys to the actual keys - currently, my fact table (with actual values only) is linked 1:n to a LinkTable - so that would have to go inbetween I guess?

Then many of the unique keys would link to the same budget value?

Thanks a lot!

I might come up with more questions on this - but there is a thread for this and Marcus is already taking care of it - it's the first time I do this, so the effort will not be much less than that for doing it another way ...

Best regards,

DataNibbler

0 Likes
1,977 Views
Employee
Employee

I would avoid the link table and use one, single fact table with generic keys. See Generic keys.

HIC

0 Likes
1,977 Views
datanibbler
Esteemed Contributor

Hi Henric,

thanks a lot! I'm afraid that I can't quite grasp that, though: I have a generic key.

Say the unique key for my budget values is [part1] whereas the unique key for the actual values is [part1][part2] (so just about the first half of the key is the same, the other information is missing in the budget table)

The generic key now looks kind of like this:

>>>  [part1] | NULL() <<< (for budget values) and >>> [part1] | [part1][part2] <<< (for the actual values).

But this key is unique, it points to exactly one record, either an actual value OR a budget value.

I'm thinking - I could just use [part1] as the key to the central LinkTable and generate one more field in my fact table with [part1], which is part of  both keys anyway - but then the key will be all but unique - it will link to a lot of actual values and the budget value - but I want the key to link to exactly one actual value and the budget value.

How can I achieve this?

0 Likes
1,977 Views
jonascbi
New Contributor III

Hi Friedrich,

It sounds like you may wish to revisit the design of your central link table. Generally speaking, I would avoid retaining a bridging table (central link table) between your mixed fact table (your actuals appended/concatenated with your budget) and your dimension tables. Aim for a design that keeps one single fact table and the dimensions hanging directly off the fact if at all possible, or in a snow flake shape with tables of lesser grain (less records) as you move further away from the central fact. Unless you are trying to associate your actuals and/or budget with another type of fact (e.g. weather data, website hits, HR records...) then there is no obvious use case I can think of where a bridging table is likely to be required - and even if you do have multiple facts to handle, I'd still recommend trying to model your data with all factual records appended in one table first (concatenated fact) to avoid having to design a bridging table as it's easy to get the granularity wrong in your bridge table design and it will likely under-perform as a model when compared to a concatenated fact equivalent model.

If you REALLY need a bridging table...

If I read your description right, you are linking to your bridging table with a unique fact row ID. Unless you have a highly unusual requirement to meet, that's unlikely a sound data model. When designing a bridging table, have a think about what you're trying to associate to and what the minimum amount of information is required to define the relationship with everything that will eventually hang off it. If you for example aim to associate Actuals with Product, Country and MonthYear, then your association key in your Actuals table should ideally be a compound key of exactly that. E.g.

Product & '|' & Country & '|' & MonthYear as %ActualToDims

I.e. I'd expect a n-to-1 relationship between fact-to-bridge or in a tricky loosely associated multi-fact, mixed granularity model n-to-n. Never 1-to-n.

if you do pick your transaction row ID, you've picked a key that is very likely way more granular than what you need to describe the relationship with aforementioned dimensions and as a result, you'll have many more records in your bridging table than you need (more records in the bridge than in the fact itself) which will lead to poor performance in the front end.

My advice would be to start by attempting to model your data as a single central fact - hang the dimensions off that linking only to your Actuals first of all, ignoring the fact that Budget isn't going to link to anything yet and after you've gotten that far - re-visit Henric's info page on generic keys. It should hopefully make a lot more sense then.

If none of the above helps - I suggest building a small, simplified prototype with dummy data describing your scenario in a qvw/qvf - post it here and maybe we can better help find a solution to your problem.

Happy Qliking!

Best regards

Jonas

0 Likes
1,977 Views
datanibbler
Esteemed Contributor

Hi Jonas,

well, I have been thinking about the concept of our central LinkTable a few times and I do not like it very much myself - I never built LinkTables in my own apps before, I always had a single fact_table or, if I really needed it, a calendar as central Link_table - but well, it was there from the start - don't the Qlik_consultants themselves propose using a LinkTable? The relationship is n to 1 - 1 instance of a key in the fact table (where it is unique) links to n records in the LinkTable (where it is not).

I will not change that for now.

I guess a generic key is problematic here because, when appending budget data to my real data, the key is indeed way more granular in the fact table than the key I have in my budget_table - the budget is made up of Year/Month/CompanyCode/ProfitCenter and in the fact_table I have Year/Month/day/CompanyCode/ProfitCenter/MATNR/VBELN/POSNR/PSP/thingy - so that is a lot more - the thing is, I cannot subsume the elements of the fact_key into the elements of the budget_key, there is simply information missing - so I have to map the entire keys against one another - so a generic key, if I understand that concept correctly, would consist of the common elements - so it would by default link to a few hundred rows of my fact_table plus one budget_record.

The chance I have is making an inbetween LinkingTable that will have about double the nr. of rows as compared to my fact_table - I can then have one (formerly unique) key in that LinkingTable twice - once for exactly one fact_record and once for one budget_record.

That doesn't seem like a very good idea and it will probably not enhance performance.

I guess generic keys are not the best way to go in my situation.

Now I have a unique key in every one of my tables except the central LinkTable of course - well, the key from the central LinkTable to those fact_tables is actually unique even in that, only the keys to dimension_tables like ProfitCenter are of course not unique. I would have a hard time getting that back.

I will rather keep the budget data in a separate table and mess around a bit with those charts where I need to display it.

Thanks a lot anyway!

Best regards,

DataNibbler

0 Likes
1,977 Views