Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
See more in the attached files.
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.
Key 1 Max=70631810; Min=4
Key 2 Max=25310 Min=1
How would you create the key?
These are unfortunately 8 digits
Sometimes a LinkTable can solve the dimensional modelling requirement. Here is an example of a model where you can chose between an Link Table of Concatenated Fact Table:
I have 2 Fact tables linked by a LinkTable. In Fact A, I have a security constraint on values linked to a dimension linked directly to this Fact A. However, I do not want to propagate security for the rest of the model, data from Fact B can not be excluded. Could use the concept of Generic Keys, <Any> in this case. Anyone experienced this? Moacir Lisboa da Costa Jr
Hi Fabiano,
I have the same your scenario with 2 fact tables (sales transactions + budget) with 1 link table.
I'm also using the section access for data reduction with multiple data reduction fields (one field is customer).
The budget data is for some dimensions (but not for customer) that are different from sales dimensions (including customer) and for this reason I have to find a way to get all budget data when no customer is selected even after section access data reduction.
In the section application I have something like that:
STAR IS *;
REDUCTIONFIELD CUSTOMER
'ALLVALUES' *
This means all possible values for the CUSTOMER field in data model but I need to see also the budget data not linked to customer (through sales transactions), that is with customer = NULL.
Actually the section access data reduction cuts from budget table a lot of rows because of that.
Can I use Generic Keys to achieve it ?
This is a very difficult concept to grasp. This is one of the few areas in development that if I get it working I'm still not 100% sure why it is working. Sometimes I feel like I fell like I may have reached this point where I require generic keys because I am trying to do too much in one application. I'm still battling on trying to figure out the right balance of data model for a application before it becomes too overwhelming.
great job
Great concept, though I have encountered an issue. I have many different Fact data sources that I concatenate into a single fact table, with many generic key dimension tables, unfortunately this has resulted in poor performance for the App. It may very well be my implementation, but I think I'll need to look at removing the '<ANY>' element and having set analysis exclusions to see if that improves performance.
STAR IS *
Thanks. That worked
I've read this.. but I have a Question.. If I have a hierarchy for product group... How do I mix the products groups link table with hierarchy?
Is there any example of that?
I'm confussed... thanks