1. Administering Financial Consolidation and Close
  2. Working with Rules
  3. Troubleshooting Financial Consolidation and Close Retrieval Performance

Troubleshooting Financial Consolidation and Close Retrieval Performance

The following troubleshooting steps can help retrieval performance in a Financial Consolidation and Close environment.

Perform a Smart View Health Check in Your Environment

You can perform a health check on your environment to optimize performance. See Performing a Health Check On Your System in Oracle Smart View for Office User's Guide.

Reviewing and Changing Smart View Ad Hoc Behavior

You can review the Smart View Ad Hoc behavior that you set on the Application Settings tab. Changing Smart View Ad Hoc Behavior to Native may help zoom performance. See Smart View Behavior Options in EPM Cloud in Working with Oracle Smart View for Office.


Reviewing Smart View Setting

Application Metadata Analysis

To ensure that your metadata is valid, you can run Validate Metadata from the Application Overview tab at any time. See Validating Metadata.


Validating Metadata process

Fix any metadata errors when possible, especially members with Level 0 Dynamic Calculations without formulas. If necessary, add a stored child placeholder member as described below. A Level 0 Dynamic Calc member must have a member formula or it will adversely affect performance.

Check seeded Level 0 Dynamic Calc Account Dimension members

Review the following FCCS seeded members in the Account Dimension in the Dimension Editor to ensure they each have a child member with Data Storage set to Store.

If your application does not need child members for any of the following FCCS seeded members, you will need to create a stored "dummy" or placeholder account since you cannot directly change the FCCS seeded members to Store.

  • FCCS_Cash And Cash Equivalents
  • FCCS_Acct Receivable
  • FCCS_Inventories
  • FCCS_Fixed Assets
  • FCCS_Other Long Term Assets
  • FCCS_Acct Payable
  • FCCS_Other Current Liabilities
  • FCCS_Long Term Liabilities
  • FCCS_Sales
  • FCCS_Cost of Sales
  • FCCS_Operating Expenses
  • FCCS_Other Income Expense
  • FCCS_Provision for Income Tax
  • FCCS_Other Equity

For example, if "FCCS_Cash And Cash Equivalents" does not have a stored child member, create one called "FCCS_Cash And Cash Equivalents_Placeholder".


Example of Placeholder Member

Make sure that you set Data Storage to Store on both Member Properties and Member Formula tabs.


Member Properties tab

Member Formula tab

Ensure Correct Solve Order for Dynamic Calc Dimension Members

The following table shows the Consol Solve Order values when you are using the Standard option of Account as the Dense dimension.


Solve order table for standard option

The following table shows the Consol Solve Order values when you are using the Dense Sparse Optimization option that uses Period and Movement as the Dense dimensions.


Solve Order for DSO applications

Note:

A Solve Order conflict may exist with the Parent Total member formula and YTD formula. The FCCS_Parent Total member applies only for Parent Currency in Multiple-Currency applications. It is recommended that you use Entity Total instead of Parent Total, if possible. If not, try changing the Solve Order on FCCS_Parent Total to 51 and then refresh the database. Be sure to verify your data.

Example Screenshots for changing Solve Order

Click Applications, then Overview, then select the Dimensions tab. Right-click on a column and uncheck Default mode. Then scroll over to the Consol Solve Order column to edit it. You can click and drag columns for easier viewing.


Solve Order column

Member Properties solve order

After all the above changes have been made, run Validate Metadata again and then run Refresh Database.


Validating Metadata process

Note:

Unnecessary Solve Order on Custom Dimension

Be sure to clear any Solve Order on Custom dimension members. These conflict with the Account dimension solve order, and unnecessarily aggregate Account as the last dimension.

Two Pass Calculation on Account Dimension Hierarchy Not Required

Make sure that Two Pass Calculation is set to No. See Working with Member Formulas in Administering Financial Consolidation and Close .

Retrieval Optimization Analysis

Do not use HSGetValue Formulas

Convert your HSGetValue spreadsheet retrievals to Ad Hoc retrievals. They perform much better as retrieval size grows and are easier to maintain once converted. HSGetValue formulas are supported. Use them only when necessary for small to medium size retrievals.

Start with Periodic View

If you are using the Control To-Date View, the stored View dimension member YTD is #missing. Use the YTD_Rule member to dynamically calculate the YTD Balances upon retrieval. Note - You can use YTD or YTD_Rule member when using the Dense/Sparse Optimization option.

For Optimization Analysis, change your View from YTD_Rule to Periodic for quicker analysis. Once optimized, you can switch back to YTD_Rule as needed.

Analyze the Account Dimension for slow member formulas

Check performance for all accounts in the spreadsheet, especially with member formulas. Remove the accounts with member formulas from the spreadsheet and execute the retrieval. If the retrieval is now quick, add groups of accounts with member formulas back in the retrieval to isolate the slow members formula(s). Once isolated, optimize the member formula, ensuring the correct Consol Solve Order and add them back to the spreadsheet to confirm they are optimized.

If the retrieval is still slow without the member formulas and with Periodic View, you can continue to isolate account members to determine which ones are slow. This is a rare occurrence that is usually a metadata problem with level 0 dynamic accounts without formulas or a solve order problem.

For Hybrid-enabled applications, use @NONEMPTYTUPLE(); directive at the beginning of a member formula.

Enable the Control To-Date feature if needed

If the application is currently an Extended Dimension application that does not use the Dense/Sparse Optimization option and your YearTotal and QTD retrievals are slow, enable the Control To-Date feature. Enabling this feature replaces these formulas with more efficient ones.

Analyze the other Dimensions for slow member formulas if needed

Check performance one dimension at a time in the spreadsheet, especially with member formulas. If the Dimension to be analyzed is not in the POV dialog box, drag the Dimension into the POV dialog box. Then drill down in the POV Member Selector to the next level and execute the retrieval process. Continue doing this until you find the member formula or level member that slows down the retrieval. Once found, optimize the member formula or level member, ensuring the correct Consol Solve Order and add them back to the retrieval process.

Periodic to YTD_Rule

If your Periodic View retrieval is now faster after the preceding analysis and optimization, change your View to YTD_Rule as needed. Your YTD_Rule View member should now perform better. If not, consider pulling back the Periodic View for most of your Balance sheet accounts as they already have a YTD Balance, and then adding up the periods in Excel for your YTD balance for Income Statement accounts. You may have to make a few extra worksheet tabs with retrievals and add Excel sum formulas. You can then create a Summary YTD balance report worksheet tab referencing the Excel formulas and data on the other worksheet tab(s). Once done, you can just click Retrieve All in the SmartView menu and the multiple retrieval and Summary tabs will be refreshed and the formulas calculated.

Executing Update View Calculation Rule to optimize retrieval performance for the Control To-Date View Storage option

When you use the Control To-Date View Storage option, the stored View dimension member YTD is #missing and not calculated and stored unless you execute the Update View Calculation rule. The YTD_Rule member will calculate the YTD Balances dynamically and should be used when possible.

If a retrieval is still too slow using the YTD_Rule member, consider moving to the Dense Storage Option or pre-consolidating and storing YTD for some Entities. You can determine which Entities are slowest by using a process of elimination based on level in the Hierarchy and size, and then run the Update View Calculation rule for those Entities to store YTD and retest.

When you consolidate for those Entities in the POV(s) using this feature, the consolidated values will be stored in the YTD View member. Only the Entities consolidated with the Update View Calculation feature will have values for the YTD View member; all other Entity values will be #missing. For the other Entity values, you will need to use the YTD_Rule View member. You can mix and match YTD and YTD_Rule View members as it makes sense in your reports. For reports where this is not feasible and you are using just the YTD View member, you must remember to consolidate all Entities on the report using the Update View Calculation feature.

Each time a Periodic Consolidation is executed again for a Period, any previously executed Update View Calculation for that Period and Entities will need to be re-executed to reflect the latest Period Consolidation in the YTD values.

Disable View Calculations

Financial Consolidation and Close provides an Application Settings option to disable View calculations. This option disables computation and storing of YTD, HYTD, and QTD data for data input (through forms, data import, Data Management, and so on) and reporting in the YTD, HYTD, and QTD members. Turning this setting to Yes helps improve the performance of data loads and reports. For more information, see Disabling View Calculations in Administering Financial Consolidation and Close.

Review Spreadsheet design

Asymmetric

Symmetric report design is much faster than Asymmetric. A high number for nOdometers in the Activity Report (see the following sample report for an example) is a good indicator of an inefficient Asymmetric report. Ideally a Symmetric report would have nOdometers:1.

Top 10 Worst Performing Essbase Queries over 15 seconds


Essbase Query report

Ideally, the columns have only one Dimension where the members are changing, for example, periods (Jan-Dec). As you start adding more Dimensions, for example, Year (FY15,FY16,etc.), the grids become more Asymmetric and retrievals may slow down. The same concept applies for Rows.

When needed for performance, create multiple worksheet tabs, each pulling smaller specific retrievals. You can then use Excel functionality to combine data referencing the smaller specific retrieval worksheet tabs into a summary sheet. Once created, you can then just click Retrieve All in the menu and the multiple worksheet tabs and summary sheet will be refreshed.

Other Considerations

If an application has a member formula that requires a lot of data for proper calculation, it may make sense to store the data versus Dynamic Calc if the member is not in the Account the dimension for the Control To-Date View Storage, or the Movement and Period dimension for the Dense Storage Option.

Spreadsheets with a smaller number of worksheet tabs and well-defined Oracle Smart View for Office queries with dynamic POVs perform better than large spreadsheets with all kinds of different data and text.

Apply traditional Oracle Essbase Hybrid retrieval optimization procedures.

Using Substitution Variables

To improve performance for applications that use the Dense/Sparse Optimization option (where Period and Movement are the Dense dimensions), you can add the ParallelCustomDimDSO and ParallelCustomDimTranslation substitution variables. To see performance improvements, you should set both of these substitution variables to True.

To improve performance for multi-period consolidations in applications that use the Dense/Sparse Optimization option, you can add a substitution variable named EnableYearlyConsol and set the value to True.

See Substitution Variables for Financial Consolidation and Close.