IBM DB2 Data Warehouse Edition is a suite of products that combines the strength of the DB2 database server with a powerful business intelligence infrastructure from IBM. DB2 DWE integrates core components for warehouse administration, data transformation, and data mining, as well as OLAP analytics and reporting. In this article, we will focus on the comprehensive OLAP services provided by DB2 DWE.
On-Line Analytical Processing (OLAP) is a category of software technology that enables the users (business analysts, managers, and executives) to gain insight into data through fast, consistent, interactive access to a wide variety of possible views of information that has been transformed from raw data to reflect the real dimensionality of the business.
OLAP functionality is:
- Multidimensional -- OLAP services provide a wide variety of possible views or a multidimensional conceptual view of the data by supporting a dimensional aggregation path or hierarchies and/or multiple hierarchies.
- Easy to understand -- The data mart designed for OLAP analysis should handle any business logic and statistical analysis that is relevant to the application and the developer, while at the same time, keeps it easy enough for the target user.
- Interactive -- OLAP helps the user synthesize business information through comparative, personalized viewing, as well as thorough analysis of historical and projected data in various "what-if" data model scenarios. The users are allowed to define new ad hoc calculations as part of the analysis and can report on the data in any desired way.
- Fast -- OLAP services are usually implemented in a multi-user client/server mode and offer consistently rapid responses to queries, regardless of database size and complexity. The consolidated business data can be pre-aggregated along with the hierarchies in all dimensions to reduce the runtime calculation for building the OLAP reports.
The traditional (SQL or other) script-based data analytical reports are usually built by pulling data from two-dimensional relational database tables. Those reports are formatted and then presented to users. The disadvantages of the two-dimensional reports are:
- The reports are predefined, built-in reporting applications. In many cases, they cannot be easily modified to incorporate changing business reporting requirements.
- IT professional(s) are needed to change the reports or build new reports.
- The report user is relatively isolated from the business data.
OLAP reporting, on other hand, integrates the complex issues of business data structures, procedures, algorithms, and logic into its multidimensional data structures, and then presents the easy-to-understand dimensional information views to end-users while empowering them to explore their business data in a very instinctive way. The multidimensional structure sounds very complicated, yet, indeed it is not. OLAP does not add extra data structures or dimensions to business data. It simply recognizes complex data in a dimensional way and presents it to data consumers in a very easy-to-understand fashion.
With OLAP services, users can easily navigate the pre-defined reports and explore the business data to build new ad hoc business reports with little assistance needed from IT professionals. OLAP reporting brings users so near to business data that the users become aware of the existence of business data dimensions and understand what business questions can be answered.
OLAP reporting is so popular today that it is widely used as the basis for presenting the power of data warehouse solutions to customers.
When talking about a data mart, many people might immediately think about multidimensional or OLAP analysis. By definition, a data mart is a subset of a data warehouse designed for a specific group of users or a particular subject area. OLAP is one of the approaches to the analysis of the data in a data mart. In this article, we name the data mart specifically designed for the multidimensional analytics as the OLAP data mart. The DB2 DWE OLAP data mart is a database with the following characteristics:
- It has a star or snowflake-like dimensional database schema design.
- The database is enabled for storing OLAP metadata, including an OLAP models and cubes.
- The database may contain materialized query tables (MQTs), the pre-joined and pre-aggregated tables for DWE OLAP models and cubes.
OLAP is one of the reporting implementations in most data warehouse solutions. An OLAP solution is sometimes misleadingly called a data warehouse solution. This is especially the case when the OLAP solution is developed for a department or for limited user groups.
The most important feature of a data warehouse is data integration, while the most important purpose of a data warehouse is informational data presentation. The OLAP service is not primary designed for data integration; however, it is a powerful data presentation method used in most data warehouse solutions.
A typical OLAP service usually starts from one or more specifically designed data marts. OLAP services should be considered as a part of data warehousing solution as shown in figure 1.
Figure 1. OLAP reporting in a data warehouse
There are many concepts involved with OLAP services. We introduce them here as required in context. However, all of the OLAP concepts can also be found in the DB2 Online Information Center, accessible from the Resources section of this article.
OLAP dimensions provide a way to categorize a set of related data attributes that together describe one aspect of a business measurement. Dimensions organize the data in the facts object according to logical categories such as region, product, or time.
OLAP dimensions reference zero or more hierarchies. Hierarchies describe the relationship and structure of the referenced data attributes that are grouped into levels (such as year, month, and day in the time dimension), and provide a navigational and computational way to traverse the dimension.
OLAP models and OLAP cubes are two critical concepts in DB2 DWE OLAP services. A cube model is built to represent a data structure and relationship in an OLAP data mart. A cube model contains metadata objects that describe relationships within the data that resides in the base tables and also describes where pertinent data is located. A cube model provides a new perspective for the information consumers from which to understand their data.
Figure 2. OLAP model object
An OLAP cube has a specific set of similar but more restrictive metadata objects derived from the parent cube model, including cube dimensions, cube hierarchies, cube levels, and a cube facts object. A cube can have only one cube hierarchy defined for each cube dimension, but a dimension can have many hierarchies that are defined for the cube model.
Cubes can be used when optimizing a cube model to specify the regions of the cube model that are the most active and the most important. You can specify optimization slices that define specific regions of the cube that are most often queried. Once the optimization (such as MQTs) is created for a cube model, all the cubes derived from it benefit.
DB2 Data Warehouse and OLAP Services
IBM DB2 Data Warehouse Edition (DWE) V9.1 is a fully integrated powerful and flexible data warehousing platform, which provides deep integration of metadata and runtime infrastructure. DWE helps you achieve information on demand by integrating core components for warehousing along with the improved performance and usability of business intelligence (BI) features for real-time insight and decision making.
You can use DWE to build a complete data warehousing solution that includes a highly scalable relational database, data access capabilities, business intelligence analytics, and front-end analysis tools. DWE integrates core components for warehouse administration, data mining, OLAP and inline analytics and reporting.
The following products are provided in DB2 Data Warehouse Enterprise Edition:
- DWE Design Studio, which includes a subset of IBM Rational® Data Architect.
- DWE SQL Warehousing Tool
- DWE Administration Console
- DB2 Universal Database™ Enterprise Server Edition, Version 8.2, which includes the DB2 Data Partitioning Feature
- DWE Cube Views™, Version 8.2.3
- DB2 Query Patroller, Version 8.2.3
- DWE Intelligent Miner™
- DB2 Alphablox, Version 8.4
- WebSphere® Application Server Version 6.0.2
DWE components are arranged into three logical groups. Typically you install these groups on three different computers, but you can also install them on two computers. For example, you can install the Data Warehousing Server components and Application Server components on the same computer, or on two different computers. The groups are shown in the following figure.
Figure 3. DWE runtime architecture
Of the nine software components in the current release of DB2 Data Warehouse Edition 9.1, eight of them provide OLAP services in one way or anther. DB2 Cube Views, SQL Warehousing Tool (SQW) and a subset of IBM Rational Data Architect have been nicely integrated into DWE Design Studio, which make the Design Studio component an ideal Business Intelligence developer's workbench.
The DWE Design Studio is built on the Eclipse workbench, which is a powerful development environment that you can easily customize. Together with DB2 Alphablox, the Design Studio can be used for developing an end-to-end OLAP solution.
Let's take a look the basic procedure to develop a DB2 DWE OLAP solution and how the Design Studio is used in it:
- Collect and analyze business requirements.
All OLAP solutions should begin with collecting and analyzing the customer business requirements. One of the most important approaches used in analyzing business requirements is data modeling. DWE Design Studio provides rich logical and physical data modeling functions for data modeling.
- Design and implement OLAP data marts.
Besides the logical and physical data modeling functions for creating OLAP database, you can also examine, modify and reuse existing database models by pulling metadata from an existing database via its reverse engineering function and then generate the physical database via its forward engineering function.
- Design data flow process to populate OLAP data marts.
The library of Design Studio SQL operators provides data flow and control flow designs to cover data operations that are typically needed to move data from data sources to populate tables in OLAP data marts.
- Design OLAP cube models and cubes.
Design Studio cannot only be used for designing, managing and deploying OLAP models and cubes, but also allows for exchanging OLAP metadata with other business intelligence tools. The cube models are defined once in DB2 and then used by Alphablox and other ISV business intelligence tools. Because the shared common metadata includes aggregation formulas and calculations, you benefit from greater consistency of the analytical results produced across the business.
- Optimize OLAP models and cubes.
From DWE Design Studio, you can design and deploy materialized query tables (MQTs) for OLAP models and cubes. These pre-joined and pre-aggregated MQTs are exploited by the DB2 optimizer, which rewrites incoming queries and routes eligible OLAP queries to the appropriate MQT for significantly faster query performance.
- Design and generate OLAP reports.
The OLAP cubes can be exported from Design Studio to Alphablox for building OLAP reports based on the business requirements.
Figure 4. DWE Design Studio in OLAP Services
It is very important to understand that this procedure is an iterative process. The domain experts from both the solution provider and the customer should be actively involved in the OLAP design and test phases in order to deliver a successful OLAP solution that will ensure customer satisfaction.
Stay tuned for the upcoming articles in this series, which will explore the details of designing and developing OLAP data marts using DB2 Design Studio.
- To learn more about DB2 Data Warehouse, visit the
DB2 Online Information Center.
- Visit IBM Solution Builder Express Portfolio for free business solutions for all IBM business partners.
- Find details about IBM DB2 Business Intelligence Website for updated product specific information.
- For more technical resources on Business Intelligence, see the
DB2 Business Intelligence zone on developerWorks.
- The International DB2 Users Group offers many helpful resources.
Leon Gong is a solution architect working in the IBM Solution Builder Express team. He has been working to help enable business partners to create solutions in different industries and solution areas including, but not limited to, business intelligence, infrastructure, and e-commerce. He has DB2 certifications in both application development and administration. You can reach him at leongong@us.ibm.com.
Donna Venditti is a project lead in Solutions Builder Express. For the last 6 years, she has delivered both industry and cross-industry starting point solutions for IBM Mid-Market Business Partners. Her focus has been on e-commerce and business intelligence solutions for the retail industry. You can reach her at donnav@us.ibm.com.
Comments (Undergoing maintenance)
Table of contents
Next steps from IBM
My developerWorks community
Tags
Use the slider bar to see more or fewer tags.
Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).
My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
