Restructuring Data

While an analysis tool like PowerPlay works its magic on a multidimensional database, a query tool like IQ/Objects looks at relational tables and flat-file databases to create a desired view -- and often produces the multidimensional data that an analytical engine needs. Just how does a query tool organize its data?

One approach might be a large, comprehensive multijoin, but this could be impossible or impractical, as well as highly wasteful and inefficient. IQ/Objects takes a different approach, creating what's called a star schema. In essence, this is a table formed by joining the most important base tables and connecting to it smaller subsidiary tables. The subsidi ary tables hold keys for accessing other data (see the figure "Data Is the Star"). At first blush, this might sound like a slight variation on a relational database system, but it differs significantly in that the subsidiary files are all linked to a central data table. This creates a data topology similar to that of a star network. Database systems that support star schemata include Red Brick, Oracle, Sybase, and Informix.

The alternative is a true multidimensional database, such as Oracle's (Redwood Shores, CA) Data Warehouse or Arbor Software's (Sunnyvale, CA) Essbase. A relational table represents data as a 2-D flat matrix. A multidimensional database, however, stacks multiple tables into a 3-D block (see the figure "Cubic Data"), much like pages in an Excel spreadsheet or Quattro Pro notebook. More-powerful multidimensional databases extend this metaphor into higher dimensions by "stacking" blocks into hyperblocks. While these constructions offer greater flexibility, they also consume more computin g power and considerably more overhead. For a query tool, creating a true multidimensional database is overkill; for an analysis engine, a multidimensional database is ideal, giving maximum flexibility for slicing and dicing data to create new views and for drilling down to increasing levels of detail.

---

Cubic Data

illustration_link (9 Kbytes)

Multidimensional databases don't use flat tables related by key fields and values. Instead, they stack 2-D tables into cubes, cubes into 4-D hypercubes, and so on. This structure lets you create different views and slices of data by approaching it from different directions and along different dimensions.

---

Data Is the Star

illustration_link (13 Kbytes)

IQ/O creates a star schema for its data, taking original relational tables and combining them into a new format that speeds up report generation.