15 Replies Latest reply: Oct 23, 2017 11:06 AM by Vijay Vira RSS

    Multiple Fact Tables Data Modeling

    Samantha Kumar K

      Hi,

       

      I am using QlikView for last one month, it is very good reporting tool. I created one data model and prepared the reports, but I am getting the duplicate data in the reports.

      More Info:

      I have two tables TRANSACTIONS, TICKETS with below fields. I want to create data model using these two tables. Here master_doc_id as the key between these two tables.

       

      TRANSACTIONS:

          order_no,                // order number

          eu_master_doc_id, // end user id

          res_master_doc_id, // reseller id

          order_date,

          mant_end_date,

          region,

          product

          etc.....

       

      TICKETS:

      ticket_id,

      master_doc_id // it can be end user id or reseller id or both

      ticket_open_date,

      ticket_soved_date,

      etc..

       

      Important Notes:

      1. [TRANSACTIONS] : At order line item level eu_master_doc_id, res_master_doc_id  can have different or same values

      2. [TICKETS]: At ticket_id level master_doc_id can have the value which can be belong to end user id or reseller id or both

       

      Please find the data model below for your reference. My problem is while creating the reports I am getting duplicate data.

      It would be a great help.

       

      Please let me know if you need more details.

       

      Thank you!

      Samanth

       

      Data Model:

      Using the above two tables, I have created the below data model.

      data_model_image.png

      Data Model Script:

       

      TRANS_REF:
      LOAD DISTINCT order_no, eu_master_doc_id as master_doc_id, 'enduser' as cust_type Resident TRANSACTIONS ;
      OUTER JOIN
      LOAD DISTINCT order_no, res_master_doc_id as master_doc_id, 'reseller' as cust_type Resident TRANSACTIONS;
      //
      EU_MID2OrderDateMap:
      Mapping LOAD DISTINCT eu_master_doc_id , order_dt Resident TRANSACTIONS;
      RES_MID2OrderDateMap:
      Mapping LOAD DISTINCT res_master_doc_id, order_dt Resident TRANSACTIONS;
      EU_MID2MaintEndDateMap:
      Mapping LOAD DISTINCT eu_master_doc_id, maint_end_dt Resident TRANSACTIONS;
      RES_MID2MaintEndDateMap:
      Mapping LOAD DISTINCT res_master_doc_id, maint_end_dt Resident TRANSACTIONS;
      TKT_MID2CreatedDateMap:
      Mapping LOAD DISTINCT zen_master_doc_id, ticket_open_date Resident TICKETS;
      TKT_MID2SolvedDateMap:
      Mapping LOAD DISTINCT zen_master_doc_id, ticket_solved_date Resident TICKETS;
      DateBridge:
          Load DISTINCT eu_master_doc_id as master_doc_id, Applymap('EU_MID2OrderDateMap', eu_master_doc_id,Null()) as EventDate, 'eu_order_date' as EventDateType
          Resident TRANSACTIONS;
          Load DISTINCT res_master_doc_id as master_doc_id, Applymap('RES_MID2OrderDateMap', res_master_doc_id,Null()) as EventDate, 'res_order_date' as EventDateType
          Resident TRANSACTIONS;
          Load DISTINCT eu_master_doc_id as master_doc_id, Applymap('EU_MID2MaintEndDateMap', eu_master_doc_id,Null()) as EventDate, 'eu_maint_date' as EventDateType
          Resident TRANSACTIONS;
          Load DISTINCT res_master_doc_id as master_doc_id, Applymap('RES_MID2MaintEndDateMap', res_master_doc_id,Null()) as EventDate, 'res_maint_date' as EventDateType
          Resident TRANSACTIONS;
          Load DISTINCT zen_master_doc_id as master_doc_id, Applymap('TKT_MID2CreatedDateMap', zen_master_doc_id,Null()) as EventDate, 'tkt_created_date' as EventDateType
          Resident TICKETS;
          Load DISTINCT zen_master_doc_id as master_doc_id, Applymap('TKT_MID2SolvedDateMap', zen_master_doc_id,Null()) as EventDate, 'tkt_solved_date' as EventDateType
          Resident TICKETS;
      // Drop Tables EU_MID2OrderDateMap, RES_MID2OrderDateMap, EU_MID2MaintEndDateMap, RES_MID2MaintEndDateMap, TKT_MID2CreatedDateMap, TKT_MID2SolvedDateMap;
        • Re: Multiple Fact Tables Data Modeling
          Vijay Vira

          HI,

           

          Would it be possible for you to provide sample of those two tables, application and calculations you are trying to do. It will help us to help you.

           

          I have a complex data model with more than one fact tables, look up tables, calendar, etc.. As can be seen from screen shot below. My calculations and data model has worked just fine. I've used it in multiple dashboards and for complex calculations.

           

          datamodel2.png

            • Re: Multiple Fact Tables Data Modeling
              Samantha Kumar K

              Hi Vijay,

               

              Thank you very much for your reply.

               

              Please find the attached two CSV files having sample data for two tables TRANSACTIONS, TICKETS.

               

              Please let me know if you need more details.

               

              Note: In future, like TICKETS, I should be able to associate another table 'LEADS'

               

              Regards,

              Samanth

                • Re: Multiple Fact Tables Data Modeling
                  Vijay Vira

                  HI Samantha,

                   

                  Would you be able to share your app?

                   

                  BR,

                  Vijay

                    • Re: Multiple Fact Tables Data Modeling
                      Samantha Kumar K

                      Please find the attached QVW file which I created based on the sample data.

                       

                      Thanks,

                      Samanth

                        • Re: Multiple Fact Tables Data Modeling
                          Vijay Vira

                          Hi Samanth,

                           

                          Can you please advise how different combination of Zen, EU and Res Master document have to be joined or available to you for performing data analysis/calculation. This is in relation to data you have provided in sample.

                           

                          Following what I've observed so far and trying to betterunderstand it so I can provide a meaningful DM

                           

                             

                          Unique Zen Master Doc ID
                          zen_master_doc_idZen Match to EUZen Match to Res
                          O.22922-392.176366.0-nlO.22922-392.176366.0-nl#N/A
                          O.80722-250.12444.5-fr#N/A#N/A
                          O.333096-528.769547.3-nlO.333096-528.769547.3-nlO.333096-528.769547.3-nl
                          O.333116-528.769547.5-nl#N/AO.333116-528.769547.5-nl

                           

                          Unique EU and matching it to Zen

                           

                          eu_master_doc_idEU Match to Zen
                          O.57261-528.113253.0-nl#N/A
                          O.22922-392.176366.0-nlO.22922-392.176366.0-nl
                          O.333096-528.769547.3-nl

                          O.333096-528.769547.3-nl

                           

                           

                          Unique RES and Matching it to Zen

                             

                          res_master_doc_idRes Match to Zen
                          O.333096-528.769547.3-nlO.333096-528.769547.3-nl
                          O.333116-528.769547.5-nlO.333116-528.769547.5-nl

                           

                          Combination of EU and Res in data set

                             

                          Distinct Records
                          eu_master_doc_idres_master_doc_id
                          O.57261-528.113253.0-nlO.333096-528.769547.3-nl
                          O.57261-528.113253.0-nlO.333096-528.769547.3-nl
                          O.22922-392.176366.0-nlO.333096-528.769547.3-nl
                          O.57261-528.113253.0-nlO.333096-528.769547.3-nl
                          O.22922-392.176366.0-nlO.333096-528.769547.3-nl
                          O.333096-528.769547.3-nlO.333116-528.769547.5-nl
                          O.333096-528.769547.3-nlO.333116-528.769547.5-nl
                          O.22922-392.176366.0-nlO.333116-528.769547.5-nl
                          O.333096-528.769547.3-nlO.333096-528.769547.3-nl
                          O.333096-528.769547.3-nlO.333116-528.769547.5-nl
                          O.22922-392.176366.0-nlO.333116-528.769547.5-nl
                          O.22922-392.176366.0-nlO.333116-528.769547.5-nl
                          O.22922-392.176366.0-nlO.333116-528.769547.5-nl
                          O.333096-528.769547.3-nlO.333096-528.769547.3-nl
                          O.333096-528.769547.3-nlO.333096-528.769547.3-nl
                          O.22922-392.176366.0-nlO.333116-528.769547.5-nl
                          O.333096-528.769547.3-nlO.333116-528.769547.5-nl
                          O.333096-528.769547.3-nlO.333116-528.769547.5-nl
                          O.22922-392.176366.0-nlO.333096-528.769547.3-nl
                          O.333096-528.769547.3-nlO.333116-528.769547.5-nl
                          O.22922-392.176366.0-nlO.333116-528.769547.5-nl
                          O.333096-528.769547.3-nlO.333096-528.769547.3-nl
                          O.22922-392.176366.0-nlO.333116-528.769547.5-nl
                          O.22922-392.176366.0-nlO.333116-528.769547.5-nl
                          O.22922-392.176366.0-nlO.333116-528.769547.5-nl
                          O.22922-392.176366.0-nlO.333116-528.769547.5-nl
                          O.22922-392.176366.0-nlO.333116-528.769547.5-nl
                          O.22922-392.176366.0-nlO.333116-528.769547.5-nl
                          O.22922-392.176366.0-nlO.333116-528.769547.5-nl
                          O.22922-392.176366.0-nlO.333116-528.769547.5-nl

                           

                           

                          Unique combination of EU and Res from data set

                             

                          Unique eu res combination
                          eu_master_doc_idres_master_doc_id
                          O.57261-528.113253.0-nlO.333096-528.769547.3-nl
                          O.22922-392.176366.0-nlO.333096-528.769547.3-nl
                          O.333096-528.769547.3-nlO.333116-528.769547.5-nl
                          O.22922-392.176366.0-nlO.333116-528.769547.5-nl
                          O.333096-528.769547.3-nlO.333096-528.769547.3-nl

                           

                           

                           

                          I hope my question is clear to you and makes sense.

                           

                          BR,

                          Vijay

                           

                           

                           

                           

                           

                           

                            • Re: Multiple Fact Tables Data Modeling
                              Samantha Kumar K

                              Hi Vijay,

                               

                              Thank you so much for your time.

                               

                              In our data model, we have two types of customers enduser, reseller.

                              We have two tables:

                                  (a) TRANSACTIONS: order line items by customers

                                  (b) TICKETS: tickets raised by the customers.

                               

                              Here is the table having different combinations between EU, RES and ZEN

                               

                              Dimension 1Dimension 2Dimension 3Dimension 4Dimension 5Dimension 6Dimension 7Dimension 8
                              EUcountryregionorder_fyorder_fqorder_monthmaint_fymaint_fqmaint_month
                              REScountryregionorder_fyorder_fqorder_monthmaint_tymaint_fqmaint_month
                              ZENcountryregionticket_created_fyticket_created_fqticket_created_month
                              ZENcountryregionticket_solved_fyticket_solved_fqticket_solved_month

                               

                               

                              For example, here is my scenario to get better understanding of the requirement:

                                  1. Bookings Value given by customer (eu_master_doc_id/res_master_doc_id) in ordered FY, FQ, Month, Country

                                  2. Along with Ticket Count raised by the same customer in the ticket created FY, FQ, Month, Country

                              EX:- master_doc_id, FY, FQ, Country, $EU Bookings, $RES Bookings ,#EU Tickets, # RES Tickets


                              I am not sure whether I have provided the required details to get better understanding.


                              Please let me know if you need more information.

                               

                              Thanks,

                              Samanth

                                • Re: Multiple Fact Tables Data Modeling
                                  Vijay Vira

                                  Hi Samantha,

                                   

                                  I'll read it once I reach office in about an hour or so. I'll let you know if more information or clarification is required.

                                   

                                  I Hope to get it working for you.

                                   

                                  BR,

                                  Vijay

                                  • Re: Multiple Fact Tables Data Modeling
                                    Vijay Vira

                                    Hi Samanth,

                                     

                                    Is there a reason EU Doc Id and Res Doc Id are together in same table? Having both of them in same transaction table is making it difficult to tie it to tickets. Also if you were to split them up what data points will gets associated with each of them.

                                     

                                    I just created two separate Transaction tables with all fields. If I were to leave eu_master doc_id in res_mst or res_master_doc_id in eu_mst along with actual transaction table then it would create a loop. A loop in data model will cause problems.

                                     

                                    With data model I've got you will/may have to use set analysis for certain calculations.

                                     

                                    Please see attached QVW file.

                                     

                                    BookingCalc1.png

                                      • Re: Multiple Fact Tables Data Modeling
                                        Samantha Kumar K

                                        Hi Vijay,

                                         

                                        Thank you very much for your valuable time spending on this.

                                         

                                        Q: Is there a reason EU Doc Id and Res Doc Id are together in same table?

                                        Ans: The only reason is: Both EU and RES are having the common data like order_no, order_dt, bookings_usd, acv_local etc…

                                                 There is no problem even if we separate them into two tables by renaming the columns.

                                         

                                        I have tested the data model that you have given. It looks very nice.

                                        I tried to get some report by adding date calendar to date fields. But, I am getting the duplicated data like displayed in below report.

                                         

                                        report.jpg

                                         

                                        Report:

                                        1. Dimensions:
                                          1. master_doc_id
                                          2. Fiscal Year         :  fiscal year of ticket_created_date or order_dt 
                                          3. Fiscal Quarter  : fiscal quarter of ticket_created_date or order_dt
                                        2. Measures:
                                          1. #EU Tickets : number of tickets raised by enduser       => count of ticket_id
                                          2. #RES Tickets : number of tickets raised by reseller      => count of ticket_id
                                          3. $ EU Bookings : total bookings by enduser                     => sum of bookings_usd
                                          4. $ RES Bookings : total bookings by reseller                     => sum of bookings_usd

                                         

                                         

                                        Can you please help me in getting the above report? Based on that, I can derive any other reports.

                                         

                                        If we are able to get this kind of report that would be great!

                                         

                                        Thanks & Regards,

                                        Samanth

                                          • Re: Multiple Fact Tables Data Modeling
                                            Vijay Vira

                                            Hi Samantha,

                                             

                                            How about following....

                                             

                                            MultiFact.JPG

                                             

                                            NoConcatenate

                                            Tickets:

                                            LOAD zen_master_doc_id as master_doc_id,

                                                 ticket_id,

                                                 ticket_open_date,

                                                 ticket_solved_date,

                                                 language,

                                                 country,

                                                 company_name,

                                                 region

                                            FROM

                                            TICKETS.csv

                                            (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

                                             

                                             

                                            NoConcatenate

                                            TransactionTmp:

                                            LOAD eu_master_doc_id,

                                                 res_master_doc_id,

                                                 order_no,

                                                 sku,

                                                 order_dt,

                                                 maint_start_dt,

                                                 maint_end_dt,

                                                 trans_region,

                                                 revenue_type,

                                                 res_name,

                                                 res_city,

                                                 res_geo,

                                                 eu_name,

                                                 eu_city,

                                                 license_no,

                                                 eu_geo,

                                                 lic_type,

                                                 qty,

                                                 bookings_local,

                                                 bookings_usd,

                                                 acv_usd,

                                                 acv_local

                                            FROM

                                            TRANSACTIONS.csv

                                            (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

                                             

                                             

                                            NoConcatenate

                                            Txn_Master:

                                            LOAD

                                                 eu_master_doc_id as master_doc_id,

                                                 eu_master_doc_id & Chr(59) & res_master_doc_id  as combine_master_doc_id,

                                                 'EU Master' as 'doc_type'

                                            Resident TransactionTmp;

                                            Concatenate

                                            LOAD

                                                 res_master_doc_id as master_doc_id,

                                                 eu_master_doc_id & Chr(59) & res_master_doc_id  as combine_master_doc_id,

                                                 'RES Master' as 'doc_type'

                                            Resident TransactionTmp;

                                             

                                             

                                            NoConcatenate

                                            Transactions:

                                            LOAD eu_master_doc_id & Chr(59) & res_master_doc_id as combine_master_doc_id,

                                                 eu_master_doc_id,

                                                 res_master_doc_id,

                                                 order_no,

                                                 sku,

                                                 order_dt,

                                                 maint_start_dt,

                                                 maint_end_dt,

                                                 trans_region,

                                                 revenue_type,

                                                 res_name,

                                                 res_city,

                                                 res_geo,

                                                 eu_name,

                                                 eu_city,

                                                 license_no,

                                                 eu_geo,

                                                 lic_type,

                                                 qty,

                                                 bookings_local,

                                                 bookings_usd,

                                                 acv_usd,

                                                 acv_local

                                            Resident TransactionTmp;

                                             

                                             

                                            DateTmp:

                                            LOAD

                                            min([ticket_open_date]) as min_ticket_open_date,

                                            max([ticket_open_date]) as max_ticket_open_date,

                                            min([ticket_solved_date]) as min_ticket_solved_date,

                                            max([ticket_solved_date]) as max_ticket_solved_date

                                            RESIDENT Tickets;

                                             

                                             

                                            LET vMin_ticket_open_date = peek('min_ticket_open_date');

                                            LET vMax_ticket_open_date = peek('max_ticket_open_date');

                                            LET vMin_ticket_solved_date = peek('min_ticket_solved_date');

                                            LET vMax_ticket_solved_date = peek('max_ticket_solved_date');

                                             

                                             

                                            Drop Table DateTmp;

                                             

                                             

                                            DateTmp:

                                            LOAD

                                            min([order_dt]) as min_order_dt,

                                            max([order_dt]) as max_order_dt,

                                            min([maint_start_dt]) as min_maint_start_dt,

                                            max([maint_start_dt]) as max_maint_start_dt,

                                            min([maint_end_dt]) as min_maint_end_dt,

                                            max([maint_end_dt]) as max_maint_end_dt

                                            RESIDENT TransactionTmp;

                                             

                                             

                                            LET vMin_order_dt = peek('min_order_dt');

                                            LET vMax_order_dt = peek('max_order_dt');

                                            LET vMin_maint_start_dt = peek('min_maint_start_dt');

                                            LET vMax_maint_start_dt = peek('max_maint_start_dt');

                                            LET vMin_maint_end_dt = peek('min_maint_end_dt');

                                            LET vMax_maint_end_dt = peek('max_maint_end_dt');

                                             

                                             

                                            Drop Table DateTmp;

                                            DROP Table TransactionTmp;

                                             

                                             

                                            LET vStart = $(vMin_ticket_open_date);

                                            LET vEnd = $(vMax_ticket_open_date);

                                             

                                             

                                            IF $(vStart) > $(vMin_ticket_solved_date) then

                                            LET vStart = $(vMin_ticket_solved_date);

                                            ENDIF;

                                             

                                             

                                            IF $(vStart) > $(vMin_order_dt) then

                                            LET vStart = $(vMin_order_dt);

                                            IF $(vStart) > $(vMin_maint_start_dt) then

                                            LET vStart = $(vMin_maint_start_dt);

                                            IF $(vStart) > $(vMin_maint_end_dt) then

                                            LET vStart = $(vMin_maint_end_dt);

                                            ENDIF;

                                            ENDIF;

                                            ENDIF;

                                             

                                             

                                            IF $(vEnd) < $(vMax_ticket_solved_date) then

                                            LET vEnd = $(vMax_ticket_solved_date);

                                            ENDIF;

                                             

                                             

                                            IF $(vEnd) < $(vMax_order_dt) then

                                            LET vEnd = $(vMax_order_dt);

                                            IF $(vEnd) < $(vMax_maint_start_dt) then

                                            LET vEnd = $(vMax_maint_start_dt);

                                            IF $(vEnd) < $(vMax_maint_end_dt) then

                                            LET vEnd = $(vMax_maint_end_dt);

                                            ENDIF;

                                            ENDIF;

                                            ENDIF;

                                             

                                             

                                            LET vStart = floor(YearStart($(vStart)));

                                            LET vEnd = floor(YearEnd($(vEnd)));

                                             

                                             

                                            LET NumOfDays = $(vEnd) - $(vStart) + 1;

                                             

                                             

                                            Date_src:

                                            LOAD

                                            $(vStart) + Rowno() - 1 as DateID

                                            AUTOGENERATE $(NumOfDays);

                                             

                                             

                                            [MasterCalendar]:

                                            LOAD

                                            DateID as order_dt, // just in case

                                            date(DateID) as CalDate, // it will be in format defined in your SET DateFormat=, or in your system format

                                            day(DateID) as CalDay,

                                            week(DateID) as CalWeek,

                                            month(DateID) as CalMonth, // simple month name; it is dual - numeric and text

                                            dual(month(DateID) & '-' & year(DateID),

                                            year(DateID) & num(month(DateID), '00')) as CalMonthYear, // Month-Year format, dual

                                            year(DateID) as CalYear,

                                            weekday(DateID) as CalWeekday,

                                            'Q' & ceil(month(DateID)/3) as CalQuarter, // in format Q1, Q2, Q3, Q4

                                            year(DateID) & ceil(month(DateID)/3) as CalQtrYear // Qn-Year, dual

                                            // and whatever else you may want here...

                                            RESIDENT Date_src;

                                             

                                             

                                            Drop Table Date_src;