The ROLAP Easy Button – An Introduction to the Teradata Aggregate Designer tool
The Teradata Database offers a unique native capability, the Aggregate Join Index (AJI), to help support multi-dimensional Business Intelligence solutions. An AJI is an aggregated result set saved as an index in the database. The AJI will be used automatically by the Teradata Optimizer when like columns and aggregates are made frequently within a query plan. The AJIs perform aggregations automatically as the data is loaded into the data warehouse, resulting in highly performing response times when required. Also, the BI tool and BI administrator can remain unaware of the AJI and send the same SQL statement to the database.
AJIs can be used for almost any type of query submitted to the database, but can be a very powerful capability for ROLAP type architectures. The ROLAP approach is designed to access data stored in the data warehouse directly instead of accessing them from the OLAP cube stored on the middle-tier server (MOLAP). The ROLAP architecture with Teradata eliminates data movement, makes all the aggregate and detailed data available to BI analytical tools, and no middle tier server hardware is required other than to host the BI applications themselves. Using Teradata AJIs in a ROLAP architecture can provide MOLAP-like analytic response times without the hassle of moving the data off of the database.
The use of AJIs allows Teradata to do the “heavy lifting” for BI OLAP solutions. Leaving the data in Teradata allows for:
- Increased breadth and depth of data analytics
- Improved analytics response times
- Reduced data latency
- Reduced data storage requirements
- Eliminated cube scalability limitations
Creating an AJI is pretty simple. Here’s an example of a CREATE JOIN INDEX statement:
CREATE JOIN INDEX AJI_Example SELECT COUNT(*)(FLOAT, NAMED CountStar ), ae.Brand_Category_Id , ac.Product_Category_Id , ad.Business_Type_Id , ad.Channel_Id , ak.Area_Id , al.Year , al.Quarter , al.Month , SUM(ad.Sales )(FLOAT, NAMED SALES ) FROM Product ac , Fact ad , Brand ae , Sales_Center ak , Time al WHERE (((ad.product_id = ac.product_id ) AND (ad.brand_id = ae.brand_id )) AND (ad.sale_center_id = ak.sale_center_id )) AND (ad.day = al.day ) GROUP BY ae.Brand_Category_Id, ac.Product_Category_Id, ad.Business_Type_Id, ad.Channel_Id, ak.Area_Id, al.Year, al.Quarter, al.Month PRIMARY INDEX ( Brand_Category_Id, Product_Category_Id, Business_Type_Id, Channel_Id, Area_Id, Year, Quarter, Month );
Looks simple enough, right? However, identifying, designing, and creating the “correct” AJI to support a ROLAP architecture can be challenging. Using AJIs optimally involves a multi-step manual process that includes validating the design requirements (loading process, semantic layer, and database schema considerations); capturing the OLAP definition attributes, and designing, building, testing, and deploying the identified AJIs in Teradata. This process can be time consuming, confusing, error-prone, and sometimes difficult.….until now.
In order to help customers deploy AJIs for ROLAP, Teradata has recently released a new tool called the Teradata Aggregate Designer. The Teradata Aggregate Designer is a desktop administrative design-time productivity tool used to automate the design, recommendation, and creation of AJIs in the Teradata database. The tool bridges the gap between the multidimensional OLAP BI environment and the relational database environment by helping recommend, design, and create AJIs. The Teradata Aggregate Designer takes the guesswork out of creating AJIs. The tool creates AJIs that increase the likelihood of being chosen by the BI optimizer by the way it designs them.
The Teradata Aggregate Designer works like a wizard and steps the user through the various processes to design an accurate AJI.
Here are the capabilities of Teradata Aggregate Designer tool:
1. The tool first consumes a multidimensional OLAP definition from a Business Intelligence application. The Teradata Aggregate Designer analyzes this schema to understand the constructs of the multidimensional definition and breaks it down into measures, dimensions, hierarchies, and other dimensional objects. Schemas can either be provided to the tool via simple flat files, or the tool can integrate with the OLAP engine via web-services for seamless interoperability.
2. Once the schema has been consumed and parsed, the Teradata Aggregate Designer performs a series of validations to ensure the database is suitable for AJI creation. These validations include:
- Database elements in the schema are defined
- Primary and Foreign Keys are NOT NULL
- Primary Keys are UNIQUE
- COMPRESSION is NOT set on the columns
- REFERENTIAL INTEGRITY is set
If issues are identified, the tool provides specific instructions to the user about how to resolve any errors. These validations are important because database schemas that fail to meet the specified conditions preclude either the appropriate creation and load of AJIs or their use by the query optimizer.
3. The Teradata Aggregate Designer can be used in two different ways – Manual and Automated. The Manual Mode is targeted to expert users who are already familiar with AJIs and who know the exact AJI they want to build. They can leverage the GUI design tool to create the appropriate AJIs. The Automated Mode is targeted to novice users who are not experienced in creating AJIs. The AJI Advisor is used to recommend AJIs based on the dimensional model. The Teradata Aggregate Designer AJI Advisor feature leverages best-practices heuristics and algorithms to recommend the optimal AJIs to build. The AJI Advisor recommends two AJIs: a base and a broad AJI. A base AJI does not join to any dimension tables. It is an aggregate index that only aggregates rows on a Fact or Transaction table. A broad AJI joins to one ore more dimension tables and aggregates to a high level than was available in the Fact or Transaction table.
4. The Teradata Aggregate Designer features a Creation Services module used to create, edit, or delete an AJI. The Creation Services module provides a GUI interface to allow the user to define the name of the AJI, select a predefined schema, select the dimensions to aggregate, and define the Teradata indexes. The tool supports the creation and editing of PI, PPI, and MLPPI. The Creation Services module also provides AJI storage cost estimates by calculating and displaying the AJI space requirements and overhead relative to the Fact Table estimates. Finally, the Creation Services module automatically writes and creates the AJI DDL statement, connects to the database, and executes the AJI. The Creation Services component creates AJI DDL based on knowledge of how the Teradata query optimizer works so the AJIs designed by the tool have a very high likelihood of being updated and used by the optimizer.
For more information on the Teradata Aggregate Designer product and other ROLAP material, see the White Paper on Teradata.com.
If you are interested in learning more about the Aggregate Designer and see if it can help your migration to a Teradata ROLAP architecture, contact your local Teradata PS or Account team for more information.
This article is an introduction to the Aggregate Designer tool. In upcoming Teradata Developer Exchange articles we will be sharing best practices, tips, tricks, hints, demos, and other information to help you better understand ROLAP and how to use the Aggregate Designer tool.