9 Replies Latest reply: Aug 25, 2017 8:24 AM by Andrea Gigliotti RSS

    Generic Keys

    Sandra Pinto

      Hi All,

      I have an issue where I have several Fact tables and some Dimension Tables.

      Some Dimensions should be linked to some of the fact tables, but not to all.

      I have read about this matter here. Generic keys


      But still I need a concrete example for me to be able to implement what this article suggests.


      Does anyone have an example qvf file in which there are:

      • Multiple Fact tables,
      • Dimension Tables,
      • Dimension Link table and
      • One Master Link table?



      Much appreciation for any help.

      Thank you,



      =================================================== Edit:


      Hi Tomasz,


      I have tried to reduce my model to make it relatively simple (My original data is much larger and more complex...).

      Here is the qvf.


      What I would like to do is create for each dimension a Dimensional Link Table

      Then, create a Master Link Table which links the Dimensional Link tables to the 2 Fact Tables (Labor and P&L)


      This is based on the way suggested in the attached PDF




      Message was edited by: Sandra Pinto

        • Re: Generic Keys
          Tomasz Truszkowski

          Can You give us sample data?


          • Re: Generic Keys
            Antonio Mancini

            Hi Sandra,

            below a simple Generic Load Script

            LOAD id,'data'&AutoNumber(RowNo(),id) as D1,data Inline
            id, data
            1, 01/02/2016
            1, 03/02/2016
            1, 05/02/2016
            2, 11/02/2016
            2, 21/02/2016
            2, 22/02/2016




            • Re: Generic Keys
              Andrea Gigliotti

              I saw 2 syn tables in your data model.

              to avoid it you should create only one fact table or create a link table.


              why do you think you need a generic keys approach ?

                • Re: Generic Keys
                  Sandra Pinto

                  Hi Andrea,

                  Yes. you are indeed right. There are 2 syn tables.

                  I forgot to explain in my initial question that in my original model the 2 fact tables are actually concatenated and so there is only 1 syn table there - and that is because of the interval match. and it looks like an OK syn...


                  What I would like is - to create the model in such a way that the 2 fact tables are Not concatenated .

                  Instead, I would rather the 2 fact tables to get connected to the Dimensions via 2 "Bridges" - One bridge of the Dimensional Link Table and the other is the Master Link table.


                  This thought of mine is based on the model below - suggested by HIC in his article. (PDF attached)

                  Problem is I am not sure how to write the script to get there ...

                  Here is the model from the article:


                    • Re: Generic Keys
                      Andrea Gigliotti

                      Hello Sandra,

                      did you create a link table before?

                      to do it i suggest you a script similar as below:


                      [Link Table]:

                      LOAD Distinct





                      FROM FacTable1;


                      LOAD Distinct





                      FROM FacTable2;

                      DROP Fields sharedDim1, sharedDim2, ....From FacTable1, FacTable2;

                      Where the two %KEY are concatenated string as ( sharedDim1 & '|' & sharedDim2 & '|' & ...) as  %KEY_FacTable that you have to create before in each FacTable.

                      while sharedDim1, sharedDim2, etc... are all common dimensions on both FacTable.

                      Hope it may help you.