8 Replies Latest reply: Oct 12, 2017 2:42 AM by Marcus Sommer RSS

    What are your comments in terms of performance for this type of data model?

    Daniel Wardzynski

      Will renaming the main Key in each table actually improve the performance?

        • Re: What are your comments in terms of performance for this type of data model?
          Marcus Sommer

          I suggest to develop the datamodel in the direction of a star-scheme. It's recommended as the best compromize between handling and performance. Many more could you find here:

           

          Get started with developing qlik datamodels

          Advanced topics for creating a qlik datamodel

          More advanced topics of qlik datamodels

           

          - Marcus

            • Re: What are your comments in terms of performance for this type of data model?
              Daniel Wardzynski

              Marcus,

               

              I have the same question as Stefan. In what way would changing the key field name for each table improve the performance? I understand the best practice of data modelling in Qlik, but would like to understand the performance advantage when making this change. I mean, if they all use the very same key, then maybe they could all be joined instead, table by table, which would results in an un-normalized table which would improve the performance, even if the size would be bigger. Am I missing something?

               

              Would a linking table help with the performance?

                • Re: What are your comments in terms of performance for this type of data model?
                  Marcus Sommer

                  Improving the performance respectively optimizing an application could have quite different directions - load runtimes, RAM and storage space, opening times, UI response times - and you couldn't optimize it in all ways at the same time. Your biggest bottleneck by your system-resources and/or related to your requirements will determine which optimizing approach will have the biggest effect.

                   

                  If you have no performance issues you might just keep this datamodel probably directly pulled from a database without bigger changes. But if there are performance issues you should reduce the number of tables by joining, mapping and/or concatenating the tables together and to de-normalize the datamodel which in general better worked in qlik than full normalized sql-models. Renaming keys/fields is just one working step to reach the above mentioned transforming and not alone the solution.

                   

                  To your last point about a link-table datamodel - from a performance point of view it's most often the worst possible approach because the link-tables could become quite large and often larger as the fact-tables itself - this meant I wouldn't recommend such an approach.

                   

                  - Marcus

                    • Re: What are your comments in terms of performance for this type of data model?
                      Daniel Wardzynski

                      The bottleneck that I am currently dealing with is the slow performance of the UI, which I want to improve be removing a lot of unused fields (when the new doc analyser for Qlik Sense is out), but also improving the data-model. And I was a bit surprised when I saw the data model that I pasted, which actually is not a data model but merely a bunch of tables sharing the same primary key. I was also on the thinking path where I wanted to join all these tables one way or another and forming a large de-normalised table instead.

                       

                      I have only used link tables when connecting actuals with budgets, due to the different dates involved, but I was not sure if using a link-table in between all these tables would improve the performance. Is it because the linking table would need to have a pair of the renamed key for each table, which would create a very big amount of rows? Feel free to clarify this for me if you have time.

                       

                      Thanks Marcus!

                        • Re: What are your comments in terms of performance for this type of data model?
                          Marcus Sommer

                          I think you could remove a lot of fields (at first by commenting them out) even before the document analyzer for qlik sense is released by just leaving those one which you noticed within ui. Are there really some missing then you could comment them in again.

                           

                          Normally a loading-process should start with: load F1, F2, F3 ... with explicitly specified fields and not with a load/select * from ... especially if the data comes from databases which usually contain a lot of fields which have only a meaning on the database-side respectively to the processes which are stored with it and which have no added value from a analysis point of view, for examples various flags to control anything on the input screen or any table-id's and quite probably a lot of old and since years unused stuff.

                           

                          Beside this it's important to differ the tables into fact-tables with measures and dimension-tables with just describing informations. The dimension-tables are often not very important from a performance point of view and could be handled later when there are resources to fine-tune the application. Important are the fact-tables and they should be if possible merged by joining/mapping and often much easier with a concatenate (or union in sql). In qlik you could even concatenate tables which are quite different to eachother and they perform usually quite well.

                           

                          This meant you could concatenate the actual- and the budget-data together. A different kind of granularity could be often solved with quite simple methods. For example it's quite usual to have actual data on a daily-level on budget-data on a monthly-level which could be with makedate() and similar functions converted into a real date.

                           

                          Your mentioned link-table should be the last approach when all other ideas are out especially if there are any performance-issues.

                           

                          - Marcus

                            • Re: What are your comments in terms of performance for this type of data model?
                              Daniel Wardzynski

                              Marcus,

                               

                              Thank you for another extensive reply from your side.

                              Yes, I have done the manual drop of fields, even if it can be very time consuming, but it is indeed a good approach, before the Doc Analyser existed, or even the Governance Application.

                               

                              I do understand the basic principles of a Kimball data-modelling approach that Qlik is based on.
                              I was just trying to understand from a technical point of view why a linking table would be a less efficient solution from a performance perspective.From my understanding you would create a very big table with all the matching key pairs for each particular table. But shouldn't this allow fewer fields to be engaged in the live QIX association instead of all tables being engaged if you have this same generic key for all tables?

                               

                              Daniel

                    • Re: What are your comments in terms of performance for this type of data model?
                      Stefan Wühl

                      Why should renaming a key field in each table change anything?