Skip to main content

Query Settings

AtScale supports the following settings for configuring queries.

query.cache.entry.sizeLimit​

  • Default: 10000
  • Restart: No

The maximum result size allowed for a query's results to be cached. Set this to zero to disable all caching. This should not be negative.

query.cache.eviction.entryAge​

  • Default: 60 minutes
  • Restart: Yes

The maximum amount of time that a cached result can stay in the query cache.

query.cache.eviction.sizeLimit​

  • Default: 1000000
  • Restart: Yes

The maximum size for the query cache, or -1 to not set a capacity. (Running without a capacity is not recommended.)

query.calculationGroups.defaultPrecedence​

  • Default: 10
  • Restart: Yes

The default precedence for calculation groups. It specifies the order the calculation groups are combined with the underlying measure.

query.characterLimit​

  • Default: 100000
  • Restart: No

The maximum number of characters to allow for an incoming query.

query.characterLimit.displayInbound​

  • Default: 100000
  • Restart: No

The maximum number of characters to include from inbound query text in ActivityMonitorRest payload

query.characterLimit.displayOutbound​

  • Default: 50000
  • Restart: No

The maximum number of characters to include from outbound query text in ActivityMonitorRest payload.

query.drillthrough.max_rows​

  • Default: 500
  • Restart: No

The maximum number of rows to return for drillthrough queries.

query.emptyValueCrossJoin.enabled​

  • Default: false
  • Restart: No

When enabled (true), AtScale can calculate crossjoins of dimensions that contain empty values.

Important

Enabling this setting may result in slowed query performance.

query.emptyValueCrossJoin.requireConstraints​

  • Default: true
  • Restart: No

When enabled (true), crossjoins of dimensions that contain empty values (controlled by query.emptyValueCrossJoin.enabled) are only calculated if the query contains constraints.

Important

Disabling this setting when query.emptyValueCrossJoin.enabled is enabled is not recommended, as it may result in unconstrained crossjoins.

query.factless.ignoreIncidentalFilter​

  • Default: true
  • Restart: No

Whether to ignore unrelated dimension filters in dimension only queries. Disabling this may increase the time it takes to load filter screens in applications like Tableau.

query.factless.useIncidentalFacts​

  • Default: true
  • Restart: No

Whether to allow dimension only queries to contain unrelated dimensions in the results.

query.flattenOuter​

  • Default: true
  • Restart: No

Whether to avoid generating a subquery where possible.

query.includeComments.general.userComments​

  • Default: true
  • Restart: No

Whether to include the user comments in the outbound query from inbound queries.

query.inList.maximum​

  • Default: 2147483647
  • Restart: No

The maximum number of values in an IN list.

query.joins.optimization.simplifyNullJoins​

  • Default: true
  • Restart: No

Enable the removal of from clauses from join queries or sub-queries that have been planned to only return NULL.

query.joins.optimization.simplifyNullSemiAdditives​

  • Default: true
  • Restart: No

Enable the removal of from clauses from semi-additive sub-queries whose metrics has been planned to be NULL. This occurs even if there are dimensional selections along with the NULL metric. This is only applied if query.planning.semiAdditive.multiMeasureHandling is set to measure.

query.language.dax.decimal.maxDigits​

  • Default: 15
  • Restart: No

The maximum number of digits allowed by a numeric or decimal column to have on the left of the decimal place for DAX queries (PBI Left-Of-Decimal Limit = Column Precision - Column Scale). This config avoids silent errors on Power BI when it is not showing results for large numbers.

query.language.mdx.caseInsensitiveStringComparison​

  • Default: true
  • Restart: No

Whether string comparisons should be case sensitive in MDX queries.

query.language.mdx.cellLimit.enabled​

  • Default: true
  • Restart: No

Whether to impose an MDX cell limit on queries.

query.language.mdx.cellLimit.value​

  • Default: 1000000
  • Restart: No

The cell limit for MDX queries (if enabled).

query.language.mdx.currentMember.allowLegacySyntax​

  • Default: false
  • Restart: No

Whether to allow using legacy CurrentMember syntax.

query.language.mdx.dimquery.result.limit​

  • Default: -1
  • Restart: No

The number of data rows to be returned for Dimension-only mdx queries(if enabled).

When you are using very large dimensions, you can use this setting to set the number of data rows (for example, 10000) to be returned for dimension-only queries (if enabled).

-1L indicates this is disabled. When disabled, the limitation from the query.result.max_rows setting is applied.

query.language.mdx.drilldown.allLevel.suppressExcelHidden​

  • Default: true
  • Restart: No

Suppress drilldown cells not displayed by Excel. This reduces the number of queries, which may improve query performance, but the results will be incomplete and may be incompatible with other MDX clients. This also hides grand totals in some queries.

query.language.mdx.drilldown.allLevel.suppressSubtotalExcelHidden​

  • Default: false
  • Restart: No

Suppress drilldown cells not displayed by Excel. This reduces the number of queries, which may improve query performance, but the results will be incomplete and may be incompatible with other MDX clients. This does display grand totals.

query.language.mdx.slicers.allowHideNoData​

  • Default: false
  • Restart: No

Use filters, metrics, slicer lists, and/or rows and columns in the pivot table to determine which members in a slicer list will be hidden when 'Hide items with no data' is selected. This option uses the 'slicers.indicateNoData.X' flags to determine what is constrained. This may affect performance.

query.language.mdx.slicers.indicateNoData.useConstraints​

  • Default: true
  • Restart: No

Use filters in the pivot table and other slicers to indicate which members in a slicer list are affected by those constraints. This may affect performance.

query.language.mdx.slicers.indicateNoData.useMeasures​

  • Default: false
  • Restart: No

Use metrics in the pivot table to indicate which members in a slicer list have data in the metrics' fact tables. This may affect performance.

query.language.mdx.slicers.indicateNoData.useSelections​

  • Default: false
  • Restart: No

Use displayed slicer lists and rows and columns in the pivot table to indicate which members in a slicer contain data. This may affect performance.

query.language.mdx.subquery.limit​

  • Default: 500
  • Restart: No

The limit of generated subqueries for MDX queries.

query.language.sql.addCollations​

  • Default: true
  • Restart: Yes

Whether to add COLLATE "C" to text selections.

query.language.sql.preventClientSideAggregation​

  • Default: false
  • Restart: No

When enabled, all metrics in .tds files are sent as either averages or distinct counts.

Important

When this setting is enabled (true), the configured aggregation for metrics cannot be overridden from within Tableau Server or Tableau Desktop. If you need to change a metric's aggregation, you must create a new metric or calculation to perform the desired aggregation.

Also note that when this setting is disabled (false), the tableau.rawSqlAggs.enabled global and model settings are not applied. For more information, see Tableau Settings and Other Model Settings.

query.limitZero.shortCircuit​

  • Default: true
  • Restart: No

Whether to short-circuit limit-zero queries by suppressing outbound queries when possible.

query.looker.kill_test.delay​

  • Default: 10 seconds
  • Restart: No

The delay to end the query used in Looker connection test flow to test if Looker can kill queries. If it ends too fast, Looker test may fail.

query.noFrom.connectionId​

  • Default:
  • Restart: No

Enter the connection ID name to use for queries that do not contain a FROM clause. If the connection name is not found, AtScale uses the first connection found in the catalog. This setting is useful for BI Tools that generate queries without FROM clauses such as Looker.

query.planning.allowCtes​

  • Default: true
  • Restart: No

Allow CTEs (Common Table Expressions) to be used in queries, which reduces query size.

query.planning.defaultHierarchyOverride​

  • Default:
  • Restart: No

The default hierarchy to use if a qualified hierarchy is required and no other qualified hierarchy is present in the query. This only applies to the dimension where the defined hierarchy exists.

query.planning.independentCustomEmptyMembers​

  • Default: true
  • Restart: No

Whether custom empty member configuration on nested dimension hierarchy is interpreted independently of any intermediate dimension hierarchies between the nested dimension and the connected Fact Data sets. This requires redeploying the catalog the model is in to take effect.

query.planning.semiAdditive.defaultAggregationWhenOutOfScope​

  • Default: true
  • Restart: No

Configures the default totaling behavior for semi-additive metrics. When True, show the First/Last Non-empty or first/last child subtotal or grand total only when the query contains one of the metric's configured Semi-Additive dimension attributes. When False, show theFirst/Last Non-empty or first/last child subtotal or grand total when the query does not contain a configured Semi-Additive dimension attribute.

query.planning.semiAdditive.multiMeasureHandling​

  • Default: dataset
  • Restart: No

How to handle multiple semi-additive metrics. They can be split into their own subqueries based on these settings: dataset, measure, or model:

  • dataset (default) creates one subquery for each group of metrics belonging to the same fact dataset.
  • measure creates a subquery per metric.
  • model creates one subquery for all metrics.
Important

When this setting is set to model or dataset, AtScale does not submit subqueries per individual semi-additive metric, resulting in fewer passes of the data. However, this approach requires that you engineer your fact data to ensure data is available at the same level throughout. Without this preliminary data engineering work, query results may be incorrect.

If you are unsure if your data meets this standard, or you do not have data engineering practices in place to ensure data integrity (such as eliminating null data for inventory use cases), this setting should be changed to measure. When set to measure, AtScale submits subqueries for individual metrics, which ensures a correct response, but results in multiple passes of the data.

query.planning.simplifyConstantSubqueries​

  • Default: true
  • Restart: No

Enable the removal of from clauses from queries or sub-queries that are not selecting any columns from a table.

query.planning.simplifyNullSubqueries​

  • Default: true
  • Restart: No

Enable the removal of from clauses from non-join queries or sub-queries that have been planned to only return NULL.

query.planning.unsecureTotals.enabled​

  • Default: false
  • Restart: No

Whether to allow the unsecure totals flag to be applied to row security objects.

query.result.fetchSize​

  • Default: 10000
  • Restart: Yes

The size of JDBC query fetch batches. Does not limit full result set.

query.result.frame.timeout.idle.duration​

  • Default: 20 minutes
  • Restart: Yes

The amount of time to use for the idle timeout, if enabled.

query.result.max_rows​

  • Default: 200000
  • Restart: No

The maximum number of rows that can be returned for a query.

query.slowQuery.cutoff​

  • Default: 4 seconds
  • Restart: No

The query duration cutoff for a completed query to emit a SlowQueryEvent.

query.suppressGrouping​

  • Default: true
  • Restart: No

Whether to avoid generating GROUP BY clauses where possible.

query.timeout​

  • Default: 60 minutes
  • Restart: No

The maximum amount of time to allow a query to run.

query.topN.useKeysForUniqueAttributes​

  • Default: false
  • Restart: No

Whether to use keys rather than values when joining the sub-selects of TopN/BottomN queries.

query.totalGraphPlanCost.maximum​

  • Default: 10000.0
  • Restart: No

The maximum cost of a query component.