Vatsal Gaonkar
, March 15, 2021
Share this article

Often, it’s necessary to clear data from Oracle Enterprise Cloud Management (EPM), whether it’s for administrative or user functions. In this blog, I will describe the top three data clears below, as well as situations in which these are helpful.

Data Load Clear 

Commonly, we clear data before we load. Loading can occur using data management in the cloud or the import data functionality, which loads directly into the target plan type, using either a file system or a native Essbase file system. It’s a good idea to clear the target before loading so that you get the cleanest cut of data loaded. For example, loading actual data from a year-to-date data source, meaning the data source provides full fiscal year-to-date data. If your fiscal year is January through December, and you're loading May 2020, the data source will give you the data from Jan through May 2020.  

Another typical data loading example is the month-to-date data source, wherein only the May 2020 data source will be provided. The scope of the data source will govern what the clearing scope will be. Actuals was one good example, but there could be many such examples of loading, whether it's loading drivers or loads of departmental budgets from templates. 

Hot tip: CLEARDATA does not work if placed in an IF statement. Use CLEARBLOCK instead of CLEARDATA if you wish to remove blocks from the database, which can improve performance.

Essbase has two distinct storage options: Aggregate Storage Option (ASO) and Block Storage Option (BSO). Each has its own unique significance.

Data load clears in BSO 

For clears prior to data-loading, the scope of the clear is defined based on the data source. Data management provides easy and powerful options to clear data in the target as the load is happening. BSO as a target will avail both business rules and clear region methods to clear data. There's an event-based option, which means that data can be cleared using a business rule in the target based on the events in the data load cycle.  

Remember, when it comes to data management, any data load goes through a four-step process of import, validate, export, and check. The business rule at the target can be invoked before or after, either imports, load, or checkpoints of the data loading cycle. 

There's also an option to dynamically generate the scope of the clear based on clear region definition. The definition is used to compile a business rule on runtime, which is executed prior to data loading. The REPLACE DATA at target in the data integration load rule invokes the clear region definition of one that's defined. 

There are other, more extensive options to clear BSO targets as well. Using clear cues for BSO targets, you can clear everything or just upper-level blocks or dynamic blocks, or empty blocks. We use the clear empty blocks quite a bit in our implementations when we have extensive calculation scripting logic that is creating non-existing blocks. Other available options are to clear relational data that resides in the system such as supporting detail, attachments, and comments. 

How-To: 

On the Clear Cube page, click Create, complete the job name, select the cube, then select a clear option: 

Clear All—For both block storage and aggregate storage cubes, delete all data within a cube that is associated with the entities you select:

  • Supporting Details
  • Comments
  • Attachments
  • Essbase Data

Data load clears in ASO 

Data management business rules and clear region 
With ASO as a target to load in data management, the clear region option along with the clear region definition dynamically executes an MDX script to execute a partial physical clear on the ASO target. Clears on the ASO target can either be logical or physical. Logical clears create reversing entries to nullify the data, while physical clears wipe the data on the ASO target. 

Clear cube – clear all data 
Another option to clear an ASO plan type is through the clear cube. The clear cube on an ASO plan type can be used to clear all data, or all aggregations for an ASO plan type. When it comes time to defining the scope of the clear, it comes down to the same methods of whether you want to create using logical or physical clear methodologies. In each case, a scope must be defined much like the scope defined in the data integration clear region definition.

Focused Clears

Focused clears are more process oriented. For instance, a REST API invocation that requests a focused clear. In focused clears, the underlying artifact for clearing has a global scope, but using a REST API invocation overrides that scope. In our experience, we have used focused clears for integrating data between plan types in an EPM Cloud Planning when a change in the source dynamically generates the scope of the clear on target as opposed to a predefined clear scope.

 

Once a clear cube job has been defined with the scope, you can further override the definition on the clear cube job using a planning REST API invocation. We have invoked this Planning REST API from a groovy business rule. The use case here is that a business user makes a change in a data form, and we want to clear a focused intersection in the reporting plan type to which a data map will push data.

Now, keep in mind, in our implementation example, the planner was just changing a separate data point. Let's say it's a planning assumption for a department. Upon changing this assumption, we recalculated revenue accounts in the BSO plan type that now needs to be pushed to the ASO reporting plan type. From the form, we are invoking the rule to first recalculate the revenue accounts, and then clear the ASO plan type by overriding a global ASO clear cube definition, which is already defined. 

The override in this case will be to just clear revenue accounts in the ASO reporting plan type for the changed department. After clearing the department, the data map will be run to push revenue accounts for the changed department only.

Other Clears

Clears that fall into the “other” category are mainly utility-oriented and are quick. Administrators can quickly clear process-specific data with a simple click.  

These kinds of clears are quick administrative functions to either clear relational data such as cleaning supporting details, comments, or attachments on cells. Much like the clear cube jobs. You can perform the same kind of clears from the calculation monitor in database properties.

 

For implementers and customers alike, this information on clearing targets should be helpful.

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.

Share this article