12 Replies Latest reply: Sep 12, 2013 10:42 AM by GINES RUEDA RSS

    Large Fields in Fact Tables with millions of rows

    GINES RUEDA

      Hello devs,

       

      I want to ask about the suitability of having very large fields in QlikView e.g, a RTF with 4000 characters. Specially when this fields are inside fact tables with millions of rows showed inside a pivot table. I have noticed that in this case the performance goes down.

       

      Is there a document or guide that cover this? Or maybe a general document explaining that the issues with large datasets are not only with the number of rows but also with the number of fields and the size of them?

       

      The best approach for this that I have thought is to do a document chaining when the rows available go down of a limit.

       

      Any information on this topic will be appreciated.

       

      Thanks.

        • Re: Large Fields in Fact Tables with millions of rows
          Marcus Sommer

          Critical will be how many distinct values from this field exists because qv stored only distinct values. If the number of distinct values very large it will definitely have a big impact on the performance.

           

          Perhaps there are another possibilities. I assume that these "RTF" are descriptions to other fields. This could also be solved with hyperlinks to these files.

           

          - Marcus

            • Re: Large Fields in Fact Tables with millions of rows
              GINES RUEDA

              Hello Marcus thanks for answering.

               

              The thing is, as you said this fields are descriptions, so in some fact tables we have one distinct description per row.

               

              For example we have a transaction, with all the numerical data and some descriptions, this description fields are unique to the transaction.

               

              If there is a big impact in the performance as you said, do you know were can I get some kind of documentation to show to my bosses some evidence of this?

               

              Ginés.

                • Re: Large Fields in Fact Tables with millions of rows
                  Marcus Sommer

                  I don't know special documentations about this topic but I think you could find such informations somewhere in the whitepapers from qv. Generally need a string-char 1 byte and in your case is the result (as simply forecast):

                   

                  4.000 Chars * 1.000.000 Rows = 4.000.000.000 Byte or 4 GB only for this field and this will lead to not optimal until really bad performance.

                   

                  It will depend from your hardware and network ressources and the number of users. You should simply try if it worked or not. If not you need alternative solutions like hyperlinks or handling-szenarios in which only a part from this field will be loaded.

                   

                  - Marcus

                    • Re: Large Fields in Fact Tables with millions of rows
                      GINES RUEDA

                      Hello again Marcus,

                       

                      I was thinking about the handling scenarios and the possibility of a document chaining between a main report (the current one without the rtf large fields) to another that only shows a detailed view of subset of this (maybe only one), when the available selection chase that subset number.

                      This first approach of a document chaining count with a detailed view loading all the data from the database, but not allowing to selections or transformations with them (trying to reduce the ram consumption), then if you clean you selection and allows more than this subset, you will be sent to the main report again.

                      Were be much better trying to load less data or do a reduce of the data that the report load? I that even possible with a good performance? I was thinking altering the “Initial Data Reduction Based on Section Access” for example. Are there another solutions inside this approach?

                       

                      Or maybe you recommend another approach?

                       

                      Thanks,

                       

                      Ginés.

                • Re: Large Fields in Fact Tables with millions of rows
                  Christian Conejero

                  Hi.

                   

                  You may want to use DIRECT SELECT. It is a new feature in QV 11.2.

                  By using this feature you load information from big tables on demand only, that means, when a new filter is set or when a new calculation is needed.

                   

                  Use DIRECT SELECT and enjoy that relaxing café con leche in the Plaza Mayor.

                  • Re: Large Fields in Fact Tables with millions of rows
                    Christian Conejero

                    If your field is 4000 characters long, it is impossible to see more than one at a time.

                    With DIRECT SELECT you can bring one by one when needed.

                    To use it, install QV 11.2, latest version should be SR4. Type DIRECT SELECT instead of SQL SELECT and that's it.

                     

                    Activo:

                    load
                    '$(Compañia)'
                    as "Company",
                    Name as "Budget Name",
                    Activo as "Activo"
                    where Activo = 1;
                    SQL SELECT *
                    FROM "$(Compañia)$G_L Budget Name";

                     

                    Activo:

                    load
                    '$(Compañia)'
                    as "Company",
                    Name as "Budget Name",
                    Activo as "Activo"
                    where Activo = 1;
                    DIRECT SELECT *
                    FROM "$(Compañia)$G_L Budget Name";

                     

                    Of course you need an open connexion with your DB at all time. The same connector will do it if lines are open.

                    I havent tryed this feature yet. If you try it, please send some feedback.

                    Good Luck