BusinessObjects and Oracle9i OLAP

Introduction

The release of Oracle9i AW (Analytical Workspace) makes it possible for the Business Objects semantic layer to integrate closely with Oracle OLAP technology. This is because AW allows Oracle OLAP data to be queried using standard SQL. Business Objects’ patented semantic layer, which enables users to create complex SQL queries using visual objects, is already the industry-leading SQL generation technology. As a result, Business Objects users can benefit from the performance and calculation power of Oracle OLAP without leaving the familiar BusinessObjects query-building environment.

Oracle relational views also make it possible for Business Objects to exploit several trends in the OLAP market place. OLAP is moving away from pure multidimensional databases, and hybrid OLAP/relational solutions are becoming much more common. A Business Objects solution can hold both multidimensional and relational data in a manner that is completely hidden from the end user. Furthermore, the focus is moving away from proprietary OLAP APIs and query languages towards languages that are already industry standards. SQL is a prime example of this—with a Business Objects/Oracle solution you can query your multidimensional data using the most common database query language.

Advantages

Accessing Oracle cubes through the Business Object semantic layer has three big advantages:

• Performance. For common-size data sets, OLAP cubes provide better performance because they contain aggregated, pre-computed data that is instantly available to the query tool;

• Advanced OLAP calculations (see Using Oracle OLAP functions). The Oracle OLAP cube provides pre-calculated data and also allows users to compute advanced calculations such as growth ratios or trends on the fly;

• Transparent drill through from the cube to relational data (see Drilling outside the cube). With this solution, a single universe can encompass a cube and relational tables. This means that ausers can drill from aggregates to details within the same query-building environment.

Oracle9i and OLAP cubes

Oracle9i AW exposes Oracle OLAP cubes as relational views, which can be queried using standard SQL. Oracle exposes dimensions and rollups in a relational view. For BusinessObjects users to be able to profit from this capability, the BusinessObjects administrator must design a BusinessObjects universe (which transforms a visual query to SQL) around the cube view. The universe must handle rollups correctly, which imposes a non-standard approach to universe design. The next sections discuss the way in which the universe must be designed.

Universe design principles

BusinessObjects and Oracle 9i OLAP 4 of 15

In the example used throughout this paper, the view, OLAPCUBE, is derived from an Oracle cube using an AW query. The relational view contains Revenue (the measure) and two hierarchies—Time (Year, Quarter, Month) and Geography (Country, Region, City). The revenue in the Revenue column is aggregated according to the level, which means that any SQL statement returning data from the view must filter according to the values in the time_level and geo_level columns:

YEAR QTR TIME_LEVEL COUNTRY REGION GEO_LEVEL REVENUE

ALL ALL 5000

2002 YEAR ALL 2000

2003 YEAR ALL 3000

2002 Q1 QTR ALL 500

2002 Q2 QTR ALL 500

The problem that needs to be overcome occurs because the cube contains rolled-up data, and BusinessObjects generates SQL per object, whereas to handle rollups it needs to generate per object combination. For example, if you create a Year object (based on the year column in the table) and place it in a query, BusinessObjects builds a WHERE clause that restricts time_level to ‘YEAR’ (WHERE time_level = ‘YEAR’). But if you include the Year and Quarter objects, you want BusinessObjects to restrict time_level to ‘QUARTER’ (WHERE time_level = ‘QTR’). BusinessObjects default behaviour is to create a WHERE clause that returns no rows (WHERE time_level = ‘YEAR’ AND time_level = ‘QTR’).

To solve this you treat the cube view as the fact table in a snowflake schema, and surround it with dimension tables. You are not interested in the data in these dimension tables: they exist solely to force BusinessObjects to generate the correct SQL to filter the OLAPCUBE table.

The following sections:

• describe how to set up the universe;

• give examples of the SQL that the universe generates;

• explain how this SQL relates to the universe design.

You carry out the following tasks when designing the universe:

Task	Purpose
Include the relational view in the universe.	You access the OLAP cube data through the relational view.
Create tables hierarchies that correspond to the dimensional hierarchies in the cube. These hierarchies are build from Designer aliases on the SYS.DUAL table.	The dimension tables are used to generate the join conditions that restrict data in the relational view.
Join the dimension tables hierarchically using regular joins.	The joins between the dimension tables are used to generate the join conditions that restrict data in the relational view.
Join the dimension tables to the relational view using shortcut joins.	The shortcut joins are used to generate the join conditions that restrict data in the relational view.
Create dimension and measure objects	The user builds queries in the Query Panel using these objects