Oracle OLAP option

Question

I'm considering giving a try to the Oracle OLAP option. Can anyone share their experiences with it? I didn't find much discussion/blog activity on the subject. In particular, I'm interested in reporting on multidimensional data (hundreds of columns) across different time periods AND with filters. So my worry is whether whatever pre-aggregations that OLAP cube performs under the hood would still give me considerable performance boost in presence of sophisticated filtering?
Here's an example of the query I'm talking about:

give me average value for column A aggregated by months for the past 3 years, for all people in the table who have value in column B in the range [1,n] OR value in column C in range [1,m] in the past 3 months.

Is OLAP really able to pre-aggregate complicated queries like that?

Also what kind of performance does it offer? Are there interesting benchmarks available?

Answer 1

Oracle OLAP option works really well. It is truly a hidden jewel in the database, which not many people realize should be part of every Oracle-database-based BI and DW solution. This is not a new server. It has been around since early 1970s, so it is even older than Oracle database. Oracle bought it in 1995 and "embedded" it inside the database starting with version 9.2 of database.

The latest version is 11.2.0.2 (which I will highly recommend). The older version had some issues.

Some useful links are here

Some additional comments about using Oracle OLAP option in a BI/DW environment.

(1). All data/calculations reside inside Oracle database.

(2). Any calculation (or calculated measures) no matter how complex can be created inside the cubes with simple analytical syntax which automatically works at all levels of all dimensions.

(3). Dimensional calculations (or calculated members) can be either done by OBIEE's Calculated-Item functionality or "pushed" inside the database where more complex dimensional calculations can be handled very easily.

(4). Any types of hierarchies (level-based or parent-child) can be handled.

(5). Data is retrieved through SQL queries using OLAP_TABLE function or CUBE_TABLE function.

(6). Provide faster query response times for any adhoc report.

(7). If you are using Oracle's OBIEE (11.1.1.5 or future releases) then the generated queries are simple SELECT...FROM..WHERE kind of queries. Even with other reporting tools, the queries are still simple queries as there is not much to do on "relational" side.

(8). Depending on requirements, the following functionality can be easily provided: drill-thru to detail (from aggregated cubes to relational transactions) OR combining relational and cube data in the same reports.

(9). Uses Oracle database resources, hence scalable for faster cube builds as well as handling hundreds or thousands of users.

(10). Very extensive logging features are provided out of the box (for Oracle DBAs) to monitor the queries as well as cube builds. This is in addition to regular features that DBA use (like ASH, AWR, OEM etc.).

(11). No additional hardware or DBA required (as opposed to other OLAP servers).

(12). One cube can replace dozens and dozens of MVs and summary tables. Inside the cube, the data exists at all levels of all dimensions.

Answer 2

I was a bit underwhelmed with Oracle OLAP when I evaluated it circa 2005, mainly as it had poor support from front-end tools at the time (Discoverer 'Drake' had no drill-through support, and there was practically no support from third party tools). In the end that project went with MS Analysis services.

@Ali's post suggests that it does have support from OBIEE now, so if you have licenses for that you can probably put a reasonably frendly front-end on the cube. While somewhat exuberant, the points he makes are fundamentally sound.

In answer to your question:

• OLAP servers can have calculated measures defined, which include ratios and smart rollups such as running sums, YTDs and rolling windows. Ratio calculations work by aggregating the values for the numerator and denominator and calculating the ratio based on the aggregates for whatever slice you're looking at. Embedded calculations are one of the unique selling points of OLAP as a technology.
  
  You're trying to do something roughly equivalent to a WHERE EXISTS in an OLAP server. This is somewhat tricky as the paradigm is slightly different. In practice, most OLAP query lanagages have a lot of operators for manipulating sets. You can do something that calculates the set of people matching your criteria and then use it on one of the axes of your query. Chances are you won't be able to do it through a point-and-click front end on an end-user tool without having to write a query or do some intermediate stage that involves manipulating the set.
  
  End-user tools make slice-and-dice query operations easy but don't directly support more complex queries, although many will allow you to enter a hand-written query.

• OLAP servers do calculate and persist pre-aggregated rollups of the data. If a query can be satisfied from one of the aggregates then the server will use it instead of the base data. This use of pre-aggregated rollups is one of the main reasons behind the fast query peformance of OLAP servers.

• If your aggregates on the cube are tuned correctly it should be able to answer most queries very quickly. Tuning aggregates on OLAP servers is a bit of an art form, though. Because the performance is critically dependent on your aggregates benchmarks for query performance will be somewhat meaningless.

Answer 3

@MK01... Your scenario is absolutely perfect use-case to create Oracle OLAP cubes to handle adhoc reporting. All Oracle BI/DW systems should have Oracle OLAP cubes as part of design (or as part of DW aggregation strategy). My previous post had some useful links, please go through those links. You can post questions at Oracle OLAP Forum also @ https://forums.oracle.com/forums/forum.jspa?forumID=16

The refresh of cubes is much much faster now. You can even load/refresh Oracle OLAP cubes (starting with version 11.2.0.2) multiple times a day very quickly - even when users are doing querying from cubes. There is no downtime.

@ConcernedOfTunbridgeWells.....The tuning of cube has become much much simpler in 11.2.0.2 version of Oracle database. With Compressed cubes and cost-based aggregation, we can now just set all dimensions as 'Sparse' dimensions and just set the precompute %age to 35% and then the olap engine does its magic. Another great query performance improvement is the automatic 'LOOP OPTMIZATION' technique where it now only loops over only those combinations of dimensions for which there is data. This is all internal.

Creating Calculated-Measures is easy now. You don't need OLAP dml language. Instead you use the SQL Analytical Syntax to create OLAP calculated measures.

In short, Oracle OLAP cubes are very very efficient now - both when it comes to loading as well as querying in 11.2.0.2 version compared to 10gOLAP or even 11.1 version of database.

The keyword (that I keeps repeating over and over) in my olap discussions is "SIMPLICITY".

.