Improvements to aggregate planning

A new engine setting has been added to enable AtScale to follow Google BigQuery best practices for table join ordering when creating aggregates:

• query.joins.optimization.type: Defines the strategy used to order table joins when creating aggregates. Supported values:

  • table-name: Default. Joins are made in alphabetical order of the table names. This retains the behavior used in previous versions of AtScale.

    Note: Use this value if you do not have Google BigQuery.

  • large-tables-first: Joins are made according to table size, with the largest table first, followed by the smallest table. All remaining tables are joined in a descending order of size. This behavior requires that all queried tables have all of their stats calculated. This option is recommended for Google BigQuery data warehouses.

  • large-tables-first-partial-estimations: Joins are made according to table size, with the largest table first, followed by the smallest table. All remaining tables are joined in a descending order of size. Unlike the large-tables-first option, this option does not require the queried tables to have their stats calculated. Note that because the table size estimation is partial, large tables may be mistaken for small ones, impacting performance.

Note: You must enable this setting using the custom engine setting form. To access the form, click Show Custom Settings in the Overview section of the engine settings page.

For more information on optimizing join behavior for GBQ data warehouses, refer to the Google BigQuery documentation. For information on changing engine settings, see Changing Engine Settings.

ATSCALE-20209

Input measure formatting for calculations

Calculations can now be configured to use the input measure's formatting for results, rather than a specific format defined on the calculation itself. This is useful for calculations that can't have a standard output format, such as year-over-year growth in dollars, euros, or time.

To support this functionality, the New and Edit Calculation dialog boxes contain a new Use Input Measure's Format checkbox in the Formatting section. When this checkbox is selected, the calculation results always use the input measure's format. For more information, see Adding a Calculation.

Note: The new checkbox is not available when creating calculations in bulk. Whether bulk-created calculations use the input measure's formatting depends on the calculation template being used. For example, Current calculations use the input measure's formatting by default, while Pct of Parent calculations do not. If you wish to modify the output format for a bulk-created calculation, you must edit it after it has been added. For more information, see Bulk Creation of Calculations.

ATSCALE-19611

Microsoft Power BI: Support for Formatting Dimension Calculation Groups

AtScale now supports formatting of dimension calculation groups in Microsoft Power BI. To use this functionality, the query's input measures must define a format, even if you intend to override the format string on the calculation group's calculation; for more information, see Known Issues.

For more information on calculation groups, see Adding a Calculation and Bulk Creation of Calculations.

ATSCALE-13317

Embedded Dimensions On Semi-Additive Measures

You can now reference first-degree embedded dimensions in semi-additive measures. These appear in the the Semi-Additive Measure dropdown list of the Create a Measure dialog box.

For more information, including restrictions that apply to embedded dimensions, see Semi-Additive Measures.

ATSCALE-17520

Dynamic Default Members

You can now create dynamic default members on time hierarchies, which update automatically over time or in response to ETL trigger conditions. This functionality can be used to reduce query sizes and provide more relevant query results without having to add manual restrictions to your models.

You can create dynamic default members using the following new VBA functions: CurrentTimestamp, DateAdd, Day, FloorTime, Format, Month, MonthEndDate, MonthStartDate, Now, PrevMonthStartDate, PrevQuarterStartDate, PrevWeekStartDate, PrevYearStartDate, Quarter, QuarterEndDate, QuarterStartDate, selectMaxDate, TimeStampAdd, Week, WeekDay, WeekEndDate, WeekStartDate, Year, YearEndDate, YearStartDate.

For more information on creating dynamic default members, see About Default Hierarchical Members. For more information on the new functions, see VBA Date Functions.

ATSCALE-1972

New MDX Functions

AtScale now supports the following MDX functions: DatesPeriodsToDate, DatesMTD, DatesQTD, DatesWTD, DatesYTD.

These functions introduce DAX Tabular's behavior for filtering and totaling expressions using the PeriodsToDate function; i.e., the results are constrained by filters applied to the member parameter. For more information, see MDX Reference.

ATSCALE-19008

New Dates Periods To Date DMA Calculation for Dimension Levels

In addition to the new DatesPeriodsToDate MDX function (see above), you can now enable the Dates Periods To Date DMA calculation on time dimension levels. This option is available in the Dimensionally Modified Aggregates section of the Edit a Level dialog box. For more information, see Edit A Level.

ATSCALE-19849

Microsoft Excel: Support for Custom Grouping

AtScale now supports Microsoft Excel's custom pivot table dimension grouping functionality. This enables you to create custom groups within Excel, without having to make changes to your models. This change also includes support for session sub-cubes, naming, totaling, filtering, and ungrouping.

Note: Nested grouping on a single axis is not currently supported.

For more information on using AtScale with Excel, see Using AtScale With Microsoft Excel On Windows.

ATSCALE-14621