Additional Calculations Exposed Through Automatic Views

Advertisements

Additional Calculations Exposed Through Automatic Views

You can use cubes in Oracle OLAP 11g as a summary management solution, but Oracle OLAP 11g really shines when you use it to perform calculations. Oracle OLAP can perform many calculations that can be difficult to express in SQL and time-consuming to perform with the standard relational engine.

Year-to-date and share calculations are good examples of calculations that perform better with Oracle OLAP. Once defined in the analytic workspace as calculated measures, these measures are exposed as extra columns in a view that is automatically created for a cube. You can then query these measures just as you would query any base measure from the cube view.

When working with cube views, bear in mind that the view contains preaggregated data at multiple levels. As a result, you do not need to include any aggregation functions such as SUM, and you generally do not need GROUP BY clauses. But you must also specify the level of data you want to select via the appropriate WHERE clause.

Enter the query in Listing 1 into Oracle SQL Developer (or your SQL tool of choice) to look at quarterly sales and see how those sales compare on a year-to-date basis with the same periods last year. The query also shows how to return multiple levels of the time dimension in one query.

Code Listing 1: Quarterly sales and comparison query

SELECT t.long_description time,            p.long_description product,            cu.long_description region,            ch.long_description channel,            ROUND(u.sales) SALES,            ROUND(u.sales_ytd) YTD,            ROUND(u.sales_ytd_dif_yrago) DIFF,            ROUND(u.sales_ytd_pct_dif_yrago, 6) PCT  FROM time_view t, product_view p, customer_view cu, channel_view ch, units_cube_view u  WHERE t.dim_key = u.time     AND p.dim_key = u.product     AND cu.dim_key = u.customer     AND ch.dim_key = u.channel     AND (t.level_name IN('CALENDAR_YEAR', 'CALENDAR_QUARTER')        OR t.time_calendar_quarter_id = 'CY1999.Q1')     AND p.level_name IN('CLASS', 'TOTAL')     AND cu.level_name = 'TOTAL'     AND ch.level_name = 'TOTAL'     AND p.dim_key = 'CLASS_SFT'  ORDER BY t.end_date, t.time_span;    Executing the query in Oracle SQL Developer returns the results shown in Figure 2. Note how rows  are returned for years, quarters, and three specified months�all in one view. 

 

Figure 2: Quarterly and comparison results

 

This example shows only the most-basic calculations Oracle OLAP 11g can perform. A new calculated measure wizard in the analytic workspace manager enables you to calculate shares, indexes, ranks, moving aggregates, cumulative aggregates, and user-defined expressions. You can even calculate models, forecasts, and regressions.

Refreshing Cubes, Using Materialized Views

As fact tables acquire new data, Oracle Database must maintain cubes and recalculate totals. You can simplify this procedure by taking advantage of the materialized view refresh capability in Oracle Database. By checking the Enable Materialized View Refresh of the Cube check box (shown in Figure 1), you can specify the refresh of an analytic workspace to be the same as the refresh of any other materialized view. For example, to refresh the GLOBAL analytic workspace, use the following command:

dbms_mview.refresh('CB$UNIT_CUBE', 'F')

This command reloads the analytic workspace from the fact table and recalculates any aggregates that need updating. The mechanisms and syntax are exactly the same as with noncube materialized views, with methods for controlling aspects such as staleness and schedules. The materialized view logs identify which portions of the cube need reloading. Oracle OLAP then uses this information to reload and recalculate the appropriate portions of the cube.

Aggregation

Oracle OLAP always returns data as fully solved, but you have control over the extent to which the data is preaggregated. In Oracle OLAP 10g, you could specify which levels of the cube to preaggregate. That capability is still supported in Oracle OLAP 11g, but now you can also specify the percentage of the cube you want to preaggregate. Oracle OLAP will then figure out which areas of the cube to preaggregate and which areas to aggregate on the fly. Oracle OLAP figures out how to balance runtime performance against load performance and the time needed to calculate certain aggregates. Figure 3 shows a cube creation dialog box that specifies 20 percent preaggregation.

 

Figure 3: Cube creation dialog box specifying 20 percent preaggregation

 

Presenting the Data

As with previous versions of Oracle OLAP, you can access Analytic Workspace cubes via the Java OLAP API. However, Oracle has positioned SQL as the language of choice for accessing data in Oracle OLAP cubes. With views automatically generated that return cube data via standard SQL, any application environment with a SQL interface can access data from cubes. As a result, Oracle OLAP data can be accessed by Oracle applications such as Oracle Business Intelligence Suite Enterprise Edition Plus, Oracle Discoverer, and Oracle Application Express. In addition, any other application development tool that uses SQL can also access Oracle OLAP data. As an example, Figure 4 shows an Oracle Business Intelligence Suite Enterprise Edition report running against the same Oracle OLAP cube data demonstrated in this article. Oracle OLAP provides all of the totals and computes the various calculations being displayed.

 

Figure 4: Oracle Business Intelligence Suite Enterprise Edition accessing Oracle OLAP cube data

Other New Features

Other new features of Oracle OLAP 11g make it easier to work with analytic workspaces and further integrate OLAP into the database engine. There are new storage and partitioning advisors that simplify the process of designing cubes for optimal storage and maintenance. Furthermore, new data security policies make it easier to grant access to different portions of cubes to various users. A detailed discussion of these capabilities is beyond the scope of this article. For more information, see the Oracle OLAP 11g Release 1 (11.1) User's Guide.

Conclusion

With the enhanced capabilities in Oracle OLAP 11g, it is much easier to incorporate OLAP into the framework of a data warehouse. Key improvements such as materialized views, automatic view creation, and aggregation wizards make Oracle OLAP a compelling choice for accelerating query performance.