Oracle OLAP

Script for Time Dimension Table

2011-01-17T07:06:00.000-08:00

One of the more common requests I get is a script for creating time dimension tables for Oracle OLAP. The following script will create a time dimension table for a standard calendar. It starts by creating a table with dimension members and labels. The second part of the script fills in end date and time span attributes. The section that creates end date and time span can be easily adapted for completing other calendars (e.g., fiscal) where the members have already been filled in.

--
-- Create time dimension table for a standard calendar year (day, month,
-- quarter and year).
--
-- Drop table.
--
--DROP TABLE time_calendar_dim;
--
-- Create time dimension table for calendar year.
--
-- First day if the next day after TO_DATE('31/12/2010','DD/MM/YYYY').
-- Number of days is set in CONNECT BY level <= 365.
--
-- Values for end date and time span attributes are place holders. They need
-- to be filled in correctly later in this script.
--
CREATE TABLE time_calendar_dim AS
SELECT CurrDate AS Day_ID,
1 AS Day_Time_Span,
CurrDate AS Day_End_Date,
TO_CHAR(CurrDate,'Day') AS Week_Day_Full,
TO_CHAR(CurrDate,'DY') AS Week_Day_Short,
TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'D'))) AS Day_Num_of_Week,
TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'DD'))) AS Day_Num_of_Month,
TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'DDD'))) AS Day_Num_of_Year,
UPPER(TO_CHAR(CurrDate,'Mon') || '-' || TO_CHAR(CurrDate,'YYYY')) AS Month_ID,
31 AS Month_Time_Span,
to_date('31-JAN-2010','DD-MON-YYYY') AS Month_End_Date,
TO_CHAR(CurrDate,'Mon') || ' ' || TO_CHAR(CurrDate,'YYYY') AS Month_Short_Desc,
RTRIM(TO_CHAR(CurrDate,'Month')) || ' ' || TO_CHAR(CurrDate,'YYYY') AS Month_Long_Desc,
TO_CHAR(CurrDate,'Mon') AS Month_Short,
TO_CHAR(CurrDate,'Month') AS Month_Long,
TO_NUMBER(TRIM(leading '0'FROM TO_CHAR(CurrDate,'MM'))) AS Month_Num_of_Year,
'Q' || UPPER(TO_CHAR(CurrDate,'Q') || '-' || TO_CHAR(CurrDate,'YYYY')) AS Quarter_ID,
31 AS Quarter_Time_Span,
to_date('31-JAN-2010','DD-MON-YYYY') AS Quarter_End_Date,
TO_NUMBER(TO_CHAR(CurrDate,'Q')) AS Quarter_Num_of_Year,
TO_CHAR(CurrDate,'YYYY') AS Year_ID,
31 AS Year_Time_Span,
to_date('31-JAN-2010','DD-MON-YYYY') AS Year_End_Date
FROM
(SELECT level n,
-- Calendar starts at the day after this date.
TO_DATE('31/12/2010','DD/MM/YYYY') + NUMTODSINTERVAL(level,'day') CurrDate
FROM dual
-- Change for the number of days to be added to the table.
CONNECT BY level <= 365);
--
-- Month end date and time span attributes.
--
UPDATE time_calendar_dim a
SET month_time_span =
(SELECT ts
FROM
(SELECT DISTINCT month_id,
SUM(day_time_span) AS ts
FROM time_calendar_dim
GROUP BY month_id
) b
WHERE a.month_id = b.month_id
);
--
UPDATE time_calendar_dim a
SET month_end_date =
(SELECT ed
FROM
(SELECT DISTINCT month_id,
MAX(day_id) AS ed
FROM time_calendar_dim
GROUP BY month_id
) b
WHERE a.month_id = b.month_id
);
--
-- Quarter end date and time span attributes.
--
UPDATE time_calendar_dim a
SET quarter_time_span =
(SELECT ts
FROM
(SELECT DISTINCT quarter_id,
SUM(day_time_span) AS ts
FROM time_calendar_dim
GROUP BY quarter_id
) b
WHERE a.quarter_id = b.quarter_id
);
--
UPDATE time_calendar_dim a
SET quarter_end_date =
(SELECT ed
FROM
(SELECT DISTINCT quarter_id,
MAX(day_id) AS ed
FROM time_calendar_dim
GROUP BY quarter_id
) b
WHERE a.quarter_id = b.quarter_id
);
--
-- Year end date and time span attributes.
--
UPDATE time_calendar_dim a
SET year_time_span =
(SELECT ts
FROM
(SELECT DISTINCT year_id,
SUM(day_time_span) AS ts
FROM time_calendar_dim
GROUP BY year_id
) b
WHERE a.year_id = b.year_id
);
--
UPDATE time_calendar_dim a
SET year_end_date =
(SELECT ed
FROM
(SELECT DISTINCT year_id,
MAX(day_id) AS ed
FROM time_calendar_dim
GROUP BY year_id
) b
WHERE a.year_id = b.year_id
);
--
COMMIT;

Simba previews Cognos8 Analysis Studio accessing Oracle Database OLAP Option cubes

2010-12-02T04:26:00.000-08:00

Hot on the heels of support for BusinessObjects Voyager, and in addition to the native Excel 2003/2007/2010 pivot table access, Simba are previewing the same connectivity for Cognos8 Analysis Studio - the dimensionally aware UI in the Cognos BI suite.

Together with the unique SQL access to the same multidimensional data & calculations in Oracle Database OLAP cubes (meaning that *any* tool or application capable of connecting to Oracle and issuing simple SQL can leverage the power of Database OLAP - like Oracle Application Express for example), plus the existing support for Oracle's own BI tools including

Oracle BIEE 10g and 11g (see http://oracleolap.blogspot.com/2010/07/first-look-at-obiee-11g-with-oracle.html ) and
Oracle BI Discoverer Plus OLAP (see http://oracleolap.blogspot.com/2010/08/discoverer-olap-is-certified-with-olap.html ),

together with the big functionality and performance improvements in 11g , there is now every reason to move to Oracle Database 11gR2 and to fully exploit the OLAP Option - whatever your choice of front end tool(s).

For Cognos fans: Here is the Video on YouTube:

&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;/param&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;param name="allowFullScreen" value="true" frameborder="0"&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;

More information, see the Simba website : http://www.simba.com/MDX-Provider-for-Oracle-OLAP.htm

Simba previews Oracle OLAP MDX Provider connectivity to SAP BusinessObjects Voyager

2010-11-16T13:39:00.000-08:00

Simba technologies have released a short video to preview 'Using MDX Provider for Oracle OLAP to directly connect SAP BusinessObjects Voyager to Oracle Database OLAP Option'

This will be a great capability for users of both Oracle OLAP and BusinessObjects and will futher extend the reach of Oracle database embedded OLAP cubes.

You can get more details on the Simba website

Microsoft Certifies Simba’s MDX Provider for Oracle OLAP as “Compatible with Windows 7”

2010-11-04T13:30:00.000-07:00

Simba announced today that Microsoft has certified its MDX Provider for Oracle Database OLAP Option for Windows 7.

The press release is here : http://www.simba.com/news/MDX-Provider-for-Oracle-OLAP-Certified-for-Windows-7.htm

This is great news for MS Office connectivity to your Database OLAP cubes. Already supported was Excel 2010 with its cool new BI features, as well as 2007 and 2003 versions. With Windows 7 support too, even the most up-to-date PCs are in good shape to exploit the OLAP Option.

Remember - via the native Excel pivot table connectivity, other features of MS-Office can also leverage the power, performance and calculation functionality of Oracle OLAP. So for example, your Powerpoint presentation to the senior management team, or the one you use when talking to suppliers or key customers can now contain live charts onto the latest information available in the Oracle Database cubes. Instead of spending time cutting and pasting static images into your slide shows each day, week or period-end you can be focusing on the news in the data and doing a better analysis of the results.

This is the perfect complement to your use of Oracle BI Enterprise Edition (10g or 11g) onto the exact same cubes for the broader BI use-case.

In addition to Microsoft Excel, Simba is gradually certifying other MDX front ends for the OLAP Option MDX Provider - with SAP-BusinessObjects Voyager already available and others due soon.

You can learn all about it on the Simba web site : http://www.simba.com/MDX-Provider-for-Oracle-OLAP.htm

Connectivity into Oracle Database OLAP 11g is market leading: with the same cubes and calculations being accessible via MDX as well as a huge range of SQL based tools and applications, it has never been easier to deploy multidimensional analytics to the masses.

Cell level write-back via PL/SQL

2010-10-22T08:03:00.000-07:00

A topic of conversation that regularly comes up when I talk to customers and developers about the OLAP Option is write-back to OLAP cubes. The most frustrating of these conversations usually involves someone saying 'but... the OLAP Option doesn't support write-back'. This is not the case and never has been.

Since the first OLAP Option release in 9i it has always been possible to write-back to cubes via the Java OLAP API and OLAP DML. But in recent releases, a new PL/SQL package based API has been developed. My thanks go to the ever-excellent David Greenfield of the Oracle OLAP product development group for bringing this to my attention.

At the most simple level, it is possible to write to a qualified cell:

dbms_cube.build(
'PRICE_COST_CUBE USING (
SET PRICE_COST_CUBE.PRICE["TIME" = ''24'', PRODUCT = ''26''] = 711.61, SOLVE)')

In the example above, a cube solve is executed after the cell write. The objects are referenced by their logical (ie. AWM) names.

This approach is very flexible. For example you can qualify only some dimensions, in this case the assignment is for all products:

dbms_cube.build(
'PRICE_COST_CUBE USING (
SET PRICE_COST_CUBE.PRICE["TIME" = ''24''] = 711.61, SOLVE)')

You can also skip the aggregation:

dbms_cube.build(
'PRICE_COST_CUBE USING (
SET PRICE_COST_CUBE.PRICE["TIME" = ''24'', PRODUCT = ''26''] = 711.61)')

or run multiple cell updates in one call:

dbms_cube.build(
'PRICE_COST_CUBE USING (
SET PRICE_COST_CUBE.PRICE["TIME" = ''24'', PRODUCT = ''26''] = 711.61,
SET PRICE_COST_CUBE.PRICE["TIME" = ''27'', PRODUCT = ''27''] = 86.82,
SOLVE)');

You can also copy from one measure to another.

dbms_cube.build('UNITS_CUBE USING (SET LOCAL_CUBE.UNITS = UNITS_CUBE.UNITS'));

This will copy everything from the UNITS measure in UNITS_CUBE to the UNITS measure in the LOCAL_CUBE. You can put fairly arbitrary expressions on the right hand side and the code will attempt to loop the appropriate composite. You can also control status.

For more details, take a look at the PL/SQL reference documentation

Incremental Refresh of Oracle Cubes

2010-10-07T08:11:00.001-07:00

One of the more common questions I get is about how cubes are processed, in particular how and when Oracle cubes are processed incrementally. Here is a short summary of how the cube refresh process works, a few scenarios and some suggestions on being smart about solutions.

- The cube will load all data from the source table.

- You can limit the data loaded into the cube by a) presenting new/changed data only via a staging table or filtered view or b) making the cube refreshable using the materialized view refresh system and using a materialized view log table.

- The cube will understand if data has been loaded into a partition. If data has not been loaded into a partition, it will not be processed (aggregated).

- If a partition has had data loaded into it, it will processed (aggregated).

- The cube will understand if a loaded value is new, changed or existing and unchanged. Only new or changed values are processed (the cube attempts to aggregate only new and changed cells and their ancestors).

- Changing parentage of a member in a non-partitioned dimension will trigger a full solve of the cube (across all partitions).

- If a member is added to a non-partitioned dimension, the cube will attempt an incremental aggregation of that dimension (that is, the new member and ancestors only).

Here are two scenarios that illustrate how this works.

1) How to trigger a lot of processing in the cube during a refresh:

- Load from the full fact table rather than a staging table, filtered view or MV log table. The full table will be loaded into the cube.

- Load data into many partitions. Each partition will need to be processed. For example, load data for the last 36 months when the cube is partitioned by time.

- Load data into large partitions. For example, partition by year or quarter rather than quarter or month. Smaller partitions will process more quickly.

- Make frequent hierarchy (parentage) changes in dimensions. For example, realign customers with new regions or reorganize the product dimension during the daily cube refresh. This will trigger a full cube refresh.

2) How to efficiently manage a daily cube refresh:

- Load from staging tables, filtered views or MV log tables where the tables/views contain only new or updated fact data. This will reduce the load step of the build. This becomes more important the larger the fact table is.

- Use as fine grained partitioning strategy as possible. This will result in smaller partitions, which process more efficiently (full or incremental refresh) and offer more opportunity for parallel processing. Also, it is likely that fewer partitions will be processed.

There can be a trade off with query performance. Typically, query performance is better when partitions are at a higher level (e.g., quarter rather than week) because there may be fewer partitions to access and less dynamic aggregation might be required. That said, the gain in refresh performance is typically much greater than the loss in query performance. Building a cube twice as fast is often more important than a small slowdown in query performance.

- Only add new data into the partitioned dimension. For example if the cube is partitioned by time, add data only for new time periods. Only the partitions with those time periods will be refreshed.

Clearly, there are many cases where data must be added to the non-partitioned dimensions. For example, new customers might be added daily. This is ok because a) new members are processed incrementally and b) new customers will likely affect only more recent partitions.

Schedule hierarchy realignments (e.g., changing parentage in product, geography and account type dimensions) weekly instead of daily. This will limit the number of times a full refresh is required. It might also allow you to scheduled the full refresh for a time where the availability of system resources is high and/or the query load is low.

The above scenarios help you understand how to most efficiently refresh a single cube. Also consider business requirements and how model the overall solution Two scenarios follow.

1) Data is available at the day, item and store levels in the fact table. The business requirements are such that all data must be available for query, but in practice most queries (e.g., 95% or more) are at the week, item and store levels.

Consider a solution where data is loaded in the cube at the week, item and city levels and more detailed data (day, item and store levels) are made available by drilling through to the table. This is very easy to do in a product such as Oracle Business Intelligence (OBIEE) or any other tool that has federated query capabilities and will be transparent to the business user.

In this case, the cube is simply smaller and will process more quickly. The compromise is that calculations defined in the cube are only available at the week, item and city levels. This is often a reasonable trade off for faster processing (and perhaps more frequent updates).

2) Data is available at the day, item and store levels in the fact table. The business requirements are such that all data must be available for query, but in practice:

- Longer term trending (e.g., year over year) is done at the week level or higher.

- Analysis of daily data (e.g., same day, week or month ago) is only done for the most recent 90 days.

In this scenario, consider a two cube solution:

- Cube A contains data at the week, item and store levels for all history (e.g., the past 36 months). This might be partitioned at the week level and aggregated to the month, quarter and year levels. Depending on reporting requirements, it might only be refreshed at the end of the week when the full week of data is available.

- Cube B contains data only at the day level for the most recent 90 days. It is not aggregated to the week, month, quarter and year levels (aggregates are serviced by Cube A). This cube is used for the daily sales analysis. This might be partitioned at the day level so that a) any full build of the cube can be highly parallelized and b) the daily update processes only a single and relatively small partition.

Using a tool such as Oracle Business Intelligence, which has federated query capabilities, a single business model can be created that accesses data from both cubes and the table transparently to the business user. When ever the user is querying at the week level or above, data OBIEE queries Cube A. If data is queried at the day level within the most recent 90 days, OBIEE queries Cube B. If data at the day level that is older than 90 days is access, OBIEE queries the table. Again, this can all be transparent to the user in a tool such as Oracle Business Intelligence.

Simba Technologies and Vlamis Software Solutions hosting special reception at OpenWorld 2010

2010-09-18T10:16:00.000-07:00

Please join Simba Technologies and Vlamis Software Solutions at a special OpenWorld 2010 reception for current and prospective Oracle OLAP users.

Cocktails and hors d’oeuvres will be served.

Time: Tuesday, September 21st, 2010 - 5pm – 7pm

Location: walking distance from Moscone Center. Please RSVP for location.

By Invitation Only. RSVP to events@simba.com

Special OpenWorld 2010 Cocktail Reception

2010-09-17T08:35:00.000-07:00

Join us! Simba Technologies and Vlamis Software Solutions are celebrating Oracle OLAP with a reception for current and prospective users. Please contact events@simba.com for exact location. Tuesday September 21, 2010 from 5-7PM. Walking distance from Moscone.

Simba product managers will also be available at the Oracle OLAP pod in Moscone West to answer questions you might have about Excel Pivot Tables and the MDX Provider for Oracle OLAP.

Discoverer OLAP is certified with OLAP 11g

2010-08-10T04:02:00.000-07:00

A few people have asked me recently when an updated version of Discoverer OLAP will be released that supports the 11g OLAP Option. The answer is simple - it has already been released!! (but I guess that many people missed it because it was bundled as part of a broader patchset and not widely announced)

If you are interested, you can download it from OTN under Portal, Forms, Reports and Discoverer (11.1.1.3.0)

An updated version of the BI Spreadsheet add-in has been released too and can also be downloaded from OTN

A first look at OBIEE 11g with Oracle OLAP

2010-07-20T14:53:00.000-07:00

For those who missed it, the global launch for the 11g release of the Oracle Business Intelligence Enterprise Edition suite (OBIEE) took place in London on July 7th.

And the fantastic news for Oracle OLAP customers is that OBIEE 11g will work out-of-the-box with Oracle OLAP in almost exactly the same way as OBIEE 10g does - with just one additional configuration step required to enable the new OLAP-style front-end functionality.

Of course, there are other features that are relevant such as the WebLogic application server, and the new security model, but these have already been well blogged elsewhere so the focus of this posting will be Oracle OLAP integration.

To illustrate how easy it is, I will use a trusted old friend as a starting point - the 11g Global sample schema. I have installed this in an Oracle 11.2 database instance, created an Oracle OLAP Analytic Workspace, and then refreshed this AW so that the dimensions and cubes are built.

With an AW in place, the next step is to use the OBIEE plug-in for AWM to generate the metadata required for the OBIEE Server. For those who have not used the plug-in before, check out this excellent demonstration of how it works. While this particular version of the plug-in was originally released to work with OBIEE 10g, and presumably an updated version will be released in due course, it can be used in exactly the same way in OBIEE 11g to import metadata into the Administration tool.

And at first glance, aside from a few updated icons, this version of the Administration tool looks very similar, but the biggest change related to the administration of OLAP data sources (relational or MOLAP) is the ability to map hierarchy objects right through into the presentation layer.

Here is the Metadata generated by the plug-in for the Channel Dimension in both the Business Model and Presentation layers

The new 11g OLAP-style front-end functionality is enabled by adding these hierarchies into the Presentation layer too. This can be achieved by a simple click-and-drag for each hierarchy like the following which is again for the Channel Dimension

Hopefully, the next release of the plug-in will handle this additional step automatically (and also provide support for value-based hierarchies which were not supported by the front-end in OBIEE 10g) but in the meantime it really is just a simple click-and-drag for each dimension.

Once all the hierarchies are mapped through into the Presentation Layer, the cube is ready to query. I can log into the OBIEE 11g home page and create a new analysis based upon my Oracle OLAP subject area. The new hierarchies are available for selection when I construct a query

I can then select all of the 'columns' I need for my query and view the results as a pivot table. Here is a really simple example showing Sales by Time. I have also added some calculated measures which have been created inside the AW and derive really useful analytics from the Sales measure. This is a classic reason for using the OLAP Option in the first place - it facilitates the easy creation of calculations that are difficult (or often impossible) to express in SQL. And by having them embedded in the cube, the only thing that the SQL tool (in this case OBIEE) needs to do is select the calculation as a field in a view. How easy is that?!

Once a pivot table with Hierarchy-based columns has been created, this is where the new front-end features really come into play. Some highlights include Calculated Items (derived Dimension members) and a new Selector (which allows dimension selections to be built up as a series of steps based upon add/keep/remove logic):

For those familiar with Discoverer OLAP, or Sales Analyzer, Financial Analyzer and Express Objects/Analyzer, these aren't exactly revolutionary features, but combined with all the other great features of the OBIEE suite, this is now a very compelling platform for your Oracle OLAP data.

Finally, I would guess that there are probably thousands of old Oracle Express/OLAP systems that have been waiting for a BI platform like this. If you work on one, what are you waiting for?

***OBIEE 11g can now be downloaded from OTN***

Bissantz DeltaMaster - Cool Tool for OLAP

2010-06-18T07:56:00.000-07:00

I recently returned from a trip to Germany where visted a Bissantz, a relatively small company in Nürnburg that develops and markets an interesting reporting and data visualization tool named DeltaMaster that works with Oracle OLAP (and other data sources). I was very impressed with this tool. There are few things that I really liked about it:

It's very good at displaying a lot of information within a single report. One of the ways that it does this is by mixing graphical representations of data with numerical representation (they are very big on something called 'Sparklines'). This makes it very easy to create a single report that includes data on, for example, sales for the current quarter but also provides indications of sales trends and shares.

The presentation of data is very clean. While the reports themselves are very sophisticated, the developers have done a terrific job of presenting them to users. The presentation tends to be more functional than fluffy, but it's done very well. It is easy on the eyes.

DeltaMaster goes way beyond basic cross tabs and charts. There are prebuilt reports / analysis templates for rankings, concentration analysis, portfolio analysis, etc. There's quite a few different types of pre-built analysis and I won't try to do justice to them here. See for yourself.

It works better on OLAP than tables. I'm obviously biased when it comes to this topic, but for the end user this means more analytic power and flexibility.

Below is a concentration analysis report. This is along the lines of a Pareto chart. There are many different types of built-in analysis, but this one looks nice in the confined space of this blog's page.

Here are some links:

The DeltaMaster page at Bissantz: http://www.bissantz.com/products/

A clever blog by Bella, the Bissantz company dog: http://www.bella-consults.com/

Bella, if you happen to find your way to this blog, here's a 'hello' from Henry (the OLAP product manager's dog).

Time Dimensions with Hourly Time Periods

2010-05-06T06:26:00.000-07:00

I was working on an application last week that required time series analysis at Hour, Day, Month, Quarter and Year levels. Two interesting things came out of this application.

First, a little implementation detail. The data was supplied in the fact and dimension tables at the Hour level with a TIMESTAMP data type. As you might expect then, there were time periods at the hour level such as:

02-JAN-10 10.00.00.000000000 AM
02-JAN-10 11.00.00.000000000 AM
02-JAN-10 12.00.00.000000000 PM
02-JAN-10 01.00.00.000000000 PM
02-JAN-10 02.00.00.000000000 PM

In my first attempt at building the time dimension I loaded hours directly from TIMESTAMP data type. In that case, the members at Hour level were loaded into the dimension stripped of the hour (e.g., 02-JAN-10). Since this isn't what I wanted, I converted the hours into a CHAR as follows:

CREATE VIEW time_dim_view AS
SELECT
TO_CHAR(hour_id, 'DD-MON-YYYY HH24') AS hour_id,
TO_CHAR(hour_id, 'DD-MON-YYYY HH24') AS hour_desc,
hour_time_span,
hour_id AS hour_end_date,
.. and so on.

This gave me dimension members at hour as follows:

01-JAN-2010 00
01-JAN-2010 01
01-JAN-2010 02
01-JAN-2010 03
01-JAN-2010 04

That worked just fine. I did the same for the descriptions (so that they would be more easily readable by end users) and added a corresponding column to a fact view so that the time view and fact view joined correctly on the TO_CHAR(...) columns.

For the TIME SPAN attribute, I used a fractional value of DAY (0.041667, which is 1/24th of a day). I read the DATETIME into the END DATE attribute as is (no conversion required). From there on, everything worked perfectly (cube builds, time series calculations, etc).

If you happen to look at the END DATE attribute from the OLAP DML side, be sure to wrap the END_DATE object in a TO_CHAR function so that you see the hours. Otherwise, you will see only the day in most cases (it depends on the NLS_DATE_FORMAT setting for the session). For example:

REPORT DOWN TIME TO_CHAR(TIME_END_DATE 'DD_MON_YYYY HH24')

The other thing that was interesting has more to do with the application design. As so often happens, the customer was inclined to build one cube with all history at the hour level (two years of history). When examining the reporting requirements, however, it turned out that hour level analysis very rarely occurs more than 2 month back. Almost all of the reporting looking back over the two years was at the day level or higher (that is, not hourly level reporting).

We could have built the one cube (two years, hour and higher), but most of the processing of hour level data would have been a waste because users don't look at the older data at that level. Instead, we built a very efficient application with two cubes. One cube contained only three months of data at the hour, day, month, quarter and year levels. Another cube contained two years of history starting at the day level.

Presentation of the data is mostly done using Oracle Business Intelligence Enterprise Edition(via SQL to the cube). Some reports examine hourly level data. Other reports examine more aggregate data over longer time periods. Time series calculations (e.g., period to date, moving average, etc.) were added to both cubes and made available in the OBIEE reports.

Occasionally, a user will want to drill from day to hour more than three months back. To support this, OBIEE was set up to drill from day (in the two year cube) to hour in the fact table. The only compromise was that the time series calculations of the cube were not available when drilling to hour in the fact table. That didn't matter to these users.

From the end user perspective, the fact that there were two cubes instead of one (as well as a fact table) was completely irrelevant since OBIEE presented all data in reports in a single dashboard. From a processing perspective, the system was much more efficient and manageable as compared to the single big cube approach.

It is very worthwhile to keep this lesson in mind when you design your applications. Pay careful attention to reporting requirements and build cubes that meet those requirements. You can tie multiple cubes together in a tool such as OBIEE. This approach is often much better then building a single cube every level of detail.

In this case, the example is about what level of detail is in which cube. The same concept applies to dimensions. You might find it much more efficient to build Cube 1 with dimensions A, B, C and D and Cube 2 with dimensions A, B, E and F rather than one big cube with all dimensions.

Parallel Execution of OLAP DML

2010-03-16T11:44:00.000-07:00

While I was teaching a workshop in Moscow recently a student asked about parallel execution of OLAP DML. Their cube processing included the usual loading and aggregation, which are automatically parallelized, but they also have some assignments into the cube which are done using an OLAP DML program. They noted that this was slow because it was single threaded on one CPU. I told the student that Oracle OLAP supports a multi-write attach mode that can be used to update multiple objects in parallel using separate sessions. I checked the documentation, but didn't find a clear explanation of how this works in the context of processing partitions of a cube in parallel.

Here's a code sample that illustrates how to process partitions of a cube in parallel. For this example, assume that the cube has four dimensions: time, product, geography and channel. The cube is partitioned on the time dimension at the retail year level. An OLAP DML program is used to assign data into the cube at the day level (it will be aggregated later).

" Attach the AW
AW ATTACH global_business multi

" Set status of time to match the partition. In this example, the partition is 'P3'
LIMIT time TO PARTITION(sales_prt_template) EQ 'P3'

" Keep data at the day level.
LIMIT time KEEP time_levelrel 'DAY'

" Set status of other dimensions.
LIMIT product TO product_levelrel 'ITEM'
LIMIT geography TO geography_levelrel 'STATE_PROVINCE'
LIMIT store TO store_levelrel 'STORE'

" Acquire the partition for write.
ACQUIRE RESYNC sales_stored_stored (PARTITION p3)

" OLAP DML assignments.
sales_stored(sales_measure_dim 'DOLLAR_SALES') = sales_stored(sales_measure_dim 'QUANTITY') * price_stored(price_measure_dim 'PRICE') across sales_stored_prt_template

" Update and commit to save work.
UPDATE multi
COMMIT

" Release the partition.
RELEASE sales_stored (partition p3)

" Detach the AW
AW DETACH global_business

The important points are:

The attach mode is MULTI
Use ACQUIRE to attach the appropriate partition.
The PARTITION function can be used to set status of the partitioned dimension to the dimension members in the partition.
Use the MULTI keyword with UPDATE.
RELEASE the partition.
While it doesn't have anything to do with running this in parallel, "across sales_stored_prt_template" will cause the OLAP DML to loop the local composite index of the partition rather than loop over the base dimensions.

Repeat this code (or use parameters) for each partition. To run in parallel, run these commands in seperate database sessions for each partition. The above code sample is pure OLAP DML. In practice, it makes sense to execute this in the context of PL/SQL that you can call in separate SQL Plus sessions. E.g.,

BEGIN

-- Attach the AW in multi-write model.
dbms_aw.execute('AW ATTACH global_business multi');

-- Set status of time to match the partition.
dbms_aw.execute('LIMIT time TO PARTITION sales_prt_template) EQ ''P1''');
dbms_aw.execute('LIMIT time KEEP time_levelrel ''DAY''');

-- Set status of other dimensions.
dbms_aw.execute('LIMIT product TO product_levelrel ''ITEM''');
dbms_aw.execute('LIMIT geography TO geography_levelrel ''STATE_PROVINCE''');
dbms_aw.execute('LIMIT store TO store_levelrel ''STORE''');

-- Aquire the partition for write.
dbms_aw.execute('ACQUIRE RESYNC sales_stored_stored (PARTITION p1)');

-- OLAP DML assigment.
dbms_aw.execute('sales_stored(sales_measure_dim ''DOLLAR_SALES'') = sales_stored(sales_measure_dim ''QUANTITY'') * price_stored(price_measure_dim ''PRICE'') across sales_prt_template');

-- Update to save work.
dbms_aw.execute('UPDATE multi');

-- Release the partition.
dbms_aw.execute('RELEASE sales_stored_stored (partition p1)');

-- Detach the AW
dbms_aw.execute('AW DETACH global_business');

END;
/

COMMIT;

If this is done with PL/SQL, then you can run a shell script such as this to run all partitions in parallel.

sqlplus olaptrain/***** @set_sales_cost_p1.sql &
sqlplus olaptrain/***** @set_sales_cost_p2.sql &
sqlplus olaptrain/***** @set_sales_cost_p3.sql &
sqlplus olaptrain/***** @set_sales_cost_p4.sql &

Excel and Oracle OLAP - Reporting No-Agg Measures

2010-02-23T06:41:00.001-08:00

I've run into this a few times recently, so here's a quick tip related to using Excel with Oracle OLAP (via the Simba MDX Provider for Oracle OLAP, of course).

Here's a situation that's been reported as a bug, but you really just need to know the right Excel Pivot Table option to choose. Consider a cube that has measures that do not aggregate but is dimensioned by a dimension with a hierarchy. In this case, there is a cube with a Store dimension with levels Store > Store Type > All Stores. The stores are located in different countries and sell in local currencies. There is a Local Currency measure, with sales reported in whatever the local currencies might be (Euros, Dollars, Yen, etc.) and a Dollar Sales measure with the U.S. Dollar conversation. As a common currency, Dollars can be aggregated. Local currencies can't be aggregated.

Here's a sample report in Excel.

Note that Dollar Sales is reported for Direct and Indirect but Local Sales is not. That's correct because Local Sales doesn't aggregate.

But what if I happen to select only Local Sales (which is null at the aggregate members Direct and Indirect). By default, Excel will display the report as shown below.

This isn't very useful because I can't drill down on the Direct member to get at the stores. The solution is simple, but a lot of people seem to miss it. Just choose the Show items with no data in rows PivotTable option.

Now you will be able to see the Direct and Indirect members, allowing you to drill to stores.

Now, after the drill.

Oracle Exadata: A Single Source of Truth (New Video)

2010-02-11T08:03:00.000-08:00

Over the last 6-9 months we have been releasing a series of videos on YouTube under the banner of "DBA2.0" (a quick search on YouTube will list all these videos). These videos follow the work of a dedicated team of DBAs and business users as they try to get their jobs done in an environment that just keeps throwing up new challenges. Fortunately, Oracle is there to save the day...

This latest scenario deals with data warehousing and specifically Exadata. It is in two parts and the first installment has just been released - "Oracle Exadata: A Single Source of Truth". Here is the story so far:

A stroll through the halls of your IT department may reveal that there is no love lost between the DBAs and business analysts they support. In today's hyper competitive environment, business analysts need to perform more and more predictive analytics and they want the answers yesterday, but managing separate BI and OLAP servers and ensuring fast query performance can be a challenge for DBAs.

Watch this short video to see how the dynamic DBA duo address this challenge using Oracle Exadata, forging a truce with their new business analyst and even getting her to crack a smile - sort of.

Enjoy this video at http://www.youtube.com/watch?v=WgVSu-4Mizs and stay tuned for Part 2 which will be available shortly...

(Please Note: No DBAs or Business Analysts were harmed in the making of these videos)

Using MindMapping to view OLAP hierarchies...

2010-02-10T03:02:00.000-08:00

I came across this interesting article by accident. One thing I have always wanted within AWM is a way of viewing a hierarchy or series of hierarchies. In the old days of Express I wrote a various Express programs, EIS code (who remembers Express EIS?), Express Objects extensions, that would list out a hierarchy. However, this article by Robert Brooke takes this idea way beyond anything I have seen because it uses the open source tool called MindMapping. Take a look at the series of articles Robert has written:

http://ofaworld.wordpress.com/2010/02/09/ofa-mindmapping/
http://ofaworld.wordpress.com/2009/06/10/sample-olap-dml-code-gplcode/
http://ofaworld.wordpress.com/2009/12/01/oracle-olap-mindmapping/

Now that is cool!

Now I am thinking if we could just add the MindMapping GUI into AWM and somehow allow people to build a hierarchy visually using the MindMapping GUI that would be something. This would give AWM a very powerful way of designing a hierarchy in a live/interactive manner. One for the OLAP PM team - I think. In the short-term I think it might be possible to use the AWM extension API to add the MindMapping GUI to AWM menus but this would simply launch an the MindMapping tools outside of AWM but that might be sufficient for the moment?

Nice one Robert!

Creating Cubes with Simple SQL

2009-11-04T13:57:00.000-08:00

If you didn't look carefully at some of the Oracle OLAP 11.2 collateral there's a very interesting new feature that you've probably missed, the ability to create Oracle cubes and dimensions using only SQL. This feature is implemented at the CREATE_MVIEW program in the DBMS_CUBE package.

Here's how it works. You define SQL dimension objects and a table-based materialized view and then run the DBMS_CUBE.CREATE_MVIEW program. This program will (1) create an analytic workspace, (2) create OLAP dimensions from the SQL dimensions, (3) create a cube from the table-based materialized view and (4) create a cube-organized materialized view on the cube to enable query rewrite into the cube.

This feature is primarily designed to make it very easy to use the cube as a summary management solution for applications that query relational tables. For example, you might have an Oracle Business Intelligence (or Business Objects, Cognos or MicroStrategy) application that queries tables. If the application queries summary level data, can benefit from a performance boost and queries tables that tables represent data that is dimensional in nature (e.g., a star or snowflake schema), a cube is a great way to manage that summary data.

Let's look at a scenario. There's a star schema with time, product, customer and channel dimension tables and a sales fact table. The hierarchies are as follows:

Time: Day > Month > Quarter > Year
Product: Item > Subtype > Type > Category > Department
Customer: Customer > City > State > Country > Region
Channel: Channel > Class

The fact table contains data at the Day, Item, Customer and Channel levels for measures Sales and Quantity.

The objective is to create a cube and cube-organized materialized view that manages all summary data beginning at Month, Item, City and Channel levels. Note that in this example the detail of the cube will be a summary of the fact table.

Step 1 is to create SQL dimension objects for each of the dimension tables. (If you want to try this script yourself, download and install the OLAPTRAIN schema from the OLAP page on OTN. http://www.oracle.com/technology/products/bi/olap/11g/samples/schemas/readme.html .)

--
-- Create SQL dimension objects for hierarchies and attributes. The cube will
-- use these as the source definitions for cube.
--
-- Time dimension.
--
CREATE DIMENSION times
LEVEL day IS (times.day_key)
LEVEL month IS (times.month_id)
LEVEL calendar_quarter IS (times.calendar_quarter_id)
LEVEL calendar_year IS (times.calendar_year_id)
HIERARCHY calendar (
day CHILD OF
month CHILD OF
calendar_quarter CHILD OF
calendar_year)
ATTRIBUTE day DETERMINES (day_description)
ATTRIBUTE month DETERMINES (month_name, month_end_date)
ATTRIBUTE calendar_quarter DETERMINES (calendar_quarter_name, calendar_quarter_end_date)
ATTRIBUTE calendar_year DETERMINES (calendar_year_name, calendar_year_end_date);
--
-- Product dimension.
--
CREATE DIMENSION products
LEVEL item IS (products.item_key)
LEVEL subtype IS (products.subtype_key)
LEVEL type IS (products.type_key)
LEVEL category IS (products.category_key)
LEVEL department IS (products.department_key)
HIERARCHY departments (
item CHILD OF
subtype CHILD OF
type CHILD OF
category CHILD OF
department)
ATTRIBUTE item DETERMINES (item_name)
ATTRIBUTE subtype DETERMINES (subtype_name)
ATTRIBUTE type DETERMINES (type_name)
ATTRIBUTE category DETERMINES (category_name)
ATTRIBUTE department DETERMINES (department_name);
--
-- Customer dimension.
--
CREATE DIMENSION customers
LEVEL customer IS (customers.customer_key)
LEVEL city IS (customers.city_key)
LEVEL state IS (customers.state_province_key)
LEVEL country IS (customers.country_key)
LEVEL region IS (customers.region_key)
HIERARCHY regions (
customer CHILD OF
city CHILD OF
state CHILD OF
country CHILD OF
region)
ATTRIBUTE customer DETERMINES (customers.customer_number)
ATTRIBUTE city DETERMINES (customers.city_name)
ATTRIBUTE state DETERMINES (customers.state_province_name)
ATTRIBUTE country DETERMINES (customers.country_name)
ATTRIBUTE region DETERMINES (customers.region_name);
--
-- Channel dimension.
--
CREATE DIMENSION channels
LEVEL channel IS (channels.channel_key)
LEVEL class IS (channels.class_key)
HIERARCHY classes (
channel CHILD OF
class)
ATTRIBUTE channel DETERMINES (channels.channel_name)
ATTRIBUTE class DETERMINES (channels.class_name);

The next step is to create a table-based materialized view that summarizes data to the level you want to load into the cube. Note that you do not need load data into this materialized view (use the BUILD DEFERRED option). The DBMS_CUBE.CREATE_MVIEW program will simply use the table-based MV to understand how to query the source fact table and to find to the measures to be added to the cube.

--
-- Create the table-based materialized view. In this example, the table-based
-- materialized view aggregates data from the day to month levels in time
-- and the customer to city levels in customer.
--
-- MV is build deferred because the cube doesn't need data from the MV (it just
-- uses the MV's definition.
--
CREATE materialized VIEW sales_mon_ite_cit_cha_mv build deferred
AS
SELECT t.month_name,
p.item_name,
cu.city_name,
ch.channel_name,
SUM(f.sales) sales,
SUM(f.quantity) quantity
FROM times t,
products p,
customers cu,
channels ch,
sales_fact f
WHERE t.day_key = f.day_key
AND p.item_key = f.product
AND cu.customer_key = f.customer
AND ch.channel_key = f.channel
GROUP BY t.month_name,
p.item_name,
cu.city_name,
ch.channel_name;

Finally, run the DBMS_CUBE.CREATE_MVIEW program.

--
-- Create and load the cube-organized materialized view.
--
declare
myCubeMv varchar2(32);
begin
myCubeMv :=
dbms_cube.create_mview(
mvOwner =>user,
mvName =>'sales_mon_ite_cit_cha_mv',
sam_parameters=>'logDest=serverout,build=immediate' );
end;
/

There's several parameters to this program, but I found the defaults to be very reasonable. It built my cube as compressed, partitioned by quarter and pre-aggregation settings of 0 for the top partition and 35 for the bottom partition. You can control any of these settings with the sam_parameters argument. You can also defer the loading of the cube and load the cube later with the DBMS_CUBE.BUILD or DBMS_MVIEW.REFRESH programs.

The cube can be viewed and edited in Analytic Workspace Manager, so you can use it to check the design or make changes such as adding custom measures.

As with any other materialized view, you will need to make sure all the necessary constraints are in place on the base tables. I used AWM’s Relational Access Advisor feature (in the Cube / Materialized View tab) to generate constraint recommendations.

Automatically Create Oracle Business Intelligence Repositories for Oracle11g Cubes

2009-04-30T08:37:00.000-07:00

An OBIEE Plug-in for Oracle11g OLAP was released on the OLAP Option page on the Oracle Technical Network today. The plug-in allows you to automatically create OBIEE repositories that can be used to query Oracle11g cubes. It's really, really easy. The OBIEE repository created by the plug-in allows OBIEE to query all content of the cube, including summary data and advanced calculations. This is a great way to demonstrate how the Oracle cube can enhance the performance and analytic content of Oracle Business Intelligence Enterprise Edition.

The OBIEE Plug-in is used with Analytic Workspace Manager (AWM), the administrative tool of the Oracle OLAP Option. Only the most basic understanding of the OBIEE repository is required to use this AWM plug-in. The plug-in creates fully configured a physical database, business model and mapping layer and presentation catalog in the OBIEE repository.

Here are some useful links:

The Oracle OLAP OTN page.
The OBIEE Plug-in instruction sheet.
A demonstration video.
Download the plug-in.

Call for OpenWorld 2009 papers has started...

2009-04-07T01:00:00.000-07:00

This years Oracle OpenWorld conference will be held on October 11-15 at the Moscone Center in San Francisco. A few days ago the OpenWorld website opened for customers and partners to submit proposals for presentations to be included in this years conference. This year there will be more sessions allocated to customer and partner papers which increases your chances of having your paper selected. The OpenWorld conference is a great opportunity to present your ideas to the Oracle Community and to add to increase your own knowledge by attending other presentations. Most importantly,all the Oracle OLAP Blog team will be onsite at the demo grounds so come and say "Hi" and checkout the latest cool 11g OLAP demos.

Presenting at OpenWorld is an amazing experience and I would recommend it to anyone and everyone. There is nothing quite like it. The deadline for submission is April 19 so act fast!

Are there any benefits to presenting? Of course - but the benefits/goody bags change every year and I am not sure what we will be offering this year. Obviously you get a complimentary Oracle OpenWorld Full Conference pass which means you can get into the vast majority of events during the conference. You get access to the whole presentation catalog via the OpenWorld website. On top of the Oracle goodies, most of the vendors in the demo grounds hand out lots of other goodies (it pays to bring an extra suitcase) and most evenings someone, somewhere, is usually offering free beer. What more could you ask for!

What should I present on? The topic is completely up to you. You can talk about a recent project or a specific database feature and how you used it. Just pick any topic you think will be of use and interest to other people.

The 2009 Conference web page is here. Look for the red link "Now Live" and good luck.

Excel Update - Beta Version of MDX Provider Now Available

2009-04-02T05:55:00.000-07:00

A quick update on using Excel to query an Oracle11g cube ....

Yesterday Simba announced availability beta 1 MDX provider for Oracle OLAP. I've been using prerelease version of the provider for a few months now and have been very happy with it. There's still some features to be added and some performance work to be done, but I think the beta 1 version is well worth trying. In my experience, the MDX provider is pretty stable and I very much like Excel as a front end to Oracle cubes.

To sign up, visit the MDX Provider for Oracle page at http://simba.com/MDX-Provider-for-Oracle-OLAP.htm . You can also find a video demo on this page.

Using Excel with Oracle OLAP

2009-03-07T05:56:00.001-08:00

I did two webcasts about using Excel with Oracle OLAP this week. They were incredibly sucessful. If you've never looked at Excel 2007 pivot tables, I'll tell you that they are very good. Using the MDX Provider for Oracle OLAP, Excel makes a direct/native connection to the Oracle database and has the ability to query OLAP cubes in a multidimensional context.

I won't every try do to justice to this solution in this blog. It's really best to just see it in action. I've posted a video on simba.com (go to http://simba.com/MDX-Provider-for-Oracle-OLAP.htm and click on the product demo link).

You can also sign up for the beta test at simba.com. Do yourself a favor - try it out.

Querying an Oracle OLAP Cube using Excel.

New! Oracle OLAP Overview Video

2009-02-16T00:48:00.000-08:00

A new fifteen minute Oracle OLAP video has been released - describing the benefits of Oracle OLAP in the context of the Oracle Database, Data Warehouse and Business Intelligence platform.

You can either watch this video online, or download for replay on an iPod:
• Click here to view the video now
• Click here to download the iPod video

Experience with Oracle University OLAP Essentials Course

2009-02-06T06:29:00.000-08:00

I've recently taught two sessions of the Oracle University Course 'OLAP Database 11g: OLAP Essentials'. The first session was in Reading UK and the next in Munich. Both sessions were highly rated by students (9.4 out of 10). As an instructor I found the course a pleasure to teach (kudos to Brian Pottle and Marty Gubar who put much of this class together).

This is a three day introductory course that is appropriate for anyone who is approaching the OLAP Option for the first time or who is upgrading to 11g from earlier releases (there is so much new in OLAP 11g that even experienced users can learn a lot in this course). The students in the class were a mix of OLAP Option newbies and upgraders. Both groups were equally pleased with the course.

Students remarked that one of the real strengths of the course is the hands-on exercises. By the end of day 1 students have defined, populated and queried their first cube. Later in the course they get hands-on experience with cube-organized materialized views, SQL query of cube, creating calculated measures, forecasting, security and even building a custom application in Application Express on the OLAP cube. One of the things about the hands-on exercises that I found remarkable was the high success rate experienced by the students. These are very well put together.

The outline of the course follows:

Day 1

1. Examining the role of the OLAP Option in the Oracle BI/DW platform.
2. Examining the OLAP Data Model.
3. Building an OLAP Cube (with 2 hands-on exercises).

Day 2

4. Examining Cube-Organized Materialized Views (with hands-on exercise).
5. Creating Calculated Measures (with hands-on exercise).
6. Using SQL to Query OLAP Cubes (with hands-on exercise).
7. Enhancing Analytic Content (with hands-on exercise). (This uses OLAP DML programs to create a forecast.)

Day 3

8. Using Oracle Application Express and Oracle Business Intelligence with OLAP Data (with hands-on exercise).
9. Cube security (with hands-on exercise).
10. Designing cubes for performance and scalability.
11. Performance tuning (this is really about things the DBA should know to tune the Database for cubes).

These three very full days. Expect to get a lot of value from this class. And, it was lots of fun.

Here is one student's perspective of the class: http://ofaworld.wordpress.com/2009/02/18/oracle-university-oracle-11g-olap-essentials-training-course/

For more information, see the Oracle University site.

New Tutorial - Creating Interactive APEX Reports Over OLAP 11g Cubes

2009-02-06T02:05:00.000-08:00

The latest in a recent series of 11g OLAP tutorials has been added to the Oracle OLAP product page on OTN.

The tutorial is called 'Creating Interactive APEX Reports Over OLAP 11g Cubes' and shows how you use Oracle Application Express (APEX) to create an interactive sales analysis report that runs against OLAP 11g data.

You learn how to query and create analytic reports of OLAP 11g cubes, including both stored and calculated measures. You also learn how to apply query techniques that leverage unique characteristics of OLAP 11g cubes.

The other tutorials already published in this series are:

Oracle OLAP Newsletter - February 2009

2009-02-05T16:36:00.000-08:00

The latest Oracle OLAP newsletter, February 2009, has been posted onto OTN and is available by clicking here

The customer feature this time is R.L. Polk who have used 11g OLAP to simplify their delivery of aggregate data through the use of cube organised materialised views. This is a fantastic case study which captures the true value of this functionality (note the dramatic improvements in both build and query times), and of having Oracle OLAP embedded in the Oracle Database.

The highlights of the Product Update section this time are the release of the latest version of AWM 11g (11.1.0.7B), and also a new version of the BI Spreadsheet Add-in (10.1.2.3.0.1 - enough digits?!) which now includes support for Excel 2007.

Gartner identifies Oracle OLAP as a key strength in Oracle's DW proposition

2009-01-26T11:50:00.001-08:00

You may have already seen elsewhere that Gartner recently published an updated magic quadrant for Data Warehouse Database Management Systems.

It was a little bit surprising to see that the HP Oracle Exadata Storage Server and the HP Oracle Database Machine hadn't provided a bigger boost to Oracle's rating (although, as you would expect, Oracle is still firmly entrenched in the leaders quadrant). Perhaps it is still a little early for their significance to be fully appreciated. There is likely to be a more pronounced improvement in Oracle's rating the next time this magic quadrant is published.

Of particular interest to this blog is the fact that Oracle OLAP is identified as a key strength of Oracle's Data Warehousing proposition. The research highlights the enhanced materialized view and cube management features added in 11g, along with transparent SQL access and incremental update.

It is really great to hear such a significant endorsement of Oracle's strategy to embed the Oracle OLAP engine inside the Oracle Database.

If you want to read more about what 11g OLAP could do for your Oracle Data Warehouse, then check out this white paper

Get hands-on with 11g OLAP & Oracle Business Intelligence Enterprise Edition

2009-01-07T01:20:00.000-08:00

Happy New Year to everyone!!

Following the announcement last month of two new 'Oracle By Example' tutorials on building and querying 11g OLAP cubes, here are the details of a further two tutorials on working with 11g OLAP and Oracle Business Intelligence Enterprise Edition (OBIEE).

The first tutorial shows how to create OBIEE metadata over 11g OLAP cubes

(if you are using 10g OLAP, use this tutorial to create OBIEE metadata instead)

The second new tutorial shows how to query 11g OLAP cubes using OBIEE Answers - using the metadata repository created during the first tutorial

While OBIEE Answers is a widely used query tool for Oracle OLAP (for an example, see the article on Micros Systems), it would be interesting to hear from people using some of the other components of OBIEE, especially some of the newly integrated 'plus' components like Smart View which appears to be receiving a lot of development effort from the BI/EPM folks.

Please feel free to share any experiences you might have in our comments section.

Now Available! Two new Oracle OLAP Demonstrations

2008-12-29T11:26:00.000-08:00

Two new Oracle OLAP demonstrations have been added to the Oracle OLAP product page on OTN:

Fast Answers to Tough Questions Using Simple SQL :- Oracle OLAP is a world class analytic engine embedded in the Oracle Database. OLAP Cubes and dimensions are easily accessible thru a star-model. Using very simple SQL, Oracle OLAP delivers fast answers to tough, analytic questions. This demonstration shows how to query OLAP cubes using several tools, including: Oracle Business Intelligence Enterprise Edition, Application Express and SQL Developer.

Transparently Improving Query Performance with Oracle OLAP Cube MVs :- Oracle OLAP cubes may also be deployed as materialized views. Summary queries written to base fact tables can transparently leverage the fast query performance delivered by Oracle OLAP - without any changes to the application's query. The Oracle Optimizer automatically rewrites queries to cubes when appropriate. This demonstration shows how Oracle Business Intelligence Enterprise Edition seamlessly benefits from this capability. The demonstration then provides an "under the covers" view of how this improvement is achieved.

Oracle OLAP Newsletter - December 2008

2008-12-23T04:55:00.000-08:00

The latest Oracle OLAP newsletter, December 2008, has been posted to the OLAP Home Page on OTN and is available by clicking here.

As usual, it contains very useful information about what is happening in the world of Oracle OLAP and includes regular features such as the OLAP skills corner and DBA tips, as well as useful links for those wanting to download the software or get training or assistance.

The featured customer this time is JD Sports in the UK. This is an excellent example of Oracle OLAP being used as part of a wider Data Warehouse solution. It is also a significant endorsement of Oracle's Data Warehouse strategy to bring smart, embedded analytics to the data and highlights the benefits of an embedded OLAP server; JD Sports has been able to leverage other advanced Oracle technologies such as Real Application Clusters (to deliver scalability and availability), whilst additionally benefiting from many of the features taken for granted by Oracle's RDBMS customers (DW integrated security, storage, backup, transaction control, etc) but often not so easily delivered by stand-alone OLAP engines.

This customer feature is also a good example of the scalability of the Oracle OLAP engine itself. Whilst far from being the largest implementation (there are customers managing several Terabytes of data in Oracle OLAP cubes), this example shows how relatively large volumes of DW data can be loaded and aggregated in Oracle OLAP, and how the cube compression and partitioning features first introduced in 10g OLAP have completely changed the game compared to what was previously possible. Taken in isolation, loading 300 million source records is not a major achievement (not for Oracle OLAP anyway), neither is having a 10 dimensional cube, or indeed is aggregating across 28 hierarchical levels. What is more impressive is doing all of these three things combined in a single cube (which is only one cube out of a total of six), and still being able to deliver all of the key benefits you would associate with an well implemented OLAP system - fast query performance and lots of advanced calcs (literally 100's in this case) serving a reasonably sized user community.

All things considered, it is easy to see why Oracle OLAP is a key, strategic component of the Oracle Data Warehouse platform.

Let's hope for some more customer features in the near future.

Greetings of the season to everyone!

BTW - you can have the OLAP newsletter sent directly to your email box each quarter by following the link at the top of the current newsletter (Unsubscribe/Subscribe to this Newsletter)

Get hands-on with 11g OLAP

2008-12-21T09:22:00.000-08:00

You may have already noticed but over the past couple of weeks some new 11g OLAP training material has been published on the OTN OLAP home page.

Two new tutorials have been added to the popular Oracle By Example (OBE) series.

The first is titled 'Building OLAP 11g Cubes' and covers using Analytic Workspace Manager (AWM) 11g to build and load an OLAP cube.

The second is titled 'Querying OLAP 11g Cubes' and is a guide to querying a cube via SQL, both directly using OLAP Cube Views, and indirectly using Cube Materialized Views.

Supporting both of the tutorials is a new sample schema which gives you the opportunity to get hands-on and experiment in your own environment. Remember, that patch level 11.1.0.7 is required and to always check the recommended release details for your chosen operating system.

ComputerWeekly.com : E.on transforms financial insight with Oracle OLAP Option

2008-12-16T09:50:00.000-08:00

http://www.computerweekly.com/Articles/2008/12/11/233863/e.on+transforms+financial+insights+with+bare-knuckle.htm

The article above, published last week in ComputerWeekly, follows an earlier customer profile posted on OTN. E.on, one of Europe's leading energy suppliers, has gone live with a financial transformation software project that will give it an accurate view of the profits generated by each customer and provide better forecasting of future demand. E.on has accounts with over eight million customers in the UK. To quote directly from the project leader Lawrence Edwards: "It (accurate forecasting of demand, and, therefore, margin) is a massive problem because of the data volumes and complexity. Others have tried and failed, but we persevered."

It provides excellent proof points for the use of Oracle OLAP to solve business problems that require rapid data loading and analysis of large data volumes. The E.on system is currently operating at about 6Tb and is growing rapidly, with 150Gb of new data added every 14 hours. There is a great quote from Lawrence in the OTN PDF explaining what they have achieved using Oracle OLAP Option:

"The analytical power, centralized administration, and scalability of Oracle OLAP have allowed us to process and present data in a way that was not previously possible. This has provided us with an unprecedented depth of understanding of customers’ energy use and the demand for all our products and offerings"

New 11g OLAP Cube Materialized Views tutorial posted onto OTN

2008-12-16T09:30:00.000-08:00

Another new tutorial has been added to the Oracle OLAP home page on OTN.

This tutorial is titled 'Oracle OLAP 11g: Setting Up Cube Materialized Views for Query Rewrite'

The tutorial describes how to enable cubes as Cube Materialized Views, and how to enable and troubleshoot Query Rewrite using Analytic Workspace Manager 11.1.0.7. It is intended as a quickstart for intermediate developers.

Oracle Database 11g: OLAP Essentials - First dates announced

2008-12-08T10:19:00.000-08:00

Following the announcement last week about the new Oracle OLAP 11g Oracle University Training Course, the dates and locations for the first classes have been announced.

The very first class will be in Bridgewater, New Jersey, US from 20-Jan-2009 through to 22-Jan-2009.

The first class in Europe will be in Reading, UK from 21-Jan-2009 through to 23-Jan-2009.

The code for the course is D70039GC10 and more details on both events can be found on the Oracle University web site

Be sure to register early if you wish to attend as places are sure to be high in demand.

New! Oracle OLAP 11g Oracle University Training Course

2008-12-04T01:50:00.000-08:00

A brand new OLAP 11g training class has been added to the Oracle University schedule.

Here is a brief synopsis:

Oracle OLAP 11g, a fully-integrated component of Oracle Database 11g, provides a full featured multidimensional data model and calculation engine that is easily accessible to any SQL based business intelligence application or tool.

In this course, students learn to progressively build an OLAP data model to support a wide range of business intelligence requirements. Students learn to design OLAP cubes to serve as a summary management resource for existing SQL table queries. Students also learn to leverage the power of Oracle OLAP by adding rich analytic content to your data model.

Students learn to create sophisticated reports of OLAP data by using simple SQL queries. Students also create and execute OLAP queries in SQL Developer, Oracle Application Express (APEX), and in Oracle BI Enterprise Edition. Students learn to implement cube security, including how to authorize access to cube data and methods for scoping user views of data. Finally, students learn to design OLAP cubes for performance and scalability.

Learn To:

* Design and create an Oracle OLAP data model
* Enable query rewrite to OLAP Cube MVs for relational summary management
* Easily create OLAP calculations that enrich the analytic content of your data model
* Query OLAP data using simple SQL
* Implement cube security
* Efficiently design cubes for performance and scalability

More details and scheduling information can be found on the Oracle University Website

New 11g OLAP tutorial posted onto OTN

2008-11-25T01:07:00.000-08:00

A new tutorial has been added to OTN.

The tutorial is aimed at newcomers to Oracle OLAP and is a guide to creating and populating an 11g OLAP cube.

This is perfect for people who are looking for a gentle introduction to using the Analytic Workspace Manager OLAP administration tool and understand the basic steps in building an 11g OLAP cube.

New article on 11g OLAP Cube-Organised Materialized Views published onto OTN

2008-10-21T12:32:00.000-07:00

Oracle ACE Director Arup Nanda has published a series of articles onto OTN covering important new features in Oracle Database 11g titled 'Oracle Database 11g: Top Features for DBAs and Developers'.

The series includes a feature on 'Data Warehousing and OLAP' which looks at how Cube-Organized Materialized Views can be implemented alongside other features to deliver a compelling platform for data warehousing.

With Oracle's data warehousing proposition featured heavily in the news at the moment following the recent announcement at Oracle Open World on the availability of the Exadata Storage Server and Database Machine, this is an excellently timed reminder that Oracle OLAP is a core part of this data warehousing proposition.

New Oracle OLAP White Paper released to OTN

2008-10-10T05:58:00.000-07:00

A new Oracle OLAP white paper has been released to OTN titled "Using Oracle Business Intelligence Enterprise Edition with the OLAP Option to Oracle Database 11g"

This contains a guide on how to configure the OBIEE metadata layer to leverage the 11g Oracle OLAP option, both indirectly via OLAP cube based materialized views, and directly via OLAP cube views. For those working with 10g OLAP (cube views only), the best guide to configuring OBIEE is found in the online tutorial on OTN.

Personally, I think that it is great that this white paper captures an explanation of how to write SQL that is optimised for Oracle OLAP cube views. This is something I find customers initially struggle with - they write what they believe is a simple query and then cannot understand why the performance is not good.

This is because there are a few golden rules to writing optimal SQL for OLAP cube views and while they are simple to understand, they are not obvious to those who are new to the technology. I hope to write a more detailed Blog entry on this subject very soon, but for the time being take a look at the white paper (particularly pages 10 & 11) to see what I mean.

More 11.1.0.7 ports now available

2008-10-10T04:14:00.000-07:00

The Oracle Database 11g Release 11.1.0.7.0 Server Patch has now been released for several other ports, including Windows 32-bit.

The full list of operating systems currently supported is:

Linux x86
Linux x86-64
Solaris (SPARC) (64-bit)
IBM AIX (64-bit)
HP-UX Itanium
Microsoft Windows (32-bit)

I would recommend that all 11g OLAP users apply the 11.1.0.7 patch as soon as it is available for their operating system.

I would also recommend that all 11g OLAP users upgrade their AWM client to the 11.1.0.7A release which can be downloaded from Metalink or OTN

As always, the best source of information for recommended releases and patches is the Oracle OLAP certification page on OTN

I'm now going to download the Windows 32-bit patch (all 1.5GB of it!), and give it a roadtest....

Analytic Workspace Manager 11.1.0.7A released to Metalink

2008-10-06T02:46:00.000-07:00

The 11.1.0.7A version of AWM has been released to Metalink as patch 7420490

It includes important fixes and new features including:

the ability to add multiple languages to a single analytic workspace
individual aggregation definitions may now be defined for each measure of a cube
the Create Dimension user interface has been modified to allow levels of the dimension to be created at the same time as the dimension
the functionality of dimension and cube mapping has been enhanced to allow the application to refresh the definitions of database objects interactively to reflect the current state of database schema tables

To take advantage of all the new fixes and features, the Oracle Database 11g Release 11.1.0.7.0 Server Patch must be installed as well. This is currently only available for Linux 32-bit & Linux 64-bit, but other ports are likely to be available soon.

Oracle OLAP Newsletter - September 2008

2008-09-29T04:05:00.000-07:00

The latest edition of the excellent Oracle OLAP Newsletter has just been released here

Highlights this time include a customer feature on Oss Council in the Netherlands who use Oracle OLAP for management reporting, details of the new 11.1.0.7 release, and a guide to delivering summary management through cube materialized views.

Oracle Open World 2008 - HP-Oracle Database Machine launch

2008-09-28T15:59:00.000-07:00

The big news at Oracle Open World last week was that Oracle CEO Larry Ellison used his keynote, entitled "Extreme. Performance." to talk about Data Warehousing and launch the HP-Oracle Database Machine featuring the innovative new Oracle Exadata storage server.

You can learn all about these exciting new products on the oracle.com web site, so I won't bore you with that here, except to tell you that the performance of the Database Machine that we have seen from our Beta test customers is deeply impressive (the "10x" claims in the ads are pretty conservative from what I've seen).

The market reaction to the news that Oracle, together with HP, are now offering a 'DW Appliance' with superior performance, based on proven hardware components & including Oracle Database 11g, has reflected a common view that I heard while answering questions from customers at Open World: that the rationale for purchasing one of the niche vendors' DW Appliances, with their narrow sweet spot and less capable database software is now more questionable than ever.

But why would anyone say that? Surely 'performance' is a game of leap-frogging, and if/when one of the DW Appliance vendors releases a newer faster machine before HP-Oracle does, won't that mean that the Oracle Database Machine's advantage is short-lived?

Not at all.

The HP-Oracle Database Machine is a DW Appliance like no other. This is because Oracle Database has depth & breadth of functionality and power that none of the others can claim. When you purchase a Database Machine you get Oracle Database EE, Real Application Clusters and Partitioning pre-installed and preconfigured, with ASM being used to manage the storage grid. All of the features included in there are available for use as soon as you plug in your new machine. But you can leverage much more even than that, of course.

And this is a key point - all the capabilities, features and options available in Oracle Database 11g are available on the Database Machine. Security, High Availability, Manageability, support for a Mixed Workload, and Embedded Analytics - all of it. And because this is standard Oracle Database, it is easy to run any of your applications on it - no specialised knowledge of rarely used niche RDBMS's required. Anything that runs your existing Oracle servers will run on Database Machine without change.

This includes OLAP of course. Oracle Database OLAP is there too - pre-installed along with the rest of Oracle Database EE. You just need to license it for use on the DB Machine when you choose to use it. And this is great news as it adds multidimensional calculation and analysis sophistication to an already awsome piece of kit.

A majority of the Oracle OLAP Option customers that I have met who report disappointing performance turn out to be IO Bound on their servers. That is, the server and storage they are using is out of balance, and constraining the ability of the Database to process the data. The HP-Oracle Database Machine (like some of the Optimized Warehouses also available from Oracle and it's other hardware partners) provides excellent IO performance thanks to the balanced high speed infiniband interconnects between the storage and the database servers included in the machine, and is optimised for data warehousing queries across the spectrum - OLAP included. Good IO performance directly translates to even more effective OLAP implementations.

So, the HP Oracle Database Machine is a great fit for Oracle Database OLAP Option, and other embedded analytics features of the Oracle Database, including Data Mining, SQL Analytics and Statistical Functions, and the OWB (Oracle Warehouse Builder) Data Profiling and Data Quality features. Delivering BI applications that leverage these powerful capabilities is also much easier thanks to them being pre-installed on the Database Machine.

And Oracle OLAP is a great fit for the Database Machine, too. Which leads to the other question I have been asked by a couple of people: if Database Machine makes queries really really fast, does it mean that OLAP is no longer needed? Can I just dump all the data into tables and ignore all the other optimisations for Data Warehousing that Oracle Database provides?

This question misunderstands the primary reason that people invest in OLAP systems. It is not only about performance, but also (especially) about the calculation capability, and the ease with which even the most complex of business calculations can be expressed. Many business calculations are difficult to do in SQL on regular relational tables. Some are still not even possible. And in turn, many BI tools resort to the transfer of large amounts of data across the network to mid-tier servers, or even the client, where the calcs are performed. Database Machine will allow them to pull the raw data much faster than before, but you still have a more complex architecture than you need, and network performance will be impacted. The OLAP Option makes time series, shares, indexes, ratios and so on that businesses use on all their performance dashboards really easy to define, and really efficient to process. In the Database.

As regular readers of this blog know, the OLAP Option provides sophisticated multidimensional calculation and query functionality, accessible by pretty much any tools via a simple SQL query. There are hundreds of multidimensional-aware analytic calculation functions delivered by the OLAP Option - best of breed capability. This in turn leads to further performance benefits of course, but especially phenomenal ease of use and cost of ownership improvements. If all your KPIs are calculated within the Database, and surfaced to your BI tools, BI apps and Dashbards as simple columns that can be SELECTed; you reduce to trivial levels the amount of work you need to do in each BI Tools metadata layer with regard to (re)defining those calculations.

Other Data Warehouse vendors - appliance & non-appliances alike - cannot do this, and instead force you into further fragmenting your information asset across multiple servers and engines, with the added complications to the BI infrastructure that brings. The other vendors will require you to purchase, install, build and manage seperate standalone multidimensional data marts, usually from 3rd party vendors. None of them have embedded analytics into the core of the Database like Oracle has. And none provide simple high performance SQL access to the results of these analytics.

The combination of extremely fast performance for IO intensive queries (which characterise the work of some of the users of the data warehouse and are typicaly the queries targetted by the DW Appliance vendors) together with the multidimensional calculation power of the OLAP Option (which are commonly consumed by the masses via interactive dashboards etc, as well as used by the analyst users) in an easy to install, pre-configured, balanced hardware platform is very compelling.

Exciting times for Oracle BI and Data Warehousing.

Article : Closing the Ad Hoc Query Performance Gap for Good

2008-07-09T02:46:00.000-07:00

I found an interesting article today in amongst my Google alerts. It related to a topic of conversation at the recent TDWI conference in May about the issue of adhoc query performance within data warehouse environments. The article, on the whole, was very good (in my opinion) and included comments by Oracle's vice-president of database marketing, Willie Hardie.

The full article in Enterprise Systems is available here.

The article examines the reasons why business users feel their queries are taking too long and what steps companies are taking to try to improve query performance. The basic reasons for poor query performance was given as being down to two issues:

success of pervasive BI - so more users are running adhoc queries
more data - users want access to more and more data, both in terms of level of detail and time span.

I would add a third reason, which is increasing sophistication. Simply presenting users with reports that show revenue and expenses for the latest month, quarter and year to date are not adequate in today's highly competitive environment. Business users want to know about trends - this period vs last period, this year vs last year, this period vs the same period last year, like-for-like, shares, ranks, forecast, customer segmentation, market basket analysis. The list goes on and on and on.

Here is a direct quote from the article:

......The RDBMS -- or, more specifically, the Oracle RDBMS -- is an unmatched analytic workhorse, Hardie argues. Oracle is one of the biggest data warehousing players in the business, he points out, and the Oracle database powers some of the largest DWs in existence.

"The Oracle database is proven to be the fastest database out there for both transactional systems and data warehousing systems, across all scales, from small to extremely large systems. You ask any Oracle customer out there and they'll all give you the same answer: Oracle is the fastest database out there on the market right now," he claims.

What I would add is the Oracle Database is the only database with embedded multidimensional OLAP, which is fine tuned for adhoc query performance, query scaleability and, most importantly, calculation power. As I stated above it is no longer about simply what is happened in the last trading period, BI analysis is now all about comparisons, trends and KPIs (calculations) at aggregate levels with the ability to drill right through to the lowest level of detail.

As the article quite clearly acknowledges there is a trend to opening up the corporate data warehouse to more and more users, which means query performance and scalability are becoming increasingly important. Only Oracle Database has specific built-in optimisations, such as OLAP and data mining, to meet these growing requirements.

For those of you new to Oracle OLAP Option and Oracle Data Warehousing you can get more information from these links:

Oracle OLAP Option on OTN
Oracle OLAP Option Forum on OTN
Oracle OLAP Option Wiki
Oracle OLAP Option on Oracle.com
Oracle Data Warehousing on OTN
Oracle Data Warehousing on Oracle.com
Optimised Warehouse Initiative

Best Practice Tips : SQL Access to Oracle DB Multidimensional AW Cubes (#2)

2008-06-02T03:34:00.000-07:00

This post is the second in a series that I will use to share some general best practice tips to get the most out of this feature, so that you can deliver even better solutions to your business end-users:

Best Practice Tip #2: General AW Object Naming Conventions for dimensions, levels, hierarchies and attributes…(Oracle Database 10g and 11g)

The following advice will result in much easier to understand and use relational views over your AW. It makes the implementation much cleaner to visualise, and easier for other users to understand what they are looking at. It also saves a lot of typing for developers that are writing their own SQL queries!

The objective is to ensure that the generated column names in your views are easy to read, and also to avoid the possibility that generated column names may get truncated to fit within the limits for a column name in Oracle Database (when that happens your views get really ugly really quickly). Finally, it has the additional desirable side effect of making it easier and therefore quicker to do the mappings in AWM because the screens are less cluttered with long-winded object names!

Note: this advice follows both for Oracle Database 10g OLAP (eg views created by the AWM10g View Generator Plug-in) and for Oracle Database 11g OLAP, where views are auto-generated (eg when creating your Standard From AW via AWM11g).

Here is the idea:

Keep the names used for dimensions, levels, hierarchies, and attributes as short as possible, while still meaningful of course.
If possible (simply for readability in the resulting relational view and column names), avoid the use of the "_" char especially for dimension, hierarchy, level and attribute names.
If possible (also recommended if Oracle OLAP API clients such as OracleBI Spreadsheet Add-in , OracleBI Discoverer Plus OLAP and OracleBI Beans will be used on the same AW), create the AW in its own schema.

Don't be seduced into thinking it is a good idea to put "DIM" in the name of everything that is a dimension, or "ATT" into the name of all the attributes. You don't need to do this. The AW knows what objects are what, and you can very simply query the AW if you need, for example, to find out the names of all the Dimensions in an AW. (Another topic for another day is to walk thru all the Data Dictionary stuff that helps with this).

In other words: If you have a Product Dimension, it is self-evidently a dimension, so clogging up its name with "_DIM" or "_DIMENSION" is just extra wear and tear on your keyboard!

Example:

To illustrate the impact this advice can have, here are two Product Dimensions, which apart from the fact one follows best practice advice and one does not, are identical (example is from Oracle Database 11g AW) (you can click on the picture to see it full size):

First – two ways I could have created my Product Dimension:

Second – what the resulting dimension views for the Main hierarchy would look like in each case:

Third – how much harder it is to read and write the SQL to query the AW’s dimension as a result:

Which of these functionally identical examples is easier to read, easier to understand and easier to query?

I rest my case. Giving a bit of thought to the way you build your AW before you build it nearly always pays dividends later.

Best Practice Tips : SQL Access to Oracle DB Multidimensional AW Cubes (#1)

2008-05-31T11:48:00.000-07:00

One of the most useful features introduced with Oracle Database OLAP is the ability for the powerful multidimensional calculation engine and the performance benefits of true multidimensional storage in the Analytic Workspace (AW), to be accessed and leveraged by simple SQL queries.

This single feature dramatically increases the reach and applicability of multidimensional OLAP – to a vast range of BI query and reporting tools, and SQL-based custom applications – BI and operational – that can now benefit from the superior performance, scalability and functionality of a first class multidimensional server, but combined within the Oracle Database with all the other advantages that derive from that. Bottom line: if you have a tool or application that can (a) connect to an Oracle Database instance, and (b) fire simple SQL at that Database, then you can get benefit from the AWs in that tool or application.

This post is the first of a series that I will use to share some general best practice tips to get the most out of this feature, so that you can deliver even better solutions to your business end-users.

If any of you have tips and advice of your own that we can share, please contact us – we’ll be happy to publish your good ideas and experience with this feature of Oracle Database OLAP.

Anyway. Enough pre-amble. Let’s get on with it. Here goes:

Best Practice Tip #1: Creating your views (Oracle Database 10g and 11g)

Basically the first tip in the series boils down to two things:

1) Always build your AWs to Oracle Database OLAP ‘Standard Form’. This is what happens if you build them with AWM, OWB (10g-only at the time of this post, but support for 11g target AWs is due in OWB very soon), or the supplied AW API if you need to programmatically build and maintain your AW.
2) Use the free-ware “View Generator” plug in for AWM10g to build your 10g views, and leverage the automatically generated views in 11g, unless you have a very good reason not to.

Together, if you follow this advice you will save a lot of time on your project, and also increase your ability to support the application going forward. And it will be a lot easier for others (such as Oracle Support, or your local friendly Oracle OLAP Consultant) to help you if you have any problems.

More detail:

In Oracle Database 10g, there is nothing to stop you coding your own views using the SQL OLAP_TABLE() function. And, if you have an entirely custom built AW this is pretty much your only option. However, if you have developed your AW to Oracle’s OLAP Standard Form specification you can save yourself the time, by using a handy dandy little plug-in for AWM10g. The plug-in is free shareware for AWM10gR2 & can be downloaded from here, with the associated ReadMe here.

The plug in steps you thru a simple wizard within AWM, allowing you to choose which measures etc you need, and then creates the views for you (storing the biggest lump of syntax – the ‘limitmap’ parameter which describes which AW objects show up in what columns in your view – inside the AW itself, in a multi-line text variable/measure).

In Oracle Database 11g, while OLAP_TABLE() is still available for you to use if you like (and sometimes it is perfect for your needs as it has lots of very clever hooks by which you can trigger various OLAP actions whenever a user selects from the view), for most cases, the new CUBE_TABLE() function added in Database 11g is much easier and therefore recommended.

CUBE_TABLE() views are what AWM11g automatically creates for you when defining the objects inside the AW. Assuming you have a valid Standard Form 11g Database AW, such as you might build in AWM11g, CUBE_TABLE() is much, much easier to use than OLAP_TABLE().

For example, the entire syntax required to create a Dimension View, for a specified hierarchy of that Dimension in an AW (not that I even have to type any of this in, as the AWM tool does it automatically for me) is as follows:

CREATE OR REPLACE FORCE VIEW MYDIM_MYHIER_VIEW AS
SELECT *
FROM TABLE( CUBE_TABLE('MYSCHEMA.MYDIM;MYHIER') );

How easy is that?!

All you need to know about your AW is the name of the Hierarchy (MYHIER), Dimension (MYDIM) and schema that the AW is built in (MYSCHEMA). All the object mappings that you have to tell OLAP_TABLE about, in the limitmap parameter, are automatically done as a result of improvements in Database 11g’s Data Dictionary (which is now fully aware of the details of the contents of the AW).

Here (below) is what an example Product Dimension looks like in AWM11g, and the resulting View:

Note that the OLAP Option only allows one Dimension or Cube (and therefore Dimension View, or Cube View) of a given name in each SCHEMA. For this reason, it is our recommendation that each AW be built in its own schema if possible. This will allow you, if you ever need to, to have a PROD dimension or SALES Cube in more that one unrelated AW. This tip will be included again, in an upcoming Post on Best Practice AW Design practices, and naming conventions.

Tuning Guidance for OLAP 10g

2008-04-21T22:00:00.000-07:00

My assumption with this posting is: you are familiar with all the basic OLAP terms such as, dimensions, levels, hierarchies, attributes, measures, cubes, etc. If this is not the case then go to the Oracle Wiki and checkout these links:

http://wiki.oracle.com/page/Oracle+Olap+Option

Terminology - Key Concepts and Terms
Oracle OLAP How To - Performance Tuning and more ...
Script Samples - for DBAs managing the OLAP option.
Did You Know? - that in the Oracle OLAP option you can.
Diagnostic Techniques - for those using the OLAP option.
RAC & GRID - for those using OLAP option with RAC or GRID

Most people when they approach OLAP for the first time, create a data model that either takes too long to build or too long to query. The “too long to query” is usually the first problem to arise and in trying to solve this issue they create the second problem “too long to build”. There is a balance that needs to be achieved when designing OLAP data models. That balance is between pre-solving every level across all dimensions, which increases build time, and providing users with fast query performance. Most people assume there is a direct relationship between the number of levels that are pre-solved and query performance. As one goes up so does the other: pre-solve more levels and query performance improves. Therefore, the answer to poor query performance is to pre-solve all levels across all dimensions correct? Yes and no. Most systems do not have an infinite window for building cubes. Fortunately, using Oracle OLAP Option it is possible to balance the amount of time taken to build a cube and still ensure excellent query performance. How is this achieved?

Oracle OLAP is the most powerful and scalable OLAP server on the market. Because OLAP is inside the database it inherits all the native scalability, security and performance of the Oracle database and it is because the database is so fast and scalable there is a tendency to ignore certain design principles when building an OLAP data model. If the original design and methodology is sound then tuning is very quick and easy to manage. But there is no silver bullet to make OLAP go faster, as one of our OLAP gurus states: there is no ”_OLAP_TURBO_MODE=YES” setting for the init.ora.

What follows is a series of recommendations and observations based on my experience on various OLAP projects to help optimize OLAP builds. This is not the authoritative guide to tuning OLAP data models; just my thoughts.

When asked to tune an existing OLAP data model I break the work up in to five sections:

Tuning a data load process needs to be done in a step-by-step process. Trying to rush things and changing too many settings at once can simply create more problems than it solves. It is also important to start at the beginning with the hardware and lastly look at the database instance itself. Most DBAs will be tempted to rip open the init.ora file and start tweaking parameters in the hope of making the build run faster.

However, the area that is likely to have the biggest impact is refining (or possibly even changing) the implementation of the logical model. But when making changes that improve the build performance you should also check the impact on query performance to ensure the amount of time taken to return a query is still within acceptable limits.

Below are the steps I use when I am asked to analyse the build performance on an OLAP schema. But before you start a tuning exercise, I would recommend reading the 2-Day Performance Tuning Guide that is now part of the database documentation suite. It provides a lot of useful information. It is available as an HTML document and PDF document. The PDF document can be downloaded and stored on your laptop/memory stick etc for easy reference.

Part 1 - Analysis of Hardware
In any situation the first challenge in a tuning exercise is to ensure the foundation for the whole solution is solid. This tends to be the biggest challenge because it can involve a working with a number of hardware and software vendors. Trying to make sure your environment is based on an adequate configuration can be time consuming and risky, and will probably end in a compromise between performance, scalability, manageability, reliability, and naturally price.

Configurations can be difficult to analyse and most of the time. This analysis typically tends to degenerate into each vendor in the hardware stack blaming the other vendor and/or the database.

Step 1 – Check Patches
When analysing an existing environment make sure all the latest firmware, drivers and O/S patches have been applied. Refer to the Oracle database installation guide, Metalink, and the hardware vendors web sites for more details.

Step 2 – Determine Workload
In a good environment you should be expecting to load about 1 million rows per minute via OLAP. This is the benchmark. Check the XML_LOAD_LOG table from previous builds to determine if this is being achieved. Here is a log from a data load for the Common Schema AW based on a relatively simple view that joins two fact tables together to load three measures. Approximately 900,000 records are loaded in 57 seconds.

In this case, we could conclude this is a reasonable starting point to begin the next phase of the tuning exercise. However, don’t forget the performance initially listed in XML_LOAD_LOG could be influenced by a number of factors, but if the data source is a table or a very simple view, then 1 million rows a minute should be achievable. Anything less tends to indicate some sort of I/O issue, or possibly the use of inefficient SQL to extract data from the source. The ADDM analysis of I/O performance partially depends on a single argument, DBIO_EXPECTED, that describes the expected performance of the I/O subsystem. The value of DBIO_EXPECTED is the average time it takes to read a single database block in microseconds. Oracle uses the default value of 10 milliseconds, which is an appropriate value for most modern hard drives. If your hardware is significantly different, such as very old hardware or very fast RAM disks, consider using a different value. To determine the correct setting for DBIO_EXPECTED parameter, perform the following steps:

Measure the average read time of a single database block read for your hardware. Note that this measurement is for random I/O, which includes seek time if you use standard hard drives. Typical values for hard drives are between 5000 and 20000 microseconds.
Set the value one time for all subsequent ADDM executions. For example, if the measured value if 8000 microseconds, you should execute the following command as SYS user:
EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER( 'ADDM', 'DBIO_EXPECTED', 8000);

Also review the Performance Tuning Guide, Chapter 8 : I/O Configuration and Design. Specifically, review these two sections:

Parallel vs Serial Processing
As part of this step some consideration needs to given to parallel vs serial processing. I find most people will start by running a build in serial mode and then assume if it takes X amount of time to process in serial mode, running in parallel mode will naturally take X/No of parallel jobs. Of course this is true up to a point. There is a definite tipping point in parallel processing where the law of diminishing returns sets in very quickly. As a starting point if I am going to process a load in parallel I will start by using a job queue = “No. of CPUs-1”. This is usually a good starting point and depending on where the bottlenecks start appearing (CPU waits vs I/O waits) I may increase or decrease this figure during testing.

Parallel processing is a very useful tool for improving performance but you need to use partitioned cubes and the data being load must map across multiple partition keys to result in parallel processing. As before, this is not a silver bullet that will simply make everything run faster. It needs to be used carefully.

Step 3 – Determine Best Reference Configurations
What can be useful is to work from a set of known configurations designed to provide a stated level of performance. Oracle has worked with a number of hardware vendors to provide documented configurations for data warehouse solutions. These configurations can be used as benchmarks and/or recommendations for your environment.

Each configuration combines software, hardware, storage, I/O and networking into an optimized environment for different scales of customer data warehouse requirements. Using extensive customer experience and technical knowledge, Oracle and its hardware partners have developed configurations for data warehouses with varying raw data sizes, concurrent user population and workload complexity. By offering customers reference configurations suited for different profiles, customers can select the one that best suits their business and price, performance requirements. And since they're built on scalable, modular components, these reference configurations enable customers to aggressively pursue incremental data warehouse growth.

One of the key questions for the performance tuning exercise is: Are you just tuning the model based on today’s data volumes or should the exercise look to maximise performance of future load volumes. This is a very tricky area to manage and difficult to plan and test. Which is why having a referenceable configuration is so important. Why? Because the reference configurations provide a clear upgrade path and levels of performance are certified along that upgrade path.

Step 4 – Match/Compare/Contrast with Existing Configurations
In reality altering your hardware configuration is going to be a given. To change a configuration is likely to be a costly and time-consuming exercise. However, it should not be ignored. If you have followed and tuned your data model based on the following recommendations and the load and aggregation phase is still too long then a full hardware review may in fact be needed and upgrades may need to be purchased. Hopefully, the next result of this whole exercise is to provide some sort of cost/benefit report to outline expected performance improvements based on additional hardware costs.

Part 2 - Analysis of Dimensions
The second stage is to review the logical model for the dimensions. This stage is largely to confirm the dimensions are correctly implemented and the source data is of good quality. Most of this analysis is really just making sure there are no big issues within the various dimensions and possibly making small changes based on experience from various projects. But it is important to ensure the dimensions are of “good quality” before moving on to review the cubes – there is no point building a house on a sand bank and then wondering why it gets washed away (if that makes sense?). Good foundations are needed.

Step 1 - Analysis of Attributes
Many customers implement dimensions when they only really need attributes. This usually happens when they are migrating from a legacy OLAP server to Oracle OLAP. We have a customer at the moment that has an existing OLAP data model in a legacy OLAP server based on 60 dimensions. Reviewing the queries the users make against the data model it became clear that many of these dimensions were in fact simple attributes within Oracle OLAP. This can have a significant impact on the design of related cubes and the whole loading process, since fewer dimensions within a cube will improve both load and aggregation times.

Can Oracle OLAP support extremely large dimensional models? Yes it can. The engine will support up to 256 dimensions within a single cube and within the AW you can have as many dimensions as you need. The key point here is: each cube can have its own dimensionality. Oracle OLAP does not implement hyper-cubes where every cube has to share the same dimensionality – one of the key benefits of Oracle OLAP over other legacy OLAP engines is that it does support cubes of different dimensionality.

There is an excellent customer case study in the December 07 OLAP Newsletter that examines how one customer managed a very large data models based on lots of dimensions. The E.ON data model contains multiple cubes (subject areas) each with between 6 to 12 dimensions. The cubes are updated weekly with many millions of rows loaded and aggregated, with about 1 million rows updated in the cube per minute. For more information, read the complete review by clicking here. There are many other customers with even bigger models.

From a UI perspective you need to think very carefully about the number of dimensions within a cube. Many UI studies have shown business users find it increasingly difficult to interpret the results from a dataset where there are more than nine dimensions. Although, as the above case shows, it is possible for some users to interact with larger more complex models providing the information is presented in usable format, it is worth spending some time clarifying the exact dimensionality of each cube.

If you think about a typical crosstab layout, a nine dimensional cube results in one row edge dimension, one column edge dimension and seven page edge dimensions plus the measure dimension. That is a huge amount of information to absorb and, in my opinion, makes constructing queries very difficult. Another, issue that frequently occurs, as the number of dimensions increases, is the game of “hunt-the-data”. Even with only nine dimensions in a cube it is likely the data set will be extremely sparse and drilling down only one or two levels across a couple of dimensions can result in crosstabs with little or no data. Some BI tools try to mask this problem by providing an NA and/or Zero row filters. The net result is usually a “no rows returned” message appearing in the body of the report at regular intervals.

My main recommendation is: Check the number of dimensions in your model and for the sake of your users and try to keep the number within each cube down to something intelligible. For example approximately nine – this is not a hard and fast rule; just a recommendation but do read the E.ON case study as well. If you are presented with a data model, do not be afraid to challenge the dimensionality of the cubes within the model. Make sure all the dimensions within a cube are really required because I can guarantee some are simply basic attributes.

Step 2 - Analysis of Level Keys
There is not much that needs to be done here except to make sure to use surrogate keys except when you are certain the dimension keys are unique across all levels. This is not always the case and using a surrogate key is a good way to ensure your hierarchies are correctly populated. OLAP creates a surrogate key by prefixing the original source key with the level identifier. Therefore, from a storage perspective it makes sense to make the level key as short as possible. For example, don’t create a level identifier such as “PRODUCT_SUB_CATEGORY_SKU_IDENTIFIER”. There is a limit of 30 characters for level names. In practice I have seen issues with both data loading and aggregation where very large dimension keys (i.e. greater than 400 characters) have been created.

In practice I recommend using simple level identifiers such as L1, L2, L3 although this does make writing SQL statements a little more challenging via the SQL Views as the level identifier is used in the column name along with the dimension and it is not exactly obvious what each column contains when they are called PODUCT_L1, PRODUCT_L2 etc.

Surrogate vs Natural Keys
The use of surrogate keys is an interesting area. During some projects it has been found that by not using surrogate keys build performance has increased. This does make sense since the source data for cube will have to be reformatted at load time to ensure the key is valid. In some cases the amount of time required to manipulate the incoming key values may be minimal. In other cases it has had a significant impact on load performance – the “1 million rows a minute” benchmark was not achieved and reverting to natural keys did improve load times. If the data source can be guaranteed to provide unique keys across all levels it is probably worth switching to natural keys. But be warned – you cannot switch between using surrogate and natural keys if the cube already contains data.

Step 3 - Quantitative Analysis of Members
This is an important step as it will allow us to determine which levels to pre-aggregate within the cube. In most cases the default skip level approach to pre-solving levels within a cube is a reasonable starting point. But it is possible to design a much better model by analysing the number of members at each level and the average number of children for each level.

Lets look at two real customer examples:

Dimension 1

A change as simple as this could have a huge impact on the amount of time taken to aggregate a cube.

However, in some cases the OLAP Compression feature can be useful in terms of allowing you to pre-compute additional lower levels within a hierarchy for little or no additional cost because the sparsity of the data allows higher levels to compressed out of the cube. If you have a situation where there is almost a 1:1 relationship between a level and the next level down in the hierarchy it would make sense to pre-compute that level since the compression feature will compress out the redundant data. For example:

Dimension 2

In this example the hierarchy is relatively flat and the number of children returned at each level varies quite a lot. But at the lowest levels, there is likely to be a large number of instances where a parent only has a single child and in these situations the compress feature can compress out the repeated values. Therefore, it might make sense to solve levels L5 and L4.

Dimension 3

In this example the hierarchy here shows the normal pyramid approach and is definitely bottom heavy. But the upper levels contain relatively few members and drilling typically returns very few members. The default skip level approach for this dimension may in fact be pre-solving too many levels. In practice it may take 2 or 3 builds to determine which are the best levels to pre-solve, with a good starting point being:

Run 1: L7, L5, L2
Run 2: L7, L6, L1
Run 3: L7, L4, L2

This dimension shows that it may be necessary to schedule multiple runs to test these various scenarios. Again we need to consider the impact of using compression, which allows OLAP to solve additional levels very cheaply.

Step 4a - Hierarchy Validation
Always, always check your hierarchies are functioning correctly. This involves using the Data Viewer feature within AWM. You should make sure the dimension is drillable and that selecting each level in turn returns the correct result-set.

A better approach is actually to make the database do the work, but this requires some additional SQL commands to be executed against the source tables. Ideally, try and create a relational dimension over the source table(s). Normally, the relational dimension object is used within query rewrite, which in this case we are not really concerned with for 10gR2 (in 11g the story is quite different as a cube can be registered as a materialised view and used for query-rewrite). But this does allow us to use the dbms_dimension.validate_dimension procedure verifies that the relationships specified in a dimension are valid. The rowid for any row that is found to be invalid will be stored in the table DIMENSION_EXCEPTIONS in the user's schema. The procedure looks like this:

DBMS_DIMENSION.VALIDATE_DIMENSION (
dimension IN VARCHAR2,
incremental IN BOOLEAN := TRUE,
check_nulls IN BOOLEAN := FALSE,
statement_id IN VARCHAR2 := NULL );

Note that before running the VALIDATE_DIMENSION procedure, you need to create a local table, DIMENSION_EXCEPTIONS, by running the provided script utldim.sql. If the VALIDATE_DIMENSION procedure encounters any errors, they are placed in this table. Querying this table will identify the exceptions that were found. To query this table you can use a simple SQL statement such as this:

SELECT * FROM dimension_exceptions
WHERE statement_id = 'Product Validation';

However, rather than query this table, it may be better to query the rowid of the invalid row to retrieve the actual row that has generated the errors. In this example, the dimension PRODUCTS is checking a table called DIM_PRODUCTS. To find any rows responsible for the errors simply link back to the source table using the rowid column to extract the row(s) causing the problem, as in the following:

SELECT * FROM DIM_PRODUCTS
WHERE rowid IN (SELECT bad_rowid
FROM dimension_exceptions
WHERE statement_id = 'Product Validation');

Step 4b - Hierarchy Order
The order of hierarchies within a dimension can have a significant impact on query performance. When solving levels at run-time the OLAP engine will use the last hierarchy in the list as the aggregation path. Consider this example using a time dimension:

Let’s assume we pre-compute the levels Month and Quarter. But decide not to pre-compute the Year level because the main hierarchy used during queries is the Julian Year-Quarter-Month-Day and, therefore, the total for each Year will be derived from adding up just 4 values. In fact, the aggregation engine looks at all the hierarchies to find the lowest common level across all hierarchies, which in this case is Day. It then selects the last hierarchy in the list containing the level Day, in this case the Week hierarchy. Therefore, the value for the each dimension member at the Year level will be the result of adding up 365/366 values and not simply 4 Quarter values.

The obvious question is why? The answer is to ensure backward compatibility with the Express ROLLUP command from which the AGGREGATE command is derived. When the Aggregate command was introduced one of our requirements was that it produced numbers that matched those of Rollup, thus in cases where an aggregate node was declared in multiple hierarchies we always produced numbers based on the LAST definition of the node because that would be the number that matched the procedural approach taken by rollup. Because of this feature an alternative approach to hierarchy ordering might be as follows:

Now the run-time aggregation for Year will be derived from the level Quarter, which has been pre-computed, and the result will be returned much faster.

Step 5 - Check the Data Quality
This last step is probably the most important, especially as OLAP style projects tend to be scheduled once all the ETL has been completed. But you should never take the quality of the source data for granted. Ideally you can use the Data Quality option of Warehouse Builder (which is a costed option for OWB) and analyse the source data for each dimension to make sure the data is of a reasonable quality. Things to check are:

Consistent data type
Number distinct values
Min and Max values
Domain members
Number of members not present in the fact table

OLAP stores all members as data type text. Even if there are inconsistent data types within the source data, everything gets converted to text. This can mask some issues where unusual dimension members are included in the source data such as –9999, or XXXX. In many cases the data owners are completely unaware these values exist, or, worse still – they are included to allow the data to balance correctly and used as journal buckets. It may not be possible to remove these values but it is important to know they exist and equally important to clarify if they are in fact needed.

The last one is an interesting check especially if you are using that dimension as a partition key. If you are creating lots and lots of empty partitions that will never contain data then should those members even be loaded? In a recent project I identified a dimension that contained over 300,000 leaf node members, but in the main fact table there was only data for 50% of those members. The obvious question is why load 150,000 plus members if you are never going to post data to them.

Part 3 - Analysis of Cubes
The next stage is to review the data model for each cube in turn. It is in this area the biggest impacts on load time are likely to be achieved.

Step 1 - Analysis of Storage Model
It is important to assign an efficient storage model to a cube, as this will have a significant impact on both the load and aggregation times.

Make sure compression is enabled.
Data type should be either DECIMAL or INTGER

Warning do not use NUMBER as this uses approximately 3.5 times the storage compared to DECIMAL, but it is the default. Number requires 22 bytes and Decimal requires 8 bytes (See OLAP Application Developers Guider, 10.2.0.3, Chapter 7 Aggregating Data).

Try not to use Global Composites. There is little need to use this feature, except in very special cases where you need to optimise the retrieval of rows via SQL access and you want to only report non-NA and/or non-zero rows. Note – if you are using compression it is not possible to use the “Global Composites” feature even though in AWM10gR2 the option box is still enabled even after you select to use compression. (In 11g there are database events you can use to optimise the retrieval of non-NA/zero rows. See the posting by Bud Endress on the OLAP Blog: Attribute Reporting on the Cube using SQL)

Step 2 - Analysis of Sparsity Model
Management of sparsity within a cube is critical. Firstly the order of the dimensions is very important. When using compression, which should always be enabled, dimensions should be ordered with the dimension with the least number of members first and the dimension with the most number of members last. The most common question is: Should time be dense or sparse?

Answer – it depends. This is where you need to have a deep understanding of the source data and the data quality features in OWB can really help in this type of situation. In some models time works best dense and in other models time works best when it is sparse. This is especially true when time is used as the partition dimension. Therefore, you need to plan for testing these different scenarios.

There is sparsity advisor package in the database, which analyses the source data in relational tables and recommends a storage method. The recommendations may include the definition of a composite and partitioning of the data variable. The Sparsity Advisor consists of these procedures and functions:

SPARSITY_ADVICE_TABLE Procedure
ADD_DIMENSION_SOURCE Procedure
ADVISE_SPARSITY Procedure
ADVISE_DIMENSIONALITY Function
ADVISE_DIMENSIONALITY Procedure

The Sparsity Advisor also provides a public table type for storing information about the dimensions of the facts being analyzed. I have to say this is not the friendliest package ever shipped with the database, but it can be useful in some situations. To use the Sparsity Advisor you need to follow these five steps:

Call SPARSITY_ADVICE_TABLE to create a table for storing the evaluation of the Sparsity Advisor.
Call ADD_DIMENSION_SOURCE for each dimension related by one or more columns to the fact table being evaluated. The information that you provide about these dimensions is stored in a DBMS_AW$_DIMENSION_SOURCES_T variable.
Call ADVISE_SPARSITY to evaluate the fact table. Its recommendations are stored in the table created by SPARSITY_ADVICE_TABLE. You can use these recommendations to make your own judgements about defining variables in your analytic workspace, or you can continue with the following step.
Call the ADVISE_DIMENSIONALITY procedure to get the OLAP DML object definitions for the recommended composite, partitioning, and variable definitions, or
Use the ADVISE_DIMENSIONALITY function to get the OLAP DML object definition for the recommended composite and the dimension order for the variable definitions for a specific partition.

The OLAP Reference manual provides an example script for the GLOBAL demo schema to analyse the relational fact table. The amount of information required does seem a little excessive given that most of it could be extracted from the various metadata layers – may be some bright person will create a wrapper around this to simplify the whole process.

On the whole I still find the majority of models work best with everything sparse and to far I have only found a few cases where load and aggregation times improved when time was marked dense. But as with all tuning exercises, it is always worth trying different options, as there is no “fits-all” tuning solution with OLAP.

Step 3 - Analysis of Partition Model
Partitioning is managed at both the logical and physical levels. At the logical level, it is possible to partition a cube using a specific level to split the cube into multiple chunks. At the physical level, it is possible to partition the actual AW$ table and associated indexes that form the AW.

Logical Partitioning
Always start by use partitioning. Why? Because partitioning allows the cube to be broken down into smaller segments – much like relational table partitioning. This can help improve the aggregation phase of a build because the engine is able to load more related data into memory during processing. It also allows you to use the parallel update features of Oracle OLAP during a build. But there are some things to consider when setting up partitioning. When using partitioning you should:

Try to select a dimension that has balanced partitions, such as Time
Try to select a dimension level that is not too volatile, this is one of the reasons for electing to use a dimension such as time.
Select the Level based on the information collected during Step 3 of the analysis of dimensions. In 10g, the levels above the partition key are solved at run time (this is resolved in 11g) so select the level for the partition key carefully.

When selecting the partition key consider the impact this will have on the default partition, which contains all the levels above the partition level. For example partitioning on a level such as Day might generate nice small partitions but the default partition will contain all the other members such as Week, Month, Quarter and Year making the default partition very large.

It might be necessary to experiment with different partition keys to get the right balance between stored and run time aggregation. For example, if you partition using a Time dimension then Month is usually a good level to select as the key since each year only needs to aggregate 12 members to return a total, but if you have 30 years of data and most reports start at the year level displaying all 30 years the run time performance might not be acceptable. In this case the level Quarter or even Year might be a better option.

Parallel vs Serial Processing
Logical partitioning is required for cubes where you want to enable parallel processing. But be warned, running a job in parallel may not improve processing times. In fact using too many parallel processes can have the opposite affect. But used wisely, parallel processing can drastically improve processing times provided the server is not already CPU bound. As a starting point I always begin testing by setting the value MaxJobQueues to “No. of CPUs-1” in the XML file for the definition of the build. In some cases even this might be too high and reducing this figure can actually improve processing times. Tuning AW parallel processing is exactly the same as tuning relational parallel processing – you need to determine where the point of diminishing returns sets in, which can be a combination:

CPU loading
I/O bandwidth
Cube design

Do not assume throwing parallel resources at a performance issue will resolve the whole problem. Managed carefully this can provide a significant improvement in peformance.

Physical Partitioning
The aim of relational (physical) partitioning is to allow you to control the tablespace for each partition thus distributing the load across multiple disks, to spread data across a variety of disk types (see information on ILM on OTN) and to enhance query performance since it is possible to direct specific queries to a smaller subset of data.

Some, but not all of this applies to an AW. From a tablespace perspective it is probably easier to use ASM to manage and distribute the storage of an AW across multiple disks as opposed to creating a partitioned AW$ table spread across multiple tablespaces. The reason the AW$ table is partitioned is optimise the lob performance. Each partition has its own lob index, which manages its storage, and a separate slave process can update each partition.

The relational table that acts as a container for the AW, AW$xxxxx , can be partitioned to break the AW into more physical chunks which can reduce contention for locks on the relational objects (AW$ table and related indexes) during parallel data loading jobs. By default each AW is created using a range partition key of gen# and 8 subpartitions. The DDL below is from a default AW created via AWM. Note the clauses to manage the partition and sub-partitions:

PARTITION BY RANGE ("GEN#")
SUBPARTITION BY HASH ("PS#","EXTNUM")
SUBPARTITIONS 8

CREATE TABLE "BI_OLAP"."AW$SH_AW"
("PS#" NUMBER(10,0),
"GEN#" NUMBER(10,0),
"EXTNUM" NUMBER(8,0),
"AWLOB" BLOB,
"OBJNAME" VARCHAR2(256 BYTE),
"PARTNAME" VARCHAR2(256 BYTE))
PCTFREE 10 PCTUSED 40 INITRANS 4 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "BI_OLAP"
LOB ("AWLOB") STORE AS (
DISABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 0
CACHE
STORAGE(
BUFFER_POOL DEFAULT))
PARTITION BY RANGE ("GEN#")
SUBPARTITION BY HASH ("PS#","EXTNUM")
SUBPARTITIONS 8
(PARTITION "PTN1" VALUES LESS THAN (1)
PCTFREE 10 PCTUSED 40 INITRANS 4 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "BI_OLAP"
LOB ("AWLOB") STORE AS (
DISABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 0
CACHE READS LOGGING
STORAGE(
BUFFER_POOL DEFAULT))
( SUBPARTITION "SYS_SUBP16109"
LOB ("AWLOB") STORE AS (
TABLESPACE "BI_OLAP" )
TABLESPACE "BI_OLAP",
SUBPARTITION "SYS_SUBP16110"
LOB ("AWLOB") STORE AS (
TABLESPACE "BI_OLAP" )
TABLESPACE "BI_OLAP",
SUBPARTITION "SYS_SUBP16111"
LOB ("AWLOB") STORE AS (
TABLESPACE "BI_OLAP" )
TABLESPACE "BI_OLAP",
SUBPARTITION "SYS_SUBP16112"
LOB ("AWLOB") STORE AS (
TABLESPACE "BI_OLAP" )
TABLESPACE "BI_OLAP",
SUBPARTITION "SYS_SUBP16113"
LOB ("AWLOB") STORE AS (
TABLESPACE "BI_OLAP" )
TABLESPACE "BI_OLAP",
SUBPARTITION "SYS_SUBP16114"
LOB ("AWLOB") STORE AS (
TABLESPACE "BI_OLAP" )
TABLESPACE "BI_OLAP",
SUBPARTITION "SYS_SUBP16115"
LOB ("AWLOB") STORE AS (
TABLESPACE "BI_OLAP" )
TABLESPACE "BI_OLAP",
SUBPARTITION "SYS_SUBP16116"
LOB ("AWLOB") STORE AS (
TABLESPACE "BI_OLAP" )
TABLESPACE "BI_OLAP") ,
PARTITION "PTNN" VALUES LESS THAN (MAXVALUE)
PCTFREE 10 PCTUSED 40 INITRANS 4 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "BI_OLAP"
LOB ("AWLOB") STORE AS (
DISABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 0
CACHE
STORAGE(
BUFFER_POOL DEFAULT))
( SUBPARTITION "SYS_SUBP16117"
LOB ("AWLOB") STORE AS (
TABLESPACE "BI_OLAP" )
TABLESPACE "BI_OLAP",
SUBPARTITION "SYS_SUBP16118"
LOB ("AWLOB") STORE AS (
TABLESPACE "BI_OLAP" )
TABLESPACE "BI_OLAP",
SUBPARTITION "SYS_SUBP16119"
LOB ("AWLOB") STORE AS (
TABLESPACE "BI_OLAP" )
TABLESPACE "BI_OLAP",
SUBPARTITION "SYS_SUBP16120"
LOB ("AWLOB") STORE AS (
TABLESPACE "BI_OLAP" )
TABLESPACE "BI_OLAP",
SUBPARTITION "SYS_SUBP16121"
LOB ("AWLOB") STORE AS (
TABLESPACE "BI_OLAP" )
TABLESPACE "BI_OLAP",
SUBPARTITION "SYS_SUBP16122"
LOB ("AWLOB") STORE AS (
TABLESPACE "BI_OLAP" )
TABLESPACE "BI_OLAP",
SUBPARTITION "SYS_SUBP16123"
LOB ("AWLOB") STORE AS (
TABLESPACE "BI_OLAP" )
TABLESPACE "BI_OLAP",
SUBPARTITION "SYS_SUBP16124"
LOB ("AWLOB") STORE AS (
TABLESPACE "BI_OLAP" )
TABLESPACE "BI_OLAP") ) ;

The best overall approach here is to ensure you have the correct number of sub-partitions to reduce contention during updates. For example, if you have a cube with three years of data partitioned using the level month, it would be sensible to add and additional 36 subpartitions to the AW$ table to spread the load and reduce contention during parallel updates. You can add more sub-partitions quickly and easily as follows.

alter table aw$test modify partition ptnn add subpartition ptnn_009 update indexes;
alter table aw$test modify partition ptnn add subpartition ptnn_010 update indexes;

Therefore, I recommend adding additional subpartitions at the physical level to match the number of logical partitions within the cube.

It is possible to go to the next level (if you really feel it is necessary) and directly manage the DDL used to create the AW and there are a number of commands that allow you to control the default tablespace and the number of partitions. You can increase the number of sub-partitions within each gen# partition using either the ‘aw create command’ as shown here:

exec dbms_aw.execute('aw create owner.aw_name partitions N segmentsize N K|M|G');

Note the key word “partitions” actually refers to the number of subpartitions.
It is in fact possible to define a target tablespace for the AW via the DBMS_AW.ATTACH procedure:

DBMS_AW.AW_ATTACH (
awname IN VARCHAR2,
forwrite IN BOOLEAN DEFAULT FALSE,
createaw IN BOOLEAN DEFAULT FALSE,
attargs IN VARCHAR2 DEFAULT NULL,
tablespace IN VARCHAR2 DEFAULT NULL);

For example, the following SQL statement creates the AW GLOBAL_PROGRAMS as the last user-owned analytic workspace in tablespace USERS:

SQL>EXECUTE DBMS_AW.AW_ATTACH('global_programs', true, true, 'last', ‘USERS’);

AWM 10gR2 (10.2.0.03A) also allows you to define the tablespace when you create the AW, but the tablespace name is not included in the XML definition of the AW.
If you think you need to get right down to the base DDL level to control the allocation of tablespaces used by the AW then you will need to manually define the AW$ table. The easiest method is to create another AW$ table using the DDL from the original AW$ table and modifying it to create your own placement statements for the tablespaces. To get the DDL for an AW (table and index) you can either use SQLDeveloper or use the DBMS_METDATA package as follows:

set heading off;
set echo off;
set pages 999;
set long 90000;
spool aw_ddl.sql
select dbms_metadata.get_ddl('TABLE','AW$SH_AW','SH_OLAP') from dual;
select dbms_metadata.get_ddl('INDEX','SH_AW_I$','SH_OLAP') from dual;
spool off;

These statements show the exact DDL used to generate the AW$ table and its associated index. Once you have the DDL you can then modify the tablespace statements for each sub-partition to spread the loading across different tablespaces and hence data files. But it is much easier to use ASM to manage all this for you.

There is one major issue with manually creating an AW – the standard form metadata is not automatically added to the AW and there is no documented process for achieving this. The only reliable solution I have found is to first create the AW via AWM and then export the empty AW to an EIF file. This EIF file will then contain the standard form metadata objects. Once you have deleted and re-created the AW with the based on your specific tablespace and subpartition requirements the standard form metadata can be added by importing the EIF file. Not the prettiest of solutions but it works – at least with 10gR2.

Step 4 - Analysis of Aggregation Model
The Summarize To Tab
This is where the biggest improvements to build time are likely to be uncovered. The “Summarize To” tab allows you to select the levels to pre-solve. Based on the analysis of the number of members and children at each level it should be possible to tune the levels to pre-solve only the most important levels.

This step will require lots of testing and many builds to finally arrive at the best mix of levels.

The Rules Tab
If you are using the same aggregation method across all dimensions, such as SUM, the aggregation engine will optimise the processing order for the dimensions by solving them in the reverse order from highest cardinality to lowest cardinality. Despite this I always manually order the dimensions myself anyway on the Rules Tab.

Where you are using different aggregation methods across the various dimensions it is important to ensure the dimensions are in the correct order to return the desired result. If you change the order to improve aggregation performance where different aggregation methods are used, check the results returned are still correct. Getting the wrong answer very quickly is not a good result.

Step 5 - Analysis of Data Quality
This is another area that can have a huge impact of load times. There are three key things to consider:

Number of NA cells
Number of Zero cells
Sparsity patterns

Quite often I see situations where hundreds of thousands of either NA or zero values are loaded into a cube and then aggregated. In a recent customer situation, over 40% of the data being loaded was either NA or zero. Removing just those records from the data load saved a huge amount of time both in loading and aggregating that data set. Now in some cases it may in fact be necessary to load a zero balance because the value “0” does actually mean something and having a cell appear as NULL in a report does not infer the same meaning. If this is the case, there are much better ways of managing zero balances than loading and aggregating those balances up across all the various hierarchies to return a value of 0. My recommendation is to remove all zero and NA/null rows from the source fact table.

Where there is a need to show a zero balance, create a separate cube load only the zero balances into that cube but do not aggregate the data. Use a formula to glue the non-zero balance data to the zero balance data, such as:

Nafill(CUBE1_NON_NA_DATA, CUBE2_ZERO_BALANCE_DATA)

This will significantly improve the performance of the main cube since the aggregation engine only has to deal with real balances.

Sparsity patterns are important when you have a cube that contains a large number of measures all sourced from the same fact table. In another situation, a customer had designed two cubes with about 30 measures in one cube and two measures in the other cube. The source fact table contained 75 million rows. The data load was taking about ten hours for just three years of data. Looking at the data and executing various SQL counts to determine the number of NULL cells and Zero cells for each measure, it was clear there were five different sparsity patterns within the fact table.

By breaking the single cube into five different cubes, creating views over the base fact table to only return the relevant columns for each cube and removing all NA and zero values the amount of data being loaded each month declined to the values shown below:

This change combined with changes to the selection of levels pre-aggregated reduced the build and aggregation time by over 50% with little impact on query performance.

It is critical to fully understand the source data and how it is stored. As the number of measures within a cube increases it is likely that the number of times an NA or Zero value appears will also increase. Breaking a large cube up into smaller more focused chunks in this type of scenario can provide significant benefits.

Part 4 - Analysis of Source Schema Queries
When loading data into a cube from a relational source schema you should be able to achieve about 1 million rows updated in the cube per minute. If you are not seeing that level of throughput from the source table/view, you need to look at:

Hardware issues
Cube design issues
Query design issues

The first two issues have already been covered. This area aims to review the tuning of the query fetching the data from the relational source table/view into the cube.

Tuning the queries used to load dimension members and data into cubes can be very important. When either a data load or dimension load is executed a program is created containing the SQL to fetch the data from the relational table. It is important to make sure the SQL being executed is as efficient as possible. By using views as the source for your mappings it is relatively easy to add additional hints to ensure the correct execution path is used. Note - with 11g this can cause problems if the cube is to be exposed as a materialised view. For query re-writes to function the cube must use the underlying fact table that is part of the end-user query.

Step 1 - Analysing SQL Statements
To optimise the SQL executed during a load you have use either, or both, of the approaches:

Enterprise Manager – via Tuning Packs
Manual analysis

If you are comfortable using PL/SQL and understand a little about OLAP DML you can follow the manual approach. However, I expect most people will revert to using Enterprise Manage as it makes the whole process so simple. However, note the Tuning Pack is a costed option for EM so check your license agreement before you start using the Enterprise Manager approach.

Enterprise Manager
Enterprise Manager can be used to monitor the results from a SQL statement. The Performance Tab provides the environment for tuning SQL statements as well as monitoring the operation of the whole instance. The easiest way to find the SQL statement used by the data load process is to search for a SELECT statement against the view/table used in the mapping. The SQL can quite often be found in the “Duplicate SQL” report at the bottom of the Top Activity page:

Once you have found the SQL statement, clicking on the SQL statement listed in the table will present a complete analysis of that statement and allow you to schedule the SQL Tuning Advisor. The output from the Advisor includes recommendations for improving the efficiency of that statement. Below is the analysis of the resources used to execute the product dimension SQL statement:

Scheduling the Advisor
Scheduling the advisor to analyse your SQL statement is very simple. Click on the button in the top right corner of the SQL Details screen. This will launch the SQL Advisor where you need to provide:

A description for the job
Set the scope to either limited or comprehensive (there are on screen notes to help you make this decision)
Time and date to run the Advisor, since it might not be possible to run the advisor immediately.

Once the Advisor has completed its review, it is possible to look at the recommendations that have been generated. After you have implemented the recommendations it is then possible to view the explain plan for your query:

Note: These features are costed extensions to the Enterprise Manager console and cannot be used on a production system unless your customer has bought these extensions.

Manual Tuning
So how do you capture the SQL being executed during a data load? In 10g, during a load process an OLAP DML program is created called '___XML_LOAD_TEMPPRG'.
This program contains the code used during the build process and it is relatively easy to capture this code either via Enterprise Manager or manually at the end of the build.
(For a data load in 11g, look at the CUBE_BUILD_LOG’s “output” column. The table is in the AW’s schema).

To manually capture the program code (in 10g), create your own job to manually execute a data load. For example below is a job to refresh the members in the dimension Products. Note the first three lines and last four lines that control the dumping of the program code so we can capture the SQL.

set serveroutput on
exec dbms_aw.execute('aw attach SH_AW rw first');
exec dbms_aw.execute('cda BI_DIR');

call SQL file to refresh cube

exec dbms_aw.execute('outfile loader.txt');
exec dbms_aw.execute('DSC ___XML_LOAD_TEMPPRG')
exec dbms_aw.execute('outfile eof');
exec dbms_aw.execute('aw detach SH_AW');

The resulting program looks like this, with the SQL code highlighted in bold:

DEFINE ___XML_LOAD_TEMPPRG PROGRAM INTEGER
PROGRAM
variable _errortext text
trap on HADERROR noprint
sql declare c1 cursor for -
select SH.VW_PRODUCTS_DIM.PROD_ID, -
SH.VW_PRODUCTS_DIM.PROD_DESC, -
SH.VW_PRODUCTS_DIM.PROD_DESC, -
SH.VW_PRODUCTS_DIM.PROD_PACK_SIZE, -
SH.VW_PRODUCTS_DIM.PROD_WEIGHT_CLASS, -
SH.VW_PRODUCTS_DIM.PROD_UNIT_OF_MEASURE, -
SH.VW_PRODUCTS_DIM.SUPPLIER_ID -
from SH.VW_PRODUCTS_DIM -
where -
(SH.VW_PRODUCTS_DIM.PROD_ID IS NOT NULL)
sql open c1
if sqlcode ne 0
then do
_errortext = SQLERRM
goto HADERROR
doend
sql import c1 into :MATCHSKIPERR SH_OLAP.SH_AW!PRODUCTS_PRODUCT_SURR -
:SH_OLAP.SH_AW!PRODUCTS_LONG_DESCRIPTION(SH_OLAP.SH_AW!ALL_LANGUAGES 'AMERICAN') -
:SH_OLAP.SH_AW!PRODUCTS_SHORT_DESCRIPTION(SH_OLAP.SH_AW!ALL_LANGUAGES 'AMERICAN') -
:SH_OLAP.SH_AW!PRODUCTS_PACK_SIZE -
:SH_OLAP.SH_AW!PRODUCTS_WEIGHT_CLASS -
:SH_OLAP.SH_AW!PRODUCTS_UNIT_OF_MEASURE -
:SH_OLAP.SH_AW!PRODUCTS_SUPPLIER_ID
if sqlcode lt 0
then do
_errortext = SQLERRM
goto HADERROR
doend
sql close c1
sql cleanup
return 0
HADERROR:
trap on NOERR1 noprint
sql close c1
NOERR1:
trap off
sql cleanup
call __xml_handle_error(_errortext)
END

Once you have the statement you can use SQLDeveloper’s explain plan feature to determine the execution plan. When dealing with cubes, it is likely the source fact table will be partitioned; therefore, you need to ensure partition elimination is occurring correctly.

If additional hints need to be added to make the query more efficient, these can be added to the view definition. This approach does not automatically generate recommendations so you will need to have a solid grasp of SQL tuning to ensure your query is based on the most optimal execution plan.

Step 2 – Managing Sort Resources
Sorting the source data is quite important for both dimensions and facts. By default, OLAP sorts dimensions alphabetically in ascending order based on the long description. Therefore, it makes sense for the relational source to provide the data in the required order, especially for the dimension loads.

Optimising cube loads requires making sure the sorting is based on the same order as the dimensions are listed within the partitioned composites. This will be the same order as shown on the implementation details tab.

OLAP load operations are sort intensive. You may need to increase the sort_area_size setting within the database to try and ensure the various sorting operations during a load are performed in memory and not disk. The default setting is 262,144. As part of a load process you can increase the amount of sort memory available as follows:

exec DBMS_AW.EXECUTE('SortBufferSize=10485760');

Executing this command before starting a data load will increase the amount of resources allocated to memory sorts, in this case providing approximately 10Mb of memory. To permanently set the SortBufferSize to 10Mb, issue the following commands:

exec DBMS_AW.EXECUTE('aw attach my_aw_name rwx');
exec DBMS_AW.EXECUTE('SortBufferSize=10485760');
exec DBMS_AW.EXECUTE('update');
exec DBMS_AW.EXECUTE('commit');
exec DBMS_AW.EXECUTE('aw detach my_aw_name');

Or you can simply set the option before executing the XML job definition:

set serveroutput on
exec dbms_aw.shutdown;
exec dbms_aw.startup;
exec dbms_aw.execute('aw attach SH_AW rw first');
exec DBMS_AW.EXECUTE('SortBufferSize=10485760');

call SQL file to refresh cube

exec DBMS_AW.EXECUTE('SortBufferSize=262144');
exec dbms_aw.execute('update;commit');
exec dbms_aw.execute('aw detach SH_AW');
exec dbms_aw.shutdown;
exec dbms_session.free_unused_user_memory;

For more information on this subject area refer to the next session on monitoring system resources.

Part 5 - Analyis of the Database
There are a number of areas that are important when tuning a data load process and the areas outlined in this section are really just going to tweak the performance and may or may need result in significant performance improvements. But this area can provide the “icing on the cake” in terms of extracting every last ounce of performance.

Step 1a – Relational Storage Settings
Make sure logging is switched off on the tablespace used to store the AW. Since the AW does not support redo there is not point in generating it. Make sure you have enough space within the tablespace before you start a build. A lot of time can be consumed extending the tablespace if you are not careful.

If you are using Data Guard, it will not be possible to switch of redo. The alternative is to increase REDO Log Size to between 100M and 500M, and also modify LOG_BUFFER parameter to 10M (for example) to allow for more efficient index lob creation, and also try to move TEMP, UNDO and REDO logs to fastest disk.

Step 1b –AW Storage Settings
If the cubes within an AW contain a large number of partitions, then performance can be improved by adding additional physical partitions to AWs. The AW should be logically partitioned and modelled well and then should also be physically partitioned as it improves update performance by reducing index lob contention. For example, if the main data cube contains 36 months of data and is logically partitioned by month in the AW, then the physical partitioning of the AW should match the number of logical partitions. To override the default of eight partitions it is necessary to manually define the AW and set the required number of partitions as show here:

SQL> exec dbms_aw.execute('aw create scott.product_AW partitions 36');

However, this approach does create some additional complications regarding the creation of standard form metadata. This metadata is required to make the AW visible to AWM and other OLAP aware tools. In the vast majority of cases it will be necessary to create a standard form metadata compliant AW. See the Part 3 Analyis of Cube Model, Step 3 – Partitioning for more information.

Step 2 - Temp Storage Settings
Pre-allocating space within the temp tablespace prior to running a build can make some performance improvements. When pre-allocating space make sure the temp tablespace is not set to auto-extend and the correct (most efficient) uniform extend size is used. The procedure below will pre-allocate TEMP Tablespace. Alter the for i in 1..1000000 are required. This example will pre-allocate approximately 1.5GB of TEMP tablespace. Make sure your default temporary tablespace/group is not set to auto-extend unlimited. It should be fixed to the required size.

create or replace procedure preallocate_temp as
amount integer := 26;
buffer varchar2(26) := 'XXXXXXXXXXXXXXXXXXXXXXXXXX';
done boolean := false;
out_of_temp exception;
position integer := 10240;
pragma exception_init(out_of_temp,-01652);
tmppre clob;
begin
dbms_lob.createtemporary(tmppre, true,d bms_lob.session);
dbms_lob.open(tmppre, dbms_lob.lob_readwrite);
for i in 1..130400
loop
if (done = true) then
dbms_lob.close(tmppre);
dbms_lob.freetemporary(tmppre);
end if;
begin
dbms_lob.write(tmppre, amount, position, buffer);
exception when out_of_temp then done := true;
end;
position := position + amount + 10240;
end loop;
dbms_lob.close(tmppre);
dbms_lob.freetemporary(tmppre);
end;
/

conn prealltemp/oracle
exec preallocate_temp;
disc;

Step 3 - ADDM Report
ADDM (Automatic Database Diagnostic Monitor) is a self-diagnostic engine built into the Oracle Database kernel, which automatically detects and diagnoses common performance problems, including:

Hardware issues related to excessive I/O
CPU bottlenecks
Connection management issues
Excessive parsing
Concurrency issues, such as contention for locks
PGA, buffer-cache, and log-buffer-sizing issues
Issues specific to Oracle Real Application Clusters (RAC) deployments, such as global cache hot blocks and objects and interconnect latency issues

An ADDM analysis is performed after each AWR snapshot (every hour by default). The results are saved in the database, which can then be viewed using either Oracle
Enterprise Manager or SQLPlus. For tuning OLAP data loads, ADDM is always a good place to start. In addition to diagnosing performance problems, ADDM recommends possible solutions. When appropriate, ADDM recommends multiple solutions, which can include:

Hardware changes

Adding CPUs or changing the I/O subsystem configuration

Database configuration

Changing initialization parameter settings

Schema changes

Hash partitioning a table or index, or using automatic segment-space management (ASSM)

Application changes

Using the cache option for sequences or using bind variables

Using other advisors

Running the SQL Tuning Advisor on high-load SQL statements or running the Segment Advisor on hot objects

ADDM benefits apply beyond production systems; even on development and test
Systems. ADDM can provide an early warning of potential performance problems. Typically the results from an ADDM snapshot are viewed via various interactive pages within Enterprise Manager, as shown below:

Alternatively you can access ADDM reports using the SQL*Plus command line by calling the new DBMS_ADVISOR built-in package. For example, here's how to use the command line to create an ADDM report quickly (based on the most recent snapshot):

set long 1000000
set pagesize 50000
column get_clob format a80
select dbms_advisor.get_task_report(
task_name, 'TEXT', 'ALL')
as ADDM_report
from dba_advisor_tasks
where task_id=(
select max(t.task_id)
from dba_advisor_tasks t, dba_advisor_log l
where t.task_id = l.task_id
and t.advisor_name='ADDM'
and l.status= 'COMPLETED');

The ‘ALL’ parameter generates additional information about the meaning of some of the elements in the report. The most interesting section of the report relates to the "Findings" for each issue. This outlines the impact of the identified problem as a percentage of DB time, which correlates with the expected benefit, based on the assumption the problem described by the finding will be solved if the recommended action is taken.

In the example below the recommendation is to adjust the sga_target value in the parameter file:

FINDING 3: 5.2% impact (147 seconds)
---------------------------------------
The buffer cache was undersized causing significant additional read I/O.
RECOMMENDATION 1: DB Configuration, 5.2% benefit (147 seconds)
ACTION: Increase SGA target size by increasing the value of parameter "sga_target" by 24 M.
SYMPTOMS THAT LED TO THE FINDING:
Wait class "User I/O" was consuming significant database time. (5.3% impact [150 seconds])
...

To get more information this feature refer to the Oracle® Database 2 Day + Performance Tuning Guide 10g Release 2 (10.2).

For the HTML version, click here for 10gR2 and here for 11g.
For the PDF version, click here for 10gR2 and here for 11g

Step 4 - Dynamic Performance Views
Each Oracle database instance maintains a set of virtual tables that record current database activity and store data about the instance. These tables are called the V$ tables. They are also referred to as the dynamic performance tables, because they store information relating to the operation of the instance. Views of the V$ tables are sometimes called fixed views because they cannot be altered or removed by the database administrator. The V$ tables collect data on internal disk structures and memory structures. They are continuously updated while the database is in use. The SYS user owns the V$ tables. In addition, any user with the SELECT CATALOG role can access the tables. The system creates views from these tables and creates public synonyms for the views. The views are also owned by SYS, but the DBA can grant access to them to a wider range of users.

Among these are tables that collect data on OLAP operations. The names of the OLAP V$ views begin with V$AW:

V$AW_AGGREGATE_OP

Lists the aggregation operators available in the OLAP DML.

V$AW_ALLOCATE_OP

Lists the allocation operators available in the OLAP DML.

V$AW_CALC

Collects information about the use of cache space and the status of dynamic aggregation.

V$AW_LONGOPS

Collects status information about SQL fetches.

V$AW_OLAP

Collects information about the status of active analytic workspaces.

V$AW_SESSION_INFO

Collects information about each active session.

For tuning the two most important views from this list are:

V$AW_CALC
This reports on the effectiveness of various caches used by Oracle OLAP and the status of processing by the AGGREGATE function. Oracle OLAP uses the following caches:

Aggregate cache: An internal cache used by the aggregation subsystem during querying. It stores the children of a given dimension member, such as Q1-04, Q2-04, Q3-04, and Q4-04 as the children of 2004.
Session cache: Oracle OLAP maintains a cache for each session for storing the results of calculations. When the session ends, the contents of the cache are discarded.
Page pool: A cache allocated from the User Global Area (UGA), which Oracle OLAP maintains for the session. The page pool is associated with a particular session and caches records from all the analytic workspaces attached in that session. If the page pool becomes too full, then Oracle OLAP writes some of the pages to the database cache. When an UPDATE command is issued in the OLAP DML, the changed pages associated with that analytic workspace are written to the permanent LOB, using temporary segments as the staging area for streaming the data to disk. The size of the page pool is controlled by the OLAP_PAGE_POOL initialization parameter.
Database cache: The larger cache maintained by the Oracle RDBMS for the database instance.

Because OLAP queries tend to be iterative, the same data is typically queried repeatedly during a session. The caches provide much faster access to data that has already been calculated during a session than would be possible if the data had to be recalculated for each query.

The more effective the caches are, the better the response time experienced by users. An ineffective cache (that is, one with few hits and many misses) probably indicates that the data is not being stored optimally for the way it is being viewed. To improve runtime performance, you may need to reorder the dimensions of the variables (that is, change the order of fastest to slowest varying dimensions).

V$AW_LONGOPS
This view will identify the OLAP DML command (SQL IMPORT, SQL FETCH, or SQL EXECUTE) that is actively fetching data from relational tables. The view will state the current operation based on one of the following values:

EXECUTING. The command has begun executing.
FETCHING. Data is being fetched into the analytic workspace.
FINISHED. The command has finished executing. This status appears very briefly before the record disappears from the table.

Other information returned includes: the number of rows already inserted, updated, or deleted and the time the command started executing.
For more information refer to the Oracle OLAP Option Users Guide, Section 7 Administering Oracle OLAP – Dynamic Performance Views.

Step 5 - Init.Ora Parameters
Checking the RDBMS parameters are appropriately tuned for your OLAP environment is relatively easy. Fortunately in 10g the majority of init.ora parameters are managed dynamically, however a few parameters that may need to be changed are:

SORTBUFFERSIZE
This should be increased since OLAP AWs use this parameter instead of SORT_AREA_SIZE. So, for every AW, to increase it do the following:

exec DBMS_AW.EXECUTE('aw attach SCOTT.MYAW rwx');
exec DBMS_AW.EXECUTE('shw sortbuffersize');
262,411
exec DBMS_AW.EXECUTE('SortBufferSize=10485760');
exec DBMS_AW.EXECUTE('shw sortbuffersize');
10,485,760
exec DBMS_AW.EXECUTE('aw detach SCOTT.MYAW');

OLAP_PAGE_POOL_SIZE
This should be set to 0 or unset so that auto dynamic page pool is on and is managed by the database (will be set to 50% of PGA size). However, if you have over 8Gb of memory available then you should set the parameter manually and a good value for data loading is to set to 256MB and for multiple users querying concurrently, 64MB.

Attribute Reporting on the Cube using SQL

2008-04-10T07:27:00.000-07:00

In my last post, I noted that nearly reporting on the 11g cube occurs within SQL-based relational reporting tools such as Oracle Business Intelligence Enterprise Edition (OBI EE), Business Objects, etc. That's no surprise given that these tools dominate the BI market and that the OLAP Option is positioned as making SQL-based BI tools better with improved query performance and analytic content. While the focus is how OLAP cubes make SQL-based BI application better, it shouldn't be overlooked that SQL-based tools can make the OLAP Option better.

A perfect example is attribute reporting. The cube has never naturally supported attribute reporting. For example, if a cube is dimensioned by time, product and customer and product has attributes such as buyer and marketing manager it has never been easy to report sales by time, buyer, marketing manager and customer with just the cube.

One of the reasons for integrating the cube into the Oracle Database is that it blends the dimension storage and calculation models with the relational query model (that is, SQL). Dimensional calculations can be defined in the cube and relational calculations can be added above the cube in a query.

Getting back to our example, the SQL view of the product dimension (with the buyer and marketing manager attributes) will look something like this:

desc product_classes_view;

DIM_KEY VARCHAR2(100)
LEVEL_NAME VARCHAR2(30)
LONG_DESCRIPTION VARCHAR2(100)
SHORT_DESCRIPTION VARCHAR2(100)
TOTAL_PRODUCT_LONG_DESCR VARCHAR2(100)
TOTAL_PRODUCT_LONG_DES_1 VARCHAR2(100)
TOTAL_PRODUCT_DAVE VARCHAR2(100)
CLASS_LONG_DESCRIPTION VARCHAR2(100)
CLASS_LONG_DESCRIPTION1 VARCHAR2(100)
FAMILY_LONG_DESCRIPTION VARCHAR2(100)
FAMILY_LONG_DESCRIPTION1 VARCHAR2(100)
ITEM_BUYER VARCHAR2(100)
ITEM_MARKETING_MANAGER VARCHAR2(100)
ITEM_PACKAGE VARCHAR2(100)
ITEM_LONG_DESCRIPTION VARCHAR2(100)
ITEM_LONG_DESCRIPTION1 VARCHAR2(100)
PARENT VARCHAR2(100)
TOTAL_PRODUCT VARCHAR2(100)
CLASS VARCHAR2(100)
FAMILY VARCHAR2(100)
ITEM VARCHAR2(100)

A sampling of the data follows.

SELECT dim_key,
item_long_description,
item_buyer,
item_marketing_manager
FROM
product_classes_view
WHERE
level_name = 'ITEM';

If I want to break out sales by report sales by time, buyer, marketing manager and customer I query those columns and use SQL group by. E.g.,

SELECT
t.calendar_year_long_descr,
p.item_buyer,
p.item_marketing_manager,
SUM(f.sales)
FROM
time_calendar_view t,
product_classes_view p,
customer_shipments_view cu,
channel_channels_view ch,
units_cube_view f
WHERE
t.dim_key = f.time
AND p.dim_key = f.product
AND cu.dim_key = f.customer
AND ch.dim_key = f.channel
AND t.level_name = 'CALENDAR_YEAR'
AND p.level_name = 'ITEM'
AND cu.level_name = 'TOTAL_CUSTOMER'
AND ch.level_name = 'TOTAL_CHANNEL'
GROUP BY
t.calendar_year_long_descr,
p.item_buyer,
p.item_marketing_manager,
cu.total_customer_long_desc,
ch.total_channel_long_descr
ORDER BY
calendar_year_long_descr;

There are two things you want to keep in mind when doing any aggregation above the cube in SQL:

Make sure the aggregation method used in SQL is compatible with the aggregation method used in the cube. For example, if the cube aggregates using SUM, then using SUM in SQL makes sense. If the cube aggregates using a hierarchical weighted average, it probably doesn't make sense to SUM in SQL.
Make sure it is appropriate to aggregate the measure in SQL. For example, it makes sense to aggregate measures such as sales, sales prior period, etc. using SUM in SQL. It probably doesn't make sense to aggregate measures that are percentages or rankings.

In products like OBI EE I like to set up the presentation layer to make it clear which columns are levels and which are attributes. This helps highlight the fact that they are different. I also like to group different types of measures into seperate presentation tables. While this is convenient in general, this also highlights differences between types of measures. For example, a report designer can easily be told not to include ranking measures in a report that includes attribute columns.

In the illustration below, note the presentation tables group columns in to level, attributes and measure types.

In products like OBI EE, I can now effortlessly make reports broken out by attribute that include many types of OLAP calculations.

More on the Calculated Measure Cube

2008-04-09T09:29:00.000-07:00

In Keith's post, he describes how to create a calculated measure cube in Oracle 10g. In this post, I will describe how to create a calculated measure cube in Oracle 11g.

The basic technique is the same as Keith describes:

Create a dimension representing calculations.
Create a cube that will contain the stored measures.
Create a reporting cube that will contain the calculated measures.
Define the calculations.

Where the 11g technique varies from the 10g technique is how the calculations are defined. In 10g, the calculations are defined in OLAP DML code. In 11g, they are defined using the new calculation expression language. No OLAP DML programming is required. Also, in 11g, the SQL view is created and managed by the database so I get to skip the step of creating the view.

In this example, I have a cube that is dimensioned by time, product, customer and channel and base measures sales, units and costs. I want to add time series calculations to these base measures.

Step 1 - Creating the calculation dimension

In my example, I have a calculation dimension. It plays the same role as Keith's Time View dimension - it's members represent calculations. The dimension is very simple, with one level and one hierarchy (which I really don't even need) and an attribute used for sorting.

The data for the calculation dimension looks like this:

Step 2 - Create cube for the stored measure

In this example, I have a UNITS_CUBE cube dimensioned by time, product, customer and channel with the stored measures sales, units and costs. This is mapped to a table that contains these measures and data is loaded into this cube.

Step 3 - Create the cube for the calculations

This second cube is dimensioned by time, product, customer, channel and calculation and calculated measures. This will be the cube used for reporting purposes. Oracle 11g automatically creates a SQL view of this cube.

Since this cube contains only stored measures settings such as partitioning, compression, sparsity and aggregation are irrelevant. The cube is not mapped to source tables.

In this cube, I created one calculated measure for each stored measure in the UNITS_CUBE. I named these SALES_CALCS, UNITS_CALCS and COST_CALCS. Note the two cubes below.

Here is where 11g is a lot different from 10g. The calculation definition of the calculated measures is defined in AWM as 11g OLAP calculation expressions. The expression uses the CASE statement to choose the calculation expression based on the value of the calculation dimension.

CASE

WHEN CALCULATION.DIM_KEY = 'BASE'
THEN UNITS_CUBE.SALES

WHEN CALCULATION.DIM_KEY = 'PP'
THEN LAG(UNITS_CUBE.SALES, 1) OVER HIERARCHY ("TIME".CALENDAR)

WHEN CALCULATION.DIM_KEY = 'CHG_PP'
THEN LAG_VARIANCE(UNITS_CUBE.SALES, 1) OVER HIERARCHY ("TIME".CALENDAR)

WHEN CALCULATION.DIM_KEY = 'PCT_CHG_PP'
THEN LAG_VARIANCE_PERCENT(UNITS_CUBE.SALES, 1) OVER HIERARCHY ("TIME".CALENDAR) * 100

ELSE NULL
END

Notes:

The calculation expressions are based on the logical model, so you have none of the concerns that come with writing to the physical model of the AW (e.g., migration problems that occur when the physical model changes).
The CASE statement is used to switch between calculations. CASE in the OLAP expression language is exactly the same as in SQL.
Because the calculation is captured as on OLAP expression, it will be included in the XML template of the cube or AW.

The illustration below shows the calculation in the context of AWM and the cube.

In AWM, you can view the results. It looks like Keith's example, except that I've nested calculation under measure and put time down as rows. This shows some of the flexibility of this approach to modeling measures and calculations.

Updating INIT.ORA

Before querying your caclulated measure cube, add the following line to our init.ora file:

event="37395 trace name context forever, level 1024"

This line will control some looping code in the OLAP_TABLE table function that is used to query the cube using SQL. Without this line, null data can be returned by SQL for measures in the calculated meaure cube.

Displaying Data

This approach makes is very easy to define large numbers of calculations without cluttering the measure dimension. From a data display point of view, this approach really shines with a dimensionally aware tool such as the data viewer in AWM.

As the OLAP Option moves forward in time we find that almost all data is viewed in SQL based business intelligence tools such as Oracle Business Intelligence Enterprise Edition (OBI EE). What does this mean for data display?

If we think about OBI EE as an example of relational reporting tools that are commonly used with OLAP cubes, you have two choices: relational style reporting and pivot table style reporting.

With relational style reporting, the measures of this cube will appear as columns and the calculations as rows (because calculation is a dimension). This enables reporting in the context of a table. Note the calculation column.

The OBI EE pivot table offers additional flexibility. Note that this is the same report as in the AWM data viewer (above).

Which style reporting you use is, of course, up to you. With this cube, you can do either.

It is important to understand that tools such as OBI EE understand the fact columns from the cube view as measures and that they have no special understanding of the calculations dimension. Therefore, they don't typically offer measure formatting based on this column. In my example, I've sorted rows of the calculation dimension using the DISPLAY_ORDER attribute in the AW dimension (via the dimension view) and multiplied the percent change measure * 100 for display purposes.

Keep these types of issues in mind and you can add a lot of analytic capability to your SQL based BI applications and really take advantage of features such as the pivot table in OBI EE.

Creating A Calculated Measure Cube - IOGALFF

2008-03-27T07:30:00.000-07:00

(Firstly, this is information relates only to OLAP 10gR2. There are a number of changes within OLAP11gR1 and the following scenario has not been tested with 11g)

When you venture into most supermarkets today, somewhere on one of the many isles there will be a BOGOF – buy one get one free. Well how about an OLAP offer: “IOGALFF” – install one get at least fourteen free. I admit it is not quite as catchy as the original, but the benefits are huge and it has the result of making life so much easier.

One of the more interesting challenges of using Analytic Workspace Manager (and also OWB) relates to managing all the calculated measures within a cube. Every measure should really be accompanied by a standard set of calculated measures such as:

Current Period
Last Year
Last Year %
Prior Period
Prior Period %
Year to Date
Year to Date Last Year
Year to Date Last Year %
Quarter to Date
Quarter to Date Last Year
Quarter to Date Last Year %
3-Month Moving Average
6-Month Moving Average
12-Month Moving Average
Dimension “A” Share of All Members
Dimension “A” Share of Parent

Its is the addition of these types of measures that adds real value to the your BI application. As I have stated many times before: business users are not interested in looking at data from base measures, typically they are more interested in trends based on the prior period or prior year, share of revenue, moving averages and so on. This adds a lot of work for the cube designer as they need to add at least 14 calculated measures for each base measure as well as two additional calculated share measures for each dimension associated with each cube.

Which adds up to a lot of measures and manually creating all these measure is likely to take a considerable amount of time. If we take the command schema sample that is shipped with BI10g, this contains 5 base measures (costs, quantity, revenue, margin, price) with 4 dimensions (channel, geography, product, time), which translates to

5 * 14 calculated measures
+
5 * 3 (dimensions - channel, geography, product) * 2 calculated share measures

= 100 calculated measures.

As I said, it is very easy to generate a lot of calculated measures.

So is there a more intelligent way of managing calculated measures? What is the quickest way to add all these additional calculations to a cube? One way is to use the Excel Accelerator to add the calculated measures to your AW. It is relatively easy to use and allows you to use normal Excel tools such as cut & paste to quickly create all the calculations you could ever need. You can download the Excel Accelerator from the OLAP OTN Home Page, or by clicking here. Look for these links:

Creating OLAP Calculations using Excel
Creating OLAP Calculations using Exce Readme

During a recent POC, we used a more interesting approach that takes the use of customer calculations to a new level. In many ways this goes back to the various techniques we used when creating Express databases for use with Sales Analyzer to make life easier for both administrators and users. The approach is quite simple – use and additional dimension to define the type of calculation you want to execute and link this to a DML program to return the required results. The list of calculated measures listed above are converted into dimension members, which provides another way to slice, dice and pivot the data. As you can see here, the dimension member that identifies the type of calculation is in the row edge with the measure in the column edge. In this way, only one calculation is required which is used across all the available measures.

What this means is: by installing one simple (well, everything is relative) calculated measure you can get upto fourteen additional calculations for free. Hence the acronym “IOGALFF” – install one get at least fourteen free.

How is all this managed? There are five basic steps to creating this type of reporting solution:

Create a new dimension to control the types of calculations returned
Add some additional attributes to the time dimension to help manage some of the time series arguments for specific calculations
Create an OLAP DML program to return the data
Create a new cube that includes the new calculation type dimension and add a calculated measure that calls the OLAP DML program to return the required data
Create a SQL View over the cube

Step 1 – Creating the new Time View dimension

I have called the dimension containing the list of calculated members, Time View (mainly because the majority of the calculations are time based but may be a better name would be "measure view" ?). The Time View dimension has one level and one hierarchy with three attributes:

Long Label
Short Label
Default sort order

The source data looks like this:

With the source table definition as follows:

CREATE TABLE TIME_VIEW
(TIME_VIEW_ID VARCHAR2(2 BYTE),
TIME_VIEW_DESC VARCHAR2(35 BYTE),
SORT_ORDER NUMBER(*,0));

And the view definition as follows:

CREATE OR REPLACE FORCE VIEW VW_TIME_VIEW AS
SELECT
TIME_VIEW_ID
, TIME_VIEW_DESC
, SORT_ORDER
FROM TIME_VIEW
ORDER BY SORT_ORDER;

To populate the base table the following commands are used:

INSERT INTO TIME_VIEW VALUES ('1', 'Current Period', '1')
INSERT INTO TIME_VIEW VALUES ('2', 'Last Year', '2')
INSERT INTO TIME_VIEW VALUES ('3', 'Last Year %', '3')
INSERT INTO TIME_VIEW VALUES ('4', 'Prior Period', '4')
INSERT INTO TIME_VIEW VALUES ('5', 'Prior Period %', '5')
INSERT INTO TIME_VIEW VALUES ('6', 'Year To Date', '6')
INSERT INTO TIME_VIEW VALUES ('7', 'Year To Date Last Year', '7')
INSERT INTO TIME_VIEW VALUES ('8', 'Year To Date Last Year %', '8')
INSERT INTO TIME_VIEW VALUES ('9', 'Quarter To Date', '9')
INSERT INTO TIME_VIEW VALUES ('10', 'Quarter To Date Last Year', '10')
INSERT INTO TIME_VIEW VALUES ('11', 'Quarter To Date Last Year %', '11')
INSERT INTO TIME_VIEW VALUES ('12', '3-Month Moving Average', '12')
INSERT INTO TIME_VIEW VALUES ('13', '6-Month Moving Average', '13')
INSERT INTO TIME_VIEW VALUES ('14', '12-Month Moving Average', '14')

After this, the next set of values depends on the dimensions within your data model. For my model, I have three additional dimensions that I want to analyze: Channel, Product and Customer. For each of these dimensions I want to see the % share of each member in relation to the value for all members (i.e. the top level) and the shared based on the parent value. To enable these calculations I add the following lines to the TIME_VIEW table:

INSERT INTO TIME_VIEW VALUES ('15', ' Product Share of All Products', '15')
INSERT INTO TIME_VIEW VALUES ('16', ' Product Share of Parent', '16')
INSERT INTO TIME_VIEW VALUES ('17', ' Channel Share of All Channels', '17')
INSERT INTO TIME_VIEW VALUES ('18', ' Channel Share of Parent', '18')
INSERT INTO TIME_VIEW VALUES ('19', ' Customer Share of All Customers', '19')
INSERT INTO TIME_VIEW VALUES ('20', ' Customer Share of Parent', '20')

(Obviously, these additional share calculations are not really related to time, so using the name Time View is sort of confusing and in hindsight the term Measure View would have been a better name for the dimension). Once the table has been defined, it can be mapped in AWM to create the dimension mapping (in this case I am using a view over the source

The last part of this step is to then load the members into the dimension using the dimension data load wizard or via the SQL command line using a load script. The result should look like this when you view the members using the Dimension Data Viewer:

As can be seen here, there are fourteen base calculations that can be applied to any model, plus the additional share calculations that are based on the dimensions within the source cube. Hence the title “IOGALFF” – install one get fourteen free. In reality it is more like: install one and as many calculations as you like or need, but that translates to “IOGASMAYNORL” which does not really role off the tongue.

Step 2 – Updating the Time Dimension with new Attributes.

To make the program that delivers all these calculated measures as simple as possible a number of attributes are added to the time dimension:

Lag Prior Year

At the Year level this equates to 1

At the Quarter level this equates to 4

At the Month level this equates to 12

At the Day level it is the number of days in the year (365 or 366)

Parent Quarter

This is the quarter for each time period and used as the reset trigger for the cumulative totals

Parent Year

This is the year for each time period and used as the reset trigger for the cumulative totals

The information for the two parent attributes is taken from existing columns within the source data since this information already exists and the Lag Prior Year is a simple hard-coded value apart from the day level where the timespan value for the year is used to cope with leap years. Therefore, all the information for these three attributes should be readily available within your source data, especially if you are using the OWB Time Wizard to create your time dimension. Obviously using a view over the source table for the mapping within AWM makes adding this information relatively trivial.

Step 3 – Create the OLAP DML Calculation Program.

The new OLAP DML program, called TIME_VIEW_PRG (again feel free to change the name of program if you want), takes two arguments:

The cube.measure_name for the base measure, in this case SALES_M1, which is the Revenue measure in the SALES cube.
The identifier for the calculated measure within the REPORT_CUBE cube, which in this case is M1_PRG (this is explained in more detail in the next section)

The program code is as follows, which for those of you familiar with OLAP DML will find relatively easy to understand:

DEFINE TIME_VIEW_PRG PROGRAM DECIMAL
argument T_MEASURE text " Full measure name including cube name
argument T_MEASURE_ID text " Measure name

variable T_FORM text " Name of the formula
variable T_TIME_VIEW text " Value of TIME_VIEW dimension
variable D_RETURN decimal " Return value

trap on ALLDONE noprint

T_TIME_VIEW = TIME_VIEW
T_FORM = joinchars('REPORT_CUBE_', T_MEASURE_ID)

switch T_TIME_VIEW
do
case 'VL1_1':
D_RETURN = &T_MEASURE
break
case 'VL1_2': "LY
D_RETURN = lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL)
break
case 'VL1_3': "LY%
D_RETURN = lagpct(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL)
break
case 'VL1_4': "PP
D_RETURN = lag(&T_FORM(TIME_VIEW 'VL1_1'), 1, TIMES, LEVELREL TIMES_LEVELREL)
break
case 'VL1_5': "PP%
D_RETURN = lagpct(&T_FORM(TIME_VIEW 'VL1_1'), 1, TIMES, LEVELREL TIMES_LEVELREL)
break
case 'VL1_6': "YTD
if TIMES_LEVELREL EQ 'YEAR'
then D_RETURN = &T_FORM(TIME_VIEW 'VL1_1')
else D_RETURN = cumsum(&T_FORM(TIME_VIEW 'VL1_1'), TIMES, TIMES_PARENT_YEAR)
break
case 'VL1_7': "YTD LY
if TIMES_LEVELREL EQ 'YEAR'
then D_RETURN = lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL)
else D_RETURN = cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_YEAR)
break
case 'VL1_8': "YTD LY%
D_RETURN = (&T_FORM(TIME_VIEW 'VL1_6')-cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_YEAR))/cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_YEAR)
break
case 'VL1_9': "QTD
if TIMES_LEVELREL EQ 'YEAR'
then D_RETURN = NA
else if TIMES_LEVELREL EQ 'QUARTER'
then D_RETURN = &T_FORM(TIME_VIEW 'VL1_1')
else if TIMES_LEVELREL EQ 'MONTH'
then D_RETURN = cumsum(&T_FORM(TIME_VIEW 'VL1_1'), TIMES, TIMES_PARENT_QUARTER)
else if TIMES_LEVELREL EQ 'DAY'
then D_RETURN = cumsum(&T_FORM(TIME_VIEW 'VL1_1'), TIMES, TIMES_PARENT_QUARTER)
else D_RETURN = NA
break
case 'VL1_10': "QTD LY
if TIMES_LEVELREL EQ 'YEAR'
then D_RETURN = NA
else if TIMES_LEVELREL EQ 'QUARTER'
then D_RETURN = cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_QUARTER)
else if TIMES_LEVELREL EQ 'MONTH'
then D_RETURN = cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_QUARTER)
else if TIMES_LEVELREL EQ 'DAY'
then D_RETURN = cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_QUARTER)
break
case 'VL1_11': "QTD LY%
if TIMES_LEVELREL EQ 'YEAR'
then D_RETURN = NA
else if TIMES_LEVELREL EQ 'QUARTER'
then D_RETURN = (&T_FORM(TIME_VIEW 'VL1_9')-cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_QUARTER))/cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_QUARTER)
else if TIMES_LEVELREL EQ 'MONTH'
then D_RETURN = (&T_FORM(TIME_VIEW 'VL1_9')-cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_QUARTER))/cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_QUARTER)
else if TIMES_LEVELREL EQ 'DAY'
then D_RETURN = (&T_FORM(TIME_VIEW 'VL1_9')-cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_QUARTER))/cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_QUARTER)
break
case 'VL1_12': "3MMA
if TIMES_LEVELREL eq 'MONTH'
then D_RETURN = MOVINGAVERAGE(&T_FORM(TIME_VIEW 'VL1_1'), -2, 0, 1, TIMES, LEVELREL TIMES_LEVELREL)
else D_RETURN = NA
break
case 'VL1_13': "6MMA
if TIMES_LEVELREL eq 'MONTH'
then D_RETURN = MOVINGAVERAGE(&T_FORM(TIME_VIEW 'VL1_1'), -5, 0, 1, TIMES, LEVELREL TIMES_LEVELREL)
else D_RETURN = NA
break
case 'VL1_14': "12MMA
if TIMES_LEVELREL eq 'MONTH'
then D_RETURN = MOVINGAVERAGE(&T_FORM(TIME_VIEW 'VL1_1'), -11, 0, 1, TIMES, LEVELREL TIMES_LEVELREL)
else D_RETURN = NA
break
case 'VL1_15': "Product Share of All Products
D_RETURN = &T_FORM(TIME_VIEW 'VL1_1')/&T_FORM(TIME_VIEW 'VL1_1', PRODUCTS limit(PRODUCTS to TOPANCESTORS using PRODUCTS_PARENTREL)
break
case 'VL1_16': "Product Share of Parent
D_RETURN = &T_FORM(TIME_VIEW 'VL1_1')/&T_FORM(TIME_VIEW 'VL1_1', PRODUCTS PRODUCTS_PARENTREL)
break
case 'VL1_17': "Customers Share of All Customers
D_RETURN = &T_FORM(TIME_VIEW 'VL1_1')/&T_FORM(TIME_VIEW 'VL1_1', CUSTOMERS limit(CUSTOMERS to TOPANCESTORS using CUSTOMERS_PARENTREL)
break
case 'VL1_18': "Customers Share of Parent
D_RETURN = &T_FORM(TIME_VIEW 'VL1_1')/&T_FORM(TIME_VIEW 'VL1_1', CUSTOMERS CUSTOMERS_PARENTREL)
break
case 'VL1_19': "Channels Share of All Channels
D_RETURN = &T_FORM(TIME_VIEW 'VL1_1')/&T_FORM(TIME_VIEW 'VL1_1', CHANNELS limit(CHANNELS to TOPANCESTORS using CHANNELS_PARENTREL)
break
case 'VL1_20': "Channels Share of Parent
D_RETURN = &T_FORM(TIME_VIEW 'VL1_1')/&T_FORM(TIME_VIEW 'VL1_1', CHANNELS CHANNELS_PARENTREL)
break
doend

ALLDONE:
return D_RETURN
END

In simple terms, the program checks to see which member of the dimension TIME_VIEW is being requested and then executes the correct OLAP function to return the required data. At the moment this has only been tested on a Julian calendar hierarchy but I am doing some more testing over the next few weeks for other types of hierarchies. Some of the measures are dependant on the level within the Time dimension, so you may need to change references to specific level names etc if you want to reuse this program code. For example:

if TIMES_LEVELREL EQ 'YEAR'

My time dimension has levels; Year, Quarter, Month, and Day. In some cases it is necessary to change the processing depending the level, for example:

if TIMES_LEVELREL EQ 'YEAR'
then D_RETURN = NA
else if TIMES_LEVELREL EQ 'QUARTER'
then D_RETURN = &T_FORM(TIME_VIEW 'VL1_1')
else if TIMES_LEVELREL EQ 'MONTH'
then D_RETURN = cumsum(&T_FORM(TIME_VIEW 'VL1_1'), TIMES, TIMES_PARENT_QUARTER)
else if TIMES_LEVELREL EQ 'DAY'
then D_RETURN = cumsum(&T_FORM(TIME_VIEW 'VL1_1'), TIMES, TIMES_PARENT_QUARTER)
else D_RETURN = NA

In this code extract you can see the reference to one of the additional time attributes we added in the previous step, PARENT_QUARTER that is used by the program. All the references within the program are based on Standard Form naming conventions. Therefore, the full standard name for this attribute is TIMES_PARENT_QUARTER since the dimension is called TIMES and the attribute is called PARENT_QUARTER.

For simplicity I created the program in the AW containing the cubes, which is not exactly ideal because if you delete the AW to rebuild it you will lose the program code. Alternatively, you can just create a new AW and add the program to the new AW then modify the ONTTACH program in your data AW to automatically attach the program AW.

Calculations for dimension members VL_15 to VL_20 are provided as examples and would need to be changed to match the dimensions within your own AWs. For each dimension you will need to create two new dimension members within the TIME_VIEW dimension to return the following share calculations:

Share of based on the top level total, i.e. All Members
Share based on parent.

As the code uses Standard form notation the only thing you should need to change is code highlighted in bold and replace the phrase “Channels” with your own dimension name:

case 'VL1_19': "Channels Share of All Channels
D_RETURN = &T_FORM(TIME_VIEW 'VL1_1')/&T_FORM(TIME_VIEW 'VL1_1', CHANNELS limit(CHANNELS to TOPANCESTORS using CHANNELS_PARENTREL)
break
case 'VL1_20': "Channels Share of Parent
D_RETURN = &T_FORM(TIME_VIEW 'VL1_1')/&T_FORM(TIME_VIEW 'VL1_1', CHANNELS CHANNELS_PARENTREL)
break

Step 4 – Creating the New Cube.

In my demo schema I have a cube called SALES and it contains two measures: Revenue (called M1) and Quantity (called M2). The dimensionality for this cube is Time, Channel Product, and Customer.

To provide all the time series and comparative calculations I create a new cube that has the same dimensionality as the SALES cube, with one additional dimension. In this cube the Time View dimension is also used. This reporting cube contains no stored measures, but it does contain custom calculated measures for each measure in the SALES cube, however, each calculated measure will return 14 additional time calculations and 6 share calculations to support the base measure controlled by the dimension TIME_VIEW.

The implementation for the cube is largely irrelevant since no stored measures will be present within the cube, only calculated measures. Therefore, you can if you want either accept the default settings or de-select all the various options.

To add the calculated measures to the cube requires the use of a custom measure XML template. To create a calculated measure to support the measure “Revenue” from the SALES cube you will need to create a custom calculation either using an XML template (email me I can send you a blank template) or use the Excel Calculation Utility that can be downloaded from the OLAP Home Page on OTN to install the two custom calculations.

Once you have added these calculated measures to the REPORT_CUBE, the tree in AWM should look like this:

By selecting each of the calculated measures, the details of each calculation will be displayed within the right-hand panel of AWM. For the first calculated measure linked the Revenue measure in the Sales cube the panel looks like this:

For the second calculated measure linked the Quantity measure in the Sales cube the panel looks like this:

The key part is the line showing the “Expression” (in the XML Template this is the attribute ExpressionText), this defines the call to an OLAP DML program called TIME_VIEW_PRG passing the two required arguments of

ExpressionText="TIME_VIEW_PRG('SALES_M1', 'M1_PRG')”

The cube.measure_name for the base measure, in this case SALES_M1, which is the Revenue measure in the SALES cube.
The identifier for the calculated measure within the REPORT_CUBE cube.

At this point you may or may not have the correct formula/expression text. When you load calculations via the XML interface OLAP tries to be a bit too clever and it attempts to convert any physical name it can match with the equivalent standard form name. In most cases this is fine, but in this case we need to refer directly to the physical objects. Modifying the expressions is relatively easy if you follow these steps. Using AWM, open the OLAP Worksheet and then:

CNS REPORT_CUBE_M1_PRG
EQ TIME_VIEW_PRG('SALES_M1', 'M1_PRG')
UPDATE
COMMIT
CNS REPORT_CUBE_M2_PRG
EQ TIME_VIEW_PRG('SALES_M2', 'M2_PRG')
UPDATE
COMMIT

If want to do this via the PL/SQL interface then you can do something like this:

EXEC DBMS_AW.EXECUTE('AW ATTACH AW_NAME RW FIRST')
EXEC DBMS_AW.EXECUTE('CNS REPORT_CUBE_M1_PRG')
EXEC DBMS_AW.EXECUTE('EQ TIME_VIEW_PRG(''SALES_M1'', ''M1_PRG'')')
EXEC DBMS_AW.EXECUTE('UPDATE')
EXEC DBMS_AW.EXECUTE('COMMIT')
EXEC DBMS_AW.EXECUTE('CNS REPORT_CUBE_M2_PRG')
EXEC DBMS_AW.EXECUTE('EQ TIME_VIEW_PRG(''SALES_M2'', ''M2_PRG'')')
EXEC DBMS_AW.EXECUTE('UPDATE')
EXEC DBMS_AW.EXECUTE('COMMIT')
EXEC DBMS_AW.EXECUTE('AW DETACH AW_NAME')

Step 4a – Viewing the Data.

Once the calculated measures have been added to the new cube, REPORT_CUBE, the AWM Data Viewer can be launched to check the results:

In the Query Wizard both sets of measures (stored measures and the new calculated measures) are both available for selection. To see the new calculations that are now available we can select both of the Report View measures as shown here.

Once the measures have been selected, the dimension selector will allow us to pick the calculations we want to display from the list of twenty (there are fourteen base calculations that can be applied to any model, plus the additional share calculations that are based on the dimensions within the source cube, in this case six additional share calculations, making twenty calculated measures in total).

The Dimension Viewer shows all the available calculations for this demo schema and the final report is shown below.

Step 5 – Making the Data Available via SQL.
Is there any real point to this post except to show how clever Oracle OLAP can be? In opinion yes, since this technique can be extremely useful when you need to make an OLAP cube visible to SQL based tools. Analytic Workspace Manager 10g has an relational view generator that is available as a plugin and this makes generating the SQL views to support your OLAP cubes a very quick and easy process. But there we know users don’t just want base measures they want lots and lots of calculations as well. When the time comes to create SQL views as the limit on the number of columns within a view is 1000. This may have been increased in 10g/11g, but even so, navigating a cube with that many columns is not easy.

The OLAP View Generator plugin for AWM10gR2 can be downloaded from here, and the associated readme is here.

Using this model, the calculations simply resolve to another dimension, which translates to one additional column in the view as opposed to, at least, 14 additional columns per source measure and in this case 20 additional columns per source measure. Therefore, this approach makes exposing the cube much easier to manage as can be seen here:

As we can see here, all the calculations are contained within the dimension Time View, which simply gets exposed like any other dimension.

Eh voila, install one measure and you can automatically generate fourteen or more additional measures which are guaranteed to bring a smile to the face of any business user.

OLAP Option Wiki Update

2008-03-20T20:32:00.000-07:00

The OLAP Blog team has been adding lots of new content to the Oracle OLAP Option Wiki site. To link to the Wiki click here. We have added both general and techie/DBA focused information. Don't forget the Wiki is open to contributions from everyone within the Oracle community: customers, partners and oracle employees. To join the Oracle Wiki community simply click here, or go to http://wiki.oracle.com/accountnew. Registration is free.

This is what we currently have on the Wiki for the OLAP Option:

General Information
Background and History - of the OLAP Option. This covers the key milestones in the development of Express and its evolution into the the OLAP Option.

Getting Started with Oracle OLAP - This is a series of shortcuts to get you up and running with the OLAP Option as quickly as possible. We have included additional reading material and links to all the relevant documentation.

Terminology - Covers the key concepts and terms used when working with multidimensional data and more specifically the OLAP Option. There are still some blank pages but we are slowly making progress.

Versions - This page provides the key features for each version, from the latest version of the OLAP Option (11g Release 1) all the way back to Express Server 4.8. The Express server product line does go back much further (I started with PCX2.5, I think) but there is no documentation to consult to extract a list of features. If anyone can fill in the missing products please feel free to add the content.

Oracle OLAP Newsletter - Links to the current newsletter and archive copies. We have extracted the main headlines from each newsletter to make searching for a specific article a little easier.

For DBAs and Techies
Script Samples - A complete library of 29 scripts to help DBAs and developers manage the OLAP option.

Oracle OLAP How To - The purpose of this page is to cover basic principles and recommendations around generic tuning of a database running the Oracle OLAP option. This page makes the assumption that tuning the OLAP option takes precedence over any other application or option on the server.

Did You Know? - this covers how to use the OLAP Option with other key database features such as :

Flashback
Recylce Bin
Resumable operations
Virtual Private Databases
Job Scheduler
ADDM/AWR
Real Application Custers

Diagnostic Techniques - for those using the OLAP option. At the moment we have two entries but we are planning to add a lot more over time, and feel free to upload your own scripts as well.

Diagnose OLAP API Client Sessions with SYS.OLAP$ALTER_SESSION (i.e., BI Beans, Discoverer for OLAP, Spreadsheet Add-in) (Link)
Relevant Diagnostic Parameters for the Oracle OLAP option (Link)

Monitoring OLAP Builds

2008-03-18T07:30:00.000-07:00

Recently I was working on a project where the customer’s server was in out South Africa office and I was running various build configurations testing data loading performance. In the past when I have been monitoring build times I always used SQLDeveloper’s excellent auto refresh facility. Firstly, it is worth downloading the “Scripts for OLAP DBAs” created by Jameson White (who also contributes to this blog and is very active on the Oracle OLAP Wiki).

http://www.oracle.com/technology/products/bi/olap/OLAP_DBA_scripts.ZIP

These scripts are extremely valuable during tuning exercises. Jameson also provided me with two additional scripts for monitoring the XML_LOAD_LOG table, which holds all the build messages generated during a data load process. In the SQLDeveloper tree below you can see all these scripts

(Note, the OWB team has also provided a set of predefined scripts/reports as well). Here is the main report for the XML_LOAD_LOG table that generates a report based on the whole table:

The code for this report is here:

select XML_LOADID as "Load ID"
, XML_RECORDID as "Record ID"
, XML_AW as "AW"
, XML_DATE as "Date"
, TO_CHAR(XML_DATE, 'HH24:MM:SS') as "Actual Time"
, substr(XML_MESSAGE, 1, 9) as "Message Time"
, substr(XML_MESSAGE, 9) as "Message"
from olapsys.xml_load_log order by 1 desc, 2 desc

The other report allows you to focus on a single job, which is passed to the report as a parameter:

and the code for this report is here:

select XML_LOADID as "Load ID"
, XML_RECORDID as "Record ID"
, XML_AW as "AW"
, XML_DATE as "Date"
, TO_CHAR(XML_DATE, 'HH24:MM:SS') as "Actual Time"
, substr(XML_MESSAGE, 1, 9) as "Message Time"
, substr(XML_MESSAGE, 9) as "Message"
from olapsys.xml_load_log
where XML_LOADID = :i_LoadId
order by 1 desc, 2 desc

Once you have installed the reports into SQLDeveloper you can then use the auto refresh feature to keep each report up to date. SQLDeveloper lets you set the refresh rate for 5, 10, 15, 20, 25, 30, 60, or120 seconds

This works well if you have a good connection to your remote server and most importantly you can stay connected during the build process. Unfortunately, my connection to South Africa was very slow and sometimes it was necessary to run a job in background mode and just disconnect and walk away. Which causes a problem of know when the build has actually completed?

To help resolve this I created some utilities to help resolve this particular issue. Using the utl_smtp package that is part of the database I created a routine that would scan the XML_LOAD_LOG table and once a build was complete it would send me an email.

For ease of use I created three procedures to monitor:

Dimension builds
Cube builds
AW builds

Depending on what is being monitored the title and body of the email changes accordingly. For example when monitoring a dimension build:

The email that is sent has the title:

Data Load for PRODUCTS finished at 15:43:41

And the message body can contain either just three simple lines:

07-MAR-08 14:03:20 Started Loading Dimension Members for PRODUCTS.DIMENSION (1 out of 1 Dimensions).
07-MAR-08 14:03:20 Started Loading Dimension Members for PRODUCTS.DIMENSION (1 out of 1 Dimensions).
Total Time 00:00:00

Or the body of the message can contain the complete XML_LOAD_LOG for that job, for example:

07-MAR-08 14:03:33 Completed Build(Refresh) of SH_OLAP.SH_AW Analytic Workspace.
07-MAR-08 14:03:22 Finished Updating Partitions.
07-MAR-08 14:03:21 Started Updating Partitions.
07-MAR-08 14:03:21 Finished Loading Dimensions.
07-MAR-08 14:03:21 Finished Loading Attributes.
07-MAR-08 14:03:21 Finished Loading Attributes for PRODUCTS.DIMENSION. 6 attribute(s) LONG_DESCRIPTION, PACK_SIZE, SHORT_DESCRIPTION, SUPPLIER_ID, UNIT_OF_MEASURE, WEIGHT_CLASS Processed.
07-MAR-08 14:03:21 Started Loading Attributes for PRODUCTS.DIMENSION (1 out of 1 Dimensions).
07-MAR-08 14:03:21 Started Loading Attributes.
07-MAR-08 14:03:21 Finished Loading Hierarchies.
07-MAR-08 14:03:21 Finished Loading Hierarchies for PRODUCTS.DIMENSION. 1 hierarchy(s) STANDARD Processed.
07-MAR-08 14:03:20 Started Loading Hierarchies for PRODUCTS.DIMENSION (1 out of 1 Dimensions).
07-MAR-08 14:03:20 Started Loading Hierarchies.
07-MAR-08 14:03:20 Finished Loading Dimension Members.
07-MAR-08 14:03:20 Finished Loading Members for PRODUCTS.DIMENSION. Added: 0. No Longer Present: 0.
07-MAR-08 14:03:20 Started Loading Dimension Members for PRODUCTS.DIMENSION (1 out of 1 Dimensions).
07-MAR-08 14:03:20 Started Loading Dimension Members.
07-MAR-08 14:03:19 Started Loading Dimensions.
07-MAR-08 14:03:19 Attached AW SH_OLAP.SH_AW in RW Mode.
07-MAR-08 14:03:19 Started Build(Refresh) of SH_OLAP.SH_AW Analytic Workspace.
Total Time 00:00:14

The code will monitor both foreground and background jobs but for the background jobs access to the scheduler is required. For both types of job access to DBMS_LOCK.SLEEP function to allow the code to continuously loop while the job continues to process. During each loop of checking to see if the specified load has completed the DBMS_LOCK.SLEEP forces the monitoring process to sleep for 60 seconds (if anyone knows of a better way to do this please let me know).

Overview of the Code
The monitoring code is split into two packages with associated procedures:

AW_Monitor

Dim_Build

Cube_Build.

Aw_Build

Send_Complete_Log

Send_Mail

Monitor_Sched_Process

Create_Job

Drop_Job

Dim_Build
This procedure monitors the build process for a dimension, looking for the string 'Finished Loading Members for ' to determine if the build has completed.

Cube_Build
This procedure monitors the build process for a dimension, looking for the string 'Finished Auto Solve for Measure' to determine if the build has completed.

Aw_Build
This procedure monitors the build process for a dimension, looking for the string 'Completed Build(Refresh) of ' to determine if the build has completed.

Send_Complete_Log
Emails the complete log file for a build

Send_Mail
This procedure sends an email containing just the Start and End messages from the build being monitored

Create_Job
This procedure creates a new job within DBMS_SCHEDULER but does not enable the job

Drop_Job
This removes the job from DBMS_SCHEDULER

What to Monitor?
There are three monitoring options:

Dimension
Cube
AW

All this really does is determine the definition of the string used in the search criteria within the XML_MESSAGE column. If you want to know when a specific dimension has completed its refresh then use DIM_BUILD procedure. If you want to know when a specific cube has completed its refresh then use CUBE_BUILD procedure. The final procedure, AW_BUILD, monitors the refresh of the AW, which can be useful if you are refreshing lots of cubes and/or dimensions within a single job.

How to Monitor a Foreground Job
Monitoring a foreground job is relatively easy. If you want to run a job that maintains a dimension called product and then have an email sent once the refresh of the dimension has completed then you would use the DIM_BUILD procedure. The parameters for each procedure are much the same. You need to provide:

Schema name
AW Name
Object name (dimension name or cube name)
Report Type (Summary or Full)
Job Name (if the monitor process is being scheduled)

So the command would be as follows:

EXEC AW_MONITOR.DIM_BUILD('SH_OLAP', 'SH_AW', 'PRODUCTS', 'SUMMARY', null);

How Monitor a Background Job It is important to schedule the monitoring of XML_LOAD_LOG to start after the AW job has started. Therefore, you need to set the time passed to CREATE_JOB procedure to a point in time after the BuildDate details in the AW XML script.

Job Name
Script to run
Date and Time to run
Job Description

So the command would be as follows:

exec monitor_sched_process.create_job('MONITOR_PROD_1','aw_monitor.dim_build(''SH_OLAP'',''SH_AW'', ''PRODUCTS'', ''SUMMARY'', ''MONITOR_PROD_1'')', '07-MAR-2008 15:46:00', 'Starts the monitor of PRODUCTS dimension build');

It is important to schedule the monitoring of XML_LOAD_LOG to start after the AW job has started. Therefore, you need to set the time passed to CREATE_JOB procedure to a point in time after the BuildDate details in the AW XML script.

You can review the job details via the Scheduler Jobs page in Enterprise Manager. Here you can see an AW build process is scheduled to run at 3:45 and the monitoring job, ‘MONITOR_PROD_1’, is scheduled to run at 3:46.

You can use the features in Enterprise Manager to halt the job at any point in time via the delete button. Once the job itself has completed, i.e. the email is sent, the job is stopped and removed from the job queue.

Possible Code Changes
Before running the code you may need to change the recipient, from, and mail server details in the AW_MONITOR package. Each of the monitoring procedures has a call to SEND_MAIL procedure that includes the name of the “To” part of the email. This would need to be changed unless you want to send all your emails to me.

send_mail('keith.laker@oracle.com', v_title, v_body);

The procedure SEND_MAIL has the following lines that need to changed

msg_from VARCHAR2(50) := 'keith.laker@oracle.com';
mailhost VARCHAR2(30) := 'mail.oracle.com';

The Code
A note of caution - I am not a brilliant PL/SQL coder, therefore, I am sure most of the code I have created can be improved. I am not going to post all the code here, as I suspect it will cause problems. At the moment I cannot find a convenient location to host the Zip file containing the two PL/SQL packages, therefore, if you want the code send me an email (keith.laker@oracle.com) and then I will send you the zip file.

There are two basic packages:

AW_MONITOR
MONITOR_SCHED_PROCESS

The AW_MONITOR package contains the following procedures:

Dim_Build - This procedure monitors the build process for a dimension, looking for the string 'Finished Loading Members for ' to determine if the build has completed.
Cube_Build - This procedure monitors the build process for a dimension, looking for the string 'Finished Auto Solve for Measure' to determine if the build has completed.
Aw_Build - This procedure monitors the build process for a dimension, looking for the string 'Completed Build(Refresh) of ' to determine if the build has completed.
Send_Complete_Log - Emails the complete log file for a build
Send_Mail - This procedure sends an email containing just the Start and End messages from the build being monitored

Example code:
monitoring a build for a specific dimension. The parameters are schema, AW Name, Dimension Name, email type:
exec aw_monitor.dim_build('SH_OLAP', 'SH_AW', 'PRODUCTS', 'SUMMARY');

monitoring a build for a specific cube. The parameters are schema, AW Name, Cube Name, email type:
exec aw_monitor.cube_build('SH_OLAP', 'SH_AW', 'SALES', 'SUMMARY');

monitoring a build for a specific AW. The parameters are schema, AW Name, Cube, email type
exec aw_monitor.aw_build('SH_OLAP', 'SH_AW', 'SUMMARY');

The above procedures all finish by sending an email to the specified recipients, where the body of the email can either be a summary of the build (just the start and end times) or the complete build log for the dimension, cube or AW.

exec send_mail('keith.laker@oracle.com', 'Data Load for PRODUCT finished at 12:00pm', 'Start at...., Finished at.....);

exec send_complete_log(795, 'keith.laker@oracle.com', 'Data Load for PRODUCT finished at 12:00pm');

The result is an email delivered to your Inbox:

Then depending on the parameter that controls the body of the email, the body will contain either the complete log from XML_LOAD_LOG table:

or simply a summary containing the start and end times for the build process:

The MONITOR_SCHED_PROCESS package contains the following procedures:

create_job - This procedure creates a new job within DBMS_SCHEDULER but does not enable the job
drop_job - This removes the job from DBMS_SCHEDULER

Example code:

exec monitor_sched_process.create_job('AW_DIM_MONITOR',aw_monitor.dim_build('SH_OLAP','AH_AW', 'PRODUCTS', 'AW_DIM_MONITOR', 'SUMMARY'), '23-JAN-2008 12:15:00', 'Starts the monitor of the PRODUCTS dimension build');

exec monitor_sched_process.drop_job('AW_DIM_MONITOR');

Comparing Materialized Views and Analytic Workspaces in Oracle Database 11g

2008-03-13T17:56:00.000-07:00

One of the great new features of Oracle OLAP in 11g is the Cube Organized Materialized Views. This feature allows OLAP Analytic Workspaces (AWs) to be included in the Summary Management capabilities of the DB including automatic query rewrite and refresh. This provides the same capabilities of Materialized Views (MVs), a feature which has been part of the database since 8i. Now that 11g supports both AWs and MVs the question of when to use relational materialized views and when to use OLAP AWs becomes more interesting.

Oracle has created a new white paper that explains the shared capabilities and differences of both of these powerful features.

A key benefit of this capability, from the whitepaper, is that "Materialized views are used for pre-computed results for both relational and multi-dimensional data: the cost based optimizer can now rewrite SQL queries to the analytic workspace and materialized views can be defined over the cubes."

This ultimately provides greater capabilities to increase performance and provide robust analytics without requiring the BI tool or SQL programmer to understanding where the summary data is. Performance and advanced analytical functions are provided automatically whenever they exist.

Here is an overview of the materialized view architecture taken from the white paper.

I will be posting more information and examples of how this feature can be used to improve BI and Analytical applications.