1. Administering Financial Consolidation and Close
  2. Working with Rules
  3. Using the Control To-Date View Storage Option

Using the Control To-Date View Storage Option

About the Control To-Date View Storage Option

By default, the Consolidate and Translate rules automatically calculate and store all the View member values for QTD, YTD and HYTD members (if enabled) during the consolidation and translation processes. The Control To-Date View Storage application setting provides the option of whether or not to store the To-Date View member values.

The Control To-Date View Storage option is only available for Extended Dimension applications.

Choosing not to calculate and store To-Date View member values can result in smaller application size, faster consolidation times, faster restructure times, and easier maintenance.

Note: Before you enable the Control To-Date View Storage option, you should consider the possible effects on your application. Make sure you review and understand the following steps before enabling the feature, and to see the best performance results.

You enable the Control To-Date View Storage option during application creation, or from the Application Configuration screen for existing applications. See Enabling Application Features.

When you enable this feature, the system automatically adds the following Consolidate and Translate rules to the application:

  • Consolidate - by selected View

  • ForceConsolidate - by selected View

  • Translate - by selected View

  • ForceTranslate -by selected View

In the "Consolidate or Translate - by Selected View" rules, you select the required View dimension members (Periodic is selected by default and is always calculated). During the consolidation or translation process, the system runs the calculations for only the View member or members that you selected.

When you run these rules, the To-Date values are deleted from the application. For best performance, you should also restructure the cube.

If you want to reduce the application size by removing the To-Date values for already locked periods, you must unlock the period and reconsolidate the Scenario/Year/Period/Entity combinations as outlined below. The performance improvement will be best if you perform this step for all Scenario/Year/Period/Entity combinations.

If you prefer not to do these steps, you can continue to use the existing Consolidate and Translate rules, even after enabling this feature.

Using the Control To-Date Storage Feature

Important: Since these actions will take a while to complete depending on the size of the application, you must disable any scheduled jobs, Automated Maintenance Window (AMW) processes or any background processes until this process is completed.

  1. On the Home page, click Application, select Configuration, then from the Enable Features screen, enable the Control To-Date View Storage feature. Wait for the functionality to be enabled, then log out and log back into the application.

  2. On the Home page, click Rules and from the Rules list, verify that the Consolidate and Translate "by Selected View" rules have been added to the application.

  3. From Rules, run Consolidate –by selected View on all months loaded to the application. This process will delete all QTD, HYTD and YTD data currently stored in the application.

  4. From Rules, run the ClearEmptyBlocks rule.

  5. From the Navigator, select Jobs from the Application column. Click on Schedule Jobs and run a Restructure Cube job. See Restructuring Cubes.

  6. Run a database refresh, then test consolidation times using the Consolidate – by Selected View rule.

  7. Tip: You can use the Disable To-Date View Calculations setting in Application Settings to skip computing and storing View members as part of data loads to see improvements in data loads as well. See Disabling View Calculations.

Expected Results

  • Application size reduced by approximately 40% or more.

  • Consolidation time decreased by approximately 40% or more.

  • Restructure times decreased by approximately 25% or more.

Next Steps - Modifying Member Formulas

The Control To-Date View Storage option will have an impact on custom-created member formulas. After you have enabled the option and reduced your cube size, you must make the following change to any of your current member formulas that return a constant number.

Example: Original Formula


Original Member Formula example

This should be changed to:


Changed Member Formula example

The system stores a zero (0) at the intersection "Scenario"->"Years"->"Period"->"Entity"->"Account"->"FCCS_Entity Input"->"FCCS_No Data Source"->"FCCS_No Movement"->"FCCS_Periodic"-> <No members of Currency, MultiGAAP and Custom dimensions> " to be used for this purpose.

Possible Business Flows for To-Date View Values

  1. Approach 1: Continue using the existing Consolidate and Translate rules. You do not need to enable the Control To-Date View Storage feature. If you select Consolidate or Translate, then during the consolidation or translation process, the system automatically calculates and stores the QTD, YTD, and HYTD values for every intersection in the system. Note that this will make the cube size very large.

  2. Approach 2: Use the new Consolidate - by Selected View and Translate - by Selected View option by selecting only the Periodic member. When these rules are run, only the Periodic data is consolidated and stored. To retrieve the data in the QTD, YTD, and HYTD View members, use the associated View dimension system _Rule members: FCCS_QTD_RULE, FCCS_YTD_RULE, and FCCS_HYTD_RULE. These are Dynamic Calc members and the performance of retrieval will depend on your application. See Troubleshooting Financial Consolidation and Close Retrieval Performance and make sure you follow all the steps. This should resolve any issues with using the _Rule members. If you are not satisfied with the results, use Approach 3.

  3. Approach 3: Use the new Consolidate - by Selected View and Translate - by Selected View option by selecting only the Periodic member. Then run the Update View Calculations rule. When these rules are run, only the Periodic data is consolidated and stored. Based on your existing reports and forms, as a one-time exercise, you will need to identify all Scenario, Year, Period, Entity, Currency, and View members on which you want to report. You can then run the Update View Calculations rule using these parameters. This process will compute and store only those intersections on which you are reporting. This operation will take considerably less time than computing and storing View member values for all intersections.