The success of an OLAP implementation can be measured in many ways, since customers have many different goals, but there are a few general questions that many implementers ask to ensure success, such as: Does the implementation scale for a lot of data? Does the implementation scale for a lot of users? Does it perform well?

The Oracle OLAP option is unique in the OLAP space since it is part of the Oracle Database and thus, adopts characteristics of the Database. Analytic Workspaces (AWs), stored in tables, can be large and administered as part of the Database. E.ON, for example store over 1 Terabyte of data in analytic workspaces, and update around 1 million rows of data per minute; See Newsletter article. E.ON commented that "the combined analytical power, centralized administration and scalability of Oracle OLAP in the Oracle database has allowed us to process and present data in a way that was not previously possible".

An even bigger OLAP implementation, at one of the world's largest telecom companies, manage over 4 Terabytes of data in OLAP AWs. This advanced implementation has many different AWs and cubes which are updated daily in parallel. Many millions of rows of data are loaded and aggregated to these multiple AWs and cubes. Hundreds of users from different lines of business, from many locations, access the system and use Discoverer OLAP.

Circle K Sunkus, a convenience store chain in Japan, update millions of data points daily; performance for their Discoverer reports is about 8 times faster than their previous relational implementation e.g. reports that took minutes now take seconds.See Newsletter article.

BNP Paribas support over 400 users and over 20 million data requests are processed per day. There are billions of rows of data in AW management. they state they implemented Oracle OLAP for a number of reasons including ..."'the advantages of Oracle 10g's scalability .... made it the natural choice.." See here for further comment.

The Gallup Organization needed a solution that would allow them "to achieve 1000 concurrent users across the globe". They currently have a few hundred concurrent users for one of their applications which displays graphs and thumbnails to the Web, in seconds, using SQL access to the cubes. They have just completed another OLAP system into production. See Newsletter article.

So, to answer those common questions that you may have on OLAP scalability and performance, the answer is yes - Oracle OLAP scales and performs well, and it has been proven by many implementations.

View more Oracle OLAP Customer and Partner references here.

back to the top

Oracle OLAP Q&A

Q: What is the recommended version of Oracle OLAP if using Oracle Database 10g? A: 10.2.0.4 is now available for as patch 6810189 for most platforms, and should be used if you are a new customer. The 10.2.0.4 A patch will be available for Linux 32, Solaris and AIX 5L. Check MetaLink for patch availability. If you have an existing implementation and have specific 10.2.0.3 patches, check here for further information.

Refer here for more information on recommended releases.

Q: What about using Oracle Database 11g? A: Oracle Database 11g (11.1.0.6) is available for most platforms on OTN. The required OLAP 'A' patch is available on MetaLink as Patch #6459753 for all platforms, and the OLAP 'B' patch is available for Solaris, Linux 64 and AIX 5L. If available, the 'B' patch (which includes the A patch) should be used. AWM 11g version (11.1.0.6A) for Windows is available on OTN and MetaLink.

Q: What is the next version of Oracle Database 11g? A: 11.1.0.7 is the next version of 11g which will be available later this year.

Q: What about migrating from OLAP 10g to 11g? A: Until migration tools become available, analytic workspaces created in 10g or before will need to recreated in 11g to make use of 11g features. 10g AWs can still be used in 11g (only uses 10g features). See here.

Q: What help can I get for implementing OLAP 11g? A: The Oracle OLAP Development group is offering some technical help and advice to a small number of committed customers, as part of an OLAP 11g Reference Program initiative. If interested please send an email to orpnews_us@oracle.com. We'll contact you to find out your plans and if we can help each other.

Q: What versions of BI Standard Edition should I use with Oracle Database and OLAP? A: BI Standard Edition 10.1.2.2 (including BI Beans, Discoverer OLAP and Excel Add In) is supported and recommended to be used with with Oracle Database 10.2.0.4 (or 10.2.0.3 with OLAP A patch). Additionally,

• Spreadsheet Add In is certified to work with Excel 2000, 2003 or Excel XP with Oracle 10.2.0.3A or 10.2.0.4.
• BI Beans is also certified with 10.2.0.3A and 10.2.0.4. For 11g access, the "New Beans" - now called ADF Visualization Components which are available in JDev 11g, access data using regular SQL.  They access the Database OLAP Option using SQL to the cube views. There is no longer a dependency on the OLAP API or any additional relational OLAP Catalog metadata. 

Read more about Oracle OLAP on OTN .
 

AWM 11g is packed with great features; many new to AWM completely. Here's an overview:

1. Materialized Views: The ability to create Materialized Views (MVs) from OLAP data and access OLAP data 'transparently' in an application is groundbreaking. One can use AWM to define an MV over a cube or a dimension; there they can be refreshed and enabled. That's all that needs to be done to allow any SQL based tool to leverage Oracle OLAP data; i.e. the query tool does not need any special set up (Database option Query Rewrite must be turned on). Fewer cubes than traditional MVs need to be created and materialized to give unparalleled simple and transparent access to aggregate data.

R.L. Polk & Co., a market research firm for the automotive industry, has worked with Oracle 11g: they state that the use of Oracle OLAP 11g is "a big development for us because we no longer have to try to anticipate every possible way our customers might want to view each set of dimensions. Each OLAP cube calculates every possible combination." See Oracle Magazine July cover feature for more information on RL Polk's use of Oracle OLAP 11g.

2. Dynamic Cube Views: AWM 11g dynamically creates 'cube views'. These views are created for you in the form required for simple SQL access to the cube which basically makes access to multi dimensional data simple and open! Maintenance is easy since views will refreshed when the cube is refreshed: any changes made to your model will always be reflected in the views automatically. See blogspot tip on this for some great information.

3. AWM Advisors: Modeling your cube correctly is extremely important for performance. AWM 11g makes this easier for you through the use of advisors:

• The Cube Storage Advisor in 11g is available on the cube storage tab. Click the 'Cube Storage Advisor' button and it will give you a recommended way of defining the cube (i.e. what dimensions should be inside a composite, whether to use compression. The way a cube is defined and stored is important for aggregation and data retrieval performance.
• The Partitioning Advisor will select the levels at which it thinks you should partition the cube. Partitioning allows the maintenance of your cube to be run in parallel: multiple cpus can be used to run jobs, and updates to your cube can be done in parallel. Less space is used to maintain the smaller partitions, and at query time, smaller partitions can be processed. Partitioning can be based on a TIME dimension level or any other dimension level. TIME is usually the dimension to partition by, since these partitions are the most likely to be of equal size; it makes performance and manageability more predictable, and you can roll off unused historical data easily. If life-cycle management is not a primary consideration, and/or you update incrementally, so that you won't make best use of TIME partitioning, then run the Cube Partitioning Advisor and choose the Statistics option which may give a partitioning strategy based on another dimension. If the relational source table is relationally partitioned, that partitioning strategy will also be chosen in AWM.

If you accept the recommendations from an advisor and you have a cube already created, it will delete the cube and recreate. You will need to maintain the cube if you had any data loaded. (i.e. load and aggregate the data again).

4. Preaggregation: Preaggregation of data in OLAP allows better performance at query time. Level Based Aggregation and new Cost Based Aggregation is available. Using cost based, you can set a percentage of data to be precomputed, rather than selecting specific levels. AWM will work out what to aggregate; you select a percentage based on how much time your build window has. The default aggregation is set to 20% of the lowest level of data to be precomputed. You can adjust this and also add top levels of data to be precomputed. Of course, since its aggregating, build time is increased by the amount you choose to precompute, but query time will be better the more that is aggregated. Cost based aggregation is especially useful for value based models!

5. Security: Object and data security can be implemented in AWM; roles and users can be assigned access to objects via AWM 'Data Security Roles'. Additionally, you can assign which cells of data a user may have access to. This is done by creating an object security on the dimension of choice, and then editing this data security object to choose which members to allow access to.

6. Calculation templates: There are a number of templates available in AWM which define calculations. There is also a free form calculation window which allows you to create your own calculations. Some details on this are in April's Newsletter.

back to the top