Petrol and OLAP

According to this NYT article (Big Rise Seen in Demand for Energy - New York Times), "It started with a simple question by Samuel W. Bodman, the energy secretary: What does the future hold for supplies of oil and natural gas?".

In order to answer this question, the National Petroleum Council developed an analytic data warehouse using Oracle relational and OLAP technology. Implemented by Vlamis Consulting, the NPC report states:

The data warehouse was designed to be the main analytical tool for the Task Groups, accepting all data collected from the survey questionnaire and other data sources. As the survey data were multi-dimensional, Oracle OLAP database technology was used and the collection was organized using 7 dimensions:
1. Time (year)
2. Geography (country or geographic region)
3. Energy type (e.g., Oil, Gas, Coal, Nuclear, Renewable)
4. Energy sector (e.g., Commercial, Residential)
5. Case type (e.g., Business as usual, Alternative energy policy)
6. Units (applicable unit of measure)
7. Source (e.g., Public, Proprietary)

The statistics contained in the report posed interesting technical challenges, including non-additive data, skip-level hierarchies, non-standard aggregation rules, and more - all of which Oracle OLAP is designed to manage. Discoverer OLAP was used to analyze the data.

You can find more on the Vlamis blog here (Vlamis Software » Blog Archive » Vlamis Used Oracle OLAP For National Petroleum Council Study)

Thanks to Marty Gubar from OLAP Product Management for passing this on.

How to Hide Measures for Discoverer OLAP and Excel Addin Users

There has been an on-going thread on the OLAP OTN forum regarding the process of hiding measures within both Discoverer for OLAP and the Excel Addin. As I have now managed to implement this feature, thanks to contributions from Mark O’Donnell and many others, I thought it would make for an interesting post.

Within an AW is possible to scope down the list of available dimension members using the PERMIT_READ command (another post on this subject later) and this process is quite well documented. However, removing measures has (at least for me) been a more difficult problem to resolve. When I developed the latest BI10g common schema sample analytic workspace I expanded the number of measures available to over a hundred measures. Compared to the original 9i schema the latest version has a lot of new features. However, the Query Builder measure panel now looks like this:




As the measures are split by functional area (customer analytics, sales analytics and brand analytics) it makes sense to have three different users and scope down the list of available measures accordingly.

To create the environment to manage the scoping process I added a new user called SH_SCOPE to my instance and created a table within this schema call SH_AW_SECURITY:

CREATE TABLE "SH_SCOPE"."SH_AW_SECURITY"
("USERID" VARCHAR2(50 BYTE)
,"MEASURE_NAME" VARCHAR2(250 BYTE));


The table contains the list of measures a user is able to access. To populate the table for the user SH_OLAP to only view he sales analytics cube I inserted the following rows into the SH_AW_SECURITY table viaSQL Plus.

insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.REVENUE.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.COSTS.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.QUANTITY.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.C_MA_12M.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.C_MA_6M.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.C_PC_PP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.C_PC_PY.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.SR_MA_12M.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.SR_MA_6M.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.SR_PC_PP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.SR_PC_YA.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.PROFIT.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.MARGIN.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.P_MA_12M.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.P_MA_6M.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.P_PC_PP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.P_PC_PY.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.GSP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.GSPC.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.GSPP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.GST.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.GSTC.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.GSTP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.PSP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.PSPC.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.PSPP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.PST.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.PSTC.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.PSTP.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.SCTREND.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.SPTREND.MEASURE');
insert into SH_AW_SECURITY values('SH_OLAP', 'SALES.SRTREND.MEASURE');

Each user will next SELECT priviledges on this table as an OLAP DML program will be used to query the contents of the table as will be shown later.

The next step is to add a procedure to the D4OSYS schema that will be called each time a tool connects to a schema via OLAPI connection. The procedure is called D4O_AUTOGO and can be relativelty simple:

create or replace PROCEDURE D4O_AUTOGO (user_id IN VARCHAR) authid current_user
is
BEGIN dbms_aw.aw_attach(‘SH_OLAP.SH_AW’, false, false, 'FIRST');
END;

In this case as I know the only AW this user will access is called SH_AW, I can hard code the reference in to the procedure. Alternatively, the procedure could be made more generic by using a cursor to return a list of all available AWs and then attach each one in turn, with each AW being scoped according to its own PERMIT_READ command (more on this later):

The AW_ATTACH procedure attaches an existing analytic workspace to your SQL session so that you can access its contents. The analytic workspace remains attached until you explicitly detach it, or you end your session.

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 more information on this procedure please refer to OLAP 10g Reference manual.

Please note I am not a PL/SQL expert so I am sure this code could be optimised further. It is important to test this procedure to make sure it works as expected before moving on to the next series of steps. This procedure will get called every time a tool makes a connection to a schema via the OLAPI. This forces each AW to be attached in read-only mode.

The next step is to ensure each user has execute rights to D4O_AUTOGO, so from the D4OSYS account simply GRANT EXECUTE rights to SH_OLAP.

The last step is to create an OLAP DML program called PERMIT_READ that will scope down the list of available measures when the AW is attached in read-only mode. This is an important point – the program should not execute when the AW is attached in read-write mode as this will affect the metadata used by Analytic Worksapce Manager and Warehouse Builder. If the code is executed with the AW is attached in read-write mode it is likely that both AWM and OWB will fail to work correctly with the AW.

Using Analytic Workspace Manager you need to attach to the required workspace in read-write mode. Then open the OLAP Worksheet and define a new program called PERMIT_READ:

DEFINE PERMIT_READ PROGRAM BOOLEAN

Permit read needs to return a boolean answer of ‘Yes’ to allow the attach process of the AW to complete successfully. Personally, I prefer to call a program from PERMIT_READ rather than coding directly within this program. Therefore, create another program called SET_MEASURES to manage the code to hide the measures. The code can either directly list the measures to be excluded within a SWITCH/CASE or IF THEN/ELSE block or alternatively, in this case I stored the list of available measures in the relational table created earlier. The program can query this table directly as follows:

DEFINE SET_MEASURES PROGRAM BOOLEAN
argument T_USER text

variable T_LIST text
variable T_MEASURE text

variable B_RETURN boolean

trap on error noprint
B_RETURN = NO

" Check to make sure AW is attached in read-only mode
if aw(RO 'SH_OLAP.SH_AW')
then do
" Make sure AW is the first attached in the list
AW ATTACH SH_OLAP.SH_AW RO FIRST

" Find the current user
SQL DECLARE USER_cur CURSOR FOR SELECT USER FROM DUAL
SQL OPEN USER_CUR
sql fetch user_cur into :T_USER

" Find all the measures for the current user
SQL DECLARE measure_cur CURSOR FOR SELECT measure_name FROM sh_scope.sh_aw_security WHERE userid=:T_USER
if sqlcode ne 0
then signal dclerror 'SQLERRM'

SQL OPEN measure_cur
if sqlcode ne 0
then signal dclerror 'SQLERRM'

while SQLCODE eq 0
do
sql fetch measure_cur into :T_MEASURE
T_LIST = joinlines(T_LIST, T_MEASURE)
doend

SQL CLOSE measure_cur
SQL CLEANUP

" Limit to the measures the user is allowed to see
limit ALL_MEASURES to charlist(T_LIST)
" Swap the limit to set the measures to all those the user is not allowed to see
limit ALL_MEASURES complement
for ALL_MEASURES
do
CNS &remchars(CHANGECHARS(ALL_MEASURES, '.', '_'), findchars(ALL_MEASURES, 'MEASURE')-1, NUMCHARS(ALL_MEASURES))

" Remove the properties for specified measures to remove them the Query Builder
" measure panel

PROPERTY DELETE 'AW$MEASUREDEF'
PROPERTY 'AW$ROLE' NA
doend
doend

B_RETURN = YES

ALLDONE:
return B_RETURN

ERROR:
B_RETURN = NO
goto ALLDONE

END

Make sure the program compiles correctly before proceeding. Within the PERMIT_READ simply call SET_MEASURES to execute the code. You can test the procedure by simply attaching your AW in read-only mode, the program should execute automatically and without errors.

Now when the user SH_OLAP logs into Discoverer Plus the Query Builder panel is scoped down to just the measures for sales analysis.





and the same is true for Excel:

Petrol and OLAP oracle

According to this NYT article (Big Rise Seen in Demand for Energy - New York Times), "It started with a simple question by Samuel W. Bodman, the energy secretary: What does the future hold for supplies of oil and natural gas?".

In order to answer this question, the National Petroleum Council developed an analytic data warehouse using Oracle relational and OLAP technology. Implemented by Vlamis Consulting, the NPC report states:

The data warehouse was designed to be the main analytical tool for the Task Groups, accepting all data collected from the survey questionnaire and other data sources. As the survey data were multi-dimensional, Oracle OLAP database technology was used and the collection was organized using 7 dimensions:
1. Time (year)
2. Geography (country or geographic region)
3. Energy type (e.g., Oil, Gas, Coal, Nuclear, Renewable)
4. Energy sector (e.g., Commercial, Residential)
5. Case type (e.g., Business as usual, Alternative energy policy)
6. Units (applicable unit of measure)
7. Source (e.g., Public, Proprietary)

The statistics contained in the report posed interesting technical challenges, including non-additive data, skip-level hierarchies, non-standard aggregation rules, and more - all of which Oracle OLAP is designed to manage. Discoverer OLAP was used to analyze the data.

You can find more on the Vlamis blog here (Vlamis Software » Blog Archive » Vlamis Used Oracle OLAP For National Petroleum Council Study)

OLAP Workshop Part 2 : Understanding OLAP Technology

OLAP Workshop Part 2 : Understanding OLAP Technology


In the last posting I hopefully explained some of the basic concepts behind OLAP. In this posting I want to explore how those basic concepts are exposed by the various OLAP aware ETL and reporting tools provided by Oracle and other BI vendors.

Architecture of Oracle OLAP




For a long time now Oracle has been unique in the marketplace. With Oracle Database 9i, 10g and 11g, all data (relational and multidimensional) is stored in one Oracle database. Only Oracle OLAP provides native multidimensional data types within the database.

A high-level architectural view of the Oracle OLAP option contains three parts:

Oracle Database 10g OLAP option, which comprises:

• Multidimensional data types
• OLAP calculation engine
• Open-access interfaces

End-user tools, which provide access to OLAP data for a wide spectrum of analytic needs
Administrative tools used to create and manage multidimensional data types. Oracle provides two administrative tools that can be used to create multidimensional data types in Oracle Database 10g:

• Oracle Warehouse Builder
• Analytic Workspace Manager

The following sections examine these three layers in more detail.

Components of Oracle OLAP
With the Oracle OLAP option, you get two powerful arenas of functionality:

• OLAP API and the analytic workspace (AW).
• OLAP API Functionality

Analytic Workspace The Analytic Workspace is the container for the multidimensional data types and leverages the multidimensional calculation engine of the OLAP Option. The AW also provides a standard SQL interface to the multi-dimensional model. This provides an industry standard access layer that can be used by any BI reporting tool that generates SQL commands, from SQL Developer, to Application Express on to more sophisticated tools such as BI EE.

The AW also povides an XML API for administration, and a programming language (OLAP DML).

Multidimensional Data Store The OLAP option provides true array-based multidimensional data types within the Oracle database. These multidimensional data types are contained in special tables in Oracle called analytic workspaces. Some data types are used to store data, whereas others are calculated instantaneously using the multidimensional engine.

Multidimensional Calculation Engine The OLAP Option’s multidimensional engine includes an impressive library of multidimensional-aware calculation functions and support for planning functionalities such as statistical forecasts, models, allocations, projections, and “what-if”scenarios, in the context of Analytic Workspaces. The multidimensional engine interacts with the multidimensional data types in the analytic workspace in the Oracle database.

The Oracle OLAP option provides a specialized Java API that developers can use to exploit the full power of the Oracle OLAP option by using advanced dimensionally-aware tools and applications. This API is used by Oracle Business Intelligence tools such as OracleBI Beans, OracleBI Discoverer, OracleBI Spreadsheet Add-In, and Oracle Reports OLAP Plug-in to provide a true multi-dimensional query and calculation environment.

OLAP DML The OLAP DML is an extremely powerful and analytically rich feature of the AW. It is a dimensionally-aware, high-level procedural language that runs in the database and exploits the multidimensional engine and multidimensional data types.

With the Oracle OLAP option developers can exploit the OLAP DML to add more sophisticated calculations and analysis to AWs and to extend the functionality of applications that access them. The OLAP DML is briefly introduced in the lesson titled “Previewing Advanced Oracle OLAP Features.”

AW API The AW API is a Java API that is used to define and physically build multidimensional analytic workspaces inside Oracle Database 10g. The AW API is used by administrative tools such as Analytic Workspace Manager and may be used by developers, if required, to enhance and extend analytic workspaces as necessary for a specific application.

Query Access to Oracle OLAP
Different users with different end-user tool requirements can all access the same data, taking advantage of the same calculations, and benefit from the same security, scalability, performance, and availability of the Oracle database.




OLAP API The OLAP API is designed to work with both multidimensional data types and relational data types in the Oracle database.

• The API enables you to directly access multidimensional data types in the AW.
• To access relational data types, you can register a relational physical dimensional model (such as a star or snowflake schema) to the Oracle Database OLAP Catalog.

Therefore, tools that use the OLAP API can be deployed against multidimensional analytic workspaces or suitable relational schemas that are registered to the Oracle OLAP option’s metadata layer.


Many Oracle source business intelligence tools take full advantage of the multidimensional query data model provided by the Oracle OLAP option through the OLAP API. These include OracleBI Spreadsheet Add-In, OracleBI Discoverer, OracleBI Beans, and OracleBI Reports.

SQL Interface
Unlike other multidimensional OLAP server products, Oracle OLAP provides not only a specialized API but also industry-standard SQL to access multidimensional data types.

You can use a simple SQL query with relationally oriented tools and applications to gain access to the multidimensional data types in the Oracle database. As a result, your SQL-based applications (such as report generators and ad hoc query tools) can access multidimensional data and calculations managed by the Oracle OLAP option.
SQL and PL/SQL are also used to manage and maintain multidimensional analytic workspaces and to move data between relational and multidimensional data types within the Oracle database.

For example, Oracle Application Express is an easy-to-use tool that is supplied with Oracle Database 10g for Web access to the Oracle database. Oracle Application Express is an example of a SQL-based application with no built-in OLAP knowledge that can nevertheless leverage the power of Oracle OLAP.

Many third-party tools from independent software vendors and Oracle partners, such as Arcplan, Business Objects, Cognos, and a large and growing number of business intelligence vendors throughout the world, access OLAP data through the Oracle OLAP option. Some of these vendors’ tools leverage the OLAP API, whereas others exploit the SQL query interface.

Dimensionally Aware Products
Oracle BI Spreadsheet Addin OracleBI Spreadsheet Add-In makes it easy to access OLAP data through the familiar spreadsheet environment of Microsoft Excel. After installation of OracleBI Spreadsheet Add-In, “OracleBI” appears as a new menu item in Excel. By using OracleBI Spreadsheet Add-In, you can establish a secure connection to the OLAP data source and use Excel as the front-end access tool to the data in the database.

Here are some of the features of OracleBI Spreadsheet Add-In:

• It combines the flexibility and familiarity of Excel and the power, scalability, and security of the Oracle OLAP option.
• OracleBI Query and Calculation Builders: After the connection is established, you can use the wizard-driven interface to drill, pivot, page through large cubes, and create reports.
• Access to native Excel features
• Powerful data-formatting features of Excel,
• Combine Oracle OLAP data with other Excel data
• Write Excel macros that leverage all your data.
• Create formulas and graphs in Excel

Excel users can quickly and easily combine the powerful analytic capabilities of Oracle OLAP with standard Excel functions that you know and use each day.



When using Excel OLAP calculations are performed directly in the database: The benefit of using OracleBI Spreadsheet Add-In is that you no longer need to download massive amounts of data to your spreadsheet. Oracle Database 10g OLAP performs all the OLAP calculations quickly and efficiently in the database. The calculations and business logic are defined only once in the database and then shared across the user community.

For more information goto the Spreadsheet Addin home page on OTN:

Spreadsheet Addin OTN Home Page
http://www.oracle.com/technology/products/bi/spreadsheet_addin/index.html

OracleBI Spreadsheet Add-In Feature Overview
http://www.oracle.com/technology/products/bi/spreadsheet_addin/htdocs/feature_overview/oraclebi_spreadsheet_addin_fov.htm

Introduction to OracleBI Spreadsheet Add-In
http://www.oracle.com/technology/products/bi/spreadsheet_addin/viewlets/olapsa_welcome_viewlet_swf.html



OracleBI Discoverer
This is another Oracle Business Intelligence tool that can directly access Oracle OLAP data. Discoverer Plus OLAP is an ad hoc query, reporting, analysis, and Web-publishing tool. It enables you to:

• Perform OLAP query, reporting, and analysis on both multidimensional data models (analytic workspaces) and relational OLAP data models (star or snowflake schemas).
• Access and analyze multidimensional data from your company’s database without having to understand complex database concepts. The wizards and menus of Discoverer Plus OLAP guide you through the steps to retrieve and analyze multidimensional data.

Because Discoverer Plus OLAP understands the dimensional data model, you formulate your queries in the language of business — you use real dimensions, hierarchies, levels, and measures through a simple interface. You can also exploit the rich features of OLAP through dimensionally-aware query and calculation builders, thereby simplifying the tasks of defining queries and calculations. Worksheets that are authored in Discoverer Plus OLAP are published to the Web, where Discoverer Viewer and Oracle Portal users can access them.




For more information goto the Discoverer home page on OTN:

Discoverer OTN Home Page
http://www.oracle.com/technology/products/discoverer/index.html

Creating OLAP worksheets
http://www.oracle.com/technology/products/discoverer/files/viewlets/1012_plus_olap_creating.html

Modifying OLAP worksheet properties
http://www.oracle.com/technology/products/discoverer/files/viewlets/1012_Plus_OLAP_Modifying.html


OracleBI Beans
OracleBI Beans is used by business intelligence and OLAP developers. OracleBI Beans is used for developing applications such as Oracle Enterprise Planning and Budgeting and tools such as OracleBI Discoverer and OracleBI Spreadsheet Add-In. BI Beans is also available to third-party software developers to accelerate development of custom OLAP applications.




BI Beans is a set of standards-based Java beans that is integrated into Oracle JDeveloper. It provides analysis-aware application building blocks designed for the Oracle OLAP option of the Oracle database. Using BI Beans, you can create customized business intelligence applications that take advantage of the robust analytic capabilities of Oracle OLAP.

Applications can include advanced features such as interactive user interfaces, drill-to-detail reports, forecasting, and what-if analysis. BI Beans includes Java beans for acquiring data from the Oracle database, presenting data in a variety of crosstab and graph formats, and saving report definitions, custom measures, and data selections.
Using BI Beans, you can develop business intelligence applications from Oracle JDeveloper, or any Java application development environment, and deploy them through any application server as a thin or thick client.

For more information goto the BI Beans home page on OTN:

BI Beans OTN Home Page
http://www.oracle.com/technology/products/bib/index.html

Oracle BI Beans Feature Overview
http://www.oracle.com/technology/products/bib/1012/htdocs/feature_overview/BI_Beans_Feat_Oview.htm

Developing a Dashboard Application with Oracle BI Beans
http://www.oracle.com/technology/products/bib/1012/viewlets/MS Developing Executive Insight.html


SQL Aware Products
Oracle Business Intelligence EE
Oracle Business Intelligence Suite Enterprise Edition 10g, Release 3 (BI EE 10g ) delivers significant new product enhancements to further enable enterprise-wide BI, including integration with Oracle OLAP. In this release, Oracle's native multidimensional data model -- the analytic workspace (AW) -- is made accessible to BI EE 10g by creating the required metadata in Oracle BI Administration Tool. The AW data is exposed to the BI EE 10g product stack, and the OLAP engine is leveraged for analysis of that data.

Creating access to Oracle OLAP data is a simple 3-step process. Each these steps is covered in detail as part of an training document provided as an Oracle by Example. This explains how to: prepare an AW for access by BI EE 10g; create the required metadata using Oracle BI Administration Tool; and create analytic reports of AW data using Oracle BI Answers




For more information goto the BI EE home page on OTN:

BI EE OTN Home Page
http://www.oracle.com/technology/products/bi/enterprise-edition.html

Ad-hoc query and reporting
http://www.oracle.com/technology/products/bi/enterprise-edition-platform-components.html

Oracle By Example:
These lessons are designed for completion in the order that is shown below. Each subsequent lesson depends on the completion of the previous lesson. Click on any of the links below to begin.

Lesson 1: Preparing an Analytic Workspace for Access by Oracle BI EE 10g
http://www.oracle.com/technology/obe/obe_bi/bi_ee_1013/olap/PrepareAW.htm

Lesson 2: Creating BI EE 10g Metadata for the Analytic Workspace
http://www.oracle.com/technology/obe/obe_bi/bi_ee_1013/olap/CreateMetadata.htm

Lesson 3: Querying OLAP Data Using Oracle BI Answers
http://www.oracle.com/technology/obe/obe_bi/bi_ee_1013/olap/QueryData.htm



Oracle Application Express
The Oracle OLAP option provides a SQL interface to access multidimensional data types, thus enabling any SQL-aware tool to access data in the analytic workspace.
Oracle Application Express is an easy-to-use report builder that is provided with Oracle Database 10g to simplify the creation of database-centric interactive Web pages.

Thus, with the Oracle OLAP option, you can use Oracle Application Express to provide Web-based access to key performance indicators such as profitability, sales, units shipped, trends, and period-to-period comparisons and forecasts.
It provides support for some interactive reporting (for example, you can use hypertext links to call more SQL queries), but it is not a fully interactive analysis system like OracleBI Discoverer or like other specialized business intelligence tools.




The Web report in the slide is generated with the following SQL code:

SELECT region_desc, sales, units, cost, profit,
ROUND(pct_margin ,3), fcast_sales
FROM mysalesaw_view
WHERE time_desc = TO_CHAR(ADD_MONTHS(SYSDATE,-3), 'Mon-YY')
AND product_level = ‘ALL'
AND channel_level = ‘ALL'
AND customer_level= 'REGION'
ORDER BY sales DESC;

Because the multidimensional data model presents data to the query layer as if it were precalculated, prejoined, and preaggregated, your query does not need to perform any calculations, joins, or aggregations. SQL code is thus very simple to write and fully leverages the power of the AW. The multidimensional engine returns the requested data from the AW extremely efficiently and quickly, even if the AW is calculating much of the data instantaneously.

For more information goto the Application Express home page on OTN:

Application Express OTN Home Page
http://www.oracle.com/technology/products/database/application_express/index.html

What is Oracle APEX?
http://www.oracle.com/technology/products/database/application_express/html/what_is_apex.html

3.0 New Features
http://www.oracle.com/technology/products/database/application_express/html/3.0_new_features.html



Tools to Build an Analytic Workspace

Two tools are available for IT and power users to easily build analytic workspaces (AWs) and load them with data (for analysis with tools such as OracleBI Beans, OracleBI Discoverer, and OracleBI Spreadsheet Add-In):



Shared API for AW Creation Both OWB and AWM use the AW XML API to build and maintain AWs. Therefore, an AW model that is created by AWM can be incorporated back into the OWB repository to provide version control, lineage, and impact analysis if changes to the original source systems are required.

Note: Both OWB and AWM generate scripts that may be called and scheduled from PL/SQL scripts or other scheduling processing tools, if required.

Analytic Workspace Manager (AWM) AWM is a tool that makes it easy to build and maintain AWs from a suitable (clean) data source. AWM is used on source data that has been cleaned by the ETL process.

The clean data may have been created by Oracle Warehouse Builder or by another ETL process; your IT department would have a preferred method of preparing data.
AWM is focused on the simple task of building AWs. It has an intuitive wizard-based user interface and is therefore suitable for both IT and power users.

AWM supports the complete process of creating an AW from beginning to end in a single, dimensionally-aware design environment.

The creation process includes three easy steps:

• Design the dimensional model.
• Map the dimensional object to the source data.
• Load the data into the AW.

After you have performed these steps, the AW is immediately available for your tools to query.

For more information goto the OLAP home page on OTN:

OLAP OTN Home Page
http://www.oracle.com/technology/products/bi/olap/olap.html

Analytic Workspace Manager 10.2.0.3.0A
http://www.oracle.com/technology/software/htdocs/devlic.html?url=http://download.oracle.com/otn/java/olap/AWM_102030A_Win32.zip

Analytic Workspace Manager 10g
http://www.oracle.com/technology/products/bi/olap/1451_AWM10g.pdf

Improve SQL Based Business Intelligence Tools with Oracle OLAP 11g
http://www.oracle.com/technology/products/bi/olap/Oracle_OLAP_11g_TWP.pdf

Leveraging Business Intelligence Tools
http://www.oracle.com/technology/products/bi/olap/40261_leveragingtools.pdf

Analytic Workspace Manager 10.2.0.3.0
http://www.oracle.com/technology/products/bi/olap/viewlet/AWM102_viewlet_swf.html


OracleBI Warehouse Builder 10g R2 (OWB) OWB is an advanced management and ETL tool, used by developers and database administrators to build and manage data warehouses in the Oracle database. OWB manages the entire process of collecting and cleaning data.

• Collecting data: OWB collects data from the various operational systems that feed the data warehouse.
• Cleaning data: OWB performs various required transformations and data-cleansing activities (for example, dealing with inconsistencies between different source systems, matching and merging data from them, and processing missing or erroneous data).

The result is a set of clean tables in the Oracle database.

• OWB includes advanced data-profiling features.
• OWB enables data warehouse developers to optionally populate relational star or snowflake schemas or multidimensional AWs for access by the Oracle OLAP option.
• OWB is, therefore, a professional IT tool.

Warehouse Builder is free to database customers and this free functionality includes deploying OLAP schemas. Some Warehouse Builder features are costed options and these include:

• Enterprise ETL
• Data Quality
• CRM/ERP Connectors

For more information goto the Warehouse Builder home page on OTN:

OTN Home Page
http://www.oracle.com/technology/products/warehouse/index.html

Oracle Warehouse Builder 10gR2 and Oracle OLAP
http://www.oracle.com/technology/products/warehouse/pdf/OWB10gR2 and Oracle OLAP.pdf

Benefits of a Multi-dimensional Model
http://www.oracle.com/technology/products/warehouse/pdf/Benefits of a multi-dimensional model.pdf


Summary
In this latest post you should have learned how to describe:

• Oracle OLAP and its components
• The flexibility of (and options for) accessing Oracle OLAP data
• Oracle business intelligence tools and applications that access the analytic workspace in the Oracle database
• Oracle administrative tools that enable building and maintenance of analytic workspaces