12 Replies Latest reply: Mar 20, 2016 6:32 PM by Bella Mae RSS

    Sense ETL Methodology

    Torben Seebach

      So in QlikView we used to have a QVW that creates library of QVDs in a folder. My question is what are we planning to do in Qlik Sense? What will become our new best practices?

       

      1. Should we create a QVF called Extract, and then put this as the first job in our Task list?
      2. Should we create a separate Stream for these files. And who should access these?
      3. I would also create a separate data connection to allow Self Service users to access these files.
      4. Is there a preferred location and structure to store QVDs?
        • I assume we would prefer to place them on a separate drive or SAN
        • I would suggest a structure that is somewhat similar to the QlikView Deployment Framework
        • But is there a Best Practice in place?
      5. Is there a point in using a Content Library for QVDs? Because it can be synced across clusters, it could be convenient, but I'm unsure how it can handle large frequently updated files.

       

      My answers currently are (And please challenge me and write your method):

      1. Create a QVF called Extract and Transform, to match a 2 tier ETL process. Make these run as the first jobs in the task list.
      2. Place these files in 2 separate streams, with admin rights
      3. Create multiple data connections, to reflect roles that need access these files
      4. I'd store QVD's on a separate drive or SAN. Like
        • D:\Qlik\Qlik Sense\Default\Data\Staging < First Tier
        • D:\Qlik\Qlik Sense\Default\Data\Model < Second Tier
          • Default refers to a role ie. a Department or Function
      5. No (I might be wrong).
        • Re: Sense ETL Methodology
          Torben Seebach

          No one?

           

          I hope to speak with Magnus Berg soon and if he has some value input I post it here.

          • Re: Sense ETL Methodology
            Michael Tarallo

            Hello Torben,

             

            I know it has been a number of months since you posted this, I actually have just been made aware of this post form a colleague here at Qlik. Have you made any progress on the items, best practices mentioned in this thread? Have you sought information elsewhere in regards to your questions? I am curious. If not, I will attempt to gather this information for you, or point this in the direction of some of our consultants to see what they think.


            Regards,

             

            Mike T

            Qlik

              • Re: Sense ETL Methodology
                Rene Iversen

                This thread really ought to have some answers - all brilliant questions !

                 

                I have som thoughts and ideas myself, but would really like to hear from more experienced consultants and/or Qlik.. This is so central, i can't see how it can be ignored for so long

                • Re: Sense ETL Methodology
                  Waqas Saeed

                  Hi Michael,

                   

                  Did you manage to gather information on this topic? I really need to know how we can have separate ETL, QVD and Presentation layer in Qlik Sense.

                   

                  Thanks

                   

                  Waqas

                    • Re: Sense ETL Methodology
                      Philip Doyne

                      Hello:   I am sure we are not supposed to sell on this forum - I have been a member for a long time and a Qlik Partner for 10 years so I should really know (may I beg your forgiveness please moderator! - this is not a habit nor does it form any part of our marketing strategy).  I could not resist responding to this especially in view of the dearth of responses.   At QlickiT we developed the QlickiT Methodology in 2009 and we use it to ensure that all our consultants work in the same way so it is easy to pick up on other peoples work.  The methodology covers:  3 tier architecture; standardised folder structure; standardised format for table/QVD and field names; concatenate or link table model; simple integration of data from multiple data sources to common dimensions and mapping to common dimensions; integration of section access and data reduction; simple consolidation of data from different sources (eg different ERP's in different divisions).    We teach this to our clients and training courses open to the public.  We have designed these courses to teach the methodology and Qlik at the same time so you leave with an idea of where to start your life with Qlik.    The data/ETL bit is, of course common to Qlik and Sense because the script is the same.    At the moment we are still running the courses based on Qlik because we have yet to finish converting the material to sense but we have implemented in in Qlik Sense with genuinely no changes (other than the data connectors but you can still go to "legacy mode".    You will see I could not publish all the material on this forum but there are more details on our website where you can get details of how to contact us.   This will definitely solve all your ETL problems - and modest though we are we do know what we are doing!   As it happens - we do have courses running in Rotherham, Yorkshire UK very soon ie next week and we unexpectedly have a couple of places availble if your need is urgent - (Mon 1st to Wed 3rd Feb 2016).   Do get in touch www.qlickit.co.uk.   A brochure can be obtained here:

                      http://qlickit.us7.list-manage.com/track/click?u=c561aed217697d04d8ade5df0&id=b2f3be378c&e=249013a5ce

                      Thanks Philip

                    • Re: Sense ETL Methodology
                      Pablo Labbe

                      Hi Michael,

                       

                        Do you have any update on this subject ?

                        • Re: Sense ETL Methodology
                          Michael Tarallo

                          Hi Pablo et al, I have attempted to get some more insight for you on this. At the moment, I would assume it is done the same way with Qlik Sense as it was with QlikView. Creating separate .qvf files to create the QVD layers. Also I know that mbg has made some improvements to the Qlik Deployment Framework to include Qlik Sense. Let's see what Magnus has to say as well.

                           

                           

                          Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

                           

                          Regards,

                          Mike Tarallo

                          Qlik

                      • Re: Sense ETL Methodology
                        Magnus Berg

                        Hi Torben, and sorry for late reply. Did not see this until Michael informed me today. As Michael responded you could do the same in Qlik Sense as in QlikView. In QDF you use containers as store for the qvd files (in your case Default is a container). A container is a security boundary containing content, this could be QVD, csv, xls, variables, scripts and more that belongs to each other. In Qlik Sense container is attached using a data connection (LIB), these are secured using QMC. Containers include several folders, each folder has a correlating global variable, that is used in the load script to point out where to put the files. This is a nice way to create generic scripts that can be moved between environments (and from QlikView) without breaking code.

                        Hope that this helps

                        Regards

                        Magnus


                        To your questions:

                        1. should we create a QVF called Extract, and then put this as the first job in our Task list?
                          Better to have a name standard for extract QVF (could be Data source_Extract)
                        2. Should we create a separate Stream for these files. And who should access these?
                          Create a stream with the container name of the QVF's that relate to this container
                        3. I would also create a separate data connection to allow Self Service users to access these files.
                          Depending on the security you give the connection string, for me I think qvd extracts is better for self service.
                        4. Is there a preferred location and structure to store QVDs?
                          As mentioned above use QDF
                        5. Is there a point in using a Content Library for QVDs? Because it can be synced across clusters, it could be convenient, but I'm unsure how it can handle large frequently updated files.
                          No, content library is not a good place to put QVD files


                        • Re: Sense ETL Methodology
                          Bella Mae

                          The organization I work for uses a separate stream for ETL QVFs oddly enough called ETL.  We have a QVF for extract which builds QVDs and a QVF for transform which uses the extract QVDs to build the QVDs we use in our production QVFs.