Business Intelligence Tutorial

Defining warehouse sources

In the next few lessons, you will focus on defining the Market dimension table that was introduced in Tutorial business problem. In this lesson, you will define warehouse sources, which are logical definitions 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 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.


Updating the TBC sample sources

The sample warehouse sources do not have a user ID and password associated with them. You need to add a user ID and password before you can work with these sources. In this exercise, you will add a user ID and password for the TBC Sample Sources.

To update the TBC sample sources:

  1. Expand the Warehouse Sources tree.
  2. Right-click on TBC Sample Sources, and click Properties.

    The Properties -- TBC Sample Sources window opens.

  3. Click the Database tab.
  4. In the User ID field, type the user ID that you specified when you created the sample database in "Chapter 2. Creating a warehouse database" on page 5.
  5. In the Password field, type the password for the user ID.
  6. In the Verify Password field, type the password again.
  7. Click OK.

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:

  1. Right-click the Warehouse Sources folder.
  2. Click Define --> DB2 Family --> DB2 UDB for Windows NT.

    The Define Warehouse Source notebook opens.

  3. In the Name field, type the business name (a descriptive name that users will understand) for the warehouse source:
    Tutorial Relational Source
    

    You will use this name to refer to your warehouse source throughout the Data Warehouse Center.

  4. In the Administrator field, type your name as the contact for the warehouse source.
  5. In the Description field, type a short description of the data:
    Relational data for the TBC company
    


    Define Warehouse Source notebook.

  6. Click the Database tab.
  7. In the Database name field, select or type DWCTBC as the name of the physical database.
  8. 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 Creating a warehouse database.

  9. In the Password field, type the password for the user ID.
  10. In the Verify password field, type the password again.


    The Database page of the Define Warehouse Source notebook.

  11. Click the Tables and views tab.

    Because the tables are in a DB2 database, you can import the table definitions from DB2 rather than defining them manually.

  12. Expand the Tables folder.

    The Filter window opens.

  13. 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 tables in the Available tables and views list.

  14. From the Available tables and views list, select the SAMPLTBC.GEOGRAPHIES table.
  15. Click > to move the SAMPLTBC.GEOGRAPHIES table to the Selected tables and views list.


    Figure db2tu006 not displayed.

  16. Click the Security tab.
  17. Click 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.
  18. Click >

    Adding the source to the Selected warehouse groups list authorizes the users in the group (in this case, you) to define tables and views for the source.

  19. 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:

  1. Right-click the Warehouse Sources folder.
  2. Click Define --> Flat File --> Local files.

    The source type is Local files because the file that will be used in this exercise was installed on your workstation along with the tutorial.

    The Define Warehouse Source notebook opens.

  3. In the Name field, type the business name for the warehouse source:
    Tutorial file source
    
  4. In the Administrator field, type your name as the contact for the warehouse source.
  5. In the Description field, type a short description of the data:
    File data for the TBC company
    
  6. Click the Files tab.
  7. Right-click in the blank area of the Files list, and click Define.

    The Define Warehouse Source File notebook opens.

  8. In the File name field, type the following name:
    X:\Program Files\sqllib\samples\db2sampl\dwc\demographics.txt
    

    where:

    On a UNIX(R) system, file names are case-sensitive.

  9. In the Description field, type a short description of the file:
    Demographics data for sales regions.
    
  10. In the Business name field, type:
    Demographics Data
    
  11. Click the Parameters tab.


    The Parameters page of the Define Warehouse Source File notebook.

  12. Verify that Character is selected in the File type list.
  13. Verify that the comma is selected in the Field delimiter character field.

    As you saw in the lesson Browsing the source data, the file is comma-delimited.

  14. Verify that the First row contains column names check box is cleared.

    The file does not contain column names.

  15. 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.

  16. Click the COL001 column name to change the column name.
  17. Type the new name for the column:
    STATE
    
  18. Repeat steps 16 and 17 to rename the rest of the columns. Rename COL002 as CITY and COL003 as POPULATION.


    Define warehouse source file fields.

  19. Click OK.

    The Define Warehouse Source File notebook closes.

  20. In the Define Warehouse Source notebook, click the Security tab.
  21. Select the Tutorial Warehouse Group to grant your user ID the ability to create steps that use this warehouse source.
  22. Click > to move the Tutorial Warehouse Group to the Selected Warehouse Groups list.
  23. Click OK to save your changes and close the Define Warehouse Source 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 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 | Table of Contents ]