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,

      Sandra

       

      =================================================== 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

       

      Thanks

       

      Message was edited by: Sandra Pinto

        • Re: Generic Keys
          Tomasz Truszkowski

          Can You give us sample data?

          Tomasz

          • Re: Generic Keys
            Antonio Mancini

            Hi Sandra,

            below a simple Generic Load Script

            Generic
            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
            ]
            ;

             

            Regards,

            Antonio

            • 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:

                  A.png

                    • 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

                      %KEY_FacTable1,

                      sharedDim1,

                      sharedDim2,

                      ...

                      FROM FacTable1;

                      concatenate

                      LOAD Distinct

                      %KEY_FacTable2,

                      sharedDim1,

                      sharedDim2,

                      ...

                      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.

                      BR

                      Andrea