Partial Reload

De Qknow
Ir para: navegação, pesquisa

Introduction

QvdFiles1.PNG

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 REPLACE instruction.

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 File menu.

Let's go to understand how QlikView get data from data sources without remove some tables from memory.


Data Load

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 * ;


Partial Reload

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 REPLACE.

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;

When 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).

PartialReload2.PNG

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.

PartialReload3.PNG

4. Now, use partial reload pressing CTRL + SHIFT + R from main window. Only the last two statements are executed.

PartialReload4.PNG

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
PartialReload5.PNG

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 Script Setup.

PartialReload6.PNG

PartialReload7.PNG






More Posts


Envelope01.jpg
Need more? Mail Us!
contact@qknow.com.br



Main Page