November 05, 2007

Finishing Up with Exam 730

In our look at DB2 certification exam 730, "DB2 9 Fundamentals," we've previously reviewed the planning section, along with different editions of DB2 and the tools available. The final area within planning deals with data warehousing and business intelligence (BI).

As a DBA you must understand the two major types of applications. Your database is designed for either online transaction processing (OLTP) or data warehousing. Data warehousing includes online analytical processing (OLAP), data mining or BI, and decision support reporting. 

The difference between an OLTP and a BI application is the type of queries being processed. An OLTP application is your typical Web order-type transaction. When you order a product, a single item is updated and very little data is touched to process the order. The database has been designed to be fast with granular look-up and update processing.

On the other hand, a BI-type query traverses through large volumes of data seeking patterns and/or sorting and summarizing data to answer business queries(e.g., summarize sales by region for each month over the past 12 months). A simple BI pattern is the oft-cited example of diaper purchases often being accompanied by a purchase of beer.

You may also hear the term operational data. This is the day-to-day business data that supports the OLTP systems. The database has been designed to support fast transactions and is not always in the best format for long-running BI-type queries. Performance is critical for OLTP systems, so we usually create a copy of the data and store it in a data warehouse. This supports BI-type processing while keeping long-running queries from slowing down the OLTP processing.

Two tools are needed to support the building of a data warehouse--one to extract data, and one to transform and cleanse the data. The transformation is usually to summarize the data so end users can query the data without impacting the OLTP system. Cleansing most commonly occurs is usually to convert operational code values into information that end users can understand.

For the certification exam you need to know the different versions of DB2, DB2 tools like Control Center and Configuration Assistant, and the various products available with DB2. Here's a quick list of DB2 products.

DB2 Data Warehousing Editions--DWE comes in two packages, DB2 DWE Base or DB2 DWE Enterprise edition. Both packages provide steep discounts on a complete set of data warehousing tools. DB2 DWE takes the power of DB2 9 and bundles features--including those for analytics and real-time decision-making for information on demand--to improve performance and usability for data warehousing. Also included are tools for warehouse management, analytic application development, OLAP, data mining, and very large database (VLDB) query and resource management.

DB2 Enterprise Data Partitioning--This feature lets you partition a database within a single server or across a cluster of servers. DB2 Data Partitioning allows for the scalability to support very large databases, common to the data warehousing environment, as well as complex workloads and increased parallelism for administrative task. This feature is available for purchase outside the DB2 DWE product.

DB2 Data Warehouse Edition Design Studio--This includes some of the Eclipse-based Rational Data Architect (RDA) modeling functions that have been extended to form a development environment for BI applications. Some common database design features like physical data modeling, warehouse construction, OLAP cube modeling and data mining modeling are provided.

SQL Warehousing Tool--This is designed to solve the data integration problem in a DB2 data warehousing environment. Users can model logical flows of higher-level operations, which generate units of code that are organized inside execution plans. The execution of this plan is scheduled and managed with DB2 DWE serving as the runtime engine and WebSphere Application Server software providing control and scheduling capabilities.

DB2 Data Warehouse Edition Administration Console--This Web-based application is designed for managing and monitoring BI applications. Using the WebSphere security model of administrator, manager, and operator, a variety of tasks can be performed from a unified set of console pages. Major areas of support are:

  • DWE Common: Create data sources and enable databases for OLAP and mining.
  • SQL Warehousing: Deploy, schedule, and monitor data warehousing applications.
  • OLAP: Import and Export cube models, use OLAP optimizer, and view meta data content for cube models such as tables, joins, measures and attributes.
  • Mining: View, export, update, delete models in the data mining database.
  • Alphablox: Launch native Alphablox Administration tool.

DB2 Data Warehouse Edition OLAP Acceleration--Formerly known as DB2 Cube Views, the DWE Design Studio provides easy-to-use wizards and windows to help users work with OLAP metadata.

DB2 Data Warehouse Edition Data Mining--This provides extensions to DB2 to support discovery of hidden relationships in your data without having to export to separate special data mining computers. The extensions support modeling, market basket analysis, market segmentation, profiling and more.

DB2 Data Warehouse Edition Data Visualization--This feature is used to visualize the mining models you create.

DB2 Alphablox analytics--This browser-based application allows end users to see and analyze data with real-time highly customizable multidimensional analysis, all from a client workstation. DB2 Alphablox is tightly coupled with DB2 DBW data visualization feature (Cube Views).

DB2 Data Warehouse Edition Query Workload Management--Provided through DB2 Query Patroller, this feature is designed to proactively and dynamically control the flow of queries against the database. This can be handled in various ways -- define separate query class for different size workloads, define high priority for given individual users, or, automatically put long-running queries on hold so they can be canceled or resumed execution during off hours. DB2 Query Patroller can be purchased outside the DB2 DWE package.