The materials in the article are consistant with the products available from IBM up to January 2005. IBM products introduced or made available after that date are not covered.
In today's warehouse environment, organizations are more successful with sound architectures. These architectures are defined to support the functional, technical, and data needs of the system that will address business questions posed by users.
In the mid to late 1990's, IBM introduced a Blueprint for Data Warehousing that aided data integrity and process consistency through the persistent data store (Central Data Warehouse or CDW). Additional focus was paid to analytics, and OLAP functions were provided as a key strategy to frame business questions. This key roadmap is one that is still valid today. Evolution of the functional architecture can be seen with recent additions to ANSI standards have internalized, in the database engine, OLAP functions and low-end data mining algorithms (for example: regression and standard deviation metrics).
Now information consumers are demanding timely answers to more complex questions that require processing of data from a variety of sources. In many cases the analysis is a study itself of the data that may or may not provide an answer, only another question.
This article is a presentation of the latest and best available toolsets and approaches that will aid the BI specialist in being able to source data and assimilate it into information that will provide value to the information consumer. The participant is invited to review or participate in exercises involving some classic IBM products and newly added technology solutions that support the various stages of BI evolution.
This article is broken into six sections based upon the Reference Architecture developed by the IBM® DB2® Information Management Software group. They are:
- Part 1 - BI Architecture / Methodology: discussion of the Business Intelligence Framework and introduce you to the IBM award winning approach to enabling solutions within this space.
- Part 2 - Access: The Access Layer of the Business Intelligence Framework defines the functions and services to access BI analytics with minimal effort.
- Part 3 - Data Repositories: The Repository Layer of the Business Intelligence Framework defines the functions and services to store structured data and meta data within DB2.
- Part 4 - Analytics: The Analytics Layer of the Business Intelligence Framework defines the functions and services to present solutions to business questions raised ad hoc or periodically by users.
- Part 5 - Integration: The Data Integration Layer of the Business Intelligence Framework defines the functions and services to source data, bring it into the warehouse operating environment, improve it's quality, and format it for presentation through tools made available via the Access Layer.
- Part 6 - Data Sources: The Data Source Layer categorizes data as Enterprise, Unstructured, Informational, or External. Driven by the meta data characteristics of each category, tools will be used to access and prepare the data within each category.
You should have an intermediate level of understanding of DB2 and related services, and have requisite experience with Oracle or other BI solutions. As a preparation, you can take the DB2 Tutorial: DB2 Version 8 Family Fundamentals Preparation. Also see IBM Certified Database Associate: DB2 Universal Database™ V8.1 Family for more information.
To run the examples or sample code in this tutorial, you'll need ...
- DB2 Universal Database V8.1
- WebSphere® Information Integrator V8.1
- Some Labs will require additional software, for instance, Cube Views™ and Intelligent Miner™. As a preparation, students can take the DB2 Tutorial: DB2 Version 8 Family Fundamentals Preparation.
Part 1 - BI architecture / methodology
This section discusses the Business Intelligence Framework and introduces you to the IBM award winning approach to enabling solutions within this space. You'll learn what the architecture is and components of the software and functional layers within the architecture. The rest of the article will illustrate the application with a variety of tools.
This section should take about 45 minutes to complete.
Topics:
- BI Architecture Introduction
- BI Architecture - IBM Product Mix Applied
Business intelligence architecture introduction
This section covers the discussion of methodologies, reference architectures, the IBM BI Analytical Framework and it's role as a reference architecture. Also illustrated are some industry specific applications, considerations, and justifications for using the framework.
Open the Introduction to the BI Architecture Framework and Methods document here or download the .zip file in the downloads section below and open the file named INT1ARCHINTRO.pdf.
BI architecture - Technical layers and software solutions
This section looks briefly at the layers of the BI Architecture and exposes the architecture components and IBM software solutions available within each component.
Open the BI Architecture: Technical Layers and Software Solutions document here or download the .zip file in the downloads section below and open the file named INT2TECHARCH.pdf.
The white paper "Framework For Business Intelligence" presents a simple case for BI and discusses DB2's evolution to engrain more BI functions within the core database properties, thus making it available to a wider variety of applications, while preserving the validity, integrity, and availability of the BI information. DB2 Data Warehouse Edition and Information On Demand semantics are discussed and tied into the BI Framework to present a strong argument for "functions within the database", open interfaces for access and extension, and collaboration for all other presentation services.
The DB2 framework for Business IntelligenceReview the following Web cast: Insights On Demand: Extending the Enterprise with BI. This Web cast discusses the applications of Business Intelligence analytics and sites industry examples where the various analytical layers have added value to the corporation's bottom line. The IBM BI product mix is presented and key features are discussed along with arguments for sizing the solution to the fundamental analytical requirements. A discussion of industry surveys including competitive analysis rounds out the business case discussion for the IBM solution set.
Insights On Demand: Extending the Enterprise with Business IntelligencePlease note: This is a link to a Web cast event that will launch in your browser. If you have a pop-up blocker enabled it may not launch.
The Access Layer of the Business Intelligence Framework defines the functions and services to access BI analytics with minimal effort. Access through thin client, widely available browsers, and portal solutions that add value through filtering and organizing capabilities are presented as key strategies within this section of the framework.
This section should take about 45 minutes to complete.
Topics:
- Access - Introduction
- Access - Web Services
The Access Layer provides the Information Consumers? presentation view and interaction with the business analytic applications.
Figure 1. Business Intelligence framework: Access
Examples of Access Layer tools include:
- Web Browser - Brio, Business Objects, Cognos, MicroStrategy, SAS web-based front ends
- Portals - WebSphere Portal, WebSphere Commerce with portlets from IBI, Crystal, Actuate
- Devices - PC, PDA, mobile phone, KIOSKs, ATM
- Web Services - WebSphere Business Integration
Review Chapter 14 of the Redbook - DB2 Cube Views - A Primer, for a discussion of web services that utilize Cube Views. Coverage in this chapter includes web services functions and architectures, and the inclusion of Cube Views analytics to expose OLAP functions through web services.
The Repository Layer of the Business Intelligence Framework defines the functions and services to store structured data and meta data within DB2. Large scale data warehouses are considered in addition to single service data marts, and the unique data requirements are mapped out. Single and multi-tiered data warehouse architectures are discussed, along with the methods to define the data based upon analysis needs (ROLAP or MOLAP). The doors are opened to the IBM industry specific business solutions applied to insurance, banking and retail sectors as a method to kick start an enterprise level solution to deliver BI analytics to the customer.
This section should take about 90 - 120 minutes to complete.
Topics:
- Repositories - Introduction
- Data Warehouse Architecture
- Data Warehouse - Data Architecture
- DB2 - Data Warehouse Configurations
- DB2 - Data Warehouse Partitioning
- DB2 - Partitioning in a Teraplex Environment
- DB2 Performance - Multi Dimensional Clusters
- Lab: Data Warehouse Center: Information Catalog Center
- Data Mart Configurations
- Operational Data Stores
The Data Layer contains the Business Intelligence data stores. These data stores should be viewed as single repositories even though they may exist as a set of federated data stores.
Figure 2. Business Intelligence framework: Data repositories
Examples of Tools to support the Data Repository Layer include:
- Databases - DB2 ESE, DB2 OLAP server™, Hyperion Essbase, Oracle, SQL Server, Informix, Red Brick
- Meta Data - Data Warehouse Manager, CA Repository, MetaStage
Industry semantic reference models such as Insurance Industry models (IIA/IIW) and Banking Industry models (IFW/BDW) can be leveraged to provide consistency and integration capabilities across data repositories. CIIS, DWL and Siebel often provide the ODS layer for customer data.
The following article by Jon Rubin : "IBM DB2 and the Architectural Imperatives For Data Warehousing" discusses key characteristics of the warehouse platforms, including portability, scalability, flexibility, and extensibility.
IBM DB2 and the Architectural Imperatives For Data Warehousing
Data Warehouse - Data architecture
Review the Redbook: "Data Modeling Techniques for Data Warehousing", paying close attention to Chapter 6,8,9, which cover warehouse data modeling and considerations, as well as a number of methods and processes designed to help projects deliver data driven BI solutions.
Data Modeling Techniques for Data Warehousing
To see a practice solution, review the Data Model topics within Chapters 3 and 5 of the IIW General Information Manual, located in the Resources Section at the Solution page "IBM Insurance Information Warehouse":
IBM Insurance Information Warehouse
DB2 - Data Warehouse configurations
If you are new to IBM - DB2, review the following document titled "DB2 UDB Fastpath Study Guide", which is designed to assist in learning about and finding additional information on the course : DB2 UDB Administration Fastpath Course for Version 8 (CT28D).
Open the DB2 Universal Database FastPath study guide document here or download the .zip file in the downloads section below and open the file named FASTPATHTODB2.pdf.
As an introduction to the Data Warehouse solutions provided within DB2, view the presentation: Chat with the Lab - DB2 Data Warehouse Edition V8.2, where Sriram discusses the features of the Stinger offering of DB2's Data Warehouse Edition...
BI Best Practices Technical Chat Program
Review the Overview and Description sections in the DB2 Version 8.2 Announcement which document the release features, components, and upgrades.
DB2 - Data Warehouse partitioning
Review the following article titled "A colorful introduction to DB2 UDB for Linux, UNIX and Windows Version 8 with Database Partitioning Feature", which introduces you to the Database Partitioning Feature (DPF) available on DB2 UDB Enterprise Server Edition (ESE). This article discusses the implication of this feature to warehousing environments, and application to solving OLAP and data mining problems is covered.
Review Bill Wilkins' article "Migrate to DB2 UDB Applications to a Partitioned Environment". This article discusses what DB2 partitioning is, outlines the benefits and costs of using it, and helps you decide whether or not to use a partitioned database, as well as how to migrate to one. The focus is on application migration, but design, configuration, and operational considerations for a partitioned database is also covered. Some useful script samples are also included in the document body.
Migrate DB2 UDB applications to a partitioned database
DB2 - Partitioning in a Teraplex environment
Review the following white paper titled "DB2 Cube Views Teraplex Case Study", in which the implementation of Cube Views in a Teraplex environment is put to test with a SurfAid customer to assess the feasibility of incorporating the architecture into the customer's database environment.
DB2 Cube Views Teraplex Case Study
DB2 Performance - Multidimensional Clusters
Review the following tutorial covering Multidimensional Clustering, a new feature of DB2 V8.1. MDC is a type of indexing that improves performance by reducing the amount of disk reads needed to access data, and by improving the speed of required disk input and output. You will need an IBM profile to access this tutorial. If you do not already have an id and password, you can create one by clicking here.
Multidimensional clusters in DB2
For a discussion of OLAP architectures including ROLAP (Relational OLAP) and MOLAP (Multidimensional OLAP) solutions, review the following article "Relational Extensions for OLAP" from the IBM Systems Journal.
Relational Extensions For OLAP
Review Chapter 3 of the Redbook "Building the ODS on DB2 UDB using Data Replication, WebSphere MQ, and Data Replication", covering the following topics. Please refer to the Required materials section as you will need to have those products installed.
- ODS architectures
- ODS Data Models
- Defining and describing ODS layers
The Analytics Layer of the Business Intelligence Framework defines the functions and services to present solutions to business questions raised ad hoc or periodically by users. This section exposes verification analysis of which OLAP tools are a prevalent mechanism, and discovery mode analysis of which Data Mining tools are the prevalent mechanism. Each mode of analysis answers a specific business question or hypothesis, but the approaches are varied, and in the case of mining scenarios, the results may yield undetermined or unactionable results. Exposure to IBM solutions supported by third party tools are discussed as a means to delivering the solutions to users interested in analytics.
This section should take about 90 - 120 minutes to complete.
Topics:
- Analytics - Introduction
- Data Mining
- Tutorial: Mining your Business in Retail with IBM DB2 Intelligent Miner
- Embedded Analytics - Alphablox
- Demo: Alphablox
Business Intelligence Analytics covers a wide variety of subjects, and answers questions formed from historical events that are associated to predict future outcomes, so that business can profit from investment initiatives.
Figure 3. Business Intelligence: Analytic subject areas
The value of Business Intelligence increases as the delivery of information is embedded in the processes and systems of the enterprise. Three types of analytical delivery:
Figure 4. Business Intelligence:Analytic techniques
All of these analytical methods use association methods and business rules to act on low granular transactional data, providing high quality, consistent measures that are candidates for extended analysis and actionable decisions.
Figure 5. Business Intelligence :From data to information on Demand
The Analytics Layer provides the business analytic applications and their underlying capabilities and services, thus adding value to all areas of the organization.
Figure 6. Analytics: Application examples
Components and their services:
Figure 7. Analytics: Business applications
Components and their services:
Figure 8. Analytics: Sample tools
Review Part 1 and Part 3 of the Redbook: Enhance Your Business Applications: Simple Integration of Advanced Data Mining Functions, for coverage of the following topics:
Topics:
- Data mining functions in the database
- Overview of the new data mining functions
- Business scenario deployment examples
- IM Scoring functions for existing mining models
- Building the mining models using IM Modeling functions
- Using IM Visualization functions
Enhance Your Business Applications: Simple Integration of Advanced Data Mining Functions
Review the following DB2 Intelligent Miner Brochures for more detailed information about the services provided by each of these solutions:
DB2 Intelligent Miner Modeling
DB2 Intelligent Miner Visualization
Tutorial: Mining your business in retail with IBM DB2 Intelligent Miner
This tutorial shows you how to apply data mining techniques using IBM DB2 Intelligent Miner to characterize the customers in terms of the products they purchase and the frequency of their shopping. We will show you how to use IBM DB2 Intelligent Miner Modeling to create a mining model and then IBM DB2 Intelligent Miner Visualization to evaluate it and to display its results. You will learn how to do this using the Easy Mining Procedures for DB2 Intelligent Miner, an easy-to-use SQL interface for the main steps of the data-mining process. Mining your Business in Retail with IBM DB2 Intelligent Miner
Embedded analytics - Alphablox
Review the following white paper titled "Alphablox Architecture Overview", which discusses:
- Alphablox architecture
- Alphablox components and applications
- The role played by J2EE
- Alphablox administration and roll-out
- Query a Cube Using QMF for Windows
Inside IBM DB2 Alphablox: An In-Depth Technical View of IBM DB2 Alphablox
Goals of this demo:
- Overview: See how Alphablox can connect to and interact with DB2, DB2 Cube Views and DB2 OLAP.
- Ranking report: See how to view a ranking report
- Additonal Info.: Conveys Alphablox differentiation with customized, embedded analytics
- Data Presentation: Conveys how Alphablox can be part of a closed-loop, Inline analytics solution and explains how Alphablox 'fills out' the Information Mgmt portfolio and helps the existing IBM business intelligence partners (BOBJ, COGN, MSTR)
- New user id: Shows further details of personalization.
Duration of demo: 5-10 minutes for each section
Setting the stage:
This demo is modeled after several of our banking customers. The basic problem was the same: the banks needed to empower their branch managers with information for them to make decisions at the front lines that were timely enough to directly impact their business.
For instance, one customer had an interesting dilemma. They found that prior to Alphablox, all analysis of branch information was done at the corporate level. But it was often a month before it was rolled out to the branch managers. Thus, by the time a negative trend was spotted, it was often too late to take corrective action. In their case, they were signing up the correct number of new customers per their corporate goals, but these customers were unprofitable. Thus, the more new customers they signed up the more unprofitable the bank became!
To correct the action, the bank decided to take two steps. First, they changed their goals. Branches are now measured on profit, not just revenue. Second, they pushed the analysis and decision making down to the regional and branch level. But to do this, they needed a system that was easy to use and broadly deployed to their over 1500 branches.
The scenario:This demo follows a typical analysis path taken by two people: Betty Branch, the local branch manager of branch #12, and her manager Rick Region.
Betty has several things she needs to do in any given day:
- process all of her insufficient funds (NSF) request by 10:00am each day
- verify her current ranking relative to her peers
- see if she is within range for her revenue and profit target
- if not, take corrective actions including changing her expense budget
Rick will see the same problem when he logs in, and also see and approve the changes that Betty has made.
To watch the Alphablox demo in a screen show, open the Alphabloxdemo.ZIP file below. Unzip the files to a local file and then uinzip the section you want to view. Within the files find the showme.html and click it. There you go, the demo starts!
The Data Integration Layer of the Business Intelligence Framework defines the functions and services to source data, bring it into the warehouse operating environment, improve it's quality, and format it for presentation through tools made available via the Access Layer.
This section should take 90 -120 minutes to complete.
Topics:
- Integration Layer - Introduction
- Extraction - Transformation Methods
- Lab: Business Intelligence Tutorial: Introduction to the Data Warehouse Center
- Lab: Business Intelligence Tutorial: Extended Lessons
- DB2 II and SQL Replication
Integration Layer - Introduction
The Integration Layer owns the technology and processes for moving and enhancing the data from the data sources to Business Intelligence repositories.
Figure 29. Integration layer: Services
The Integration Layer owns the technology and processes for moving and enhancing the data from the data sources to Business Intelligence repositories.
Figure 30. Integration layer: Tools
Extraction - Transformation methods
Review the following article by Dan Simchuk, "ETL solutions for IBM DB2 Universal Database". This article focuses on Extraction, Transformation, and Loading (ETL) of data for IBM DB2 Universal Database (UDB) (implementations for UNIX, Linux, and Windows) and describes the major options, technologies, and products available from IBM and from other vendors.
ETL solutions for IBM DB2 Universal DatabaseLab: Business Intelligence tutorial: Introduction to the Data Warehouse Center
This lab includes a series of exercises to define the Data Warehouse Control Database and subsequent processes that extract, transform, and load data from a source to target site. Additional discussion concerning roles, security, and process scheduling are also covered.
Business Intelligence Tutorial: Introduction to the Data Warehouse CenterLab: Business Intelligence tutorial: Extended lessons
In this lab we'll cover extended DWC functions that allow the warehouse administrator to create a Star Schema outline, catalog data within the DWC for end users, and maintain the Data Warehouse Center environment.
Business Intelligence Tutorial: Extended LessonsDB2 Information Integrator and SQL replication
Review the Overview section of the Announcement Letter: "IBM DB2 Information Integrator for Linux, UNIX, and Windows V8.2?", which discusses IBM DB2 Information Integrator V8.2, the next release of industry-leading information integration middleware portfolio from IBM that helps an enterprise become an on demand business.
IBM DB2 Information Integrator for Linux, UNIX, and Windows V8.2Review Part 1 of the Replication Guide and Reference, which covers replication planning, setup, defining sources and targets, registering tables, subscribing to sources, running the capture, apply, and monitor programs.
Replication Guide and ReferenceReview the article "Replication setup for DB2 Universal Database- a step-by-step guide to user copy replication". This article explains what replication can do for you, and shows you how to set it up, step by step, using an example from an actual customer site. The author includes hints and tips along the way to help ensure your success:
Replication setup for DB2 Universal DatabaseReview the following Redbook: "A Practical Guide to DB2 UDB Data Replication V8", which will provide you with detailed information that you can use to install, configure, and implement replication among the IBM database family - DB2 and Informix.
A Practical Guide to DB2 UDB Data Replication V8The Data Source Layer categorizes data as Enterprise, Unstructured, Informational, or External. Driven by the meta data characteristics of each category, tools will be used to access and prepare the data within each category.
This section should take 30 minutes to complete.
Topics:
- Data Sources - Introduction
- Content Management
The Data Source Layer provides the data or raw materials (both internal and external to the organization) that will be the foundation for analysis and knowledge.
Figure 31. Data sources: Characteristics
Review Chapter 2 (Overview) and Chapter 4 (Case Study) in the Redbook - "Implementing Web Applications with Information Integrator for Content and OnDemand Web Enablement Kit": Implementing Web Applications with CM Information Integrator for Content and On Demand.
| Description | Name | Size | Download method |
|---|---|---|---|
| Files used in this study pack | BISAT.ZIP | 13MB |
FTP
 |HTTP |
| Study Guide | FASTPATHTODB2.pdf | 7MB |
FTP
|HTTP |
| Introduction to the BI Architecture Framework | INT1ARCHINTRO.pdf | 751KB |
FTP
|HTTP |
| BI Architecture: Technical Layers | INT2TECHARCH.pdf | 145KB |
FTP
|HTTP |
| Alphablox demo | alphabloxdemo.ZIP | 16MB |
FTP
|HTTP |
Information about download methods Get Adobe® Reader®
- Download trial versions of the DB2 information management software products including DB2 Cube Views and Intelligent Miner.
- Visit the developerWorks DB2 Business Intelligence
technical resource page to learn more about DB2 Business Intelligence. You'll find technical documentation, how-to articles, education, downloads, product information, and more.
- Participate in developerWorks blogs and get involved in the developerWorks community.
Comments (Undergoing maintenance)
Table of contents
Local resources
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
