By default, each reload procedure will drop out all data from memory and reload either files and tables with a full scan back to the memory. Small data tables or files has no impact over this process, because QlikView will fill up tables with lower volumes of records. But, in all reload process all tables are read from sources, no matter if data was updated or not. This means, tables that has no changes will be read again and again because default process is a full reload. Each time a developer (or business user) press
CTRL + R a full reload is in turned on. It's the same process when a job is scheduled at Qlik Server.
Unless all data need to be reload, sometimes read only data changes frequently is a best way to reduce load data time. For example, tables storing columns about Country's name, States, Cities could be out of full reload. Instead of full reload, partial reload could be turn on. Probably, not all Qlik developer or business users listened about partial reload, because it's not a common subject along documentation or communities. But, in fact, exists a way to tell to QlikView to reload just some tables while others one will stay in memory. So, when a reload is executed not all tables will be deleted from memory.
It's important to put in mind, incremental load isn't partial reload, because incremental load means get updated or inserted data from data sources and, in this case, all tables will be dropped from memory and filled again. While incremental load will get data from data sources, partial reload will ignore some tables based on instructions in script. Ignore means not read tables from data sources and, at the same time, not delete tables from memory while script is run. To do that, a prefix command need to be written before LOAD statement. It's the
When QlikView find the
REPLACE in script, will act differently from full reload, depends on type of process turned on. That mean, not only REPLACE need to be used but the process should be fired differently. In QlikView two commands are available to execute scripts. First one is common full reload fired by
CTRL + R shortcut. Another one is partial reload that will run when user press
CTRL + SHIFT + R in QlikView Desktop, or when selecting
Partial Reload from
Let's go to understand how QlikView get data from data sources without remove some tables from memory.
Just to remember, load data to QlikView means run some instructions based on script created automatically by import wizards or typing manually commands to get data. In general way,
LOAD command is the most common statement used to get data from sources, with
SELECT working together or alone. The following examples are some techniques to read data from different data sources. All of these commands are using a default
LOAD statement. Not one is prepared to use partial reload.
// Read data from .QVD file LOAD * FROM [$(vCaminhoOrigem)\Objeto_Fiscalizado.QVD] (qvd); // Read data from Excel file LOAD * FROM [Ouvidoria_v3.0.xlsx] (ooxml, embedded labels, table is FATO); // Read table from database LOAD * ; SQL SELECT * FROM PROCESSOS_PRIM;
Using this default behavior, all reload process is run and all tables will be dropped from memory to be filled again with all data from data sources. Of course not all tables will be updated all the time, so that load all data from all tables can not be the best choices, especially if data tables has millions or billions of records unchanged. In other cases, tables has data updated in periodical times, monthly or by week. But, in the same dashboard other tables are updated daily. So, when scheduling job to refresh data sources, all data tables will be read while some of them will take time unnecessary, because no new data are available.
For this situations is a good idea use
REPLACE statement with partial reload procedures. To use partial reload and its variations is necessary know basic syntax of this instruction. As a prefix, REPLACE statement will be written before LOAD command, of course. Take a look of this syntax in two ways.
REPLACE LOAD * ; REPLACE ONLY LOAD * ;
To test partial reload is necessary fill
LOAD statement with
REPLACE command. To test purpose, create a new project and open script editor pressing
CTRL + E. Copy the following statements and paste in new project. Notice we have three different statements, first of all not using
Tab1: LOAD RecNo() As ID, Now() As RecordAge AutoGenerate 1000; Tab2: REPLACE LOAD RecNo() As ID, Now() As NewAge AutoGenerate 1000; Tab3: REPLACE ONLY LOAD RecNo() As ID, Now() As LastAge AutoGenerate 1000;
RELOAD is executed pressing
CTRL + R, full reload is executed and previous instructions will be run differently. With full reload, first statement above will delete table from memory and reload it reading all data from data source. For second command, using
REPLACE alone will take the same effect of use just
LOAD statement when full reload is performed. That mean a full reload will be executed dropping table from memory and reading all data from data source even if
REPLACE is used. But, for third statement above, where
REPLACE ONLY was defined, the full reload process will ignore any data load, remaining that table in memory, with no changes. Therefore, this statement, will not connect to table in data source to refresh data.
In the other hand, when a partial reload is carried out, any
LOAD statement without
REPLACE is ignored. Partial reload has a different procedure from QlikView Desktop, because its need to be performed pressing
CTRL + SHIFT + R shortcut. But, not only this different procedure will play the partial reload, because is necessary back to main window of QlikView Desktop. So, if you use this shortcut from script editor, the process is always a full reload, no matter if
CTRL + R or
CTRL + SHIFT + R is used. That's why you need back to main QlikView Desktop window before start a partial reload. The following table, statements are used with different reload procedures (full or partial).
Let's test this behavior below.
1. Copy the previous statements as a sample in a new qlikview document.
2. Save your job and go back to main window (go out script editor).
3. Use full reload through
CTRL + R shortcut. Take a look on results below. Take a look just two tables are loaded.
4. Now, use partial reload pressing
CTRL + SHIFT + R from main window. Only the last two statements are executed.
As you can see, when full reload was carried out the third command using
REPLACE ONLY LOAD was left out. In the other hand, when partial reload was in place the first command was ignored. But, what happens with tab1? Nothing. It is left in memory. So, you can decide the better approach when millions of data are present and you just want load small tables without lost large data loaded previously.
Partial Reload at Server Side
At Server side reload procedures can be full or partial, but depends of infrastructure available. When configuring a schedule to reload data for dashboards is important define what kind of reload is going to do. Full or partial. There aren't any specific point to schedule a job to full reload, but to partial is necessary have installed QlikView Publisher Server, a QlikView Server service.
Publisher is product sold separately and junt if you have one is possible to schedule jobs to partial reloads. Otherwise, only full reload is available on QlikView Enterprise (or Small) Servers. To schedule a partial reload just open QMC (QlikView Management Console) and navigate to following menus to locate all options for schedule tasks. Remember! To use QMC is necessary be QlikView administrator, that mean have login in QlikView Administrators Windows Server group.
Documents ► Source Documents ► ReloadEngine
In Publisher Server structure you can see schedules prepared before as well create new ones. Just click on plus signal (+) at begin of dashboard's name to locate all schedules configurated. In this example,
qknow_secionaccess_pub_v1.0.qvw has a job to reload data (run scripts). When clicking over scheduled task, the right pane side is updated with a lot of options split by tabs. One of these tabs is
Reload where contains informations about this procedure. To turn on partial reload just select
Partial Reload option under
- Restricting Data for User Login (Section Access)
- How to Use Variables
- Incremental Load Routine
- How to Configure a Multi Language Dashboard
- Transforming Images in Charts
- Highlighting bars on Chart
|Need more? Mail Us!|