This tutorial provides an end-to-end guide for typical business intelligence tasks. It has two main sections:
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.
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, you must install the products that are covered in the sections of the tutorial that you want to use:
If you install the DB2 server on a different workstation from the warehouse server or the Data Warehouse Center administrative interface, you must install DB2 CAE on the same workstation as the Data Warehouse Center administrative interface.
For more information about installing DB2 Universal Database and the warehouse server, see DB2 Universal Database Quick Beginnings for your operating system.
Optionally, you can install the Information Catalog Manager if you have the DB2 Warehouse Manager. If you do not have the DB2 Warehouse Manager, skip Lesson 14, Cataloging data in the warehouse for end users and Lesson 15, Working with business metadata.
For more information about installing the DB2 Warehouse Manager, see DB2 Warehouse Manager Installation Guide.
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 warehouse manager, or the remote node for the sample databases must be cataloged on the 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:
The Create SAMPLE Databases window opens.
DB2 starts to create the sample databases. A progress window opens. 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 System, you must manually register the databases with ODBC. For more information about registering the databases on AIX or the Solaris Operating System, see DB2 Universal Database Quick Beginnings for your operating system.
If you selected the Data Warehousing sample, the following databases are created:
If you selected the OLAP sample, the following databases are created:
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:
db2jstrt 6790 db2cc 6790b
The Connect window opens.
The DB2 Control Center connects to the database.
This tutorial uses typographical conventions in the text to help you distinguish between the names of controls and text that you type. For example:
Click Menu --> Menu choice.
Type text in the Field field.
This is the text that you type.
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: