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.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.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 forceIndividualMeasureCheck = true.

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.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 metrics'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.forceIndividualMeasureCheck​

  • Default: true
  • Restart: No

Require each semi-additive metric to be in its own sub-query to ensure the conditions are restricted to itself and not affected by other metrics.

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.