Create Optimized Aggregate Storage Cubes

Create optimized aggregate storage cubes using these two Optimize Cube utilities:

Utility	Data Returned
Baseline	Cube performance metrics
Solve Order	Solve order of the members in the cube

• Optimize Baseline Metrics on an Aggregate Storage Cube
• Optimize the Solve Order on an Aggregate Storage Cube

Optimize Baseline Metrics on an Aggregate Storage Cube

The metrics tracked by the Baseline utility show how the system is performing. Use these metrics to determine the baseline performance, and then to measure the benefits of the subsequent optimizations that you make.

Before using this utility, you first create an application workbook, including the outline, configuration settings, and queries you want to include in the cube.

When you run the utility, it builds the cube, loads the selected data files, builds either a default aggregation or a query based aggregation (if enabled in Customize), and runs the queries contained in the application workbook. It is important to have a representative sample of queries from your users.

The baseline utility creates a dashboard of the application and operational processes, which can help you to design and optimize the cube. As you implement changes and rebuild the cube, the baseline helps you compare iterations of cube modifications. On the Essbase.Stats.Baseline tab of the application workbook, the baseline utility appends new tables with the latest data for each iteration.

Prepare to Run the Optimize Cube Baseline Utility on an Aggregate Storage Cube

Complete these tasks before running the baseline utility:

1. Design and create your application workbook.

   To create an application workbook, you can download a sample application workbook and then modify it to suit your needs. See Explore the Gallery Templates.

2. Clear the query sheets in the application workbook of Smart View metadata:
   1. Go to the Smart View ribbon.
   2. Choose Sheet Info and click Delete.

   If the query sheets have metadata from a different server, cube designer displays a warning and pauses processing until you respond.

3. Modify the Cube.Settings worksheet with the following Application Configuration settings:

   Setting	Value
   ASODEFAULTCACHESIZE	100 Designates the default size for the aggregate storage cache. 100 is the default. Start with 100 and adjust if indicated by the output of QUERYTRACE.
   LONGQUERYTIMETHRESHOLD	-1 This setting lets you specify the lowest query-time length, in seconds, for which you want to capture statistical information. Oracle recommends setting LONGQUERYTIMETHRESHOLD when using this utility.
   QUERYTRACE	-1 Sets a query calculation flow trace to be run and the results to be printed out to a file. Setting QUERYTRACE provides a more in depth analysis.

Run the Optimize Cube Baseline Utility on an Aggregate Storage Cube

On aggregate storage cubes, the Baseline utility identifies dynamic, stored, and multiple hierarchy enabled dimensions. Additionally, it provides metrics for loading data, building aggregations, and performing queries.

To run the Baseline utility:

1. From the Cube Designer ribbon, select Admin Tasks > Optimize Cube.
2. (Optional) Click Customize to choose which baseline operations to run.
   • Build cube - Build the cube defined in the application workbook and load the data in the data sheets.
   • Build aggregations – Aggregations are intermediate stored consolidations called aggregate views. Aggregate views store upper-level intersections, which support query performance by avoiding dynamic aggregations on the most commonly queried intersections in the cube. The term aggregation is used to refer to the aggregation process and the set of values stored as a result of the process.

     When you build an aggregation, Essbase selects aggregate views to be rolled up, aggregates them based on the outline hierarchy, and stores the cell values in the selected views. If an aggregation includes aggregate cells dependent on level 0 values that are changed through a data load, the higher-level values are automatically updated at the end of the data load process.

   • Run queries - Run the queries on the Query sheets.
   • Export all - Export all the data in the cube to the cube directory. After the export time and file size are recorded, the export file is deleted automatically
3. Click Create Baseline.

   • Optionally, enter a non-zero value for Ratio to stop.
     
     

     Leaving ratio to stop at zero (the default) means there is no stopping ratio set.

     Consider setting this option to a non-zero value if there is no known common type of query executed by your cube’s users, and you want to improve performance by limiting the cube’s growth. Essbase aggregates the selected views, with the exception that the maximum growth of the aggregated cube must not exceed the given ratio. For example, if the size of a cube is 1 GB, specifying the total size as 1.2 means that the size of the resulting data cannot exceed 20% of 1 GB, for a total size of 1.2 GB

   • Check or clear the box for Based on query data.

     Essbase aggregates a selection of views that is defined based on analysis of user querying patterns. This is a good approach if similar types of queries are typically executed by the users of your cube. The utility runs the queries contained in the workbook first, then it creates the aggregate views based upon those queries.

4. Select whether to Enable alternate rollups.

   Consider checking this box if your cube implements Alternate Hierarchies for shared members or attributes, and you want to include them in the aggregation.

5. Click Okay.

   If you don’t have a data sheet in the application workbook, you’ll be prompted to select data and rule files from the catalog. It is a good practice to store the data and rule files in a shared directory in the catalog so that the files won’t be lost when you rebuild the cube.

   It will take some time to build the cube.

   Essbase generates the Essbase.Stats.Baseline sheet and adds it to the workbook.

6. View the Essbase.Stats.Baseline sheet in the application workbook.
   • The first table on the sheet displays the number of cells loaded, the default aggregate storage cache size, whether to build aggregations, the ratio to stop, whether it is based on queries in the workbook, and whether alternate rollups are enabled.
     
     
     
     
   • The colors in the Baseline table identify the hierarchy type for each dimension.
     • Green – multiple hierarchy dimension
     • Blue – stored hierarchy dimension
     • Gold – dynamic hierarchy dimension
     
     
     
     
     
   • Under Load and Calc, the rows show load time, input level data size, and aggregate data size for the initial data load and after building aggregations.
     
     
     
     
   • Under Query, the Formulas column shows the number of formulas executed in the query.

     Review the solve orders of calculated members and make changes to reduce the number of formula executions and improve performance, or consider storing a calculated member containing formulas to reduce the number of formula executions and improve performance.

     
     
     
     
     
   • The last table on the sheet displays the export time and file size.

Optimize the Solve Order on an Aggregate Storage Cube

The Solve Order utility gives you a visual representation of the solve order flow used in the application. This can help diagnose query performance problems relating to formulas.

To run the Optimize Cube Solve Order utility:

1. From the Cube Designer ribbon, select Admin Tasks > Optimize Cube.
2. Click Solve Order.
3. View the Essbase.Stats.SolveOrder sheet of the application workbook.

Use the information in the Essbase.Stats.SolveOrder sheet to adjust the solve order to optimize query performance. See Calculation Order.