SQL Server Magazine

With all the excitement that Microsoft SQL Server OLAP Services (formerly code-named Plato) is generating, looking at an established player in the online analytical processing (OLAP) market can give you an idea of how a mature OLAP server works and what it can provide. Arbor Software is one of the most notable established players. Arbor was a founding member of the OLAP Council and was instrumental in developing a benchmark for OLAP servers, the APB-1 sales and marketing benchmark application database. The APB-1 benchmark is similar to the Transaction Processing Council (TPC) benchmarks that database management system (DBMS) vendors use.

Arbor continues to assert a leadership role in OLAP development, the standards process, and the OLAP marketplace. Its Arbor Essbase OLAP Server 5 shares market leadership with Oracle's Oracle Express Server. Many vendors sell products that work with Essbase. (IBM has even licensed Essbase for its DB2 OLAP Server. For more information, see the sidebar "DB2 OLAP Server 1.0," page 94.) And many users rely on Essbase to support complex calculations, provide good performance, and offer lights-out functionality. With the recently announced merger between Arbor and a high-end analytical applications vendor, Hyperion Software, Arbor (which will operate under a new name, Hyperion Solutions) seems poised to dominate the high end of the OLAP and analytical applications markets.

Essbase and Its Extended Family

Essbase is a multiplatform OLAP server that runs on Windows NT, Windows 9x, OS/2, AS/400, HP-UX, IBM AIX, and Sun Solaris. Essbase 5 takes advantage of parallel processing and works on symmetric multiprocessing (SMP) systems. You can partition data across servers and operating systems (OSs). You can even nest partitions. Each partition can use Essbase's multidimensional or relational data store; both stores operate with full functionality, including replication, calculation, querying, and navigation.

Arbor offers a full product line of developer and end-user tools to use with Essbase. These tools include:

• Arbor WIRED for OLAP, which provides Windows and Java browser versions of the OLAP client tool. You use WIRED for OLAP much like Visual Basic (VB); you construct a form using components such as the WIRED Designer. The WIRED Analyzer component lets you perform interactive queries and create reports. This tool supports email, export (including slide show screens and annotations), and other capabilities. It also works with MDSS.
• Arbor Essbase API, which has C++ and VB bindings. This API library contains more than 300 functions to create custom OLAP applications.
• Arbor Essbase Objects, which consist of eight OLAP-aware ActiveX controls.
• Arbor Essbase Web Gateway, which provides all essential OLAP features, including drill down, roll up, pivot, and full read and write capabilities from standard Web browsers.
• Arbor Essbase Adjustment Module, which integrates secure, auditable controls for corporate adjustments
  into a comprehensive reporting, analysis, and planning environment.
• Arbor Essbase SQL Interface, which directly accesses more than 20 PC and SQL relational databases for data loading.
• Arbor Essbase SQL Drill-Through, which lets you drill down from Essbase to raw data. SQL Drill-Through supports Open Database Connectivity (ODBC) and includes native drivers for Oracle, Informix, Sybase, Microsoft (SQL Server), and IBM (DB2).
• Arbor Integration Server, which will include OLAP Builder and OLAP Architect tools that let developers create reusable OLAP dimensions, hierarchies, and sophisticated calculation logic that Essbase maintains in a shared OLAP metadata catalog. More than 20 partners will support this forthcoming tool.

The Basics of OLAP Servers
Before you use Essbase or another OLAP server, you need a basic understanding of OLAP terms. Some common terms include dimensions, members, cubes, cells, density, sparsity, and navigational directions.

Dimensions. Dimensions represent the facts you want to analyze. For example, the APB-1 sales and marketing benchmark database consists of six dimensions: Time (86 time periods consisting of 2 years of monthly data), Measure (15 measures representing financial calculations, such as margins), Scenario (16 scenarios), Channel (10 channels), Customer (1000 customers categorized into 3 levels), and Product (10,000 products categorized into 7 levels). Dimensions consist of members.

Members. Members are groupings of data that you use in analyses. You can think of members as by items. For example, you can analyze products by size, by color, by quarter, or by sales campaign. Essbase uses hierarchical terminology (e.g., descendants and ancestors, roots and leaves, and generations and levels) to describe roles and relationships between members.

Cubes. A cube is a block of data that contains three or more dimensions. Multi-dimensional cubes are better suited for complex data analyses than for relational databases because relational databases are limited to two dimensions. (For more information on the differences between two- and multi-dimensional databases, see the sidebar "OLAP, ROLAP, MOLAP, and HOLAP.")

An Essbase database consists of miniature cubes that make up a larger cube, or hypercube. The database also consists of indexes (which keep track of the cubes' locations) and a variety of additional files (such as a database outline that defines the structure of the database, load rules, a security file, log files, and calculation and report scripts).

Cells. If an OLAP cube were a Rubik's Cube, each colored square in the cube would be a cell. Cells can contain data or calculations, orthey can be empty.

Density and sparsity. A cube containing the APB-1 database would have more than 2 trillion possible cells. However, most of the cells would be empty, a concept called data sparsity. When most of the cells contain data, the concept is called data density.

   Prev. page   [1] 2     next page