Business Intelligence Tutorial

About the tutorial

This tutorial provides an end-to-end guide for typical business intelligence tasks. It has two main sections:

Data warehousing
Do the lessons in this section to learn how to use the DB2 Control Center and Data Warehouse Center to create a warehouse database, move and transform source data, and write the data to the warehouse target database. Completing this section should take you about 5 hours.

Multidimensional data analysis
Do the lessons in this section to learn how to use the OLAP Starter Kit to perform multidimensional analysis on relational data using Online Analytical Processing (OLAP) techniques. Completing this section should take you about an hour.

The tutorial is available in HTML or PDF format. You can view the HTML version of the tutorial from the Data Warehouse Center, OLAP Starter Kit, or the Information Center. The PDF file is available on the DB2 Publications CD-ROM.


Tutorial business problem

You are a database administrator for a company that is called TBC: The Beverage Company. The company manufactures beverages for sale to other businesses. The financial department wants to track, analyze, and forecast the sales revenue across geographies on a periodic basis for all products sold. You have already set up standard queries of the sales data. However, these queries add to the load on your operational database. Also, users sometimes ask for additional ad-hoc queries of the data, based on the results of the standard queries.

Your company has decided to create a data warehouse for the sales data. A data warehouse is a database that contains data that has been cleansed and transformed into an informational format. Your task is to create this data warehouse.

You plan to use a star schema design for your warehouse. A star schema is a specialized design that consists of multiple dimension tables, and one fact table. Dimension tables describe aspects of a business. The fact table contains the facts or measurement about the business. In this tutorial, the star schema includes the following dimensions:

The facts in the fact table include orders of the products over a period of time.

The Data warehousing part of this tutorial shows you how to define this star schema.

Your next task is to create an OLAP application to analyze your data. You first create an OLAP model and metaoutline, and then use them to create the application. The Multidimensional Analysis part of this tutorial shows you how to create an OLAP application.


Before you begin

Before you begin, you must install the products that are covered in the sections of the tutorial that you want to use:

You must also install the tutorial. In DB2 for Windows, you can install the tutorial as part of a typical install. In DB2 for AIX or the Solaris Operating Environment, you can install the tutorial with the documentation.

You need sample data to use with the tutorial. The tutorial uses the DB2 Data Warehousing sample data and the OLAP sample data.

The Data Warehousing sample data is installed on Windows NT only, when you install the tutorial. It must either be installed on the same workstation as the DB2 Warehouse Manager, or the remote node for the sample databases must be cataloged on the DB2 Warehouse Manager workstation.

You can install the OLAP sample data on Windows NT, AIX, and the Solaris Operating Environment. It must either be installed on the same workstation as the OLAP Integration Server server, or the remote node for the sample databases must be cataloged on the server workstation.

This tutorial contains several references to sample data under the X:\sqllib directory, where X is the drive under which you installed DB2. If you used the default directory structure, the data is installed under X:\Program Files\sqllib instead of X:\sqllib.

You must create the sample databases after you install the files for the sample. To create the databases:

  1. Skip this step if the First Steps window is already open. Click Start --> Programs --> IBM DB2--> First Steps.

    The First Steps window opens.

  2. Click Create Sample Databases. If Create Sample Databases is disabled, the sample databases have already been created.

    The Create SAMPLE Databases window opens.

  3. Select the Data Warehousing sample check box, OLAP sample check box, or both, depending on which parts of the tutorial you want to do.
  4. Click OK.
  5. If you are installing the Data Warehousing sample, a window opens for the DB2 user ID and password to use to access the sample.
    1. Type the user ID and password that you want to use. Note down the user ID and password, because you will need them in a later lesson, when you define security.
    2. Click OK.

    DB2 starts to create the sample databases. A progress window opens. It may take a while for the databases to be created. When the database has been created, click OK.

If you are installing the sample on Windows NT, the databases are automatically registered with ODBC. If you are installing the sample on AIX or the Solaris Operating Environment, you must manually register the databases with ODBC. For more information about registering the databases on AIX or the Solaris Operating Environment, see DB2 Universal Database Quick Beginnings for your operating system.

If you selected Data Warehousing Sample, the following databases are created:

DWCTBC
Contains the operational source tables that are required for the Data Warehousing section of the tutorial.

TBC_MD
Contains metadata for the Data Warehouse Center objects in the sample.

If you selected the OLAP sample, the following databases are created:

TBC
Contains the cleansed and transformed tables that are required for the Multidimensional data analysis section of the tutorial.

TBC_MD
Contains metadata for the OLAP objects in the sample.

If you select both the Data Warehousing and OLAP samples, the TBC_MD database contains metadata for both the Data Warehouse Center and OLAP objects in the sample.

Before you begin the tutorial, verify that you can connect to the sample databases:

  1. Start the DB2 Control Center:
  2. Expand the tree until you see one of the sample databases: DWCTBC, TBC, or TBC_MD.
  3. Right-click the name of the database and click Connect.

    The Connect window opens.

  4. In the User ID field, type the user ID that you used to create the sample.
  5. In the Password field, type the password that you used to create the sample.
  6. Click OK.

    The DB2 Control Center connects to the database. If the DB2 Control Center is not able to establish a connection, you will see an error message.


Conventions that are used in this tutorial

This tutorial uses typographical conventions in the text to help you distinguish between the names of controls and text that you type. For example:


Related information

This tutorial covers the most common tasks that you can accomplish with the DB2 Control Center, Data Warehouse Center, and OLAP Starter Kit. For more information about related tasks, see the following documents:

Control Center

Data Warehouse Center

OLAP Starter Kit

Contacting IBM

If you have a technical problem, please review and carry out the actions suggested by the Troubleshooting Guide before contacting DB2 Customer Support. This guide suggests information that you can gather to help DB2 Customer Support to serve you better.

For information or to order any of the DB2 Universal Database products contact an IBM representative at a local branch office or contact any authorized IBM software remarketer.

If you live in the U.S.A., then you can call one of the following numbers:

Product Information

If you live in the U.S.A., then you can call one of the following numbers:

http://www.ibm.com/software/data/
The DB2 World Wide Web pages provide current DB2 information about news, product descriptions, education schedules, and more.

http://www.ibm.com/software/data/db2/library/
The DB2 Product and Service Technical Library provides access to frequently asked questions, fixes, books, and up-to-date DB2 technical information.
Note:
This information may be in English only.

http://www.elink.ibmlink.ibm.com/pbl/pbl/
The International Publications ordering Web site provides information on how to order books.

http://www.ibm.com/education/certify/
The Professional Certification Program from the IBM Web site provides certification test information for a variety of IBM products, including DB2.

ftp.software.ibm.com
Log on as anonymous. In the directory /ps/products/db2, you can find demos, fixes, information, and tools relating to DB2 and many other products.

comp.databases.ibm-db2, bit.listserv.db2-l
These Internet newsgroups are available for users to discuss their experiences with DB2 products.

On Compuserve: GO IBMDB2
Enter this command to access the IBM DB2 Family forums. All DB2 products are supported through these forums.

For information on how to contact IBM outside of the United States, refer to Appendix A of the IBM Software Support Handbook. To access this document, go to the following Web page: http://www.ibm.com/support/, and then select the IBM Software Support Handbook link near the bottom of the page.

Note:
In some countries, IBM-authorized dealers should contact their dealer support structure instead of the IBM Support Center.


[ Top of Page | Previous Page | Next Page | Table of Contents ]