Fact Table with Mixed Granularity

[hic]

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:

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.

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.

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

[#2]

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?

[hic]

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

[#4]

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

[Chanty4u]

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

[yurgelmartina]

Hi Henry,

What are the consequences of working with data like that?

What do you recomend?

[datanibbler]

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

[hic]

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

HIC

[datanibbler]

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?

[JonasValleskog]

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

[datanibbler]

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