How to build a metadata bridge for DB2 cube views

DB2 's DB2 cube views feature makes it easier to integrate business intelligence applications with data warehouses by sharing multidimensional metadata between relational databases and business intelligence applications. Learn how to enable business intelligence applications or tools to take advantage of this powerful new feature.

This article applies to the object

This article is intended for technical managers and technicians who want to develop or integrate OLAP applications on Ibm®db2®universal database™v8 (UDB) by using the DB2 Cube views Application Programming Interface (API). This information is especially useful for developers who build components to exchange meta data between DB2 cube views and other tools or metadata repositories. For simplicity, we refer to the software for exchanging metadata as a "metadata bridge" or "bridge".

Background knowledge of relational databases, warehouses, and OLAP concepts will help you understand this article.

Brief introduction

Companies and users now increasingly need to have the ability to analyze large datasets and to gain in-depth access to and report on more detailed data. Highly scalable database servers like DB2 UDB play an increasingly important role in delivering OLAP solutions to enterprises. The DB2 Cube views,db2 UDB provides a even more robust infrastructure that can play a "significant support" role in end-to-end warehousing and business intelligence deployments.

For many OLAP tools and applications, integration with the DB2 cube views requires building a metadata bridge. These bridges can read or create metadata by using an xml-based stored procedure interface that uses the DB2 cube views. We'll discuss how to use this interface to build a bridge in this article.

Overview of DB2 Cube views

The IBM DB2 Cube views are an additional feature of the DB2 general-purpose database, which enhances the DB2 udb, making DB2 UDB the platform for developing and deploying business intelligence products and applications. In particular, the DB2 Cube views help accelerate the development and management of OLAP solutions and applications located on DB2 UDB.

How does the DB2 Cube views feature part do this? First, it allows DB2 UDB to support OLAP. Without a multidimensional view, relational databases seem to be a daunting set of tables with some columns for people unfamiliar with the structure and content of the database. DB2 cube Views Metadata allows you to more fully document the underlying structure in the DB2 database.

Typically, a warehouse or data mart is designed specifically for dimensional analysis. In this case, the database is usually made up of tables that are organized into one or more star-or snowflake-pattern patterns. A simple star pattern contains a fact table with a few dimension tables around the fact table. For example, the fact table can record the daily sales data for each company's products and stores. Dimension tables (perhaps products and STORES tables) may contain detailed information about each product and store and are connected to the fact table.

Meta data Object

DB2 cube views Meta data is especially good at capturing the structures inherent in star or snowflake patterns (see Figure 1). This metadata provides a "spatial view" of relational data through a set of metadata objects. Metadata objects are divided into multiple layers, modeled from simple objects (such as attribute, simple attribute to table columns) to more complex objects (such as Join, hierarchy, Dimension, and Cube model). A fully defined multidimensional data model object typically conforms to the star (or snowflake) pattern and models a set of metrics that have a common dimension. It is not surprising, then, that the multidimensional data model encapsulates other DB2 cube views metadata objects (such as Dimension and Measure).

Figure 1. DB2 cube views multidimensional data model with reference to relational star pattern