4 Replies Latest reply: Aug 14, 2017 4:34 AM by Jose Miguel Vilaplana Pascual RSS

    Concatenation of tables with different types of facts

    Sofia Martinez

      Hello, after reading several conversation about this subject, i still have some questions, unfortunately.

      Let us suppose the following scenario:

      I want to build a data model for a company that offers services through a card. This card is activated associating it first to a person (table "cards").  Packages containing services are sold (associating them to the card) (table "Sales") and the user can use these services in different places (table "Usages").

      Therefore, the information that we currently have is:

      2017-08-09_11h33_41.png

      Option 1:

      A unique table of facts where all the actions, which can be done using the card, are stored. These actions can be differentiated by means of a new field (Type Fact) on the table.

       

      2017-08-09_11h35_07.png

       

      all the tables share common fields such as “Center, Cards… despite we have Dimension-Tables such as“Center” or “Card”  und they are with the Fact-Table associated.

      But in this case I find a problem, maybe because of my inexperience or because it is not an optimal solution.

      The problem is that I can not connect facts of different types. For example:

      How many times the cards activated on April 2016 have been used?

      Is it smart enough to create a field called Type in order to distinguish all the records that come from other tables? Or is it absurd, on the contrary?

      2017-08-09_11h37_11.png

       

      Option 2:

      Several Fact Tables:

      2017-08-09_11h37_57.png

        • Re: Concatenation of tables with different types of facts
          Marcus Sommer

          If the amounts of your data aren't quite huge (of course related to the resources of your environment) I would probably go with option 2 but avoiding the synthetic key by renaming some of the fields, for example Center into [Center Sales] and [Center Usage] and doubling the dimension-tables to them if really necessary.

           

          Option 1 is in general possible because asymetric fact-tables aren't a problem in Qlik but your fact-tables seems to have far more differences than similarities so that it doesn't look very sensible. Are your data huge and/or you experienced performance issues you need to reduce the number of tables and to merge as many data as possible in one big fact-table - but in this case with mapping/joining and conatenating the tables.

           

          Quite common is also to create a link-table between the fact-tables which contained their common fields - but in my opinion it makes the things not really easier and link-table models have often a poor performance.

           

          - Marcus

            • Re: Concatenation of tables with different types of facts
              Sofia Martinez

              Hello Marcus, Thanks a lot for your answer.

               

              About Option 1:

              I have many records corresponding to usages of the card, that is, mainly the largest table is the Table “Usages”.

               

              I cannot test the performance issues because I'm testing my model with a small part of the data in order to be able to verify what works and what does not and to see if it makes sense and the data is displayed correctly.

               

              The three fact tables are different because they are not the same kind of fact. I thought of it as "Actions / Facts on the card", with fields in common:

              • What kind of action is carried out (FaktTyp)
              • Where the action is carried out (Center)
              • Exactly from which terminal the action ist carried out (Terminal)
              • When the action occurs (Date)
              • On which card (Card-id)

              But it is also true that there are other fields that have nothing to do because they are specific to each kind/typ of facts

               

              Option 2 and other alternative Options for Option 1:

              we have model with 3 fact-tables

              • Example 1: (Link-table)

              1.png



              • Example 2: (mapping)

              2.png

              we can also have model with 1 fact-table, with Join-table or Concat-Table

               

              Questions:

               

              I have 3 fact-Tables that are different und sharing some dimensions but no others und the 3 fact-tables are related.

              - Doppel Dimension-Tables sind optimal?

              - Facktable with a lot of columns is optimal?

              - with only 1 Facttable can i make relations between the files of the table?

              - If I keep the 3 tables without grouping it is better to make a link table or put the data of the dimensions in the fact tables?

                • Re: Concatenation of tables with different types of facts
                  Marcus Sommer

                  It's quite difficult to estimate which approach might be the most suitable in your case. I would tend in the direction of option 2 although it looked easier to connect the tables with some combined-keys (concat of several key-fields) or to create a link-table.

                   

                  Each of the methods have their benefits and disadvantages and unfortunately there is no optimal datamodel: A perfect datamodel?

                   

                  If I get unknown data with certain requirements which I couldn't really judge at the moment I would develop two or even three datamodels parallel - starting with a small dataset and loading the tables in the easiest way and then using some pivot-tables and tableboxes to check if the data look plausible and then trying to apply the most challenging view which is required - and this quite roughly without implementing a nice looking gui or integrating it in the environment-structures.

                  If I get the needed results I increase the amount of data to the real one and if it is performant enough I build a real application. If it don't work I need to adjust my doings and maybe switch to a different model.

                   

                  It sounds a bit like trial & error and yes it is - but a systematically one. The more you understand the data and the requirements the more you could avoid the trials and just thinking some minutes/hours and to sketch the essential points on a white paper and afterwards is just some typing work (and tests of course).

                   

                  - Marcus

              • Re: Concatenation of tables with different types of facts
                Jose Miguel Vilaplana Pascual

                Hi Sofia,

                 

                Just for the first example, if the card is activated the first time that is used, why you don't create a flag in the usages fact table? It would be easier that use another fact table. (if not, you can create a map table and create the flag).

                 

                I always use one single fact table, and normally is a concatenation of several ones and I never had been problems to obtain the data.

                 

                Regards