Direct Discovery

[fernando_tonial]

Introduction

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 conect to database using ODBC CONNECT instruction. Up to now no new procedure is need. With following tradicional 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.

While 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 </code>MENSURE</code> parameter used by Direct Discovery technique are present. From the available list, add CategoryName. Remember CategoryName is a field from Excel worksheet.

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

=Sum(Quantity) 

Some aspects are important to know when working with Direct Discovery:

• Only fields from DIMENSION and DETAILS parameters can be used in Lists.
• Only fields from DIMENSION can act as fields in Used Dimensions chart.
• Not all functions from QlikView can be used through Direct Discovery.
• Sometimes DirectIdentifierQuoteChar variable needs to be defined to use MySQL or Microsoft Access as datasources.

SET DirectIdentifierQuoteChar='``';

• Calculated dimensions can't use fields from Direct Discovery.
• Only fields defined on DIMENSION parameter 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 parameter.

NATIVE('Year([OrderDate])') As OrderYear

• You don't need install any aditional 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 DirectConnectionMax variable to number of simultaneous connections. Default value is 1.

SET DirectConnectionMax=10;

Download this sample here!

• Direct Discovery Sample.QVW

Used Function

• Sum

Related Posts

• Restricting Data for User Login (Section Access)
• How to Use Variables

Qknow, Learn, Sharing.

Need more?

www.qknow.com.br

Don't worry, be Qlik.

Tonial.

[sergioabr_tocca]

TONIAL!

Thank you very much for sharing

Excellent article,

[eduardo_sommer]

Ótimo artigo

Obrigado por compartilhar com a gente

Eduardo