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.

[SunilChauhan]

Hi Henric,

Its Really a nice post.might this be a silly ques but i am not able to understand

In  code attched in zip for Customer and Country and Product and product category

why dont  we link Product to product category and Customer to Country

then link  Customer and Product to fact table.

why need to create linkage between them . what is benifit of it.

and what is disadvantages of linking without linkage.

Thanks

Sunil Chauhan

[#3]

Hi Henric,

thank you for your tutorial, it took me some time to be able to translate your samples to my real data and but I am amazed at how this 'generic keys' work. I have a question regarding the Master Link Table, on your tutorial you have a sample of the keys from Sales and you mentioned similar loads are created for the other two tables. would you be able to explain how please? I cannot understand how to add the load for the next fact table inside the new 'Master Link table'?

Thank you,

Hugo

[hic]

You need to load the Master Link Table in three load statements; one from Sales, one from Visits and one from Targets. The three load statements will be concatenated.

All three should have the same structure, but each should only use the keys that are relevant to this table. Other keys should be replaced by the '<ANY>' symbol. So, the first thing to do is to see which keys are relevant. The following table shows this:

This information is used in the white paper when creating the Load statements for the three fact tables.

For the Master Link Table, it is the same thing, but in the white paper I only show the code for the sales table. The trick is to use exactly the same load pattern in three places in the script: Once in the Fact table and twice in the Master Link table. See the red squares below.

Hence, if you create two additional load statements for the Master Link Table, but instead use the pattern of "Visits" and "Target", you will get the necessary load statements.

HIC

[marwen_garwachi]

Thanks a lot !

[SunilChauhan]

could you Please reply for my query above

[hic]

If you have nice clean data, where each transaction in the Fact table links to a Product, you can of course link

     Transaction -> Product -> ProductGroup

just like you suggest.

But like I say in the beginning of the post: If you have other cases, like a mixed dimensional granularity in the fact table, then Generic Keys is a way to solve this problem. "Mixed dimensional granularity" means that some transactions (e.g. actual numbers) link to a specific Product, whereas other transactions (e,g, budget) link to an entire Product Group. In such a case, you cannot link

     Transaction -> Product -> ProductGroup

since the middle step doesn't exist.

HIC

[#8]

Thank you so much Henric

[SunilChauhan]

Thanks for you reply

[#10]

Hi,

I have a situation where I have multiple, completely unrelated fact tables I would like to place in one document to compare on calendar dimensions. i.e. Leads, Bookings, Phone Calls, Web Stats based on day, month, year etc.

Having read your PDF on this post, 'Example 3: Link table and multiple fact tables' seems like the logical starting place, however there is no single table that I'm able to map multiple keys, to successfully create the master key/table that I can relate to all fact tables.

Do you have any advise or recommendations around this approach? 

Thanks in advance.

[hic]

The "Example 3" link table should certainly be able to solve your problem.

When you create your link table, you need several Load statements, one for each fact table:

     Load ... Resident Fact1;

     Concatenate

     Load ... Resident Fact2;

     Concatenate

     Load ... Resident Fact3;

These will together form one table.

You need to think about how to define the keys, though. Use the <ANY> symbol if the key doesn't exist in that specific fact table.

HIC