Severn Trent Water Plc have implemented a Data Warehouse on Oracle Database 10g with OLAP option, Business Intelligence Standard Edition (BI SE) and Warehouse Builder (OWB) to store and provide analysis of 5-6 million daily readings of water monitoring assets. The Data Warehouse stores 5 years of such data which results in a 1.5 Terabyte Data Warehouse. The timely availability of the information generated from these readings provide analysis capabilities for 400 users. Oracle OLAP is implemented for its ease of implementation in the Oracle Database, fast pre-summarisation of multi dimensional data, and complex calculations; speedy retrieval of information allows fast decision making in key areas. The analysis of operational KPIs related to wastage, usage and costs enables low costs to customers, the ability to meet environmental targets, and operational and cost efficiency for Severn Trent Water.

Technical Solution:

David Watts, Information System Project Manager at Severn Trent Water states "Oracle Business Intelligence gives us an end-to-end view of operations ‘from brook to tap and back’ and provides relevant, timely, usable data for performance monitoring and decision making."

Fred Dean, OLAP Consultant at Edenbrook states "Implementing Oracle OLAP as part of the Data Warehouse solution at Severn Trent Water provides fast operational analysis of the company's KPIs; they are also able to forecast on existing trends and seasonality to provide even better cost efficiency and control within company, and environmental targets. Technically, the solution is easy to manage and is very cost effective. Overall it's a simple, cost effective, money saving solution."

About Severn Trent Water:

Severn Trent Plc is the world’s fourth largest privately owned water company and serves eight million households and businesses in an area covering 10,000 square miles. The quality of its drinking and handling of waste water ranks above the industry average. Their customer bills are among the lowest in the United Kingdom. www.stwater.co.uk

See Oracle Snapshot on this implementation here.

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? A: Oracle Database 11g is the recommended version. Oracle Database 11g 11.1.0.7 is now available with a recommended OLAP 'A' patch. Check here for patch numbers and updates to availability as the 11.1.0.7 OLAP 'B' patch is coming out soon.

Q: What is latest version of AWM? A: AWM 11.1.0.7B. It's available here; this download will give you a standalone version and a database client version.

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 OLAP 'A' patch #7195088, or Windows Patchset 9 or above are available too.

Q: What are the query tools that can be used with Oracle OLAP 11g.

A: Any SQL emitting tool such as Oracle BIEE, Business Objects, Crystal Reports, Cognos, Microstrategy applications etc. Note BIEE 11g will have much improved access to Oracle OLAP cubes. Just released an AWM 11g Plug In to set up BIEE repositories for Oracle OLAP access. Details can be found below:

OTN:  Instructions
OTN:  Video demonstration
OTN:  Download the plug-in

Version 10.1.2.3.0.1 of Oracle BI Spreadsheet Add-In for Excel should be used with OLAP 10g. It can can be downloaded from OTN. Note, this version works with MS Office 2007 as well as prior versions. Remember, Excel can access data from OLAP via SQL too.

Excel access to the full multidimensional capabilities of OLAP will be available later this year from Simba Technologies . This product leverages mdx connectivity. Simba will present their prodict in a BIWA Webcast later in June - see details here.

Q: What about migrating from OLAP 10g to 11g? A: AWM 11g allows you to open and create 10g AWs in 10g mode. AWM 11.2 has the ability to allow you to open a 10g AW in 10g mode and export to 11g template. The AW can then be created in 11g mode from the template.

Q: What help can I get for implementing OLAP 11g? A: If upgrading to 11g, or thinking about it and need some guidance, please email orpnews_us@oracle.com.

Refer here for all information on recommended releases and patches.

Read more about Oracle OLAP on OTN .
 

OLAP Skills Corner - Building Cubes in OLAP 11g

So how do you build OLAP cubes? Are there any tips and recommendations for this?

There are three ways to build cubes:

1. Using AWM ( you can build in same session or submit to the Job Queue)

2. Using DBMS_CUBE.BUILD procedure

3. Using Materialized View (MV) refresh system DBMS_MV.REFRESH procedure

1. Using AWM, you are able to click on the AW, cube or dimension and choose which objects to maintain. In the wizard, you can

• select Dimension processing order dimension synchronize (set to yes so members reflect those in source table, set to no where you don't want to delete any values that have been removed) and to sort dim values in order as specified in the Dimension General Page.
• choose a refresh method of 'complete' where all data is cleared first, before reloading and reaggregating, or 'fast solve' where changed recorded are updated and affected partitions are reaggregated (incremental build). Cube scripts that have been defined in AWM can be selected (see below).
• Atomic Refresh (set to true) commits all changes in a single transaction upon completion. If you don't check this, each object will be commited one a time (which may give connecting users inconsistent views of the data).
• choose to refresh 'after errors' or to keep going even after error.
• build in the current AWM session which is fine for a small test schema since it keeps your AWM session locked in a JAVA API session, or you may submit to the Oracle Job Queue and choose one cpu to run the job (a serial job). Choose more than one cpu if your AW is partitioned to take advantage of parallelism where partitions can be processed on separate processes at the same time. It is recommended always to leave one or two cpus free and not use all of them for the OLAP build.
• All the above, i.e. the 'maintenance task' can be saved to a file. This file runs the procedure, DBMS_CUBE.BUILD. The resulting file will look something like this for a cube made up of 4 dimensions:

 DBMS_CUBE.BUILD(
   ' SCHEMA.TIME USING
 (    LOAD NO SYNCH,
   COMPILE SORT  ),
 SCHEMA.CHANNEL USING
 (    LOAD NO SYNCH,
   COMPILE SORT  ),
SCHEMA.PRODUCT USING
 (    LOAD NO SYNCH,
   COMPILE SORT  ),
SCHEMA.MARKET USING
 (    LOAD NO SYNCH,
   COMPILE SORT  ),
 SCHEMA.SALES',
   'CCCCF', -- refresh method ( Specifies a complete refresh for the 4 dimensions and fast incremental refresh for the sales cube)
false, -- refresh after errors (rollback all objects in build if there's a failure)
   4, -- parallelism (number of parallel processes)
true, -- atomic refresh (all updates will be done at the end so that all users sess a consistent view of the data)
true, -- automatic order (dimensioins are loaded first)
false); -- add dimensions (only includes explicitly listed dimensions)

This DBMS_CUBE.BUILD procedure will build the dimensions first using the default build sequence of clearing all existing dimension values, loading new values, and then will load changed rows only and reaggregate. You can specify a different build script to be used, to load and solve only for example; for this, any deleted records in the source may still remain in the AW. Also, in this example, the build will not continue if any object build errors (set refresh after errors false), and no changes at all will be commited if the build does not complete (atomic refresh true).

2. Using DBMS_CUBE.BUILD PL SQL procedure functions (as above). Using the procedure outside of AWM gives you slightly more flexibility since there are additional options available for some parameters that cannot be set in AWM. These options are documented in the OLAP User's Guide.

The procedure options are below:

DBMS_CUBE.BUILD (
script --USING CLAUSE
method --DEFAULT NULL,
refresh_after_errors --DEFAULT FALSE,
parallelism --DEFAULT 0,
atomic_refresh --DEFAULT FALSE,
automatic_order --DEFAULT TRUE,
add_dimensions --DEFAULT TRUE,
scheduler_job --DEFAULT NULL);

At its simplest level you can simply state to:

exec dbms_cube.build ('sales_cube').

You can specify a different load script to the procedures clear, load and solve script. and specify its use by executing

exec dbms_cube.build ('sales_cube USING my_build_spec') or

exec dbms_cube.build ('sales_cube USING (CLEAR, LOAD)')

3. The third method of using the MV refresh system DBMS_MVIEW.REFRESH should be used for refreshing cubes MVs. If you want to take advantage of the MV refresh system for building cubes that meet MV criteria, you must specifically build the dimensions before building the cube so that all dimension values are in synch.

An example is:

SQL> execute dbms_mview.refresh('CB$TIME_CALENDAR','C');

You must perform refreshes for all dimension MVs (dimension hierarchy combination MVs).

SQL> execute dbms_mview.refresh('CB$SALES_CUBE','C');

This will refresh the cube and the MV.

Refer here for how to set up MV refresh in AWM.

How can I monitor the success of the build ?

AWM will alert a successful (or failed) completion, if running in same session; logs are available, which will be much improved and also displayed nicely in 11.2. The Job Scheduler will tell you about successful completion of jobs; reports can be run in AWM, and running script utlolaplog.sql will create views for your manipulation. Refer to the OLAP User's Guide for more information.

back to the top