IBM Enhances Data Warehouse Capabilities

Last month IBM announced some significant enhancements to the AS/400's DB/2 flavor for data warehouse support. This announcement opens the world of the 400 to true multidimensional database support. Prior to this announcement, you had to use a third-party product, or store the data on another platform (e.g., NT or Unix) because those databases had that support. Online Analytical Processing (OLAP) support is the engine that allows large amounts of records to be analyzed quickly. This is normally done with some type of multidimensional database.

IBM licensed Hyperion's Essbase technology to provide the underlying architecture. What this does is provide tremendous power and a tool set that can slice and dice data until you can't stand it anymore (which may not take too long). Because the product allows us to use SQL-based or tools optimized for the Essbase technology, we can build data marts using what we know, or adopt what has become an industry standard set of tools.

Obviously, there is built-in support for Excel and Lotus 1-2-3, as well as other PC-based tools, and you can publish to the Web so all the world can see your data! According to IBM, other capabilities include: multiuser read and write access, large-scale data capacity, analytical calculations, flexible data navigation, consistent and rapid response, dynamic calculation, OLAP partitioning and replication, time series, and linked reporting objects.

All of these features combine to give us the ability to build sophisticated applications that can take advantage of a graphical interface for displays that can show graphs and still allow you to drill down to the source data.

Because data warehouse building and management is so complex, IBM has also announced a new product to assist in those efforts. The IBM DB2 Warehouse Manager for AS/400 product provides the tools to build, maintain, analyze and support the building of data warehouses utilizing the OLAP architecture in the new OLAP server. This includes support for more than just DB/2 databases. You can mix data from Oracle, SQL Server, Informix, Sybase, or any ODBC-connectable database. This way, you can build your warehouse from all the various dialects of data that you may have. Of course that sounds substantially easier than it is.

DB2 Warehouse Manager also comes with prebuilt programs and templates for loading data into the OLAP cubes. A cube is a multidimensional database of the data you are reporting on. This is where the warehouse gets its performance and drilling capabilities. Just as a user can kill your performance with one good query job, a user can bring the AS/400 to its knees if a poorly designed or large request is hitting the system. There are tools to help manage the usage of the cubes as well as manage the availability of the resources used by the system for any query.

While the 400 can certainly handle the performance issues of this type of system, you may have to look at some of the demands that will be placed on your machine. Memory and disk are two of the biggest. Any data warehouse environment needs lots of both. By definition you have a data warehouse because you have lots of data. The tools help optimize the storage of that data, but it usually requires multiple views and copies as you build different cubes of the same data set for various purposes. This requires disk and any time you are processing large portions of information you can never have enough memory. Therefore, when you are budgeting for this software, keep this in mind.

With this suite of releases, IBM takes another step in making the AS/400 DB2 flavor more like its brothers on other platforms. DB2 UDB had this functionality added earlier in the year. The only real problem with the announcement is that it is a little pricey. With a starting cost of around $40,000 for the OLAP server and $10,000 plus depending on your AS/400 model, you have to make a pretty serious commitment to data warehousing to make it work. For large organizations, this is just the cost of doing a data warehouse. For smaller ones, I think they will opt to do nothing, or to use more cost conscious approaches.

You will need to be on OS/400 V4R4 to take advantage of this, but you should be current anyway so this is just another good excuse.

John Bussert is president of Swift Technologies (Marengo, Ill.), a company that specializes in AS/400 and Windows software.

Related Editorial:

  • Hyperion, IBM Serve Up OLAP for Mainframes

    Related Information:

  • DB2 OLAP Server Version 1.1 Overview (new window)
  • Must Read Articles