Clearing Essbase ASO Cubes in PBCS

For those that do not know what an Essbase ASO Cubes is keep reading this paragraph, the rest please collect your boarding pass and proceed to next paragraph. I suppose if you are still reading this paragraph then your Hyperion knowledge is limited, so let me see what justice I can do in explaining Essbase and the Essbase database options in a few lines (but I do promise I will deep dive into Essbase Database Technology in a detailed Blog Post in the future). Firstly, lets start with Essbase, Essbase is a multidimensional database management system (MDBMS) that provides analytical applications/information. Unlike on-line transaction processing (OLTP), Online Analytical Processing (OLAP) is a database technology that is optimized for dimensional queries rather than transactions. Essbase is the industry-leading multidimensional OLAP server that delivers multidimensional analysis of business data and provides the capability for complex calculations, aggregations and data modelling. Essbase offers two database options, Aggregate Storage Option and the Block Storage Option. For analytical applications, Essbase block storage (BSO) cubes provide a world class analytical engine for complex modelling. BSO cubes can store data at all levels of the hierarchies and typically contain less dimensions and smaller dimensions than ASO cubes. BSO cubes are typically used for performing complex calculations and logic modelling. ASO cubes only contain data storage at bottom levels of the hierarchy and are designed to provide rapid aggregation capabilities (on the fly). ASO cubes are thus typically used for reporting purposes and BSO cubes are used for both complex modelling and reporting. Technically there is also the best of both worlds option, Hybrid BSO, where in very basic terms the complex logic/calculations can be done at bottom levels of the cube and then dynamically calculated to parent levels using ASO style aggregations. At the time of writing this article Hybrid BSO was not yet available on Oracle Planning Budgeting Cloud Service (PBCS).

Now that we have got the basics out of the way, the question is, Why do we need to clear ASO data? Well we are dealing with data and data movements and whether you need to clear data permanently (EG. Clearing data entry after testing or after data has been snapshotted in a calculating scenario to clean the slate prior starting a new forecast/budgeting period) or in an automated/scheduled fashion (EG. Clearing data nightly prior loading actual data in an automated format). Whatever your data clearing requirements, the below should guide you on this.

The options are as follows:

1. Calc Manager MDX Clear
2. Overview MDX Job Clear
3. Business Rule Clear
4. Data Management / FDMEE Clear

Below is the information summarised for each option:

Calc Manager MDX Clear – This is quite simple direct MDX capability exists to clear ASO. It allows full, aggregations or partial data clearing options. The approach cannot be scheduled. To access this approach follow the below sample steps.

• Overview MDX Job Clear – This is quite simple direct MDX capability exists to clear ASO. It allows full, aggregations or partial data clearing options. Some key notes for this approach:
  • The approach can be scheduled currently through Jobs scheduler.
  • Oracle aims to provide an EPM Automate ClearDataSlice options as part on 17.06 17.07 release.
  • At times when using the partial clear member selector the MDX generated will not validate and will need to be entered or adjusted manually (keep an eye out for this). If you are not proficient with MDX you can use the Calc Manager approach to generate the code that you can copy and paste. Assume this is a bug that Oracle will fix at some point.

To access this approach follow the below sample steps.

As can be seem above the Account dimension is automatically selected when only the Actual member was selected using the selector. We will adjust as shown below:

• Business Rule Clear – This approach uses standard business rule calculations to clear data. The nice things about this approach is that Business Rule variables can be used. This approach can be scheduled via Jobs scheduler or via EPM Automate “runbusinessrule” commands. Please note: All dimensions in the POV must be selected and at bottom levels as this is an ASO cube. A single member from the POV is then used to subtract from itself leaving a 0 value (EG. “Month” = “Month”-“Month”).

 

• Data Management / FDMEE Clear – This approach is only valid as part of a data management load EXPORT MODE options. You can use the REPLACE_DATA Option to clear the Point of View (POV) data and replace it with data in the Data Management staging table. The data is cleared for Scenario, Version, Year, Period, and Entity POV that is loaded as part of the load. This is tricky to use as the Entity dimension maybe have data move from one cost centre to another and this wont get deleted as the POV may change in this regard. There is an option to OVERRIDE ALL DATA—Clears all data in the target, and then loads from the source or file. Again this is not really useful as you almost never want to delete all data in target prior loading. Please note: If you intend to use the Override All Data option you will need to enable it from Application settings; Set the Display Data Export Option Override All Data to “Yes”. 

Advertisement