DB2 Universal Database offers the Data Warehouse Center, a component that automates data warehouse processing. You can use the Data Warehouse Center to define the processes that move and transform data for the warehouse. Then, you can use the Data Warehouse Center to schedule, maintain, and monitor these processes.
This chapter provides an overview of data warehousing and data warehousing tasks. For more detailed information about warehousing, and for information about using the Data Warehouse Center, see the Data Warehouse Center online help.
The systems that contain operational data (the data that runs the daily transactions of your business) contain information that is useful to business analysts. For example, analysts can use information about which products were sold in which regions at which time of year to look for anomalies or to project future sales.
However, several problems can arise when analysts access the operational data directly:
Data warehousing solves these problems. In data warehousing, you create stores of informational data. Informational data is data that is extracted from the operational data and then transformed for decision making. For example, a data warehousing tool might copy all the sales data from the operational database, clean the data, perform calculations to summarize the data, and write the summarized data to a target in a separate database from the operational data. Users can query the separate database (the warehouse) without impacting the operational databases.
The following sections describe the objects that you will use to create and maintain your data warehouse.
A subject area identifies and groups the processes that relate to a logical area of the business. For example, if you are building a warehouse of marketing and sales data, you define a Sales subject area and a Marketing subject area. You then add the processes that relate to sales under the Sales subject area. Similarly, you add the definitions that relate to the marketing data under the Marketing subject area.
Warehouse sources identify the tables and files that will provide data to your warehouse. The Data Warehouse Center uses the specifications in the warehouse sources to access the data. The sources can be nearly any relational or nonrelational source (table, view, or file) that has connectivity to your network.
Warehouse targets are database tables or files that contain data that has been transformed. Like a warehouse source, users can use warehouse targets to provide data to other warehouse targets. A central warehouse can provide data to departmental servers, or a main fact table in the warehouse can provide data to summary tables.
Warehouse agents manage the flow of data between the data sources and the target warehouses. Warehouse agents are available on the AIX(R), AS/400(R), OS/2(R), OS/390(R), and Windows NT operating systems, and for the Solaris Operating Environment. The agents use Open Database Connectivity (ODBC) drivers or DB2 CLI to communicate with different databases.
Several agents can handle the transfer of data between sources and target warehouses. The number of agents that you use depends on your existing connectivity configuration and the volume of data that you plan to move to your warehouse. Additional instances of an agent can be generated if multiple processes that require the same agent are running simultaneously.
Agents can be local or remote. A local warehouse agent is an agent that is installed on the same workstation as the warehouse server. A remote warehouse agent is an agent that is installed on another workstation that has connectivity to the warehouse server.
An agent site is a logical name for a workstation where agent software is installed. The agent site name is not the same as the TCP/IP host name. A single workstation can have only one TCP/IP host name. However, you can define multiple agent sites on a single workstation. A logical name identifies each agent site.
The default agent site, named the Default DWC AgentSite, is a local agent on Windows NT that the Data Warehouse Center defines during initialization of the warehouse control database.
A process contains a series of steps that perform a transformation and movement of data for a specific warehouse use. In general, a process moves source data into the warehouse. Then, the data is aggregated and summarized for warehouse use. A process can produce a single flat table or a set of summary tables. A process might also perform some specific type of data transformation.
A step is the definition of a single operation within the warehouse. By using SQL statements or calling programs, steps define how you move data and transform data. When you run a step, a transfer of data between the warehouse source and the warehouse target, or any transformation of that data, can take place.
A step is a logical entity in the Data Warehouse Center that defines:
Suppose that you want Data Warehouse Center to perform the following tasks:
You would create a process that contains several steps. Each step performs a separate task, such as extracting the data from a database or converting it to the correct format. You might need to create several steps to completely transform and format the data and put it into its final table.
When a step or a process runs, it can affect the target in the following ways:
For more information, see Defining processing options.
You can run a step on demand, or you can schedule a step to run at a set time. You can schedule a step to run one time only, or you can schedule it to run repeatedly, such as every Friday. You can also schedule steps to run in sequence, so that when one step finishes running, the next step begins running. You can schedule steps to run upon completion, either successful or not, of another step. If you schedule a process, the first step in the process runs at the scheduled time.
The following sections describe the various types of steps that you will find in the Data Warehouse Center. For more information about steps, see the Chapter 5, Defining and running processes and the Data Warehouse Center online help.
An SQL step uses an SQL SELECT statement to extract data from a warehouse source and generates an INSERT statement to insert the data into the warehouse target table.
There are several types of program steps: DB2 for AS/400 programs, DB2 for OS/390 programs, DB2 for UDB programs, Visual Warehouse 5.2 DB2 programs, OLAP Server programs, File programs, and Replication. These steps run predefined programs and utilities.
Transformer steps are stored procedures and user-defined functions that specify statistical or warehouse transformers that you can use to transform data. You can use transformers to clean, invert, and pivot data; generate primary keys and period tables; and calculate various statistics.
In a transformer step, you specify one of the statistical or warehouse transformers. When you run the process, the transformer step writes data to one or more warehouse targets.
A user-defined program step is a logical entity within the Data Warehouse Center that represents a business-specific transformation that you want the Data Warehouse Center to start. Because every business has unique data transformation requirements, businesses can choose to write their own program steps or to use tools such as those provided by ETI or Vality.
For example, you can write a user-defined program that will perform the following functions:
Creating a data warehouse involves the following tasks:
You can learn more about these tasks and others by using the Business Intelligence Tutorial, viewing the DB2 Universal Database Quick Tour, reading the Data Warehouse Center online help, or reading this book.