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 2, 2012 11:24:02 PM

Updated By:

hic

Created date:

Oct 2, 2012 11:24:02 PM

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
Not applicable

Thank Henric

Using the combination of UniqueID &' - ' & Department &' - ' & User/SalesPerson as the MasterKey- I can compare all sorts of unrelated dimensions from leads, bookings, calls, web, budgets, PPC expenditure across year, quarter, month, day by attaching a separate Master Cal to each date.

WOW - Powerful insight!

0 Likes
stabben23
Partner - Master
Partner - Master

Hi,

i have 5 levels of different authorization:

Do I need to combine all possible combination in the authorizationbridge?

This will end up with over 30 resident load from my fact table, or is there some other way doing this?

0 Likes
hic
Former Employee
Former Employee

If you have a well behaving hierarchy, you only need to use the hierarchy node. See Authorization using a Hierarchy. But if you have additional fields, independent from the hierarchy, you need to combine these with the hierarchy node.

I don't know your data, but from your example it looks as if Business-Division-Section-Department is a hierarchy, and Cost is an independent field.

HIC

0 Likes
Anonymous
Not applicable

Hi Henric,

I have used your post Basics for complex authorization,its working on desktop not on access point

Section Access;

LOAD UPPER(ACCESS) as ACCESS,

UPPER(NTNAME) as NTNAME,

UPPER(Domain) as NTDOMAIN,

REPORTING_OFFICE &'|'& LOCAL_BRANCH_ANALAYSIS_CODE as %AuthID

FROM

(ooxml, embedded labels, table is Users);

Section Application;

-----------------

//Bridge Table used for Section Access

Bridge:

     Load [Rep-OffOO] &'|'& [Loc-Br-AnalOO] as AuthID, [Rep-OffOO] &'|'& [Loc-Br-AnalOO] as %AuthID  From $(HF_OGIS_QVD)(qvd);

     Load [Rep-OffOO] &'|'& [Loc-Br-AnalOO] as AuthID, [Rep-OffOO] &'|'&'<ANY>' as %AuthID  From $(HF_OGIS_QVD)(qvd);

     Load [Rep-OffOO] &'|'& [Loc-Br-AnalOO] as AuthID, '<ANY>'&'|'& [Loc-Br-AnalOO] as %AuthID  From $(HF_OGIS_QVD)(qvd);

     Load [Rep-OffOO] &'|'& [Loc-Br-AnalOO] as AuthID, '<ANY>'&'|'&'<ANY>' as %AuthID  From $(HF_OGIS_QVD)(qvd);

---

LOAD [Rep-OffOO] as [Reporting/Issuing Office],

     [Rep-OffOO] &'|'& [Loc-Br-AnalOO] as AuthID,

     [Cur-CdOO] as [Currency Code],

     [OrgOO] as [Organization Code],

     [Mgr-CdOO] as [Manager Code],

     [Maj-LinOO] as [Major Line],

     [Min-LinOO] as [Minor Line],

     RowNo() as RowNumber

FROM

QVD

(qvd);

-------------

OGIS_DATA_Users1.xlsx- data

ACCESS NTNAME     REPORTING_OFFICE LOCAL_BRANCH_ANALAYSIS_CODE DOMAIN

ADMIN       R1-CORE\ADMIN <ANY>        <ANY>   R1-CORE

USER       R1-CORE\abc <ANY>         NA R1-CORE

0 Likes
hic
Former Employee
Former Employee

Without the data, it is difficult to see what goes wrong. But I can give you a couple of suggestions:

  • Change %AuthID to upper case. Both field name and content.
  • Test with a USER (not an ADMIN) and use strict exclusion. The ADMIN sometimes overrides the strict exclusion, so it may look as if the desktop works when it in fact does not work. If the app is open on a server, no user gets ADMIN rights.
  • Combine the domain and user name into a NTNAME like DOMAIN\USER.

HIC

0 Likes
Anonymous
Not applicable

Thanks for your reply

Change %AuthID to upper case-this is applicable for section access and bridge table both

yes i have tested with this USER       R1-CORE\abc <ANY>         NA R1-CORE and strict exclusion

NTNAME  am using R1-CORE\ABC

Sample data

[Rep-OffOO]  [Loc-Br-AnalOO]

123                00

144                NA

772                01

832                  

0 Likes
drewwilliams
Contributor III
Contributor III

Hello Henric,


My goal is to link BoM data to Inventory and Sales data. I've also read your brief on creating a BoM but I won't be applying that detail to my model quite yet.

I've posted this question on the community forum twice now and still haven't found a solution. I believe your experience with Generic Keys in combination with a Link Table may be the solution but I'm unsure on how to implement it. It may be related to your Example 3 in the Generic Keys brief but I have been unable to mimic that method.

My issue when using just a link table is that not all facts keep/ have relationships to each other. For example, if a Product is selected, I want to have a graph that shows the inventory for that product over time with respect to the inventory locations and another graph that shows the inventory of the parts that make up this product over time with respect to the inventory locations. Currently, with just a Link table, when a product is selected, I do get both graphs as mentioned, however, if I select a specific date, or select a specific location, the graphs no longer show data due to an incorrect connection in the data.

Side Notes:

• The characteristics of a part is dependent on its location (the same part in two different locations can have a different "procurement type").

• The BoM of a product is dependent on its location (the same product in different locations can have different parts that go into it as well as different costs)

If you have time, I would GREATLY appreciate any insight you have to help me correctly link this data.

Here is a link to my latest post with example data to be connected:

Link Table + Generic Keys + ?

Thank you for your time and I hope to hear from you!

Best,

Drew

0 Likes
Not applicable

Hi Heinrich,

I,ve just created a data model with generic keys to manage to different facts table (meetings and calls in a pharmaceutical company).

I would like to know if it's normal that when I select a month for the meeting (I have two different time tables, one for meeting and one for calls) I have all the list of available meeting and not only meetings done in the selected period.

It seems that using this data model qlik doesn't shoe only selection available after other selection, because generating generic keys, all data are always available. Isn't it?

Bets regards,

andrea

0 Likes
dafnis14
Specialist
Specialist

hic

I hope you can help me with this question:

If I take your pharmaceutical example, and tweak it: Product Category > Product > Packages.

Assuming a product can have one or more different packages.So if you sell a product you actually sell all the packages. 

Let's assume  the Sales fact table has Product ID only. I would like to know which packages were sold.

Is the Generic Key concept still to be used?

That's in order  to avoid creating a transaction for each package.

In my case it will save tens millions of records.

Thank You!

0 Likes
hic
Former Employee
Former Employee

If you want to be able to break up the numbers per package, you will need to have PackageID in your fact table, even if it means tens of millions of additional records.

The alternative is to reduce the grain in the fact table: Remove the PackageID and use ProductID as the finest granularity in the product hierarchy.

HIC

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