Profitability Tech Tips: New Functionality – Query Based Optimized Aggregate Views
Every so often, Oracle blesses us with new functionality in the Oracle Cloud PCM world, and the latest patch release (21.11) is one such occurrence! So what is this new feature? Well, if you look at the Monthly Update release for November 2021, you will see something similar to the below:
Let’s unpack what the new feature does and how to use it.
What are Query Based Optimized (QBO) Aggregate Views?
For those who have long been working in Oracle EPM, specifically Essbase, the term Query-Based Optimized Aggregate Views will most likely be familiar. It’s an optimization technique that we can leverage in creating Aggregate Views. This uses the query history to create more optimized and efficient Aggregate Views that can lead to significant query performance improvements.
Aggregated Views are important because they have a significant impact on performance when executing queries against the Essbase database for Reporting. In a simplistic explanation, we execute queries whenever we try to retrieve data from the Essbase database; this happens when we refresh our Smart View reports, execute Financial Reports (FR), run PCM analytics, extract data through the PCM Queries, and via Cloud Data Management (CDM) load rules. The more optimized we can make our Aggregate Views, the faster these queries will run.
Before the release of the new functionality, there was only one way for us to create Aggregate Views in Oracle Cloud PCM; when we select the “Optimize for Reporting” radio button during the execution of our calculations, the default Aggregate Views are created.
While this mechanism was a welcome feature in PCM and will be sufficient for the vast majority of clients, some clients with very large and/or inefficient data models may require Aggregate Views that are more optimized to their specific query needs. The new functionality is aimed specifically at these clients.
How To Create Query-Based Optimized (QBO) Aggregate Views in 6 Steps:
Although the November Monthly Update lists the new functionality as the introduction of a new REST API, we are going to show how to execute the steps through EPM Automate using the “optimizeASOCube“ command. This approach is a little less technical (if you would like how to execute the commands through the REST API, drop me a note on LinkedIn.
Step 1: Execute all required POV Calculations
In order to create our materialized Query-Based Optimized (QBO) Aggregate Views, we want to first execute our required POV calculations. Calculations are destructive when it comes to Aggregate Views, so it’s best to calculate and have a full data set before we continue.
When executing your calculations, you can choose to either deselect the “Optimize for Reporting” radio button or leave it selected as the default. If you leave the “Optimize for Reporting” enabled, your queries will run faster in Step 3, but I would suggest running the “clearAggregations” parameter prior to running Step 4 just to make sure you accurate performance metrics when testing in Step 5.
Step 2: Turn on Query Tracking
In order to produce the QBO Aggregate Views, we need to tell the system to start tracking our queries so it can see how best to optimize. To do this, we need to log into EPM Automate and issue the “optimizeASOCube“ command with the “ startQueryTracking” parameter the syntax is as follows (replace <APPLICATION_NAME> with your application name):
epmautomate optimizeASOCube <APPLICATION_NAME> startQueryTracking
Step 3: Run Desired Queries
Now that we have enabled query tracking, go ahead and execute all the SmartView and Financial Reports, Analytics, and exports that you would expect to run as part of your Business As Usual (BAU). Ideally, you want to run long-running queries, or at a minimum, the ones that are causing the most hassle!
In this step, I would suggest taking note of the run time of your queries. Unfortunately, in Oracle Cloud PCM, there isn’t an easy way to track the execution time of SmartView or Financial Report retrieves however, Query and CDM Exports can easily be tracked through the Job Console for Query Exports executed in PCM and Process Details for CDM Exports.
Step 4: Create Query Based Optimized based Aggregation Views
Great, so now let’s create and materialize those QBO Aggregate Views! Again, we are going to use EPM Automate by using the “optimizeASOCube“ command, but now we are going to use the “createQBOAggregations” parameter, but you could use the REST API if you want to go that route.
The syntax will look as follows for EPM Automate:
epmautomate optimizeASOCube <APPLICATION_NAME> createQBOAggregations
Step 5: Test Performance
Now that we have created and materialized the QBO Aggregate Views, we want to see if it makes a difference. Let’s go ahead and execute the same queries as in Step 3, noting the query execution times as we did before.
If the performance is now satisfactory, we can go ahead with the next step. If the performance is unsatisfactory, we may want to return to Step 3 and execute some additional queries to ensure that the Aggregate Views have been optimized sufficiently.
Step 6: Turn Off Query Tracking
Now that we are happy with the query performance, we can now go ahead and turn off the Query Tracking by running the following EPM Automate command and parameter:
epmautomate optimizeASOCube <APPLICATION_NAME> stopQueryTracking
There are two other parameters that we can use when executing the optimizeASOCube EPM Automate command and REST API; “clearAggregations” can be used when we want to remove any existing Aggregate Views and ”createAggregations” can be used to create the default Aggregate Views (similar to “ Optimize for Reporting”.
For more information on optimizeASOCube go to “https://docs.oracle.com/en/cloud/saas/enterprise-performance-management-common/cepma/epm_auto_optimize_aso_cube.html”
A Word of Warning
Now that you have learned all about the new functionality coming to PCM in the November patch, it’s worth closing with a word of warning! Although the new functionality is a welcome addition to PCM, Query performance is significantly impacted by how we design and implement our applications. Design decisions have a significantly greater impact on our reporting performance than the QBO Aggregate Views ever will. Sometimes having extremely poor query execution times is symptomatic of a larger issue that needs to be resolved although, this functionality may assist in the short term or if no other alternative exists.
For comments, questions, or suggestions for future topics, please reach out to us at email@example.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.