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.

[kavicsgym]

Hi Henric,

First of all, a big thank you for the great posts, we learn so much from them!

Second, I have a case when the fact table is on 3 (or 4) different dimension levels (beside daily data, we load precalculated kpi-s from a cube...).

From geo point of view, sales data are either by sales unit, by city, by county, or country level (null for sales unit, city and county).

From time point of view, there are daily, weekly (Yearweek) and monthly (yearmonth) data.  (And a mixture of these in the Fact)

My question is related to the weekly data, how to include it in the generic dimension concept, when weeks cannot directly be groupped into months (or a certain week, e.g. 2017.6th week belongs to more than one months)? Days can separately be groupped into YearWeek or Month....

Thanks for your reply and time!

[hic]

Strictly speaking, it is not possible to group weekly data and monthly data together like that.

Since a week can belong to several Gregorian months, the Date-Week-Month-Year structure isn't really a hierarchy. Rather, I'd say that the calendar has two different hierarchies:

• Date-Week-WeekYear
• Date-Month-Year

So, you can choose to use one of the two. For your data, it would mean that monthly aggregates cannot be attributed to the first hierachy, and weekly cannot be attributed to the second hierarchy.

Or you can re-define what you mean by "Week" or "Month", by using e.g. Broadcast months or 445-periods. In both of these, a week belongs uniquely to one "Month", but the months differ from the Gregorian months. For example, BroadCast weeks always belong to the month of the last day of the week. See https://community.qlik.com/docs/DOC-16939

Can you redefine the fact table to use other months than Gregorian months?

[kavicsgym]

Dear Henric, thanks a lot for the very quick reply.

I have to look into the proposed different calendars and discuss if we can use one of them. Thanks again!

[kavicsgym]

Hi Henric,

In the end, we have opted for a rather simple solution of plotting monthly data to the first day of the month and weekly data to the first day of the week, thus turning them into 'daily' data. Can you think of any side effects of this or any reasons against it? Thanks!!

[Juniper]

Hi Henric, thanks a lot for this paper! I have a short question on the third example (the pharmaceutical one). I would like to know for what reason you use inclusive generic symbols in your generic key tables? Aren't we in a situation closed to "actual numbers compared to budget numbers"?
Especially for the %SalesReps table I would expect that the sales representatives disappear when the user selects  specific facts of the Sales table. 

Thanks in advance

[mmarchese]

I don't understand this code from page 21:

Sales:
Load
    Sales,  // <-- What is this? The Sales table doesn't have a Sales column.
    ...
From Sales;

 Is it supposed to be this?

Sales:
Load
    1 as Sales,
    ...
From Sales;

[mmarchese]

For the budget vs actual example, another option would be to skip the generic keys and just include more columns in the concatenated actual/budget fact table, right?  Is there any reason not to do that?  Isn't it much simpler and possibly more performant?

For example, you have the %Products generic key, which links your fact-table rows to ProductIDs and CategoryIDs.  Couldn't your fact table just contain both ProductID and CategoryID columns to begin with?  For "actual" data rows, both columns would have real ID values, whereas for "budget" rows, ProductID would be null or 'N/A'.

I'm sure I'm missing something, but I don't see why we should bother with generic keys in this case when simple concatenation seems like it would work.

[killersswang]

Thanks Henric for this comprehensive Tech Brief. it is really helpful.

In fact, I have downloaded all the Tech Briefs written by Henric. Really awesome articles. I would suggest all Qlik users to read those Tech Brief, it will really help you short your learning curve.

[JMAROUF]

Hi @hic 

Thank you for this post, i find it very useful to many solutions,  i want to know what is the benefit of using link tables instead of dimensions? 

thank you in advance.

[hic]

From a data modelling perspective, link tables are sometimes easier to achieve than the alternative (a concatenated fact table + generic keys).

But from a performance perspective, I think  that the concatenated fact table is better.