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.

[JMAROUF]

Thank you;

[richards]

Thanks for the information regarding this issue.

I´m curious if someone have a solution regarding on how to concatenate two fact tables.  One table with the whole accounting system and one table with a part of this data, but other columns and a lot more rows per each verificationsnumber. 

With the solution above i cant´solve this.

This is my tables in short terms.

Table 1

Verificationnumber

...

Amount

Table 2

Verificationnumber

...

Amount

My issue is the following.

In table 1 the whole accounting system, in table 2 the the reporting system. In table 2 you could find fewer verificationnumbers than in table 1 ( the reporting system have been used in a shorter time). 

The similarity in both tables would be that the verificationsnumbers that is the same in both tables, would end up in the same numbers. However the other columns in both tables would not be found in the opposite table. The other issue is that in table 1 I could have 5 rows of one verificationsnumber but in the table 2 10 rows. Depending of the information in the columns.

So in short terms, how to concatencate the data so so that amount is amount and on the verificationnumber from table 1 where table 2 could find data on the same verificationnumber. Show the amount divided on table 2´s column and if I would like to present the columns of table 1. If no verificationnumber in table 2, table 1 amount is onle represented.

Does anyone one have a solution?

[valpassos]

Hi Henric (and everyone who would like to jump in and give their 2 cents),

What if we are dealing with a denormalized Product dimension, with all the hierarchies (Product Group, Brand, etc) all into one table? We no longer can have the 2 Ids Product_ID and Product_Group_ID in the link table...

Also, why wouldn't a mapping solve this issue? If some fact tables are on a Product Group level, then we just map the Product Group to an arbitrary Product ID, and we would have a direct link between the (concatenated) fact table and Product dimension. Of course, when we filter on the Product Group attribute, we will only see one product linked to it, but this should be considered an invalid analysis (as in, the users should only be looking at the Product Group field). 

Regards,

Lisa