The Pentaho Aggregation Designer simplifies the creation and deployment of aggregate tables that improve the performance of your Pentaho Analysis (Mondrian) OLAP cubes. Pentaho Analysis is a pure, relational OLAP engine that works solely with the data stored in your relational database rather than providing its own multidimensional data storage model. This simplifies deployment and data management, but places limitations on performance when working with very large data sets (fact tables with more than 10 million records and/or cubes with a high cardinality of levels and members). To improve performance in these scenarios, Pentaho Analysis supports aggregate tables. Aggregate tables coexist with the base fact table and contain pre-aggregated measures built from the fact table. This improves performance by enabling the Mondrian engine to fulfill certain summary level queries from the smaller aggregate table versus aggregating a large number of individual facts from the base fact table.
The Pentaho Aggregation Designer provides you with a simple interface that allows you to create aggregate tables from levels within the dimensions you specify. Based on these selections, the Aggregation Designer generates the Data Definition Language (DDL) for creating the aggregate tables, the Data Manipulation Language (DML) for populating them, and an updated Mondrian schema which references the new aggregate tables. If you are unfamiliar with aggregate table design concepts, the Aggregation Designer also includes an intelligent adviser that evaluates the structure and cardinality of your OLAP cube and recommends some initial aggregate tables to create for improving performance.
Get Started with the Aggregation Designer
The Pentaho Aggregation Designer workspace is shown below.
The Aggregation Designer contains the following sections:
- Impact Summary: The impact summary in the lower right pane provides you with information on the estimated impact for creating all of the currently selected aggregates. This summary includes the number of aggregate tables that will be created, the estimated number of rows contained in those tables, and the estimated amount of space it will occupy on the hard drive. The impact summary is automatically updated as you select and deselect aggregates from the list of proposed aggregates.
- Cost/Benefit Chart: The Cost/Benefit chart provides a high-level comparison of the benefit of all currently selected aggregates relative to their estimated cost. The benefit scale represents the relative number of queries that can be fulfilled by an aggregate table versus having to be retrieved from the base fact table. The cost scale is an indicator of the impact in terms of number of tables and disk space needed to create the selected aggregate recommendations.
- File Menu: You can save all aggregate-related data (custom- or advisor-created) in your workspace at any time. Saving ensures that all of the data (your designs) in the workspace is retained; you are saving the state of your workspace as an XML file in a location you specify. To save, go to the File menu and click Save As. To open a saved file, go to the File menu and click Open, then navigate to the design you previously saved.
Define a Data Source
To design an aggregate table, you must first establish a connection with your target relational database, then select the OLAP model to optimize. You can connect to any relational database that is supported by Mondrian. In some instances, you may need to define additional parameter-related values for your JDBC driver.
To define a data source connection:
- In the Pentaho Aggregation Designer tool bar, click Connection to open the Connect to Data Source dialog box.
- Click Configure. The Connection Properties dialog box appears.
- In the Connection Name field, enter a name for your connection; this is a free-text field. A connection name uniquely defines a connection.
- In the Connection Type list, select a database.
- In the Access list, keep the default choice, which should be Native (JDBC).
- In the Settings section, type the host name of the database server into the Host Name field. In the Database Name field, type the name of the database you're connecting to. In the Port Number field, enter the TCP port number. Optionally, in the User Name and Password fields, type the user name and password used to connect to the database.
- Click Test. If the settings you typed in are correct, a success message appears.
- Click OK.
If you must define additional parameters for your JDBC driver, or if you want to enter your server settings manually, follow the instructions below:
- Click Options in the left panel..
- Enter the parameter name and value for the settings you need to specify. For example, PORT (parameter name), 1025 (parameter value).
- Click Test when your settings are entered. A success message appears if everything was typed in correctly.
- Click OK.
Select a Model
After defining your data source, you must select the cube you want to use for defining and building aggregate tables.
To select a model:
- In the Connect to Data Source dialog box, under OLAP Model, select Mondrian Schema File.
- Click the ellipsis (...) to display a file dialog box.
- Browse to locate and select your Mondrian schema file (SteelWheels.mondrian.xml if using sample data), then click OK.
- Click Apply. The Cube list is populated with a list of cubes defined in your schema.
- Select the Mondrian cube you want to optimize, then click Connect. When the Pentaho Aggregation Designer establishes a connection, it runs several validation tests to ensure that your database structure is ready to support aggregate tables. A validation summary dialog box appears with a list of test results. If you see an error message, contact your database administrator.
Use Aggregate Advisor
If you are unfamiliar with aggregate table design and need help creating aggregates to optimize a cube, you can rely on the Aggregate Advisor to provide you with a list of recommendations. The Pentaho Aggregation Designer uses your schema file and the data in your database to create aggregate definitions.
To display recommended aggregates:
- In the Pentaho Aggregation Designer toolbar, click Advisor.
- Specify your Advisor Input Parameters.
Setting Description Max Aggregates Allows you to specify the maximum number of aggregates you want the Advisor to recommend. Max Time to Run Allows you to specify the maximum amount of time (in seconds) you want the Advisor to run before making recommendations. Note: Allowing the Advisor to run for longer periods of time allows for more potential recommendations to be evaluated and results in more accurate recommendations.
- Click Recommend. The Advisor runs for a few seconds before it displays an initial list of recommended aggregates. The Advisor is designed to keep running until it finds an optimal solution. If you stop the Advisor prematurely, the Advisor returns the best set of recommendations it has found up to the point when it was stopped.
To add an aggregate:
- In the right pane of the Pentaho Aggregation Designer, click Add.
- In the left pane, type a Name and Description for your new aggregate.
- Under Level, click the down arrows to define the hierarchy and levels associated with the aggregate you are creating.
- Click Apply. Your aggregate is added to the aggregate list.
To customize an aggregate:
- In the Pentaho Aggregation Designer, click on an aggregate in the proposed aggregate list to select it.
Note: When you modify an aggregate created using the Advisor, the aggregate becomes a Custom aggregate as indicated by the Type column in the proposed aggregate list.
In the left pane, you can (optionally) modify the Name and Description for your custom aggregate.
- In the Aggregation Levels tab, click the down arrows to make changes to the hierarchy and levels associated with the aggregate definition you are customizing.
- Click Apply . The Pentaho Aggregation Designer updates the proposed aggregate list, cost/benefit chart, and impact summary.
To delete an aggregate, select it from the proposed aggregate list and click Remove.
The Pentaho Aggregation Designer allows you to preview the DDL, DML, and schema (for relational databases) outputs before you build aggregate tables. You can also save the outputs and edit them later. If you are using OLAP, DML is the only available output.
To preview the DDL and DML outputs:
- Select the aggregates that have DML/DDL output you want to preview.
- In the Pentaho Aggregation Designer toolbar, click Export.
- In the Execute and Publish dialog box, click Preview.
- Click Copy to Clipboard or Save to retain the output.
- If you examine the DDL/DML outputs and are satisfied with the results, you can allow the Pentaho Aggregation Designer to build (Execute/Publish) the aggregate tables. Follow the instructions for publishing and exporting included in the Execute and Publish dialog box.