You can set a caching limit to the Direct Discovery query results for visualizations. Once this time limit is reached, Qlik Sense clears the cache when new Direct Discovery queries are made. Qlik Sense queries the source data for the selections and creates the cache again for the designated time limit. The result for each combination of selections is cached independently. That is, the cache is refreshed for each selection independently, so one selection refreshes the cache only for the fields selected, and a second selection refreshes cache for its relevant fields. If the second selection includes fields that were refreshed in the first selection, they are not updated in cache again if the caching limit has not been reached.
The Direct Discovery cache does not apply to Table visualizations. Table selections query the data source every time.
The limit value must be set in seconds. The default cache limit is 1800 seconds (30 minutes).
The value used for DirectCacheSeconds is the value set at the time the DIRECT QUERY statement is executed. The value cannot be changed at runtime.
Example:
SET DirectCacheSeconds=1800;
You can do asynchronous, parallel calls to the database by using the connection pooling capability. The load script syntax to set up the pooling capability is as follows:
SET DirectConnectionMax=10;
The numeric setting specifies the maximum number of database connections the Direct Discovery code should use while updating a sheet. The default setting is 1.
Information noteThis variable should be used with caution. Setting it to greater than 1 is known to cause problems when connecting to Microsoft SQL Server.
Direct Discovery can support the selection of extended Unicode data by using the SQL standard format for extended character string literals (N’<extended string>’) as required by some databases (notably SQL Server). The use of this syntax can be enabled for Direct Discovery with the script variable DirectUnicodeStrings.
Setting this variable to 'true' will enable the use of the ANSI standard wide character marker “N” in front of the string literals. Not all databases support this standard. The default setting is 'false'.
When a DIMENSION field value is selected in a Qlik Sense object, a query is generated for the source database. When the query requires grouping, Direct Discovery uses the DISTINCT keyword to select only unique values. Some databases, however, require the GROUP BY keyword. Set DirectDistinctSupport to 'false' to generate GROUP BY instead of DISTINCT in queries for unique values.
SET DirectDistinctSupport='false';
If DirectDistinctSupport is set to true, then DISTINCT is used. If it is not set, the default behavior is to use DISTINCT.
In high cardinality multi-table scenarios, it is possible to generate sub queries in the SQL query instead of generating a large IN clause. This is activated by setting DirectEnableSubquery to 'true'. The default value is 'false'.
Information noteWhen DirectEnableSubquery is enabled, you cannot load tables that are not in Direct Discovery mode.
SET DirectEnableSubquery='true';
Teradata query banding variables
Teradata query banding is a function that enables enterprise applications to collaborate with the underlying Teradata database in order to provide for better accounting, prioritization, and workload management. Using query banding you can wrap metadata, such as user credentials, around a query.
Two variables are available, both are strings that are evaluated and sent to the database.
This string is sent when a connection to the database is created.
If OSuser() for example returns WA\sbt, this will be evaluated to SET QUERY_BAND = 'Who=WA\sbt;' FOR SESSION; , which is sent to the database when the connection is created.
You can set the character used as the field delimiter in Direct Query statements for databases that require a character other than comma as the field delimiter. The specified character must be surrounded by single quotation marks in the SET statement.
SET DirectFieldColumnDelimiter= '|'
You can specify a character to use to quote strings in a generated query. The default is a single quotation mark. The specified character must be surrounded by single quotation marks in the SET statement.
SET DirectStringQuoteChar= '"';
You can specify that non-ANSI quoting of identifiers be used in generated queries. At this time, the only non-ANSI quoting available is GoogleBQ. The default is ANSI. Uppercase, lowercase, and mixed case can be used (ANSI, ansi, Ansi).
SET DirectIdentifierQuoteStyle="GoogleBQ";
For example, ANSI quoting is used in the following SELECT statement:
SELECT [Quarter] FROM [qvTest].[sales] GROUP BY [Quarter]
When DirectIdentifierQuoteStyle is set to "GoogleBQ", the SELECT statement would use quoting as follows:
SELECT [Quarter] FROM [qvTest.sales] GROUP BY [Quarter]
You can specify a character to control the quoting of identifiers in a generated query. This can be set to either one character (such as a double quotation mark) or two (such as a pair of square brackets). The default is a double quotation mark.
When Direct Discovery fields are used in a Table visualization, a threshold is set to limit the number of rows displayed. The default threshold is 1000 records. The default threshold setting can be changed by setting the DirectTableBoxListThreshold variable in the load script. For example:
SET DirectTableBoxListThreshold=5000;
The threshold setting applies only to Table visualizations that contain Direct Discovery fields. Table visualizations that contain only in-memory fields are not limited by the DirectTableBoxListThreshold setting.
No fields are displayed in the Table visualization until the selection has fewer records than the threshold limit.
Direct Discovery number interpretation variables
The decimal separator defined replaces the decimal symbol for currency in the SQL statement generated to load data using Direct Discovery. This character must match the character used in DirectMoneyFormat.
Default value is '.'
Example:
Set DirectMoneyDecimalSep='.';
The symbol defined replaces the currency format in the SQL statement generated to load data using Direct Discovery. The currency symbol for the thousands separator should not be included.
Default value is '#.0000'
Example:
Set DirectMoneyFormat='#.0000';
The time format defined replaces the time format in the SQL statement generated to load data using Direct Discovery.
Example:
Set DirectTimeFormat='hh:mm:ss';
The date format defined replaces the date format in the SQL statement generated to load data using Direct Discovery.
Example:
Set DirectDateFormat='MM/DD/YYYY';
The format defined replaces the date and time format in the SQL statement generated in the SQL statement generated to load data using Direct Discovery.
Example:
Set DirectTimestampFormat='M/D/YY
hh:mm:ss[.fff]';
