![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Data Warehousing overview
Currently, data warehouses are an integral parts of many businesses. Nevertheless, since legacy applications and various RDBMS are in use at most businesses, consolidating all of the data becomes a huge problem. The IBM Informix Warehouse Feature provides an integrated and simplified software platform to design and deploy a warehouse repository on your existing Informix infrastructure. Consolidating data using Informix for a data warehouse (DW) is an ideal solution for businesses who want to build end-to-end business intelligence (BI) and reporting solutions.
DW databases provide a decision support system (DSS) that you can use to evaluate the performance of an entire business over time. In the broadest sense, the term DW is used to refer to a database that contains very large storages of historical data. The data is stored as a series of snapshots, in which each record represents data at a specific time. By analyzing these snapshots, you can make comparisons between different time periods. You can then use these comparisons to help make important business decisions.
DW databases are optimized for data retrieval. The duplication or grouping of data, referred to as database de-normalization, increases query performance and is a natural outcome of the dimensional design of the DW, while an online transaction processing (OLTP) database automates day-to-day transactional operations. OLTP databases are optimized for data storage and strive to eliminate data duplication. A DW can be implemented in several different ways. You can use a single data management system, such as Informix, for OLTP and DSS environments. Or, you can build two separate environments depending on system requirements.
The meaning of the term DW can vary from people to people. A DW environment can encompass any of the following forms.
- Data warehouse
- Data mart
- Operational data store
- Data staging
Figure 1 is an overview of the software technologies and tools that are typically present or needed in the infrastructure of an end-to-end DW solution. It also shows how the data flows from the sources to the targets in the process of creating and maintaining the DW.
Figure 1. Big picture of DW solution
The DW itself can comprise one or more databases of the repository types (ODS, data staging, data mart, data warehouse). Some of these could be the final data warehouse version that the front-end BI tools will use, and some could be used as staging databases in more complex data consolidation projects. Depending on the business need, you may want to put different warehouse repositories available to different business intelligence tools, including reporting, dashboard, data analytics and so on. The ETL processes need to be automatic for the initial and periodic tasks of consolidating and summarizing data from the different data sources (typically, OLTP database systems, external repositories and files) into the warehousing databases. Depending on the DW requirement, the ETL processes can be deployed in different places, and can be working in several stages to deal with the complexity of data.
Let's take a look at how Informix can be used in a DW solution. You can more effectively leverage Informix for BI using Informix Warehouse capabilities to create and populate a data warehouse repository, and then utilize front-end analysis and reporting tools, like IBM Cognos, to provide BI dashboards and other types of analytic applications and reports on top of the warehouse repository. Informix users can deploy an end-to-end warehouse solution, simplifying operational complexity and reducing costs by using a single database server product for both operational and warehouse data.
Informix Warehouse has a component-based architecture with client and server pieces. The following software components are provided in Informix Warehouse.
- Warehouse client: The Informix Warehouse Design Studio used as a warehouse client. It's an Eclipse-based common design environment for defining the source and target databases involved in your DW project, creating and reverse engineering physical data models of your databases, and building SQL-based data flows and control flows to quickly and easily build in-database data movements and transformations into your warehouse.
- Warehouse server: The Informix Warehouse
Administration Console used as a warehouse server. It's a
Web-based application for administering database and system
resources related to your warehouse, as well as deploying,
scheduling, and monitoring the control flows previously created in
Design Studio through processes called the SQL Warehousing (SQW)
services. The Administration Console allows you to do the
following.
- Manage common resources, such as database connections and machine resources.
- Schedule the execution of control flows (sequences of ELT data flows).
- Monitor the execution status.
The following sections highlight some useful Informix warehouse functionalities that are available within the Informix product.
