Before you start
This tutorial series, Get started with Informix Warehouse Feature, introduces you to the highlights and capabilities of the new client and server software tools available for you in the Informix Warehouse option. These tools enable you to create and deploy a project for modeling the databases in your Informix data warehousing plaform, and for automating the data transformation and movement processes to easily acquire and integrate data from different sources into your Informix warehouse.
The tutorials in this series are organized following this line of deployment: First, you will use the client component of the Informix Warehouse software—the Design Studio—to create the data warehouse (DW) project around Informix and build the data models for source and target databases. Then, you will continue using Design Studio to create and test the jobs for extracting, loading, and transforming (ELT) data from various sources (typically, OLTP databases and external files) into the target warehouse repository on Informix. Finally, the series illustrates the use of the server components in the Informix Warehouse—the Administration Console and SQW services—to manage and monitor resources associated to your warehouse, including the ELT jobs you programmed before in Design Studio.
This tutorial is the second part of the Get started with Informix Warehouse Feature series. Get an introduction to the data and control flows, as well as the ETL operators and design data flows. Get familiar with the components of data warehouse project, and use and test data flow operators, ports, and connectors. Define a control flow, get familiar with the components of a control flow, and use and test control flows. Continue using Design Studio to create and test the jobs for extracting, loading, and transforming (ELT) data from various sources (typically, OLTP databases and external files) into the target warehouse repository on Informix.
When you have completed this tutorial, you will:
- Understand how Design Studio provides a graphical development platform for creating SQL-based Extract-Load-Transform (ELT) jobs
- Understand the difference between data flows and control flows, and how they form the ELT activities of a warehouse application
- Understand the capabilities for SQL-based ELT jobs provided by the Informix Warehouse Feature
- Design applications for populating warehouse and mart tables using SQL-based data flows and control flows
- Test data flows and control flows in your warehouse application using the execution and debugging capabilities in Design Studio
This tutorial is written based on an Informix Dynamic Server and
Informix Warehouse installation on a Microsoft® Windows®
platform. However, most of the information in this tutorial applies in
the context of a solution on UNIX® or Linux® too. The
tutorial is written for Informix database users who are familiar with
and have working knowledge of Informix tools such as
dbaccess, SQL language and scripts, Windows
environments, and Eclipse-based IDE/GUI tools. You should also have
skills and experience in relational database design, ER models, data
warehousing and ETL tools.
This tutorial assumes that you have:
- Informix Warehouse Feature Client installed. For more information about how to install the product, please refer to the Resources section.
- Informix Dynamic Server (IDS) 11.50.xC3 or later installed and a basic working knowledge of IDS. For download information, please refer to the Resources section.
- Completed the following basic installation and configuration steps
of this series:
- Followed the prerequisites for Part 1, including creating both stores_demo and sales_demo databases.
- Read the introductory information about Informix Warehouse Feature.
- Read the business scenario for the tutorial.
- Created a data warehousing project in Design Studio with connections to both source and target databases.
- Created the physical data model for source OLTP database stores_demo from reverse engineering.
- Created the physical data model for target data mart database sales_demo from an empty template, with new tables: time, customer, product, geography, and sales.
To run the examples in this tutorial, you need a system with at least the Informix Warehouse Feature client installed and an Informix Dynamic Server installed, with a database server instance up and running (local or remote).
You should also have already followed the steps in Part 1 of this series.
- Make sure you have the environment variables set at the Informix instance level, including the default setting DBDATE=MDY4/, which is needed for loading additional rows into the orders table.
- Download and uncompress into a temporary directory the following files (provided in the Downloads section):
dbaccess stores_demo - > load from 'add_orders.unl' insert into orders; > load from 'add_items.unl' insert into items;
- Connect to the database sales_demo using the same userid admin you used when creating the sales_demo database.
- Make sure you have the environment variables set at the Informix
instance level to use
dbaccessagainst the target database sales_demo.
- Download and uncompress into a temporary directory the following file (provided in the Downloads section):
sales_demo_alter.sql) against the target database sales_demo:
dbaccess sales_demo sales_demo_alter.sql
- Log in on the operating system where the Informix Warehouse client (Design Studio) is located.
- Download the following files (provided in the Downloads section):
- Notice the file time.unl contains date values using the format of DBDATE=MDY4/, which is not the default date format in Design Studio. In this tutorial, you will learn how to configure the JDBC connection to the target datamart database and the input file format to make them use the same date format used by a plain text file containing data to load a table in the target data warehousing system.