Qlik Community

Ask a Question

QlikView Documents

QlikView documentation and resources.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS

Generic keys

Henric_Cronström

Generic keys

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.

Attachments
Comments
chesterluck
Creator II
Creator II

Key 1 Max=70631810; Min=4

Key 2 Max=25310 Min=1

How would you create the key?

0 Likes
chesterluck
Creator II
Creator II

These are unfortunately 8 digits

0 Likes
nick_scott
Contributor III
Contributor III

Sometimes a LinkTable can solve the dimensional modelling requirement.  Here is an example of a model where you can chose between an Link Table of Concatenated Fact Table:

http://community.qlik.com/docs/DOC-5198

0 Likes
Not applicable

I have 2 Fact tables linked by a LinkTable. In Fact A, I have a security constraint on values ​​linked to a dimension linked directly to this Fact A. However, I do not want to propagate security for the rest of the model, data from Fact B can not be excluded. Could use the concept of Generic Keys, <Any> in this case. Anyone experienced this? Moacir Lisboa da Costa Jr

0 Likes
agigliotti
Partner
Partner

Hi Fabiano,

I have the same your scenario with 2 fact tables (sales transactions + budget) with 1 link table.

I'm also using the section access for data reduction with multiple data reduction fields (one field is customer).

The budget data is for some dimensions (but not for customer) that are different from sales dimensions (including customer) and for this reason I have to find a way to get all budget data when no customer is selected even after section access data reduction.

In the section application I have something like that:

STAR IS *;

REDUCTIONFIELD  CUSTOMER

'ALLVALUES'          *

This means all possible values for the CUSTOMER field in data model but I need to see also the budget data not linked to customer (through sales transactions), that is with customer = NULL.

Actually the section access data reduction cuts from budget table a lot of rows because of that.

Can I use Generic Keys to achieve it ?

0 Likes
michael_gardner
Creator III
Creator III

This is a very difficult concept to grasp.  This is one of the few areas in development that if I get it working I'm still not 100% sure why it is working.  Sometimes I feel like I fell like I may have reached this point where I require generic keys because I am trying to do too much in one application.  I'm still battling on trying to figure out the right balance of data model for a application before it becomes too overwhelming. 

geetaalhan
Creator
Creator

great job

0 Likes
Not applicable

Great concept, though I have encountered an issue.  I have many different Fact data sources that I concatenate into a single fact table, with many generic key dimension tables, unfortunately this has resulted in poor performance for the App.  It may very well be my implementation, but I think I'll need to look at removing the '<ANY>' element and having set analysis exclusions to see if that improves performance.

0 Likes
robert99
Specialist III
Specialist III

STAR IS *

Thanks. That worked

0 Likes
cristian_av
Creator III
Creator III

I've read this.. but I have a Question.. If I have a hierarchy for product group... How do I mix the products groups link table with hierarchy?

Is there any example of that?

I'm confussed... thanks

0 Likes