Enterprise Performance Management (EPM) Cloud Nightly Application Maintenance - Part 2: Optimizing Planning Maintenance Routines

Published April 29 2020 by Ross Martin
Back to insights

In Part 1 of this series, we discussed the details of Oracle Enterprise Performance Management (EPM) Cloud’s Automated Maintenance Window (AMW). In this post, let’s consider some other activities that can be incorporated into your overall maintenance routine to maximize the performance of your application, minimize overhead, and best prepare you for an agile response in the event of disaster.

First, let’s lay out some hypothetical application details for this exercise:

  • Multiple interactive (input) plan types
    • Block Storage (BSO)
    • Support Forecast and Budget entry
    • Perform most of the calculations/processing of the data
    • Minimal aggregations
  • One read-only reporting plan type
    • Aggregate Storage (ASO)
    • Large dimensions and sparse data
    • Aggregate views to optimize query times

To quickly recap, the AMW performs the following 3 activities, not necessarily in the order presented, within the 60-minute allotted timeframe each day:

  • Restart the application
  • Full backup (or “snapshot” as Oracle calls it) of the application objects and data
  • Deploy patching when applicable

We also discussed the best practice to download the backup/snapshot to a local environment and retain a number of historical backups in the event of disaster (remember the 3-2-1 framework!). Some additional maintenance activities that you may want to incorporate into a maintenance routine, given the above application topology, could be:

  • Clearing upper-level blocks
  • Purging hard zeros
  • Clearing aggregate views
  • Building/Rebuilding aggregate views
  • Merging incremental slices

Clearing Upper-Level Blocks

If your interactive plan types aren’t used for reporting at an aggregated level, it may make sense to purge upper-level blocks often. Clearing upper-level blocks will reduce the number of existing blocks, reduce your index size, decrease the amount of data on disk, and potentially improve processing times within the interactive plan types.

Purging Hard Zeros

Despite their nature, numerical zeros consume space within databases like Essbase, especially within the BSO plan types. During planning and forecasting cycles, many users can and do submit zeros to the database through Planning forms or Smart View ad hoc sheets. While seemingly inconsequential, these zeros often slow down processing by way of consuming blocks. They also impact the size of backups, extracts, and integrations. Therefore, it is recommended to purge zeros on a nightly basis.

Building and Clearing Aggregate Views

Aggregate views within ASO plan types pre-aggregate swaths of data within the database to enable faster query times. As metadata changes, aggregate views need to be rebuilt. Aggregate view build routines can be set up to respect settings such as including alternate rollups, basing the views on the most common user queries (“query tracking”), and growth sizing limitations. As the pre-aggregated data consumes physical disk space within the application, views influence the size of the backups. Therefore, we recommend you clear the aggregate views before the nightly AMW, and rebuild the views after the AMW completes. This is especially true for very sparse ASO data.

At one recent client with very sparse data, the aggregate views grew to be much larger than the size of the input data. This caused long backup times during the AMW and even longer download times to the client’s backup repository. By clearing aggregate views before the AMW and rebuilding them after, the backups drastically reduced in size, saving download time and potentially application restoration time, should the need arise.

Merging Incremental Slices

As data is loaded to ASO plan types, either through Smart View, Smart Push, Data Maps, or integrations, the data is layered into incremental slices. Incremental slices combine with data from the main slice to produce the values that are rendered upon the query. Said another way, the application intelligently combines the numbers in each slice when querying an intersection. Consider the below example of how incremental slices work given a specific intersection:

Slice

Value

Main Slice

10

Slice 1

5

Slice 2

(6)

Number when Queried

9

Merging slices reduces the number of incremental slices and data points, which given the above example, would result in:

Slice

Value

Main Slice

9

Number when Queried

9

Merging incremental slices increases the performance of queries within the ASO plan type while reducing the amount of data held by the application, as incremental data points are removed. Jobs that merge slices can also be set up to purge hard zeros from the ASO plan type, which is a recommended option to further reduce the dataset. We recommend merging slices in your application at least once per day.

Cut To The Chase

All the mentioned components can be incorporated into a maintenance cadence and scheduled to run in an automated manner to keep your application tuned and tight. Below is an example of an automated maintenance schedule:

Remember, you will need to balance maintenance activities with availability. Some important considerations to keep in mind when determining the balance that works for your business:

  1. The run-time of each process is variable and dependent on the application(s).
  2. Data may not be available for query or load during some of the activities, as noted.
  3. You will need to take the business needs and process run time into consideration when determining a schedule that works best.

If you would like to learn more about scheduling and optimizing routine maintenance activities for Oracle EPM Cloud environments, we would be happy to assist you.

For comments, questions, or suggestions for future topics, please reach out to us at infosolutions@alithya.com.  Visit our blog regularly for new posts about Cloud updates and other Oracle Cloud Services such as Planning and Budgeting, Financial Consolidation, Account Reconciliation, and Enterprise Data Management.  Follow Alithya on social media for the latest information about EPM, ERP, and Analytics solutions to meet your business needs.

Contact us