QlikView Direct Discovery is a new feature in QlikView 11.2
QlikView is better known for in-memory associative data experience. This means that all the data is loaded in memory for creating a visualization. Direct discovery is a big enhancement in this direction where not all the data is loaded in the memory. You can associate in-memory data to the external large data sets.
What is QlikView Direct Discovery
QlikView direct discovery capability combines the associative capabilities of QlikView in-memory dataset with external data source where not all the data is loaded into QlikView data model, instead aggregated query result is pass back to the QlikView user interface.
Note – This is useful when connecting to larger datasets. Using direct discovery, not all the fields are loaded in the memory. Fields from the large datasets will be available for querying.
How QlikView Direct Discovery Works
1. QlikView determines the direct discovery data by the use of special script syntax “DIRECT QUERY”
2. Additional keywords used are Dimension, Measure, Detail and Native
3. When a direct discovery field is used in the QlikView object, QlikView automatically creates the appropriate SQL query to run on the external data source. The result of the query will be displayed in the QlikView object.
4. When selections are made on the QlikView application, the associated data values of the direct discovery fields will be used in the WHERE conditions of the queries.
5. With each selection, the direct discovery charts will be calculated, where the calculations and aggregations will be done on the source table by executing the SQL query created by QlikView.
6. QlikView will execute SQL queries on the data source for the list boxes that use direct discovery fields.
7. Only one direct discovery table per QlikView application is allowed
8. Preceding load cannot be used
Loading Table with Direct Discovery Syntax
I will first load tables in-memory i.e. by using preceding load and SQL load syntax and then I will load using Direct discovery syntax to show the difference.
Loading the table with the load statement, loads all the fields in-memory. Association will be present between the tables with common field names.
Loading with Direct discovery syntax
In the above example,
1. Direct Query.QlikView will determine this is Direct Discovery data since it starts with Direct Query keyword
2. Dimension. Columns after the Dimension keyword will be loaded in memory. Most of the columns are required to associate with the other in-memory tables.
3. Native. Source database SQL functions can be executed using the Native keyword. It should be used within the single quotation marks.
4. Measure.Columns followed by Measure are part of the source table and are NOT loaded in memory data model. QlikView is aware of them at the meta level and these columns will be available to be used in the expressions. The measure fields require to have an aggregation function.
5. Detail. Columns followed by Detail are also part of the source table and are NOT loaded in memory. These columns do not require aggregation. They are displayed only in the QlikView table box.
6. Detach. Columns after Detach will be loaded in memory and are used to flag columns that should not be part of the associative experience but will be part of the query passed to the data source.
Unsupported QlikView functionalities in Direct Discovery
The following features are not supported due to the SQL specific syntax
- Set Analysis
- Calculated dimensions
- Direct Discovery Measure and Detail fields are not supported on Global Search
- Section access and data reduction
- Loop and Reduce
- Synthetic keys on the Direct Discovery table
- Table naming in script does not apply to the Direct table
QlikView direct discovery is a very useful feature. It is useful when you have to load a large table. As you know QlikView is in-memory and each unique field utilizes memory. With direct discovery, you have the option to use a large database table without losing the associative feature.
Further Reading : Qlik Design Blog