Business Intelligence Tutorial

Lesson 6. Defining warehouse sources

In the next few lessons, you will focus on defining the Market dimension table in Tutorial business problem. In this lesson, you will define warehouse sources, which is a logical definition of the tables and files that will provide data to the Market dimension table. The Data Warehouse Center uses the specifications in the warehouse sources to access and select the data. You will define two warehouse sources that correspond to the source data that you viewed in Lesson 3, Browsing the source data:

Tutorial Relational Source: Corresponds to the GEOGRAPHIES source table in the DWCTBC database.
Tutorial File Source: Corresponds to the demographics file, which you will load into the warehouse database in a later lesson.

If you are using source databases that are remote to the warehouse server, you must register the databases on the workstation that contains the warehouse server.

Defining a relational warehouse source

In this exercise, you will define a relational warehouse source called the Tutorial Relational Source. It corresponds to the GEOGRAPHIES relational table that is provided in the DWCTBC database.

To define the Tutorial Relational Source:

Right-click the Warehouse Sources folder.
Click Define.
The Define Warehouse Source notebook opens.
In the Warehouse source name field, type the business name for the warehouse source:
```
Tutorial Relational Source
```
You will use this name to refer to your warehouse source throughout the Data Warehouse Center.
In the Administrator field, type your name as the contact for the warehouse source.
In the Description field, type a short description of the data:
```
Relational tables for the TBC company
```
In the Warehouse source type list, select the version of DB2 Universal Database for your operating system (such as DB2 Universal Database for Windows NT).
Click the Database tab.
In the Database name field, type DWCTBC as the name of the physical database.
In the User ID field, type a user ID that has access to the database.
Use the user ID that you specified when you created the sample database in Lesson 2, Creating a warehouse database.
In the Password field, type your password as the password for the user ID that will access the database.
In the Verify Password field, type the password again.
Click the Tables and views tab.
Because the tables are in a DB2 Universal Database database, you can import the table definitions from DB2 Universal Database rather than defining them manually.
Expand the Tables folder.
The Filter window opens.
Click OK.
The Data Warehouse Center displays a progress window. The import might take a while.
After the import finishes, the Data Warehouse Center lists the imported objects in the Available tables and views list.
From the Available tables and views list, select the GEOGRAPHIES table.
Click >.
The GEOGRAPHIES table is moved to the Selected tables and views list.
Click the Security tab.
Select the Tutorial Warehouse Group (which you created in Defining the warehouse group) to grant your user ID the ability to create steps that use this warehouse source.
Click >.
The Tutorial Warehouse Group moves to the Selected security groups list.

Accept the rest of the values in the notebook. For more information about the values, see "Warehouse Source" in the online help.
Click OK to save your changes and close the Define Warehouse Sources notebook.

Defining a file source

In this exercise, you will define a file warehouse source called the Tutorial File Source. It corresponds to the Demographics file that is provided with the Data Warehousing sample. For this tutorial, you will define only one file in the warehouse source, but you can define multiple files in a warehouse source.

To define the Tutorial File Source:

Right-click the Warehouse Sources folder.
Click Define.
The Warehouse Source notebook opens.
In the Warehouse source name field, type the business name for the warehouse source:
```
Tutorial file source
```
In the Administrator field, type your name as the contact for the warehouse source.
In the Description field, type a short description of the data:
```
File data for the TBC company
```
In the Warehouse source type list, click Local files.
The file was installed on your workstation along with the tutorial. Local stands for local file.
Click the Files tab.
Right-click in the blank area of the Files list, and click Define.

The Define Warehouse Source File notebook opens.
In the File name field, type the following name:
```
X:\Program Files\sqllib\samples\db2sampl\dwc\demographics.txt
```
where:
- X is the drive on which you installed the sample. This entry is the path and file name for the demographics file.
- sqllib is the directory under which you installed DB2 Universal Database.
The name of the file must not contain spaces. On a UNIX^(R) system, file names are case-sensitive.
In the Description field, type a short description of the file:
```
Demographics data for sales regions
```
Click the Parameters tab.
From the File type list, select Character.
In the Field delimiter character field, click the comma.
As you saw in the lesson Lesson 3, Browsing the source data, the file is comma-delimited.
Clear the First row contains column names check box.
The file does not contain column names.
Click the Fields tab.
The Data Warehouse Center reads the file that you specified on the Warehouse Source File page. It defines columns based on the fields in the file, and displays the column definitions in the Fields list. It displays sample data in the File preview area. Up to 10 rows of sample data are displayed. You can scroll to see all the sample data.
Double-click the COL001 column name to change the column name.
Type the new name for the column:
```
STATE
```
Press Enter.
Repeat step 16 through step 18 to rename the rest of the columns. Rename COL002 as CITY and COL003 as POPULATION.
Click OK.
The File notebook closes.
From the Warehouse Sources notebook, click the Security tab.
Select the Tutorial Warehouse Group to grant your user ID the ability to create steps that use this warehouse source.
Click >. The Tutorial Warehouse Group moves to the Selected security groups list.
Click OK to save your changes and close the Warehouse Sources notebook.

What you just did

In this lesson, you defined a relational warehouse source and a file warehouse source. You will use these sources in Lesson 8, Defining data transformation and movement to indicate that these sources provide data to the LOOKUP_MARKET dimension table for the star schema that you are defining for the TBC Company.

[ Top of Page | Previous Page | Next Page ]