What is Direct Query? What are the advantages?

[#1]

Can someone throw some light on Direct Query and elaborate with some example? Thanks in advance.

Warm Regards,
Dawar

[MK_QSL]

If the dataset in a database is large, it is possible to load aggregated query results into QlikView without loading the complete dataset into the QlikView data model using the Direct Discovery function.

You can have more information by searching under Help -- Contents or reading QlikView Reference Manual...

[sujeetsingh]

http://community.qlik.com/servlet/JiveServlet/previewBody/3710-102-1-4293/QlikView%2011%202%20Direct...

You will get a complete details from this document.

[sujeetsingh]

QlikView determines which data resides in-memory and which data is direct discovery data by

using the special script syntax, “DIRECT SELECT”. This syntax allows certain data elements not to

be loaded into the QlikView data model during the script reload process, but still available for

query purposes from the QlikView User Interface and to be combined for analysis with the

QlikView in memory dataset.

Once the direct discovery structure is established, the direct discovery fields can be used with

certain QlikView objects. When a direct discovery field is used on the QlikView object, QlikView

will automatically create the appropriate SQL query to run on the external data source. The

result of the query will be displayed on the QlikView object. 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. 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. It is possible to use calculation condition feature of

the QlikView charts to set a condition indicating when the chart should be calculated. Until that

condition is met, QlikView will not run queries and the chart will not be calculated. Please note

that QlikView will execute SQL queries on the data source for some of the list boxes that use

direct discovery fields. This is required to achieve the associative navigation capability.

[maxgro]

QlikView Direct Discovery FAQ 11.2 SR7.pdf

[ashfaq_haseeb]

HI,

have a look at the attached document

Regards

ASHFAQ

[#7]

I don't see any real advantage.

• It just used another limited syntax - I can't use UNION ALL, RIGHT JOIN, etc.
• less filtering conditions
  • my advanced DATE filter '=DATE>date#('01/01/2015') AND DATE<date#('01/04/2015')' translated to
    WHERE Date IN ('01/01/2015', '04/01/2015'...)
  • text filter '=COMMENT LIKE 'Manifest*'' translated to
    WHERE COMMENT IN ('Manifest 1', 'Manifest 2', ...)
• less aggregation formulas - only Sum(), Avg()...
• still need reloads to refresh dimensions, detail, detach

[engishfaque]

Dear Haimanta,

The DIRECT QUERY statement allows you to link tables through an ODBC or OLE DB connection using the Direct Discovery function.

Syntax:

--

DIRECT QUERY DIMENSION fieldlist [MEASURE fieldlist] [DETAIL fieldlist] ] [DETACH fieldlist]FROM tablelist

[WHERE where_clause];

Note 1: The DIMENSION, MEASURE, and DETAIL keywords can be used in any order.

Note 2: The DIMENSION and FROM keyword clauses are required on all DIRECT QUERY statements. The FROM keyword must appear before the DIMENSION keyword.

Note 3: The DIRECT QUERY statement cannot contain DISTINCT or GROUP BY clauses.

Example 1:

Direct Query

DIMENSION A

MEASURE X,Y,Z

DETACH B,C

From MySalesTable;

Example 2:

SQL SELECT

SalesOrderID,

RevisionNumber,

OrderDate,

SubTotal

TaxAmt

FROM AdventureWorks.Sales.SalesOrderHeader;

could be changed to the following DIRECT QUERY statement:

DIRECT QUERY

DIMENSION

SalesOrderID,

RevisionNumber,

MEASURE

SubTotal

TaxAmt

DETACH

OrderDate,

FROM AdventureWorks.Sales.SalesOrderHeader;

Note 4: DIRECT QUERY statements are written directly in the Edit Script dialog box. They are not generated with the Create Select Statement.

Reference: QlikView Help

Kind regards,

Ishfaque Ahmed

[Anonymous]

Thanks alot Ishfaque. this make me understand Direct Query.

[reddy-s]

Check this out. Should be help full.

Direct Discovery in Qlikview