Optimize ASO Cube

This command allows you to perform query optimization operations on ASO cubes in cases where default aggregation is deemed insufficient to meet your data extraction or reporting needs because of large data size. The typical optimzation process is as follows:

Drop default and query-based aggregations.
Start query tracking.
Run sample queries from Profitability and Cost Management Query Manager, Oracle Smart View for Office (Windows), or Data Management, and any other MDX queries representative of the type of queries for which optimization is desired to train Oracle Essbase.
Create aggregation based on optimized or default queries.

Required Roles

Service Administrator, Power User

REST Resource

POST /epm/rest/v1/applications/{AppName}/jobs/optimizeASOCube

Note:

Before using the REST resources, you must understand how to access the REST resources and other important concepts. See Implementation Best Practices for EPM Cloud REST APIs. Using this REST API requires prerequisites. See Prerequisites.

Request

Supported Media Types: application/json

The following table summarizes the client request.

Table 24-29 Parameters

Name	Description	Type	Required	Default
`api_version`	Version of the API you are developing with	Path	Yes	None
`appName`	Name of the application used to run Optimize ASO	Path	Yes	None
`type`	Type of operation. Valid values are: `clearAggregations` removes default and query-based views. `createAggregations` creates default Essbase aggregate views. Use this option to perform default aggregation instead of query-based aggregation. `startQueryTracking` starts query tracking. Use this option to allow Essbase to collect optimization information for creating query-based aggregations. `stopQueryTracking` stops query tracking. Use this option to stop Essbase from collecting optimization information. Essbase continues to collect optimization information until you stop query tracking or stop Essbase.) `createQBOAggregations` creates Essbase aggregate views based on the optimized queries that you run after enabling query tracking.	Form	Yes	None

Response

Supported Media Types: application/json

Table 24-30 Parameters

Name	Description
`details`	In case of errors, details are published with the error string.
`status`	See Migration Status Codes
`links`	Detailed information about the link
`href`	Links to API call
`action`	The HTTP call type
`rel`	Can be `self` and/or `Job Status`. If set to `Job Status`, you can use the `href` to get the status of the import operation.
`data`	Parameters as key value pairs passed in the request

Example of Response Body

The following shows an example of the response body in JSON format.

{
   "type":"Profitability",
   "status":-1,
   "statusMessage":"In Progress",
   "details":"BksML30_OptimizeASOCube_D20220511T115135_55d",
   "links":[
      {
         "href":"http://<BASE-URL>/epm/rest/v1/applications/jobs/ChecktaskStatusJob/BksML30_OptimizeASOCube_D20220511T115135_55d",
         "action":"GET",
         "rel":"Job Status"
      }
   ]
}

Java Sample – OptimizeASOCube.java for Profitability and Cost Management

Prerequisites: json.jar

Common Functions: See Profitability and Cost Management Common Helper Functions for Java

    public void optimizeASOCube() throws Exception {
        
        JSONObject json = new JSONObject();
        json.put("type", "createAggregations");
           
        String urlString = serverUrl + "/epm/rest/"+ apiVersion + "/applications/" + applicationName+"/jobs/optimizeASOCube";
        executeJob(urlString, "POST", json.toString());
        
    }

cURL Sample – OptimizeASOCube.sh for Profitability and Cost Management

Common Functions: See Profitability and Cost Management Common Helper Functions for cURL.

funcOptimizeASOCube() {
	url=$SERVER_URL/epm/rest/$API_VERSION/applications/$APP_NAME/jobs/optimizeASOCube
	    param="{\"type\":\"createAggregations\"}"
                echo $param
	 funcExecuteRequest "POST" $url $param "application/json"

	output=`cat response.txt`
	status=`echo $output | jq '.status'`
    if [ $status == -1 ]; then
        echo "Started Optimize ASO Cube successfully"
		funcGetStatus "GET"
    else
        error=`echo $output | jq '.details'`
        echo "Error occurred. " $error
    fi
	funcRemoveTempFiles "respHeader.txt" "response.txt"
}

Groovy Sample – OptimizeASOCube.groovy for Profitability and Cost Management

Prerequisites: json.jar

Common Functions: See Appendix C: Common Helper Functions for Groovy.

def optimizeASOCube() {
		
        JSONObject json = new JSONObject();
        json.put("type", "createAggregations"); 
		
        String urlString = serverUrl + "/epm/rest/"+ apiVersion + "/applications/" + appName +  "/jobs/optimizeASOCube";
                                    
        def url;
	try {
		url = new URL(urlString)
	} catch (MalformedURLException e) {
			println "Malformed URL. Please pass valid URL"
			System.exit(0);
		}
	
		executeJob(url, "POST", json.toString());
	}