Query Settings

You can view and modify the following engine-level settings for queries.

Language settings

• query.language.mdx.dimquery.result.limit: 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). When set to -1 it is disabled, and the limitation from the query.result.max_rows setting is applied.
• query.language.mdx.vba.format.firstdayofweek: Determines the default value used for the First_Day_Of_Week parameter of the Format VBA function. The default value is vbSunday. For more information, including supported values, see Format.
• query.language.mdx.vba.format.firstweekofyear: Determines the default value used for the First_Week_Of_Year parameter of the Format VBA function. The default value is vbFirstJan1. For more information, including supported values, see Format.

Including comments in queries

When you need to track and analyze queries, you can include dedicated comments in the inbound queries.

To configure AtScale to propagate these comments to the outbound queries, enable (set to true) the following engine settings:

• query.includeComments.general.enabled: Whether to include comments in the outbound query from system-generated queries. Default value is true. Should be set in Custom Settings.
• query.includeComments.general.userComments: Whether to include the user comments in the outbound query from inbound queries. Default value is true.
• query.includeComments.general.queryId: Whether to include the query id in the outbound query from system-generated queries. Default value is false. Should be set in Custom Settings.
• query.includeComments.general.userId: Whether to include the user id in the outbound query from system-generated queries. Default value is false. Should be set in Custom Settings.

For SQL language queries, the comments must be located in the body of the query, right after the SELECT keyword. They should be wrapped like this: :

/* user_comment(`comments here`) */

Here is an example: :

SELECT /* user_comment(`comments here`) */
  CAST('Can you see the comment?' AS BOOLEAN)
FROM
  asadventuresmall
LIMIT
  1

For MDX language queries, the comments must be located in he body of the query, in the PropertyList > UserComments tag. Here is an example:

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
      <Command>
        <Statement>
          (statement ...)
        </Statement>
      </Command>
      <Properties>
        <PropertyList>
          <UserComments>This is a user comments space</UserComments>
          <Catalog>EngineDemo</Catalog>
          (other tags ...)
        </PropertyList>
      </Properties>
    </Execute>
  </soap:Body>
</soap:Envelope>

Query planning settings

• query.planning.semiAdditive.defaultAggregationWhenOutOfScope: Configures the default totaling behavior for semi-additive measures. When True (default), the first/last non-empty or first/last child subtotal or grand total is shown only when the query contains one of the measure's configured semi-additive dimension attributes. When this setting is False, the first/last non-empty or first/last child subtotal or grand total is shown when the query does not contain a configured semi-additive dimension attribute.

• QUERY.PLANNING.ALLOWCTES: Controls whether the AtScale engine uses common table expressions (CTEs) to reduce redundancy in queries. Enabling this setting helps reduce query size. This setting is enabled by default.

• QUERY.PLANNING.SEMIADDITIVE.FORCEINDIVIDUALMEASURECHECK: Requires each semi-additive measure to be in its own subquery to ensure the conditions are restricted to itself and not affected by other measures. This setting is disabled by default, and does not require an engine restart.

  Important

  When this setting is false (default), AtScale does not submit subqueries per individual semi-additive measure, 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 true. When true, AtScale submits subqueries for individual measures, which ensures a correct response, but results in multiple passes of the data.

More information

Engine Level Configuration Settings