Skip to main content
Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

Generic keys

cancel
Showing results for 
Search instead for 
Did you mean: 
hic
Former Employee
Former Employee

Generic keys

Last Update:

Oct 3, 2012 2:24:02 AM

Updated By:

hic

Created date:

Oct 3, 2012 2:24:02 AM

Attachments

Generic Keys is a way to define keys between tables in a more general way so that their values can represent other things than individual key values; they can represent groups of key values or any key value. As an example, you can combine product IDs, product group IDs and a symbol for all products into one key field.

You can use generic keys to solve many data modeling problems:

  • Authorization table with OR-logic between fields
    If you have an authorization table you sometimes want to have a slightly more complex access restriction than a simple logical AND between fields. It could be e.g., that a user is allowed to see sales for all regions for a specific product and at the same time the European sales for all products. Generic keys can be used here.
  • Mixed dimensional granularity in a single fact table
    Often you want to compare actual numbers with budget numbers. The standard method is to concatenate these two tables into one common fact table. However, this new fact table could have mixed granularity in many of the dimensions. Generic keys can be used here.
  • Multiple fact tables linked using a master link table
    Sometimes you have fact tables that are so different that you don’t want to concatenate them. To solve this problem you can make a data model that has a central link table and uses generic keys.

See more in the attached files.

HIC

PS I have been asked to make an example on comparing budget and actual numbers, so here it comes as a second attachment. It is a zip file with a mock-up order database with a budget. Create a new empty qvw; expand the zipped files in the same folder as the qvw; insert one of the two included script files into the qvw and run the script.

Comments
hugmarcel
Specialist
Specialist

Page 23:

... "as %MasterKey From Sales;".

I think it is not necessary to redefine the %MasterKey in Sales table, as it already exists there (see page 21). Field %MasterKey can loaded directly into Master Link Table.

Cheers - Marcel

0 Likes
hic
Former Employee
Former Employee

You're right that the %MasterKey exists in the loaded (resident) sales table, but the other keys don't. So, you need to load the master link table from the source sales table. And there the %MasterKey doesn't exist.

HIC

0 Likes
Matthias_Herkommer

Thanks Henric for this comprehensive, yet concise document about the use of generic keys which will definitely help solve many of the more complex scenarios we face more and more often at large enterprise accounts. Everyone has some understanding of "linking tables" etc., and when to use them, but your clear examples will be a good guideline in SiBs and projects to quickly come up with a clean data model to meet the requirements.

hic
Former Employee
Former Employee

Glad you like it! If you have ideas for topics for other similar documents, just let me know.

HIC

0 Likes
Not applicable

19.10.2012 16:18   

Are there any experiences with query performance when using generic keys for concatenating multiple fact tables or balancing different levels of granularity, e. g. how many fact tables can be concatenated that way with sufficient performance for the end user?

Are there any restrictions regarding the number of columns in the resulting fact table when concatenating multiple fact tables by generic keys?

In short: What about performance limitations?

0 Likes
hic
Former Employee
Former Employee

My experience is that concatenation of fact tables is much better than the alternative (link table and multiple fact tables).

Further, whether you concatenate two or five fact tables really doesn't matter. The main restriction is number of rows in the resulting fact table. Slightly simplified, you can say that the number of rows affect respons time and the number of columns affect amount of memory used.

Bottom line: If you have enough memory, you shouldn't hesitate to use this solution.

HIC

hic
Former Employee
Former Employee

There are many aspects of this. Are there any specific you have in mind, or do you mean a general overview?

Take a look at the following post and let me know if it is anything close to what you have in mind.

http://community.qlik.com/blogs/qlikviewdesignblog/2012/08/10/how-intervalmatch-solved-my-profit-and...

HIC

0 Likes
fosuzuki
Partner - Specialist III
Partner - Specialist III

Hi HIC,

great document, loved the idea. I will try to use in my next project.

I tried to play with your sample scripts but they did not work as expected. If I understood what you explained, there's an error in them:

"

// ============== Budget Numbers ==============

Concatenate (Facts)

LOAD Country & '|' & Null() as %CustomerID,

     ProductCategoryID & '|' & Null() as %ProductID,

"

sholud be:

"

// ============== Budget Numbers ==============

Concatenate (Facts)

LOAD Country & '|<N/A>' as %CustomerID,

     ProductCategoryID & '|<N/A>' as %ProductID,

"

Regards

Fernando

0 Likes
hic
Former Employee
Former Employee

You are absolutely right. Thanks for pointing it out.

In principal it really doesn't matter whether you use "Country & '|<N/A>'" or "Country & '|' & Null()" as expression for the generic key. Both will work. But only if you use the same in both the fact table and in the dimensional link table. And this, I missed...

The files are corrected now, anyway.

HIC

0 Likes
Not applicable

This is great anticle Henric ... Do you mind sharing data and script sample for your 3rd example (Link table and multiple fact tables) .. i am interested to learn looking in to data

0 Likes
Version history
Last update:
‎2012-10-03 02:24 AM
Updated by:
Former Employee