Skip to main content

Query Settings

The following settings configure queries at the model level.

query.factless.ignoreIncidentalFilter​

  • Type: boolean
  • Default: true

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​

  • Type: boolean
  • Default: true

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

query.joins.optimization.enableForUserQueries​

  • Type: boolean
  • Default:

Whether the joins optimization defined at query.joins.optimization.type will be applied for user queries. If false, it will use the table-name strategy.

query.joins.optimization.simplifyNullJoins​

  • Type: boolean
  • Default:

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

query.joins.optimization.simplifyNullSemiAdditives​

  • Type: boolean
  • Default:

Enable the removal of from clauses from semi-additive sub-queries whose metrics have 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.joins.optimization.type​

  • Type: string
  • Default:

Which strategy is used to organize the tables from joins.

Supported values:

  • table-name: Joins are made following the alphabetical order of the table names (AtScale's previous behavior)
  • large-tables-first: Larger tables will appear first on the joins. This is the recommended approach for Google BigQuery. This option only works if all tables used in the join have their size fully estimated.
  • large-tables-first-partial-estimations: The join is performed the same way as the previous one.

However, it allows the use of partial estimations. It is important to mention that large tables may be pointed out as small ones (since the estimation is partial), impacting performance.

query.language.dax.blockDimModifiedMeasureFilters​

  • Type: boolean
  • Default:

Whether to allow dimensionally-modified metric filters to run in DAX queries.

query.language.dax.bypassIsBlank​

  • Type: boolean
  • Default: false

Whether to bypass IsBlank for metrics in DAX. Enabling this bypass can dramatically reduce some PowerBI query sizes. This can technically produce wrong results in some circumstances. For more information, see Power BI Known Issues.

query.language.dax.format.validate​

  • Type: boolean
  • Default: true

Whether or not to enable the validation of the custom format string.

query.language.dax.periodsToDateGrandTotals​

  • Type: boolean
  • Default:

Whether to show grand totals in PeriodsToDate metrics if there is no explicit single constraint.

query.language.dax.removeEmptyRows​

  • Type: boolean
  • Default: false

Determines whether DAX query results remove rows where every value is empty. This enables you to control whether these empty rows appear in BI tool visualizations when using AtScale in SuperDAX MD or DAX Tabular mode. For more information, see About Queries on Dimensions that are Unrelated to One or More Queried Metrics.

query.language.dax.removeMixedNonNullFilters​

  • Type: boolean
  • Default:

Whether to remove non-null metric filters when they are mixed with dimensional filters in DAX queries.

query.language.mdx.allMember.caseInsensitive​

  • Type: boolean
  • Default: true

Controls whether MDX All member identification is case insensitive. If it is not, it will only match with All.

query.language.mdx.drilldown.allLevel.suppressDynamicMeasureGrandTotalExcel​

  • Type: boolean
  • Default: true

Suppress totals for calculations containing dynamic members that will produce empty results.

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

  • Type: boolean
  • Default: true

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​

  • Type: boolean
  • Default: false

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​

  • Type: boolean
  • Default: false

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​

  • Type: boolean
  • Default: true

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​

  • Type: boolean
  • Default: false

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​

  • Type: boolean
  • Default: false

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​

  • Type: integer
  • Default: 500

The limit of sub-queries generated for MDX queries. If an MDX query fails the limit check, then the aggregate system would not attempt to build aggregates based on the violating query.

query.planning.allowCtes​

  • Type: boolean
  • Default: true

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

query.planning.bigquery.dateTimeConstants.useTimestamp​

  • Type: boolean
  • Default: true

Google BigQuery cannot compare DATETIME types with TIMESTAMP literals. This setting ensures AtScale generates TIMESTAMP literals to match your data, preventing query errors. Set to true if your underlying data is stored as TIMESTAMP.

For more information, see Working with Slowly Changing Dimensions.

query.planning.cancelUnrelatedLevels​

  • Type: boolean
  • Default:

Whether to cancel queries that contain levels with lower granularities than a selected metric allows.

query.planning.defaultHierarchyOverride​

  • Type: string
  • Default:

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.dma.enabled​

  • Type: boolean
  • Default: true

Whether to use dimensional modification aggregates (DMA) during query planning. This is primarily to allow for performance testing of DMAs.

query.planning.independentCustomEmptyMembers​

  • Type: boolean
  • Default:

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.multiMeasureHandling​

  • Type: string
  • Default: dataset

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.simplifyNullSubqueries​

  • Type: boolean
  • Default:

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​

  • Type: boolean
  • Default: false

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

query.result.max_rows​

  • Type: long
  • Default: 200000

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

query.scd.isLatest.default​

  • Type: boolean
  • Default: false

Controls whether queries, by default, return only the latest version of each slowly changing dimension (SCD) record. If false, all historical versions are returned, unless the query explicitly requests the latest version.

For more information, see Working with Slowly Changing Dimensions.

query.scd.isLatest.mode​

  • Type: string
  • Default: current_date

Defines how the latest version of a slowly changing dimension (SCD) record is determined.

Supported values:

  • max: The latest record is the one whose end date is equal to or after the value of query.scd.maxEffectiveValue.datetime.
  • current_date: The latest record is the one whose end date is equal to or after the current date and time when the query is executed.

For more information, see Working with Slowly Changing Dimensions.

query.scd.joinOnFactEffectiveKey.default​

  • Type: boolean
  • Default: true

Determines whether, by default, slowly changing dimension (SCD) levels are joined to the fact table using the fact’s effective date key. If set to false, only the natural key is used for joins, and effective date alignment is not enforced, unless specified in the query.

For more information, see Working with Slowly Changing Dimensions.

query.scd.maxEffectiveValue.datetime​

  • Type: string
  • Default:

Specifies the datetime value to use when a slowly changing dimension (SCD) record’s end date is NULL. This value is treated as the effective “end” for open-ended (no end date defined) SCD records during query planning and filtering. This can be set to a datetime string value valid for your dialect.

For more information, see Working with Slowly Changing Dimensions.

query.scd.minEffectiveValue.datetime​

  • Type: string
  • Default:

Specifies the datetime value to use when a slowly changing dimension (SCD) record’s start date is NULL. This value is treated as the effective “start” for open-begin (no start date defined) SCD records during query planning and filtering. This can be set to a datetime string value valid for your dialect.

For more information, see Working with Slowly Changing Dimensions.

query.slowQuery.cutoff​

  • Type: duration
  • Default: 4 seconds

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