OLAP Newsletter Customer Feature
RL Polk recently tested Oracle OLAP 11g as a means to boost the performance of their data warehousing and BI offerings. RL Polk has used Oracle Database and BI products as their corporate standard database and a foundation of their BI strategy for many years. The Oracle July Magazine details RL Polk in a feature called "Measure Analyze Perform'. It explains how R.L. Polk uses the Oracle Database to support 4,000 registered users for one BI application and how Oracle Database with OLAP 11g will simplify the deployment of their BI applications. This success story provides pertinent information on the use of Oracle OLAP 11g in Polk's BI solution.
RL POLK called Oracle in to help assess their existing 10g warehouse relational materialized view strategy for creating aggregate levels within their data. Specifically, they were looking for ways to improve data load, data formatting, and query performance against their data warehouse. They also wanted to reduce their overall workload and maintenance window. Oracle conducted a series of benchmarks for RL Polk using the Oracle OLAP Option for cube based materialized views and Real Application Testing for monitoring changes in user performance based on changes to the data warehouse architecture .
The tests were run on an Oracle hardware environment consisting of a two Node 64 bit RAC Dell/Oracle Enterprise Linux cluster, with 2 Dual Core AMD 1.0 Ghz CPUs, 16 GB Total RAM, 1 TB Total Storage, and a 100 MB Interconnect. The data consisted of 90 million fact rows, for 65 months and 166,000 geographies. In keeping with the design of the relational materialized views, in OLAP the data was modeled as a four dimensional cube.
In order to capture performance improvements across database versions and technologies used for supplying materialized views, a series of three benchmarks were devised. First, Oracle consultants performed materialized view creation jobs to benchmark the build times of Polk’s existing 10g data warehouse for the hardware described above. Second, the same creation jobs were repeated against an Oracle 11g database using relational materialized views to capture improvements provided by simply upgrading the database to 11g. Finally, cube based materialized views, designed to provide the full aggregate capability of the relational views were created in an 11g database with Oracle OLAP.
Using both the relational and cube based builds along with a captured transaction set from Polk’s 10g data warehouse, Oracle benchmarked expected user performance and OLAP query rewrite behavior using Oracle’s Real Application Testing product. The results were compelling.
The graph below shows the results for loading and aggregating relational views and cube based materialized views for the 10g and 11g database benchmarks:
- As shown above, build times were significantly faster when using OLAP 11g. Compared to 10g and improved 11g relational materialized views, a single CPU cube built in 38 minutes (without using available parallel processing options). Other parallel tests performed the same task in only 10 to 15 minutes. By comparison, it took at least 11x longer to build partially aggregated relational materialized views on the same database server instance.
-User query response also increased dramatically when using cube based materialized views; in the case of one simple aggregate level query, a query that took 554 seconds to run with relational materialized views returned results in only 12 seconds using a cube based materialized view. Query costs were also reduced due to the increased speed of calculation and reduced joins.
- Where necessary, cube based materialized views were joined to relational tables during query execution. This allowed the team to integrate cubes into an existing snowflake schema without the need to consume all attributes of the dimension members inside of the cubes.
Comments from the team:
Rob Tenhundfeld, Oracle's Data Warehousing Sales Consultant stated, "Along with the dramatically improved performance provided through Oracle OLAP’s materialized view capability, both in terms of building and querying aggregate warehouse facts, there are other advantages of Oracle OLAP; because of its multi-dimensional architecture and ability to satisfy standard SQL queries, one or only a few cube based materialized views can replace tens or even hundreds of relational based materialized views."
He continues, "This new capability can drastically reduce maintenance costs, latency of data, CPU cycles and even disk storage when compared to traditional relational materialized views. Given the tightening of IT budgets and exponentially increasing data volumes to be managed, this capability can pay for itself in a very short period of time. Also, placing cube based materialized views in a data warehouse does not require any changes to existing applications."
Jeffrey Cowen, Oracle’s Grid Sales Consultant added, “We proved Oracle OLAP could maintain, and in many cases improve, aggregate query performance in RL Polk's data warehouses. Technologies such as Real Application Testing allow customers to benchmark their existing environments and then compare that benchmark to changes such as the introduction of cube based materialized views to the data warehouse. This helps IT minimize risks while making large leaps forward in providing better solutions for their customers."
About RL Polk:
RL Polk is the premier provider of automotive information and marketing solutions to the automotive world and its related industries—automotive and commercial vehicle manufacturers and dealers, automotive aftermarket companies, insurance companies, finance companies, media companies, advertising agencies, consulting organizations, government agencies and market research firms. They collect and interpret global data and provide extensive automotive business expertise to help assist our customers with their most important market issues. They have offices located around the world, and are based out of Michigan, USA. http://usa.polk.com/
View more Oracle OLAP Customer and Partner references here.
Copyright © 2004, 2008 Oracle. All rights reserved.