Oss Council in the Netherlands have implemented an OracleBI solution to support planning, control and management reporting within the municipal councils and water board districts. Navigus B.V. Consulting, and Oracle helped to implement the solution for the council which now has management reports for over 60 users within a week, instead of taking 5-6 weeks to create!

Technical Solution:

They run Oracle Database 10g with OLAP option. They also use OWB, AWM, and Oracle Fusion Middleware. They report OLAP data using Oracle OLAP Web Agent, and use Application Express to input data.

Data comes from many sources in this solution; OWB performs the ETL, feeding OLAP which provides a single view of the truth for consistent management reporting. A clean single source of data being more quickly available makes reporting to upper management more streamlined, and thus the control of the municipality becomes much easier and much more efficient. There are over 20 dimensions in the AW, with approximately 5 to a cube. The data is updated frequently (every 5 minutes) from about 60 users who use the system.

"‘Navigus Gemeenten’ allows us to connect applications to each other so that management can guide us better towards achieving the results we want," – Carel Smit, Group Personnel at Oss Council says.

Read further information on the solution from the Snapshot here (see here for Dutch version).

About Oss Council:

Oss municipality is the main industrial zone in Brabant province in the Netherlands. The council comprises public affairs, municipal policy and public works. www.oss.nl

About Navigus BV:

Navigus is a supplier and implementor of planning software. www.navigus.nl

View more Oracle OLAP Customer and Partner references here.

back to the top

Oracle OLAP Q&A

Q: What is the recommended version if using Oracle Database 11g? A: Oracle Database 11g 11.1.0.7 is now available Linux 32 and 64 on MetaLink as patch 6890831. Windows, Solaris, HPI and AIX will be released shortly.

If 11.1.0.7 is not available for your platform, 11.1.0.6 is available for most platforms on OTN. The required OLAP 'B' patch is available as Patch #6992921 for Solaris, Linux 32, Linux 64, HP-UX, HPI and AIX 5L. The OLAP 'A' patch is available on MetaLink as Patch #6459753 for Windows 32. AWM 11g version (11.1.0.6A) for Windows is available on OTN and MetaLink (patch 6368282). AWM 11.1.0.7A for Windows should be used as with 11.1.0.7 and will be available on OTN and MetaLink as patch 7420490 .

Q: What is new about AWM 11.1.0.7A? A: AWM 11.1.0.7A contains improvements and features such as user defined calculation templates and user defined reports capabilities.

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 #7195088 is available for Linux 32, Linux 64, Solaris 64, and AIX 5L on MetaLink; Windows patchset 9 should be used for all OLAP fixes. If you have an existing implementation and have specific one off 10.2.0.3 patches, check here for further information.Windows Patchset 27 is available for Windows platforms for 10.2.0.3 and contains OLAP fixes.

Refer here for more information on recommended releases.

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. 

Q: What other Oracle query tools can I use with Oracle OLAP 11g? A: You can use any SQL emitting tool to access OLAP data since you are simply selecting from relational views. OBI EE and Application Express (APEX) are common Oracle query tools being used.

Read more about Oracle OLAP on OTN .
 

OLAP Skills Corner - Why Summary Management and Cube Materialized Views?

Keeping your data pre-summarized quite simply improves run time query performance since you aggregate data during a build and store summary data which does not need to be aggregated at query time. One of the major benefits of using Materialized Views (MVs) to store summary data is the query rewrite capability which makes the use of presummarized data completely transparent to the end user query/ application. Query Rewrite is a query optimization technique that transforms a user query written in terms of tables and views, to execute faster by fetching data from MVs. It requires no intervention or hints in the SQL application because the Oracle Database will automatically rewrite any appropriate SQL application to use the MVs even if the MV can only satisfy part of the query. Cube MVs work in the same way.

The difference with cube organized MVs is that the data is stored as a cube in an Analytic Workspace rather than as a standard relational table. The cube MV inherits all the query and refresh performance benefits of the cube. The cube inherits the refresh maintenance benefits of MVs. There is one MV per cube, so instead of many MVS for level combinations being stored, there's only one MV making the MVs much more manageable. It improves the performance of the optimizer since there are less MVs for the Optmizer to consider for use; its also fast to locate the presummarized data in the array based storage of the cube.

Query and Refresh capabilities:

Cube MV refresh is the same as refreshing the cube (data in the analytic workspace), with the added benefits that refreshing the AW inherits the characteristics of the MV refresh. Cube MV refresh can be done via AWM using the maintenance wizard, the automatic refresh in the MV tab, dbms_cube.build or using dbms_mview procedures which are specifically for MV maintenance. MV refresh has following refresh types:

1. Complete - deletes all existing data, reloads and aggregates

2. Fast - uses the MV log system to identify which records have been changed, and loads and agggregates those records.

3. Force - will use complete if fast unavailable

4. Partition Change Tracking: Clears, loads, and aggregates only the values from an altered partition in the source tables. Should be useful if you're only updating a partition of data and not using query rewrite (i.e. you're just using MV refresh system to keep data automatically refreshed in the cubes)

5. Fast Solve: Loads all the detail data from the source tables, then aggregates only the new values. Compressed cubes and cube materialized views only.

Refer to OLAP User's Guide 11g Part Number B28124-02 for further information.

Even if you don't want to use MVs and Query Rewrite and your model has the characterisitcs to allow MVs to be built (ie specific aggregation types and no calculations), you may take advantage of the refresh features of MVs to maintain your cube. Having the different refresh capabilities means full and incremental maintenance can be efficiently managed by the Database.

back to the top