MicroStrategy ONE

Disabling dynamic sourcing for aggregate tables

Reports that use aggregate tables are available for dynamic sourcing by default, but there are some data modeling conventions that should be considered when using dynamic sourcing.

In general, aggregate tables allow accurate data to be returned to reports from Intelligent Cubes through dynamic sourcing. However, if the aggregate tables use an aggregation other than Sum, or there is different data between aggregate tables and other tables in the data warehouse, this can cause aggregate tables to return incorrect data when dynamic sourcing is used. An example of an aggregate table not containing the same data is if an aggregate table includes data for years 2026, 2027, and 2028 but the lookup table for Year only includes data for 2027 and 2028.

These scenarios are uncommon. However, if some aggregate tables do fit these scenarios, you can disable dynamic sourcing when these aggregate tables are used in reports and Intelligent Cubes. This VLDB property has the following options:

  • Aggregate tables contain the same data as corresponding detail tables and the aggregation function is SUM: This is the default option for aggregate tables, which enables aggregate tables for dynamic sourcing.

  • Aggregate tables contain either less data or more data than their corresponding detail tables and/or the aggregation function is not SUM: This option disables dynamic sourcing for aggregate tables. This setting should be used if your aggregate tables are not modeled to support dynamic sourcing. The use of an aggregation function other than Sum or the mismatch of data in your aggregate tables with the rest of your data warehouse can cause incorrect data to be returned to reports from Intelligent Cubes through dynamic sourcing.

For information on defining default dynamic sourcing behavior for aggregate tables, see Accessing the dynamic sourcing VLDB properties for a project.

The procedure below describes how to disable or enable dynamic sourcing for an individual report that uses an aggregate table.

Prerequisite

  • A report has been created in a project.

To enable or disable dynamic sourcing for a report that uses an aggregate table

  1. In MicroStrategy Developer, browse to a report, then right-click the report and select Edit. The report opens in the Report Editor.

  2. From the Data menu, select VLDB Properties. The VLDB Properties Editor opens.

  3. From the Tools menu, select the Show Advanced Settings option if it is not already selected.

  4. In the VLDB Settings list, expand Dynamic Sourcing, and then select Aggregate Table Validation.

  5. Clear the Use default inherited value check box.

  6. Select one of the options depending on whether you want to disable or enable dynamic sourcing for a report that uses aggregate tables:

    • Aggregate tables contain the same data as corresponding detail tables and the aggregation function is SUM: This is the default option for aggregate tables, which enables aggregate tables for dynamic sourcing.

    • Aggregate tables contain either less data or more data than their corresponding detail tables and/or the aggregation function is not SUM: This option disables dynamic sourcing for aggregate tables. This setting should be used if your aggregate tables are not modeled to support dynamic sourcing.

  7. Click Save and Close to save your changes to VLDB properties and close the VLDB Properties Editor.

  8. Click Save and Close to save the report and close the Report Editor.