4 Replies Latest reply: Apr 15, 2011 5:14 PM by Adam Abwat-Johnson RSS

    How to load multiple fact/transaction tables without synthetic keys being generated

      Hi,

      I am trying to add 2 fact / transaction tables to qlikview.

      The first fact table contains the follwoing fields:

      Date_Key



      , Time_Key, Advert_Key, Station_Key, AdStatus_Key, Product_Key, Agency_Key, Advertiser_Key, Campaign_Key, Program_Key, NoOfSpots, Amount

      The 2nd fact table contains the following fields:

      Date_Key, Time_Key, Advert_Key, Station_Key, AdStatus_Key, Product_Key, Agency_Key, Advertiser_Key, Campaign_Key, Program_Key, TargetMarket_Key, NoOfSpots, Amount, Reach

      I only want the fact tables to be associated with the corresponding dimension tables (Date, Time, Advert etc). Qlikview generates synthetic keys to associate the fact tables which duplicates the number of records in the 1st table.

      How do I prevent this from happening?

      Thanks.

       

        • How to load multiple fact/transaction tables without synthetic keys being generated
          Adam Abwat-Johnson

          Hi,

          I guess you'd either have to qualify the tables and then unqualify the fields that you want to link to your dimensions or concatenate the two fact tables together using null() for the fields that exist in one table and not the other. For example:

          load

          Date_Key,

          Time_Key,

          Advert_Key,

          Station_Key,

          Product_Key,

          Agency_Key,

          Advertiser_Key,

          Campaign_Key,

          Program_Key,

          NoOfSpots,

          null() as TargetMarket_Key,

          Amount,

          null() as Reach

          from table;

          Then load your second table. As it will have the same fields in it QlikView will automatically concatenate the second table onto the end of the first.

          load

           

          Date_Key,

          Time_Key,

          Advert_Key,

          Station_Key,

          Product_Key,

          Agency_Key,

          Advertiser_Key,

          Campaign_Key,

          Program_Key,

          null() as NoOfSpots,

          TargetMarket_Key,

          Amount,

          Reach

          from table2;

          As you have the two sources in the same table there will be no synthetic key to produce. If you still want to know where the information came from then put an extra flag in your load statement on each table like:

          'Source 1' as SourceTable

          Put something like that on both load statements and you'll still be able to distinguish between the two.

          Hope that helps,

          Chris

            • How to load multiple fact/transaction tables without synthetic keys being generated

              Hi Chris,

              Thanks for your response.

              I'll try to load into thesame fact table as you described. But what happens when I have more fact tables with a lot of data? Would this be efficient?

              Also, how do I qualify tables and unqualify fields as you suggested first. Does it mean that I have to create instances of the dimensions and change the names to suit the corresponding fact table?

              Thanks.

              Lemonade

                • How to load multiple fact/transaction tables without synthetic keys being generated

                  Hi All,

                  I tried to load the 2 fact tables into 1 table. I had issues with my reports because of the global nature of selected values. I didn't want any user to see values they should not see on a report before filtering the data.

                  What I eventually tried was to remove all fields from the 2nd table that were present in the 1st table. Then I linked the 2 tables via a serial number field. Kinda like a reference fact table.

                  It works now but I'm not sure what effect this model will have on performance if I had a lot of data in both fact tables.

                  Cheers.

                  Lemonade

              • How to load multiple fact/transaction tables without synthetic keys being generated
                Adam Abwat-Johnson

                Hi,

                The general rule as far as I can make out is to reduce the number of tables you have down to a minimum. Actually there is another post that may be of some general help with regards to performance and general good practice:

                http://community.qlik.com/forums/t/44345.aspx

                With regards to stopping certain users from seeing particular data have a look at what you can do with 'section access'. I've not used it as I'm only one of two people using it at my company at the moment.

                Chris