Introduction to the DB2 data warehouse OLAP Service

IBM DB2 Data Warehouse Edition is a set of products that combine the strengths of DB2 Data servers and the robust business intelligence infrastructure from IBM. DB2 DWE integrates Core Components for warehouse management, data conversion, data mining, and OLAP analysis and reporting. In this article, we focus on the OLAP Service provided by DB2 DWE.

What is OLAP?

On-Line Analytical ProcessingOLAP) is a type of software technology that enables business analysts, managers, and executives to quickly and consistently explore Data in interactive forms, users can see various information views of converted raw data, which can reflect the real dimension of the business.

The function of OLAP is

Multi-dimensional -- OLAP Service provides a large number of data views or multi-dimensional conceptual views by supporting the space aggregation path dimen1_aggregation path) or hierarchies and/or multiple hierarchies.

Easy to understand-the data marketplace designed for OLAP can process any business logic and statistical analysis related to applications and developers, while making it simple enough for target users.

Interactivity-OLAP helps users combine business information through comparative personalized viewing methods and analysis of historical data and estimated data in various "what-if" Data Model scenarios. You can define new dedicated computing in the analysis and report data in any desired way.

Fast-the OLAP Service is often implemented in a multi-user Client/Server mode, and can provide consistent and fast response to queries regardless of the size and complexity of the database. The merged business data can be pre-aggregated along the hierarchy in all dimensions to reduce the runtime computing required to build OLAP reports.

Why is OLAP executed?

Traditional SQL or other) the construction of script-based data analysis reports is often obtained from two-dimensional relational database tables. Format these reports and present them to users. The disadvantages of the Two-dimensional report are:

Reports are pre-defined and built-in in the application for report preparation. In many cases, they cannot be easily modified to adapt to changes in business reporting requirements.

IT professionals are required to modify reports or create new reports.

Reporting users and business data are separated.

On the other hand, OLAP reports integrate the complexity of business data structures, processes, algorithms, and logic into its multi-dimensional data structure, and then present an easy-to-understand dimension Information view to end users, this allows them to explore business data in a very natural way. Multi-dimensional structures may sound very complex, but they are not. OLAP does not add additional data structures or dimensions to business data. It only recognizes complex data in a dimension manner and presents data to data consumers in an easy-to-understand manner.

With OLAP, users can easily find pre-defined reports and explore business data to create new dedicated business reports without the help of IT professionals. OLAP reports make users very close to business data, so that they can recognize the existence of business data dimensions and understand which business questions can be answered.

OLAP reports are very popular today. They are widely used as the basis for providing data warehouse solutions to customers.

OLAP data market

When talking about the data market, many people may immediately think about multidimensional or OLAP analysis. According to the definition, the data market is a subset of the data warehouse and is designed specifically for specific user groups or specific theme fields. OLAP is one of the methods for analyzing data in the data market. In this article, the data market specifically designed for multidimensional analysis is called the OLAP data market. The DB2 dwe olap data marketplace is a database of the following nature:

It has a star or snowflake dimension database pattern design.

Databases can store OLAP metadata, including OLAP models and multidimensional datasets.

The database can contain the materialized query table MQT), which is used for the pre-join and pre-aggregate tables of the dwe olap model and Multidimensional Dataset.

OLAP and data warehouse

OLAP is one of the report implementations used in most Data Warehouse solutions. OLAP solutions are sometimes mistakenly referred to as Data Warehouse solutions. This statement is especially misleading when developing OLAP solutions for a department or a limited user group.

The most important feature of a data warehouse is data integration. The most important purpose of a data warehouse is information data presentation. OLAP is not designed mainly for data integration. However, it is a powerful data presentation method that uses OLAP in most Data Warehouse solutions.

Typical OLAP services often come from one or more specially designed data markets. The OLAP Service should be considered as part of the data warehouse solution. See figure 1.

Figure 1. OLAP report in data warehouse

OLAP and hierarchies

OLAP involves many concepts. We will introduce them according to the context. However, you can also find all OLAP concepts in the DB2 Online Information Center. For more Information, see references in this article ).

OLAP dimension) classifies a set of related data attributes that describe a business metric. Dimensions organize data in fact objects based on logical categories such as region, product, or time.

OLAP dimension references zero or more hierarchy ). Hierarchies describe the relationship and structure of referenced data attributes. These data attributes are divided into several levels, such as the year, month, and day of the time dimension ), this provides a way to move a dimension in a navigation and computing manner.

OLAP model and OLAP multi-dimensional dataset

OLAP models and OLAP multidimensional data sets are two important concepts in the DB2 dwe olap Service. A Multidimensional Dataset model represents the data structure and relationship in the OLAP data market. A Multidimensional Dataset model contains metadata objects that describe the relationships in the base table data and where the data is located. The Multidimensional Dataset model allows information consumers to understand data from a new perspective.

Figure 2. OLAP model object

OLAP multi-dimensional data sets have a set of specific, similar, but more restrictive metadata objects derived from the parent multi-dimensional data set model, these objects include the dimension of a multi-dimensional dataset, a multi-dimensional dataset hierarchy, a multi-dimensional dataset layer, and a fact object of a multi-dimensional dataset. For each dimension of a multi-dimensional dataset, only one hierarchy can be defined for one cube. However, in a multi-dimensional dataset model, a dimension can have multiple hierarchies.

You can use a multi-dimensional dataset to optimize the multi-dimensional dataset model so that it can be more adapted to the most active and important multi-dimensional dataset model area. You can specify an optimized slice to define the most frequently queried part of a multi-dimensional dataset. After an optimization such as MQT is created for A Multidimensional Dataset model, all multidimensional datasets derived from this model will benefit from this.

DB2 Data Warehouse and OLAP Service

IBM DB2 Data Warehouse EditionDWE) V9.1 is a powerful and flexible Data Warehouse platform that provides deep integration of metadata and runtime infrastructure. DWE integrates the core components of the data warehouse, as well as the business intelligence BI feature for real-time data exploration and decision making to improve performance and ease of use, so as to help users achieve on-demand information utilization.

You can use DWE to build a complete data warehouse solution, including highly scalable relational databases, data access functions, business intelligence analysis, and front-end analysis tools. DWE integrates Core Components for warehouse management, data mining, OLAP, and inline analysis and reporting.

DB2 Data Warehouse Enterprise Edition provides the following products:

DWE Design Studio, which includes IBM Rational®Subset of Data effecect

Dwe SQL Warehousing Tool

DWE Administration Console

DB2 Universal Database™Enterprise Server Edition, Version 8.2, which contains DB2 Data Partitioning Feature

DWE Cube Views™, Version 8.2.3

DB2 Query Patroller, Version 8.2.3

DWE Intelligent Miner™

DB2 alphabrov, Version 8.4

WebSphere®Application Server Version 6.0.2

The DWE component is divided into three logical groups. These three component groups are usually installed on three different computers, but they can also be installed on two computers. For example, you can install the Data Warehousing Server component and Application Server component on the same computer or on two computers. These groups are displayed.

Figure 3. DWE runtime architecture

Eight of the nine software components in the current DB2 Data Warehouse Edition 9.1 version provide OLAP services in some way. DB2 Cube Views, SQL Warehousing ToolSQW) and an IBM Rational Data Architect subset have been integrated into DWE Design Studio, which makes the Design Studio component an ideal work platform for business intelligence developers.

DB2 DWE Design Studio

DWE Design Studio is built into the Eclipse workbench, which is a powerful development environment that can be easily customized. End-to-End OLAP solutions can be developed in combination with DB2 alphabrov and Design Studio.

Let's take a look at the basic process of developing the DB2 dwe olap solution and How to Use Design Studio in this process:

Collect and analyze business requirements.

When developing all OLAP solutions, we should first collect and analyze customers' business needs. One of the most important methods used to analyze business needs is data modeling. DWE Design Studio provides a wide range of logical and physical data modeling functions for data modeling.

Design and implement the OLAP data market.

In addition to using the logical and physical data modeling functions to create an OLAP database, you can also use the reverse engineering function to extract metadata from an existing database and then generate a physical database through the forward engineering function, to study, modify, and reuse existing database models.

Design the data flow process to fill the OLAP data market.

The database of Design Studio SQL operators provides data flow and control flow Design to control the data operations required to move data from the data source to the tables in the OLAP data market.

Design an OLAP multi-dimensional dataset model and multi-dimensional dataset.

Design Studio can be used not only to Design, manage, and deploy OLAP models and multidimensional datasets, but also to exchange OLAP metadata with other business intelligence tools. After the Multidimensional Dataset model is defined in DB2, it can be used by alphabrov and other ISV business intelligence tools. The shared metadata includes aggregation formulas and calculations, so the analysis results generated across businesses are more consistent.

Optimize OLAP models and multidimensional datasets.

In DWE Design Studio, you can Design and deploy a materialized query table MQT for OLAP models and multidimensional datasets ). The DB2 optimizer will use these pre-join pre-joined) and pre-aggregate pre-aggregated MQT to rewrite the input query and convert the appropriate OLAP query to the appropriate MQT, this significantly accelerates query execution.

Design and generate OLAP reports.

OLAP multi-dimensional datasets can be exported from Design Studio to alphabrov to generate OLAP reports based on business needs. You can modify an OLAP multi-dimensional dataset in DB2 alphabrov and import it to Design Studio for metadata management.

Figure 4. Role of DWE Design Studio in OLAP Service Development

This is an iterative process. It is very important to understand this. Field experts from solution providers and customers should actively participate in the OLAP design and testing phase to submit OLAP solutions that satisfy customers.

(