topic Re: Sql from Direct Discovery in QlikView

Sql from Direct Discovery

Tue, 25 Mar 2014 22:03:23 GMT

Hello,

I started to check the DirectDiscovery Function and have a question to improve the by Direct Discovery generated Sql.

Example with two Tables:

Calendar:

Date,

Month,

Year

DirectDiscoveryTable:

Date

Implicit

Value

The generated Sql looks like ' Select ... from ... where Date in (...,..)'

If the user select three years there are more then 1000 entries in the in-part. It's obvious why the Sql is generated like this, but it's not the fastest query.

I'm looking for Ideas to improve the QlikView Application, so that the Direct Discovery function is able to build better queries.

Thanks for your comments.

Re: Sql from Direct Discovery

Thu, 24 Apr 2014 16:00:49 GMT

Torsten - great question, I have seen apps where the IN clause approaches 10000 entries which is really ugly. I will check with our dev folks, but in terms of general rdbms best practice, what would you suggest as a better mechanism?

Re: Sql from Direct Discovery

nagaiank — Thu, 24 Apr 2014 17:13:13 GMT

Is there any field other than Date that can be used in IN-clause?

Re: Sql from Direct Discovery

maxgro — Thu, 24 Apr 2014 18:07:05 GMT

I think you have a table (fact table) in dbms and one or more tables in qlikview, in your case 2 tables

can you join the 2 tables in dbms? no, because calendar is a qlikview table; so you can only send a where clause to the dbms for selecting data from fact table; where Date in (.....) is the simpler solution. Perhaps a between would be better but it is more difficult to generate.

Don't know if using a single table (Calendar + DirectDiscoveryTable) in dbms can be useful or if using two tables in dbms and a view for querying them in Qlikview can be useful.

IMHO if you can stay on Qlikview side (I mean time to reload Qlik doc, hardware to support your big fact table, etc....) don't use direct query.

Another thing that could be usefut (from online help)

Native data-source syntax

By design, the DIRECT QUERY statement is data-source neutral for data sources that support SQL. For that reason, the same DIRECT QUERY statement can be used for different SQL databases without change. Direct Discovery generates database-appropriate queries as needed.

Native data-source syntax can be used when the user knows the database to be queried and wants to exploit database-specific extensions to SQL. Native data-source syntax is supported:

As field expressions in DIMENSION and MEASURE clauses
As the content of the WHERE clause

Examples:

DIRECT QUERY

DIMENSION Dim1, Dim2
MEASURE
NATIVE ('X % Y') AS X_MOD_Y

FROM TableName

DIRECT QUERY

DIMENSION Dim1, Dim2
MEASURE X, Y
FROM TableName
WHERE NATIVE ('EMAIL MATCHES "\*.EDU"')

Re: Sql from Direct Discovery

Thu, 22 May 2014 14:57:41 GMT

You're right in the first test the Calendar was a QlikView-Table. But we are able to put the Date-Dimensions into the fact table. But if we do this with every dimension we got fact-tables with many columns. We will do some tests and i will post an update when i got results.