Skip to main content
Version: I2022.3.0

About Cube Level Configuration Settings

Users can specify cube-scoped values for a subset of Aggregate and Query-related settings.

If an eligible setting is not defined for a specific cube, AtScale uses the value defined at the Engine or Organization level. If the value changes, all cubes that do not define a cube-level override will use the higher-level value. Changes made to the cube-scoped settings are effective immediately and do not require an engine restart or project re-publish.

info

Attention

Attention: If your project was published using the rename publish feature, you cannot configure cube-scoped settings for the cubes associated with your project.

Before you begin​

To view and edit cube settings, a user must be assigned the Override Engine and Organization Settings permission.

Viewing and Modifying Cube-Level Settings​

To view cube settings:

  1. Go to Settings > Organization Settings > Cube Settings.

  2. Choose which cubes to see:

    • Overridden Values: display only cubes where some of the settings are overridden.
    • Effective Values: display all cubes.

  3. Click the Filter by cube text box, and select the cubes you need.

  4. Click the Filter by setting text box, and search or select the settings you want to be displayed; see the sections below for details.

  5. Review the cube settings and their values:

    • You can sort them by column.
    • There is an information icon adjacent to the name of each setting. Hovering over it shows the the full name, default value, and description of this setting.

To change the settings:

  1. Follow the procedure above to display the settings you need.
  2. Modify the settings by entering a value, or turning them on or off.
  3. Choose Save.
  4. In the Save Cube Setting dialog, review your changes and choose Continue to confirm.

To reset a setting to its default value:

  1. Follow the procedure above to display the settings you need.
  2. Locate a value that has been modified - it is marked with a colored asterisk.
  3. Hover over the area to the right of the modified value, and when the Reset button appears choose it.
  4. Save and confirm your changes.
note

Note: Some settings can have a significant impact on down-stream systems. For example, increasing the number of aggregates for a cube by overriding aggregates.systemGenerated.activeInstance.retentionLimit can increase the workload on a data warehouse. Before changing such a parameter it is recommended that you view the Effective Value for the setting across all cubes that share the same data warehouse. The user interface is designed to display these values in a column, making it easier to assess the cumulative impact of the proposed change.

Aggregate Settings​

You can view and modify the following cube-level aggregate settings:

  • aggregate.create.securityDimensions.enabled: When set to "True", aggregates containing attributes from security dimensions can be created. The default value is False.
  • aggregate.partition.bigquery.range.end: The upper bound for integer value partitioning on BigQuery. The default value is 10000.
  • aggregate.partition.bigquery.range.interval: The interval for integer value partitioning on BigQuery. The default value is 10.
  • aggregate.partition.bigquery.range.start: The lower bound for integer value partitioning on BigQuery. The default value is 0.
  • aggregates.create.aggressiveDimensionalCopyPromotion.enabled: Enables aggressive promotion of dimensional hierarchy copies into preferred storage. The default value is True.
  • aggregates.create.allowExactDistinctCountMeasures.enabled: When set to "True", exact distinct count measures may be included in system-defined aggregates. The default value is False.
  • aggregates.create.compression.threshold: Specify the compression factor that aggregates proposed by the engine must meet or exceed. This factor is a measure of the quality of a proposed aggregate. It is calculated as the number of rows in the fact table divided by the estimated number of rows in a proposed aggregate. The default value is 3.0.
  • aggregates.create.demandDefined.enabled: Enables the creation of demand-defined system generated aggregates. The default value is TRUE.
  • aggregates.create.joins.compression: Specify the minimum compression ratio for any proposed join. This ratio is calculated as the cardinality of the join key in the fact table (or in the dimension table if that is not available) to the cardinality of the grouped dimension values. Joins for which the compression ratio is below this minimum will not be used. The default value is 100.0.
  • aggregates.create.joins.prime.compression: Specify the minimum compression ratio for a proposed join from a prime query part (where aggregates cannot be stored anywhere except in preferred storage). The default value is 0.99.
  • aggregates.dataWarehouseCacheTableRequests.enabled: Whether to cache data warehouse table requests. The default value is True.
  • aggregates.dataWarehouseCacheTableRequests.maximumRowCount: The maximum number of rows for a data warehouse table request to be cacheable, if caching is enabled. The default value is 50000.
  • aggregates.largeTableOptimization.distributionKeyColumn.minimumCardinality: The minimum cardinality required for the highest cardinality dimensional attribute to be used as a table distribution or clustering key. The default value is 30.
  • aggregates.largeTableOptimization.enabled: Enables consideration of optimizations for large aggregate tables, such as column-based clustering or distribution. The default value is False.
  • aggregates.largeTableOptimization.minimumEstimatedRows: The minimum estimated row count required for the aggregate system to consider applying optimizations, such as clustering or distribution. The default value is 100000.
  • aggregates.maintenance.deactivateUnused.enabled: Whether to deactivate system-defined aggregates that have been unused for the required number of days (as indicated by aggregates.maintenance.zeroUtilizationTTLDays). The default value is True.
  • aggregates.maintenance.zeroUtilizationTTLDays: The minimum number of consecutive zero-usage days that must pass before an aggregate will be deactivated. The default value is 45.
  • aggregates.new.build.scheduled: When true, new aggregate instance builds are postponed until the next scheduled batch build for the cube. If false (default), new aggregate instances may be queued for building at any time.
  • aggregates.predictionDefined.build.scheduled: When false (default), new prediction-defined aggregate build instances are queued for building following the project publish event. If true, PDA builds are postponed until the next scheduled batch build for the cube.
  • aggregates.slowBuild.cutoff: The duration cutoff for a completed aggregate build query to emit a SlowAggEvent. The default value is 4 seconds.
  • aggregates.smallTableReplication.enabled: Whether to consider applying data replication to system-defined aggregate tables. The default value is False.
  • aggregates.smallTableReplication.factBasedAggs.enabled: Whether to allow aggregate table replication on fact-based aggregates. The default value is False.
  • aggregates.smallTableReplication.maximumEstimatedRows: The maximum estimated number of rows that an aggregate table can have to be considered for replication. The default value is 1000.
  • aggregates.systemGenerated.activeInstance.extraAllowance: The maximum number of additional system-defined aggregates that are temporarily permitted to be active when a cube has reached its retention limit. The default value is 2.
  • aggregates.systemGenerated.activeInstance.retentionLimit: The maximum number of active system-defined aggregates to retain per cube. This is also known as the Retention Limit. The default value is 20.
  • aggregates.systemGenerated.withDistinctCountMeasure.retentionPercentage: The maximum number of active aggregates with distinct count measures per cube is calculated as this percentage of the value of aggregates.systemGenerated.activeInstance.retentionLimit for each cube. This pool of system-defined aggregate tables is sized and counted separately from the regular system-defined aggregate table pool. The default value is 40.
  • aggregates.uda.build.scheduled: When false (default), new user-defined aggregate build instances are queued for building following the project publish event. If true, UDA builds are postponed until the next scheduled batch build for the cube.
  • aggregates.withDistinctCounts.widening.enabled: Allows widening of distinct count aggregates with other measures, including distinct counts. This setting requires aggregates.create.widening.enabled to be set to True, for details see Settings that are Related to Narrowing and Widening.

Query Settings​

You can view and modify the following cube-level query settings:

  • query.factless.ignoreIncidentalfilter: 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. The default value is TRUE.
  • query.factless.useIncidentalFacts: Whether to allow dimension only queries to contain unrelated dimensions in the results. The default value is TRUE.
  • query.language.dax.bypassIsBlank: Whether to bypass IsBlank for measures in DAX. Enabling this bypass can dramatically reduce some PowerBI query sizes. This can technically produce wrong results in some circumstances. The default value is False. For more information, see Known Issues.
  • query.language.mdx.drilldown.allLevel.suppressDynamicMeasureGrandTotalExcel: Suppress totals for calculations containing dynamic members that will produce empty results. The default value is True.
  • query.language.mdx.drilldown.allLevel.suppressExcelHidden: Suppress drill-down cells not displayed by Excel. This reduces the number of queries, which may improve query performance but the results will be incomplete and may incompatible with other MDX clients. This also hides grand totals in some queries. The default value is TRUE.
  • query.language.mdx.drilldown.allLevel.suppressSubtotalExcelHidden: Suppress drill-down 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. The default value is FALSE.
  • query.language.mdx.slicers.allowHideNoData: Use filters, measures, 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. The default value is False.
  • query.language.mdx.slicers.indicateNoData.useConstraints: 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. The default value is True.
  • query.language.mdx.slicers.indicateNoData.useMeasures: Use measures in the pivot table to indicate which members in a slicer list have data in the measures' fact tables. This may affect performance. The default value is False.
  • query.language.mdx.slicers.indicateNoData.useSelections: 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. The default value is False.
  • query.result.max_rows: The maximum number of rows that can be returned for a query. The default value is 200000.
  • query.slowQuery.cutoff: The query duration cutoff for a completed query to emit a SlowQueryEvent. The default value is 4 seconds.

Other Settings​

Other cube-level settings you can view and modify are:

  • virtualization.subquery.result.max_rows: The maximum number of rows that can be returned for a virtualized subquery. The default value is 10000000.