3 Replies Latest reply: Jan 26, 2017 3:00 AM by Marcus Sommer RSS

    Fact table data different (granularity?) and joining

    Dennis Norton

      Hi all,

       

      Attempting to solidify my understanding of star schemas, and the resources for my particular questions seem limited or touch on very simplistic examples.

       

      1.) I have been struggling to find the correct way to join fact tables together. I have one fact table that is at a transaction level. I want to incorporate sales goals and have what I would consider one level up from the transactions, which is Salesperson. But I also want to view these rolled up to the Manager, and BusinessType levels.

       

      TRANSFACT                              GOALSFACTS

      TransNum                                   SalespersonID

      TransDate                                   Month-Year

      SalespersonID                             GoalAmount

      CustomerID

      BusinessTypeID

      ManagerID

      TransAmount

       

      I can't just simply join GOALSFACTS to the TRANSFACT table can I? I have many repeated salespersons on the TRANSFACT table and so my goal amount would be repeated too, no?

       

      2.) In this scenario, if I want to look at one of my ALTFACT table amounts by customer, businesstype, or manager (all keys not present in ALTFACT), is the best method to applymap  3x against the TRANSFACT table for all three dimension keys, and then concat both tables into one?

       

      TRANSFACT                         ALTFACT

      TransNum                               TransNum

      TransDate                               TransDate

      SalespersonID                         SalespersonID

      CustomerID                             FedAmount

      BusinessTypeID                       AltAmount

      ManagerID                              ShipAmount

      TransAmount

       

      Regards,

        • Re: Fact table data different (granularity?) and joining
          Angel Monjaras

          Hello Dennis.

          In scenario 1, a JOIN would repeat the goal amount as you point out.

          You should just let Qlik associate the tables naturally, that way you will have no repetitions.

          Now, since your goal is stated per Salesperson / Month-Year,  a simple association by SalespersonID will not do. Instead, you need to calculate Month-Year on the TRANSFACT table from TransDate, so you can build a combined key in both tables:

          SalespersonID&'-'&Month-Year

          Then you can omit the SalesPersonID and Month-Year fields in GOALFACTS.

          This way, the GoalAmount and TransAmount will compare nicely, and you will also get the rollups you want.

           

          In scenario 2, assuming TransNum is a unique key for both tables, and both TransDate and SalespersonID are the same in both tables for any given value of TransNum, I would Load TRANSFACT first with all its fields, and then simply:

          JOIN (TRANSFACT)

          LOAD

               TransNum,

               FedAmount,

               AltAmount,

               ShipAmount

          FROM ALTFACT;

           

          Hope this helps!

          • Re: Fact table data different (granularity?) and joining
            Muñoz Héctor

            Hi Dennis,

             

            I would do the following:

             

            1) I would create a MONTHLYFACT table with the union of all GOALSFACT records as they come from their source and TRANSFACT grouped as follows:

             

            MONTHLYFACT:

            LOAD    *

            RESDENT GOALSFACT;


            DROP TABLE GOALSFACT;


            CONCATENATE (MONTHLYFACT)

            LOAD        SalespersonID,

                        Month(TransDate) & '-' & Year(TransDate)     AS Month-Year,

                        Sum(TransAmount)                             AS TransAmount

            RESIDENT    TRANSFACT

            GROUP BY    SalespersonID,

                        Month(TransDate) & '-' & Year(TransDate)

             

            The final result would be MONTHLYFACT table (monthly records) and TRANSFACT table (detail of transactions) associated by SalespersonID field.

             

            2) This scenario is similar to #1; I would join both tables in 1 with the structure of ALTFACT and the records coming from TRANSFACT grouped by common dimensions with ALTFACT. Later  would drop ALTFACT table and union table created and TRANSFACT table would be associated by a key formed by TransNum, TransDate and SalespersonID fields.

             

            I hope it serves to you.

             

            Regards,
            H

            • Re: Fact table data different (granularity?) and joining
              Marcus Sommer

              I would in case one convert the year-month from the goals-table into a date per makedate(year, month, 1) as TransDate (or maybe just Date and also duplicating this field within the trans-table and connecting this field with a master-calendar) and then just concatenate this table to the trans-table - I use this approach since years and it worked very well.

               

              In case two you might be able to join the alt-table to the trans-table depending on the key-matching but a mapping would be less risky to remove/duplicate any records whereby you could use multiple mappings or also a single-mapping with concatenated amount-fields like FedAmount & '|' & AltAmount & '|' & ShipAmount and which are splitted again with a subfield(applymap(), '|', n) function.

               

              - Marcus