Direct Discovery in Qlikview

Mon, 01 Dec 2014 11:25:07 GMT

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)

Calculated dimensions

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

Loop and Reduce

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.

Re: Direct Discovery in Qlikview

Anil_Babu_Samineni — Sat, 31 Dec 2016 14:26:53 GMT

Good, This case how to use only one User Data

Direct Discovery Table

Like Where OSUSER() = 'Anil';

Re: Direct Discovery in Qlikview

jeshil09 — Wed, 04 Jan 2017 08:31:05 GMT

In this case it seems you are not following the above the syntax should be

CONNECTION;

[MyTable]:
DIRECT QUERY
dimension
OSUSER

..other columns..
FROM tablename

Where OSUSER() = 'Anil';

Re: Direct Discovery in Qlikview

nihhalmca — Thu, 23 Mar 2017 11:11:26 GMT

Useful document.

Re: Direct Discovery in Qlikview

Anil_Babu_Samineni — Thu, 23 Mar 2017 12:40:50 GMT

I am sure, This won't work with Default functions with in Qlik.

Re: Direct Discovery in Qlikview

retko1985 — Sun, 28 Jan 2018 13:45:54 GMT

Hello,

What do you mean that columns are not supported?

Thanks

Re: Direct Discovery in Qlikview

Anonymous — Thu, 05 Apr 2018 20:50:18 GMT

very useful
Thanks

article Direct Discovery in Qlikview in Qlik Learning Documents

Direct Discovery in Qlikview

Re: Direct Discovery in Qlikview

Re: Direct Discovery in Qlikview

Re: Direct Discovery in Qlikview

Re: Direct Discovery in Qlikview

Re: Direct Discovery in Qlikview

Re: Direct Discovery in Qlikview