Tech2Tech
Hands On
An automated approach
Teradata Aggregate Designer eases AJI administration.
by Sam Tawfik
Business intelligence (BI) tools have evolved greatly and gained popularity because of their flexibility and ease of use. These highly interactive tools allow business users to navigate complex and large data volumes in simple and intuitive ways.
Companies commonly deploy multiple BI tools to meet business users’ broad range of needs and to leverage best-of-breed technology for each of these. But this proliferation of BI tools puts an extra demand on IT organizations, as they must deliver a consistent view of an enterprise’s data no matter which tool is used.
For BI solutions deployed on a Teradata system, a consistent view of enterprise data across all BI applications is provided as well as the capability to perform aggregated and detailed decision-support analytics.
While the Teradata Database supports analytics via the normalized data approach, the star/snowflake schema approach is used for dimensional analytics. The key BI technology used to enable dimensional analytics is the aggregate join index (AJI). Creating, maintaining and deploying AJIs is automated with Teradata Aggregate Designer, a desktop administrative tool that works with all multi-dimensional analytics solutions.
Click to enlarge
OLAP implementations
Most BI solutions extract, transform and load data to a mid-tier server using the multi-dimensional online analytical processing (MOLAP) method. Once the data is available, users can slice, dice, drill and explore the multi-dimensional data. Although MOLAP is considered high-performance, it has three main challenges:
- Mid-tier servers are required for cube storage and analytics, and they must be installed, maintained and tuned.
- The availability and freshness of the data is delayed because it first must be extracted and loaded into the mid-tier server.
- It is limited by the scalability of the cube and the type of data.
A better approach to analyzing dimensional data is to implement the relational OLAP (ROLAP) solution. With ROLAP, rather than extract the data to load into a mid-tier server, it stays in the warehouse where BI analytics is performed.
AJIs, which specify aggregate operations across one or more tables, support ROLAP by performing SUM or COUNTS aggregations automatically as the data is loaded into the warehouse. The result set is saved as an object in the database and used for roll-up summaries that are frequently accessed, such as sales results, orders, shipments, customers and so on. Not only does this method result in a quicker response time, but it also allows for a wider and deeper analysis of the detailed data.
To improve the build of the initial OLAP content, and to access and navigate the relational data, AJIs must be implemented into the BI solution where they are automatically activated by the Teradata Optimizer.
Teradata Aggregate Designer
To reduce implementation time of AJIs from hours or days to minutes, Teradata Aggregate Designer automates steps 1 through 6. (See sidebar "10 steps to AJI implementation.") The procedure also enables DBAs to easily identify any necessary database changes to better optimize the solution. The Teradata Aggregate Designer performs the following steps:
Captures OLAP cube definition:
- Consumes a multi-dimensional schema definition either from a partner BI tool or from the Teradata Schema Workbench
- Parses the schema definition internally to understand its constructs and breaks it down into measures, dimensions, hierarchies and other important attributes
Validates the database to ensure that the following attributes are in effect:
- Elements in the schema are available
- Primary and foreign keys are NOT NULL
- Primary key is unique
- Compression is not set on columns
- Referential integrity is set
Recommends the following AJIs:
- The optimal base AJI, which is not joined to any dimension tables
- The broad AJI, which joins to one or more dimension tables and aggregates to a higher level than was available in the fact or transaction table
Provides AJI Creation Services that:
- Enable manual modification of AJI levels
- Provide AJI storage-cost estimates
- Validate the SQL and create the Teradata Data Definition Language (DDL)
Better results, lower cost
Teradata Aggregate Designer makes it easier for DBAs to design and create AJIs. With MOLAP-like performance, AJIs provide users access to the detailed data and decision-support solutions but without the delay in data availability or the overhead cost of the mid-tier server.
Sam Tawfik is a Teradata technical product marketing manager.