Direct Discovery in QLIKVIEW 11.2
The biggest limitation of QLIKVIEW in data point of view, is that all the data should be stored in RAM. As big data introduction data is too huge, so QLIKVIEW introduce a hybrid approach. In which QLIKVIEW stores the dimension in the RAM, while measures or the numeric values are stored in the database you want.
Basically aggregation of measures are run on the database not in memory, which reduce the RAM usage. It will be used when our database speed is good enough.
QLIKVIEW introduce the new type of query for these kind of requirement “Direct Query”.
It has three types of field
1) Dimension:- which are used for association
2) Measure: - on which we perform the aggregation
3) Detail:- those field which can’t be used for association or aggregation. Like Comments.
The script look like below
DIRECT QUERY
DIMENSION
DIMENSION-1,
DIMENSION-2,
:
:
:
DIMENSION-n
MEASURE
MEASURE-1,
MEASURE-2,
:
:
:
MEASURE-n
DETAIL
DETAIL-1,
DETAIL-2,
DETAIL-3
FROM TABLE;
The unique values of Dimension will be fetched in the QVW, and that can be used for creating association between data and other data.
Example:
ODBC CONNECT32 TO [Tutorial;DBQ=D:\Database\TUTORIAL_DATA_7200.mdb];
[DirectDiscovery]:
DIRECT QUERY
dimension
CALL_CTR_ID,
CUST_CITY_ID
detail
COMMENT
measure
GROSS_DOLLAR_SALES
FROM CITY_CTR_SLS;
When we run the script it will result in the following script:
You can see in the CALL_CTR_ID- only 15 values are fetched. And in CUST_CITY_ID 436 lines are fetched. Measures are not stored in QVW.
We can check the Tracing of ODBC connection in ODBC.
And can see your file in this location
C:\Users\user12\AppData\Local\Temp\SQL.txt
Because of the interactive and SQL syntax-specific nature of Direct Discovery, several QLIKKVIEW features are NOT supported:
- Advanced calculations (Set Analysis, complex expressions)
- Comparative Analysis (Alternate State) on the QlikView objects that use Direct Discovery fields
- Direct Discovery MEASURE and DETAIL fields are not supported on Global Search
- Binary load from a QlikView application with a Direct Discovery table
- Synthetic keys on the Direct Discovery table
- Table naming in script does not apply to the Direct table
- The use of wild card * character after DIRECT QUERY keyword on the load script (DIRECT QUERY *)
- Oracle database tables with LONG data type
- columns are not supported.
- Big integers in scientific notation, outside range [-9007199254740990, 9007199254740991], can cause rounding errors and undefined behavior.
QLIKVIEW Direct Discovery can be used against the following data sources, both with 32-bit and 64-bit connections;
- ODBC/OLEDB data sources - All ODBC/OLEDB sources are supported, including SQL Server, Teradata and Oracle.
- Custom connectors which support SQL – SAP SQL Connector, Custom QVX connectors for SQL compliant data stores.
