Generic keys

[hic]

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.

[hic]

It is just a mock-up script, so it is not much to see. I can make you a better example, but I don't have time for it the nearest weeks. I'll post something here in a while.

HIC

[Anonymous]

I don't understant what's the '<ANY>' field for. Can you help me with a deeper explanation?

Thanks a lot!

[hic]

'<ANY>' is a field value that is made to link to all real-world field values. It is really the core of Generic Keys - what makes them useful.

HIC

[Anonymous]

So, should I create that '<ANY>' field wherever I need it to create the keys?

[hic]

The document describes where and how you should create a generic key. A very simple example is if you have a fact table where some records link to product 1, some to product 2, and yet some to any product. In such a case, you should link the fact table with the products table using the following table:

HIC

[Anonymous]

Hi HIC, a great job! One question regarding the memory consumtion:

I have two keys 1234567 and 7654321. Right now I'm connecting them through an underline. But somehow I have the feeling that it is quite hungry regarding the memory.

I thought first to connect the two num with an &, but theoretically this could lead to a duplicate.

I saw you're using auto number. Can I use it for incremental loads also?? So

when I have twice this numbers 1234567 and 7654321 - everytime the autonumber will create the same key, right?

Thanks in advance

//chesterluck

[hic]

Autonumber will create the same key - but only when used within the same script run. Since your incremental load probably involves several script runs, you can probably not use Autonumber.

So an underline may still be the best approach.

HIC

[Anonymous]

That doesn't sound good . I tested it and the underline is consuming a lot of memory. Are there any other approaches? What is if I connect the two numbers with two 00 inbetween? IDnum1&'00'&IDnum2

1234567&'00'&7654321

This way I'm sure that the first num can't get so big (as it strarts with 6 dig) and the second cant start with 0.

What do you think?

cheers chesterluck

[hic]

If you know that the two numbers always each have exactly 7 digits or less, then I would suggest

Key1*10000000 + Key2 as NewKey

The result will be an integer that QlikView can manage and that will use less memory than a string. QlikView can handle any integer with 14 digits or less.

HIC

[Anonymous]

Thats a nice idea. So basically

Key1 * 10^(Max(Digits))  + Key2 as NewKey

why actually '+ Key2' and not

(Key1 * 10^(Max(Digits)) & Key2)*1 as NewKey

?