OLAP Data Design
Developers tasked with building decision support databases are often faced with the daunting prospect of manually designing OLAP data cubes. This is a cumbersome process that leads many developers to run screaming down the hall.
OLAP database design is complicated by the fact that there are so many varieties of OLAP products. If you're designing an OLAP application you have an overwhelming variety of choices, from vendors such as Microsoft, Hyperion Solutions, Brio, Applix, Oracle, Business Objects, Cognos, MicroStrategy, Information Advantage and a host of others. Or, if you decide to use build your OLAP database by using a star schema on top of a relational database management system (RDBMS), you can go to Oracle, Informix, Sybase, IBM, or NCR for your solution. There are plenty of platforms to choose from once you decide which platform to deploy your solution on.
Unfortunately, there are far fewer selections to choose from when searching for an OLAP design tool. In this column, I'll discuss some of the criteria I would use to evaluate an OLAP design tool and briefly describe some of the vendors in this space.
The first thing you should look for in an OLAP tool is support for the major OLAP platforms or, at minimum, the OLAP platforms your organization is using. The ideal design tool should allow you to logically conceptualize OLAP data cubes and how they will interact with each other, while not limiting you to a specific OLAP platform. Once you are ready to enter the testing or deployment stages, the product should be able to generate the appropriate runtime cube on your target platform of choice.
Unfortunately, due to the lack of widely accepted standards, there is no product that universally addresses this requirement, yet. With the advent of Microsoft's MDD language -- part of the OLE DB for OLAP -- the potential is there for a product to fulfill this requirement. Query your vendors about their support for this emerging de facto standard. There are interoperability standards, such as the MDAPI from the OLAP Council, for accessing multidimensional data structures, but not for generating them.
Consider how easily the product supports changes to the cube structures. Ideally, it should allow you to come back at a later time and modify the logical dimensional structure of a cube, and then regenerate a new cube structure. It should also have a mechanism that allows you to easily migrate data from the old structure to the new, without significant down time.
The third thing to look for is support for collaborative programming. The days of the single developer pulling a series of all-nighters to put together an enterprise information application are gone in most IT shops. Your OLAP design tool should allow collaboration with versions, check-out and check-in, and the ability to synchronize the efforts of a team of developers. If the tool doesn't provide this functionality directly, then look for support from one of the better known configuration management tools.
Another consideration is the degree to which the tool is usable by users or business analysts, rather than IT developers. It's far more productive to give departmental users tools they can use to build and query their own cubes, rather than getting into the cube building business yourself. You should be focused on providing the information infrastructure that delivers the data to the cubes, unless the cube has specialized functionality, is unusually complex, or is so mission-critical that it needs to be carefully designed for maximum performance.
There are two independent vendors that provide sophisticated OLAP modeling tools that should be considered. Appsco (www.appsco.com) is a U.K. company that is moving into the U.S. marketplace. Their Appsmart, which is tightly integrated with Microsoft SQL Server 7, is a rapid development tool for building data marts. It includes support for DTS, SQL Server OLAP Services, and Excel. Database support also exists for IBM DB2 and Oracle. The application supports check-out and check-in, and automatically changes a SQL Server physical schema to reflect updates to a logical schema.
Another tool to consider is Constructa from Anubis (www.anubis.com). Constructa generates relational star schemas, and is oriented toward development of larger, enterprisewide data warehouses, rather than data marts. --Robert Craig is vice president of marketing at WebXi Inc. (Burlington, Mass.), and a former director at the Hurwitz Group Inc. Contact him at rcraig@webxi.com.