10 Replies Latest reply: May 27, 2017 4:30 PM by Koen Bal RSS

    Better Data Model

    Jozef Baca

      Hi, i would like to know as many opinions as possible about, what is the best approach to create data model in QlikView. What is better for QlikView performance?

       

      1. Concatenate different fact tables into one big fact table and after that join all dimensions to this table and have a nice star schema. Add source column, to distinguish different data sources. Do set analysis to select from big fact table only those fact that belongs to first resp. second fact table, that are concatenate now.

      2. Load each fact table as separate table in data model and connect shared dimensions trough linked table and other specific dimension directly to appropriate fact table.

       

      I am asking this, because i am about going to build QlikView app with various sources, planned is 5 different sources with data about same business, but different parts of it, dfata are barely connectable and there is different granularity. This fact tables have many shared dimensions like country, customers, product and so on, but each one has few specific dimensions. So before i start, i have to find out best approach to have model, that is readable and suitable for to reach best performance.

       

      Thanks

        • Re: Better Data Model
          Michalina Kuczynska

          Hi,

           

          Both options mentioned by you above are absolutely valid and both of them have slightly different purposes. It basically all comes to a couple of things:

          1. What are the volumes of your data?

          2. You mentioned something above about granularity - so, what's your lowest granularity? If you have different granularity, it might happen that following approach 1 you would end up with duplicated rows and plenty of nulls and performing sum() or count() which require set analysis, distinctness, aggr() etc from the very beginning and it some occasions you might be adding some extra complexity to your model, which could be omitted.

          3. Linking your data through link table - ask yourself a question what are the associations between your data subsets? It might happen that your only common dimension is for example time/date and/or maybe something else. Then is good to have your link table, with all dimensions used as filters in front end in it.

           

          Btw - in situation when we were to build a report to gather data from different parts of the business where we had plenty of mutual dimension (and the same granularity in most of the occasions) we decided to have a big facts table and star schema. On the other occasion when we were asked to build a report gathering data that didn't have any similarities and different granularity - we decided to build a link table, where our key was only the time/date.


          But at the end - it all depends on the business requirements of your report, so speak to your users and try to get an idea how the data is linked.

           

          hope this help.

          • Re: Better Data Model
            Sara Famiglietti

            Hello,

             

            See the attached file from page 15 to page 22.

            It may help you.

             

            Regards

             

            Sara

            • Re: Better Data Model
              Sachin duganavar

              Hi,

               

              I Would prefer concatenating tables to create fact table and also flagging them wherever needed.Which leads to start schema aswell as flags created helps a lot to play with parts of concatenated bits..

               

              Sachin

              • Re: Better Data Model
                Koen Bal

                Hello Jozef,

                 

                I guess you already finished your datamodel. But should you be interested in some tools to help checking your datamodel while building it. I created a "Key check module" (a set of objects and a script subroutine) to check the links between tables. You can read a short post about the "Key Check Module" here.

                Hope you find it useful.

                 

                Have fun Qlik'n, Koen

                QlikViewModules.com