Creating data marts with the IBM OpenAdmin Tool (OAT) for Informix

The IBM® OpenAdmin Tool (OAT) for Informix® is a web application for administering and analyzing the performance of IBM Informix database servers. You can now use OAT to administer Informix Warehouse Accelerator. Informix Warehouse Accelerator processes warehouse queries more quickly than the Informix database server does. With OAT, you can create data marts based on workload analysis, which is a process that analyzes the query workload in a data warehouse. You can use OAT to schedule loads to refresh the data in the data marts. You can also use OAT to create and drop accelerators and enable and disable data marts. In this article, learn how to create data marts using OAT.

Sumanth Rajagopal (sumanth@us.ibm.com), Software Engineer, IBM

Sumanth Rajagopal phooSumanth is a developer on the Informix OpenAdmin Tool, and has been working on it for the past 4 years. Before that, he worked as a developer on the Informix server. He works out of the San Jose, California office.



18 April 2013

Introduction

Customer quote

"Before using Informix Warehouse Accelerator, complex inventory and sales analysis queries on the enterprise warehouse with more than a billion rows took anywhere from a few minutes to 45 minutes to run. When we ran those same queries using Informix Warehouse Accelerator, they finished in 2 to 4 seconds. That means they ran from 60 to 1400 times as quickly, with an average acceleration factor of more than 450 - all without any index or cube building, query tuning or application changes." - Ashutosh Khunte, Senior Database Architect, Skechers USA

The Informix database server has traditionally been known for high-performance Online Transaction Processing (OLTP) workloads. With the integration of Informix Warehouse Accelerator and the Informix database server, tremendous query acceleration performance has been observed, with customers reporting 60 to 1400 times quicker responses for certain datawarehouse queries. (See Customer Quote.)

The OpenAdmin Tool (OAT) is an administration tool for Informix database servers that can now administer accelerators and data marts associated with the Informix server. This article explains how to create a data mart using OAT. It is assumed that you are familiar with the Informix server and the Informix Warehouse Accelerator.

Since version 3.11, data marts can be created and managed in the Schema Manager plug-in of OAT. This requires Informix server version 12.10 or higher.


Workload analysis

OAT creates data marts using the Informix server's workload analysis capability. Whether it is a simple workload with a handful of queries or a complex one with hundreds of queries, when you run the workload, the server analyzes them and determines which queries can be accelerated. Based on the join conditions in the queries, the server creates an optimal data mart definition. This definition is used to create and deploy the data mart on the Informix Warehouse Accelerator server.


Workload capture

You run queries on the Informix server using dbaccess or any client tool or application. With SQL Tracing turned on in the server, the query statements and related information are stored in the SQL Trace buffer.

From the Schema Manager in OAT, select the database (also referred to as the warehouse database) where you intend to create the data mart. On the Actions menu, select Data Marts > Create a Data Mart. Name the new workload and click Start. The SQL trace settings that you use for the workload capture, such as the number of statements, buffer size, and the user executing the queries, are set in the server as the SQL trace setting for the workload capture. Existing SQL trace settings, if any, are restored after you run the workload. See Figure 1.

Run the workload queries. When done, click Stop. (See Figure 2.) Stopping copies the statements to a table in the warehouse database. This table is named with the prefix, 'dwa_saved_workloadtab_' appended to the workload name. The SQL trace settings are restored to the values that they were before you ran the workload. A probe is done on the captured statements to determine which statements can be accelerated.

If the workload is long running and you need to do administrative tasks in OAT, you can save the state of the wizard and retrieve it later to create the data mart. To do this click Save and Close in step 1 of the wizard, as shown in Figure 1 (see larger image). To get back to the wizard, click Open on top of the details area of Schema Manager.

Figure 1. Capturing state in Create Data Mart wizard
Capturing state

Figure 2 (see larger image) shows the capture completed state in the Create Data Mart wizard.

Figure 2. Capture completed state in Create Data Mart wizard
Capture completed state

Review workload

The list of captured statements and whether they can be accelerated is displayed in step 2 of the wizard for review, as seen in Figure 3 (see larger image).

Figure 3. Review Workload in Create Data Mart wizard
Review workload

Data mart creation

In step 3, enter the data mart name and select an accelerator server. Optionally, you can load the data mart. (See Figure 3.) Data marts tend to have large amounts of data and, hence, the load operation can run a long time. You can schedule loads separately to run immediately or at a predetermined time. Also, you can retain the workload created in step 1 for later use to create a data mart without having to rerun the workload queries. Refer to Figure 4 (see larger image) and Figure 5.

Figure 4. Creating Data Mart
Data mart create

Figure 5 (see larger image) shows the results page in the Create Data Mart wizard.

Figure 5. Result page in Create Data Mart wizard
Result page

Data mart details

The data mart is listed in the Schema Manager alongside other database objects such as tables, views, and so on. The data mart details include a link to the accelerator that is associated with it, type of loads configured, load schedule, and the information about the tables that make up the data mart. See Figure 6 (see larger image).

Figure 6. Data mart details
Data mart details

Conclusion

In this article, you learned how to create data marts in the Informix server using OAT to take advantage of the powerful analytics capability of Informix.

Resources

Learn

Get products and technologies

  • Download the Informix Client SDK, which includes the web-based OpenAdmin Tool for Informix. OAT is included in the Linux x86 (32-bit), Linux x86-64 (64-bit), Windows x86 (32-bit only), and Mac OS X x64 (64-bit only) Informix Client SDK downloads.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=870691
ArticleTitle=Creating data marts with the IBM OpenAdmin Tool (OAT) for Informix
publish-date=04182013