By default, QlikView will load all data available directly to main memory based on scheduled tasks or manual actions for reload data. All calculation made to answer user navigation get data from memory where read process is very fast. As all know, QlikView/Qlik Sense has a very smart process to compact the data and use memory resources efficiently. However, sometimes data needs to be analysed as soon new records are inserted or updated from database. Of course is possible to reload data in short intervals, but depends how much data should be loaded.
When a lot of tables and data will be read again in very short intervals, is possible have no time enough between each reload procedure to maintain the dashboard updated. That is why the QlikView has the ability to get data directly from data sources every time a user interact with objects. The concept here is named Direct Discovery. This resource is used to get all measures directly from databases while maintaining dimensions loaded in memory. So, from this perspective, QlikView will work using a hybrid mode. That mean, a portion of data is loaded to memory while other one is got from databases (datasources) every single time users interact with dashboards.
This post will teach you how to implement Direct Discovery through
Direct Query instruction, that should be used at script level. Instead of use a traditional SQL SELECT instruction, use
Direct Query command to tell to QlikView get part of data from database to memory while other part will remain at datasource. The basic syntax for this instruction is shown below:
DIRECT QUERY DIMENSION dimension_fields_list MEASURE fields_for_expressions DETAIL field_list FROM table
If you wanna test this procedure is possible to load data from files available at links below. The first file is a MDB (Microsoft Access Database) used to implement the Direct Discovery procedure. The other file is a XLS (Microsoft Excel) that will be loaded at all to QlikView memory and it will associate data from Direct Discovery table. This procedure will let you understand how associative data model is capable to interact with data in memory and data from datasource at same time.
- qknow_msaccessdata_v1.0.mdb is a customer database for sales company.
- qknow_categoryfile_v1.0.xls is a category table for products.
Using Direct Discovery Instruction
Ensure you have both files or a database available to test this procedure. First step is connect to database using
ODBC CONNECT instruction. Up to now no new procedure is need. With following traditional instruction, QlikView will connect to database file in Microsoft Access format. Make sure you have updated the path for the file where you saved it.
ODBC CONNECT32 TO [QWT;DBQ=Q:\data\qknow_msaccessdata_v1.0.mdb];
After connected successfully copy the following command to get data from table using Direct Discovery procedure. Remember that all field listed on
DIMENSION parameter will be loaded to QlikView memory as a normal process. In the other hand, any field specified at
MENSAURE parameter will remain at database level. So, no data will be loaded in QlikView memory.
DIRECT QUERY DIMENSION Categoria As CategoryID, FornecedorID As SuppilerID, ProdutoID As ProductID MEASURE PrecoUnitario As UnitPrice, CustoUnitario As Cost, QuantidadePorUnidade As Quantity, UnidadesEmEstoque As QuantityInventory, UnidadesPorPedidos As QuantitySelled DETAIL NomeProduto As ProductName FROM Produtos;
Using the previous command QlikView will load all data from fields defined at
DIMENSION parameter with a normal process. This means all data from these fields are loaded in memory. But, all data from fields defined at MENSURE level will remain in database. In other words, these data will not be loaded to QlikView memory model. Save your job up to now and execute the script using
CTRL + R shortcut. After that, open the Table Viewer and click with right button over table shown. It's possible to see all fields, but
Preview command isn't available.
More one difference. When Direct Discovery is in place no
MENSURE fields are shown in dimension fields when a new chart is created. Only fields from
DIMENSION parameter are shown. But, this no mean that all fields aren't available. When creating a new chart is possible select any field from
DIMENSION parameter, but no fields from
MENSURE is available. This behavior can mistake you. In fact, all fields are available, but each group in its respective place.
DIMENSIONS are available for dimension purpose, while
MENSURE are available for expressions.
MENSURE fields aren't shown, all of them are available to be used at formulas, variables and expressions. That's why is possible enter a new expression just typing fields name. With dimension loaded in memory and expression getting data from database, QlikView will show to business users all data updated recently. Every interaction with the dashboard will create a automatic instruction to get data calculated from datasource. Of course your database should be able to answer each request as fast as possible, because QlikView needs wait data returned from datasource to update user interface. Anyway, this is good resouce to use with TERADATA solution, for example.
Now, try load Excel file that will be used to associate data completely in memory with data from Direct Discovery statement. Just return to Script Editor, pressing
CTRL + E shortcut. Below previous command, add the following sample to load data from Excel spreedsheet. To maintain the link between tables remember to change a field to get the same name from DIMENSION list. Field used to link both tables is CategoryID. So, don't forget to change its name using
As clause. After run the script go to Table Viewer again.
CategoryTable: LOAD CategoriaID As CategoryID, Descricao As Description, NomeCategoria As CategoryName FROM [Categorias.xls] (biff, embedded labels, table is Categorias$);
Now, both tables are connected. Any selection from a field on any table will get data from datasource where
MENSURE are present. For category table no Direct Discovery technique wasn't used. Therefore, any new category values will be not loaded up to full reload process. Fields present in
DIMENSION list, can be used in charts or List objects. Anymore, theses fields can be used to link tables, as we saw. With this scenario each interaction will create a SQL SELECT automatically using a GROUP BY statement available from each specific plataform. Not all records will need to be transmitted from datasources to QlikView, since just computed values are needed.
Next section will show you how to create a new chart using fields from both tables and add Lists to validate this behavior.
Creating a Chart Using Both Tables
To create a new chart just click with right mouse button over an empty place of main document and select
New Sheet Object. In sub-menu click
Chart. When Create Chart Assistent is shown choice the bar chart and click
Next. When Dimension window is shown fields from all tables loaded are available to be add to
Used Dimension list. However, no fields from
MENSURE parameter used by Direct Discovery technique are present. From the available list, add
CategoryName is a field from Excel worksheet.
Next again to see Expression Window where you can define a formula. Now, type the following code. While you're typing, take a look that
Quantity field is shown. When you finish, click Ok. Take some time to turn your chart more graceful. The following example shows how the chart created is, after some changes.
Some aspects are important to know when working with Direct Discovery:
- Only fields from
DETAILSparameters can be used in Lists.
- Only fields from
DIMENSIONcan act as fields in Used Dimensions chart.
- Not all functions from QlikView can be used through Direct Discovery.
DirectIdentifierQuoteCharvariable needs to be defined to use MySQL or Microsoft Access as datasources.
- Calculated dimensions can't use fields from Direct Discovery.
- Only fields defined on
DIMENSIONparameter of Direct Discovery can be used in Search Object.
- When working with Section Access (Reduction), data from Direct Discovery will be shown regardless user permissions.
- If specific functions from datasources are need, use
NATIVE('Year([OrderDate])') As OrderYear
- You don't need install any additional software to work with Direct Discovery.
- Both 32 and 64 bits connectors can be used through OLEDB or ODBC.
- If using Direct Discovery in multiple tables, set
DirectConnectionMaxvariable to number of simultaneous connections. Default value is 1.
Download this sample here!