Adding an Aggregate Data Action

You may want to update the data in the database outline to make sure that it is accurate. You can use the Aggregate Data action to update the data before and/or after a calculation is performed.

*  To add an Aggregate Data action to your business rule:

  1. If it is not open, open the graphical business rule to which you want to add an Aggregate Data action.

  2. If the BR Language tab is not selected, select it in the bottom of the left frame of the Analytic Administration Services window.

  3. Expand the Actions folder and double-click the Aggregate Data action, or drag it to the desired location in the business rule on the Graphical tab.

    The Aggregate Data action is added to the business rule and the Overview tab is displayed.

  4. On the Overview tab, choose one of the following three calculation options:

    • Calculate Entire Database (CALC ALL)—Calculates and aggregates the entire database based on the database outline.

    • Calculate Entire Database with Two Pass Calculation (CALC TWOPASS)—Calculates all members tagged in the database outline as Two Pass Calc. These members must be in a dimension tagged as Accounts.

    • Calculate Portion of Database Specified Below (default selection)—Calculates only the portion of the database outline specified in the table. The Calculate Portion of Database Specified Below option is the default selection that you can use to change or select the dimensions you want to calculate.

      For additional information on these calculation options, see the Essbase Technical Reference.

  5. If you selected Calculate Portion of Database Specified Below in step 4, for each dimension in the list that you want to calculate, select the value type using the Value Type drop-down list.

    Value Type

    Description

    Not Specified

    The default for every dimension

    User Defined

    Members defined by the user through Member Selection

    Derived

    The union of values defined in the destinations before the aggregation component and the values defined in the sources after the aggregation

    All

    All members to be included in this dimension’s aggregation

    Note:

    If all dimensions are <Not Specified>, no aggregation is performed. If at least one dimension has members selected, an aggregation is performed for the member values of dimensions that have selections and for all member values of the remaining dimensions.

  6. If you selected the User Defined value type in the previous step, the Member Selection button is available. Enter a member value to be aggregated or select the values to be aggregated using the Member Selection button.

    If you selected the Derived value type, values are created dynamically from the union of values in the Formula, Copy, and Clear destinations in previous components and the Formula and Copy sources in later components. Any additions or changes to the other components are automatically updated in the aggregate component. For additional information on calculation commands, refer to the Essbase Technical Reference.

  7. Select one of the following methods of calculating the database:

    • Aggregate Missing Values in the Database (SET AGGMISSG)—Specifies whether #MISSING values are aggregated in the database. If you select it, the component aggregates missing values. The default is determined by the database setting defined by the administrator. For additional information, see SET AGGMISSG in the Essbase Technical Reference.

    • Aggregate Data up to Local Currency (SET UPTOLOCAL)—Restricts aggregations to those parents with the same defined currency. The setting is only valid if the Analytic Services currency conversion module is installed and the database is associated to a currency database. This option is not selectable if none is detected. For example, all cities in France use the franc (FF) as the unit of currency. Therefore, all children of France, such as the cities Paris, Nancy, and Avignon, consolidate to France. Aggregation stops at this level, however, as Europe also contains countries that use other currencies. The option is set to Off by default if there is a currency dimension. For additional information, see SET UPTOLOCAL in the Essbase Technical Reference.

    • Optimize the Calculation of Formulas on Sparse Dimensions (SET FRMLBOTTOMUP)—Optimizes the calculation of formulas on sparse dimensions in large database outlines by turning on the bottom-up sparse formula calculation method. The default is Off or not selected as a calculation option. If you set it to On, you can use CALC DIM and CALC ALL commands to calculate large, flat dimensions of the database more efficiently. See SET FRMLBOTTOMUP in the Essbase Technical Reference.

    • Use Calculator Cache (SET CACHE)—Specifies the size of the calculator cache. The database uses the calculator cache to create and track data blocks during calculation. Using the calculator cache can significantly improve your calculation performance. The size of the performance improvement depends on the configuration of your database. The default is Not Selected which does not add a Set Cache command to the business rule. If you set it to On, there are five options: All, Default, High, Low, and Off. The size of the calculator cache at the levels is defined in the CALCCACHE {HIGH | DEFAULT | LOW} settings in the ESSBASE.CFG file. See SET CACHE in the Essbase Technical Reference.

  8. Optional: On the Range tab, restrict the aggregation to a range of members and variables in one or more dimensions. For each dimension, enter a range of members and variables in the Selected Values text box, or double-click the magnifying glass button to select a range of members and variables. For more information on selecting members, see Selecting Members.

  9. Click Save to save your changes.