3 Replies Latest reply: Feb 21, 2017 9:42 AM by Michael Tarallo RSS

    How does Qlik Sense Cloud index data?

    Victor Olade

      I'm a complete newb to QlikSense.

       

      That said, I need to better understand how Qlik Sense Cloud Indexes data in order to make a certain decision.

       

      I'm using a .csv file that will be updated every hour as a data source in QlikSense Cloud and I need to decide whether this .csv file would a) be replaced each day with current data for that day only or b) be appended with each new day so that the .csv file has historical data at least up to a month.

       

      I prefer the first option but I'm not sure whether it would limit me to only the data that is on that excel sheet for that day, or whether Qlik has a way of indexing all the previous day's data so that I can access historical data even though it is no longer on the current version of the .csv file it is connected to as it's data source.

       

      Please advise.

       

      Thanks.

        • Re: How does Qlik Sense Cloud index data?
          Michael Tarallo

          Hi Victor - welcome to Qlik Sense Cloud, thanks for your question. Allow me to address your questions individually.

           

          I'm a complete newb to QlikSense.

           

          There are a lot of great resources here where you can learn a lot in little time. Just note that these are not a substitution for official Qlik education. You may want to check out our continous classroom to learn Qlik Sense on your own time and pace.

           

           

          I'm using a .csv file that will be updated every hour as a data source in QlikSense Cloud and I need to decide whether this .csv file would a) be replaced each day with current data for that day only or b) be appended with each new day so that the .csv file has historical data at least up to a month.

           

          In direct answer to your question - you would replace the file each day - manually and reload manually if using Qlik Sense Cloud basic (free) - Qlik Sense Cloud Business (subscription) - you would still upload new file manually but there is an automated reload process that you can schedule with the app. You would still replace the entire file with the new one. You can see that here at the 10 min mark: Qlik Sense Cloud Business - Orientation Overview - YouTube

           

          (We are looking at ways to automate the upload to the Qlik Sense Cloud import data folder for Qlik Sense Cloud Business.)

           

          First let me state - that this is a very common practice and has worked well with many of our customers. I will try to add as much information as possible so it does not become confusing. Please ask me for clarifications if so. Being we do not query a database (or data source) directly during analysis, we require the data to be loaded into memory when the app is loaded. This allows a very fast and efficient process when analyzing data. So in short, we take the data from the source whatever/wherever that may be and load it into the app. Then the visualizations are created, and you can interact and aggregate metrics on the fly experiencing immediate speed of thought response time. All the charts are wired together automatically so there is no need to manually link any of the visualizations, revealing relationships among all the dimensions.  Now if there is new data to be loaded and there isn't a need for effective date reporting (point-in-time analysis, needed date stamp records, version history of the dimensions or metrics, etc.), some customers just reload the new data from the updated source (file, rdbms, etc.) without incremental updates. If there is a need for history then one can use incremental loads with Qlik, where the original first load data is usually stored in a file (we call it a .qvd file).  This .qvd file is a representation of the in-memory data mode on disk. It also represents a vert fast and efficient way to load data into a Qlik Sense app (especially if using incremental loads). Knowing this,  you have options on how you want to perform the incremental load, either new data is simply appended (incrementing the key, row id etc.) or there is a more advanced approach using change data capture techniques or slowly changing dimension procedures. Techniques such as this are usually done in the script of the app.

           

          You can read more about that here:

          https://help.qlik.com/en-US/sense/1.1/Subsystems/Hub/Content/LoadData/UsingQVDFilesIncrementalLoad.htm

           

          With Qlik Sense products there are a few editions. Qlik Sense Desktop and Enterprise (server) - can perform incremental updates and load only the new, changed data etc.- adding a new row, deleting, updating etc. So you can keep the historical data. This can be automated with Qlik Sense Enterprise using our management console and task scheduler. The app you create would have the script logic to perform this action. If the data is in a file and being updated a 3rd party automated systems would most likely put the updated data file in the appropriate folder on your network for Qlik Sense to pick up and process. When using incremental updates, for0speed and efficiency you are NOT loading the data into the app directly, you are loading the data into a .qvd file, in which the .qvd file is then used to load into the app. You most likely would have another app (like a qvd loader app) that updates the .qvd file(s) and that is the app you scheduled. Then you schedule the other app - which simply  is loaded directly from the QVD file.

           

          I prefer the first option but I'm not sure whether it would limit me to only the data that is on that excel sheet for that day, or whether Qlik has a way of indexing all the previous day's data so that I can access historical data even though it is no longer on the current version of the .csv file it is connected to as it's data source.

           

          That being said - there are automated, scheduled and efficient ways to do this - but they require a license and some knowledge on how to implement them. You could do this with Qlik Sense Cloud Business as well - but you would need to manually upload the new file to replace the existing one prior to the scheduled task that performs the incremental load. You would also then have the app used for analysis schedule to refresh from the QVD. So in all you would have 2 apps - one app (with no visualizations) to perform the incremental load and update the .QVD and one aoo (with the visualizations) to load the data form the QVD. You could do it in one app as well - but it keeps it cleaner and makes it more manageable to separate theses process.

           

          --------------NOW----------------

           

          If you want to do it with Qlik Sense Cloud Basic (free) - you could do the following:

           

          So if the new file DOES NOT HAVE the previous day's data and the column structure is the same there are a few things you can do. You can rename the new file and import to the data folder, then you can load that data with the new file name into the same app and it will automatically append the new rows to the existing data, as long as the keys are not in the old data.

           

           

          original_data.txt
          id,age,name
          1,23,mike
          2,34,sam
          3,56,linda
          
          newdata.txt
          id,age,name
          4,67,gma
          5,90,gpa
          6,34,joe
          

           

           

          The x2 - shows that there are 2 separate tables with the same structure. These are concatenated tables:

           

          Concatenating tables ‒ Qlik Sense

           

          When ADD Data - you DO NOT SELECT PREPARE DATA for each file - You select Load data and finish - otherwise it will attempt to fully-qualify the column names and try to make them unique.

           

           

          The code that is generated:

           

           

           

          So to keep it simple, you would just manually upload new files with new file names and technically could cut and paste the code in the script and change the FROM section to represent the new name. Then it would reload the data from all 3 files, etc.

           

          Sorry if this was too long, but I wanted to give you a variety of options and arm you with the knowledge of how these things could work.

           

          Let me know how you do.

           

          When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

           

          Regards,

          Mike Tarallo

          Qlik