Many people have asked: is possible to use normal SQL to access a multi-dimensional cube and associated dimensions? The answer is yes. What this means is that customers using BI EE, BI Publisher, SQL Developer and any other SQL reporting tool can access a 10g R2 OLAP multi-dimensional cube. However, a small warning -
If you plan to use SQL access with an end-user query tool, that tool needs to be able to support embedded total objects.
The SQL access to an OLAP cube returns all the required data points already precomputed so there is no need to issue an aggregation function (for example SUM()) with a GROUP BY command as the aggregation is managed internally within the cube itself. Using SQL Developer most DBAs can probably work around this feature by manually writing the query themselves. But for business users the tool is supposed to do all the work for them so it needs to be “embedded total” aware. Fortunately, BI EE can support this type of data source, so BI EE customers can combine Oracle 10g OLAP data with data from their other non-olap data sources quickly and easily.
(Unfortunately, Discoverer does not at the moment support embedded total objects so integrating SQL access to OLAP into the EUL is currently not possible).
How do you SQL enable an OLAP cube?
For this example I will use the common schema sample shipped with BI 10g (SH_OLAP schema) which contains a dimension called PRODUCTS that has the following structure:
- Levels:
- All Products
- Categories
- SubCategories
- Products
- Attributes
- Long Description
- Short Description
- Level Description
- Supplier ID
- Unit of Measure
- Weight Class
- Pack size
To query the PRODUCTS dimension using SQL you have to create a view, which would look something like this:
PRODUCTS VARCHAR2(4000) WEIGHT_CLASS VARCHAR2(4000) PACK_SIZE VARCHAR2(4000) PRODUCTS_SDSC VARCHAR2(4000) PRODUCTS_LDSC VARCHAR2(4000) PRODUCTS_PRODUCT_LVLDSC VARCHAR2(4000) PRODUCTS_SUBCATEGOR_LVLDSC VARCHAR2(4000) PRODUCTS_CATEGORY_LVLDSC VARCHAR2(4000) PRODUCTS_PROD_TOP_LVLDSC VARCHAR2(4000) PRODUCTS_STANDARD_PRNT VARCHAR2(4000) PRODUCTS_LEVEL VARCHAR2(4000) PRODUCT_ID VARCHAR2(4000) SUPPLIER_ID VARCHAR2(4000) UNIT_OF_MEASURE VARCHAR2(4000)
The SQL query used to provide the data needs to be defined as follows:
CREATE OR REPLACE FORCE VIEW "SH_OLAP"."PRODUCTS_DIMVIEW" (
"PRODUCTS"
, "PRODUCTS_LEVEL"
, "PRODUCT_ID"
, "SUPPLIER_ID"
, "UNIT_OF_MEASURE"
, "WEIGHT_CLASS"
, "PACK_SIZE"
, "PRODUCTS_SDSC"
, "PRODUCTS_LDSC"
, "PRODUCTS_PRODUCT_LVLDSC"
, "PRODUCTS_SUBCATEGOR_LVLDSC"
, "PRODUCTS_CATEGORY_LVLDSC"
, "PRODUCTS_PROD_TOP_LVLDSC"
, "PRODUCTS_STANDARD_PRNT")
AS SELECT "PRODUCTS"
,"PRODUCTS_LEVEL"
,"PRODUCT_ID"
,"SUPPLIER_ID"
,"UNIT_OF_MEASURE"
,"WEIGHT_CLASS"
,"PACK_SIZE"
,"PRODUCTS_SDSC"
,"PRODUCTS_LDSC"
,"PRODUCTS_PRODUCT_LVLDSC"
,"PRODUCTS_SUBCATEGOR_LVLDSC"
,"PRODUCTS_CATEGORY_LVLDSC"
,"PRODUCTS_PROD_TOP_LVLDSC"
,"PRODUCTS_STANDARD_PRNT"
FROM table(OLAP_TABLE ('SH_OLAP.SH_AW duration session', '', '', '&(PRODUCTS_LIMITMAP)'))
MODEL DIMENSION BY ( PRODUCTS) MEASURES
( PRODUCTS_LEVEL
, PRODUCT_ID
, SUPPLIER_ID
, UNIT_OF_MEASURE
, WEIGHT_CLASS, PACK_SIZE
, PRODUCTS_SDSC
, PRODUCTS_LDSC
, PRODUCTS_PRODUCT_LVLDSC
, PRODUCTS_SUBCATEGOR_LVLDSC
, PRODUCTS_CATEGORY_LVLDSC
, PRODUCTS_PROD_TOP_LVLDSC
, PRODUCTS_STANDARD_PRNT )
RULES UPDATE SEQUENTIAL ORDER();
Now the view looks reasonably straightforward until you get about half through the code and notice the use of an OLAP_TABLE() function. This function returns a normal two-dimensional relational table structure from the source multi-dimensional object. The function has a number of inputs one of which is a call to a LIMIT_MAP. The OLAP_TABLE uses a limit map to map dimensions and measures defined in an analytic workspace to columns in a logical table. The limit map combines with the WHERE clause of a SQL SELECT statement to generate a series of OLAP DML LIMIT commands that are executed in the analytic workspace.
Oracle OLAP Reference Manual 10g Release 2 provides more details on the OLAP_TABLE() function.
In this example the limit map is called PRODUCTS_LIMITMAP and is stored within the analytic workspace and it looks like this:
DIMENSION PRODUCTS FROM PRODUCTS WITH
HIERARCHY PRODUCTS_STANDARD_PRNT FROM
PRODUCTS_PARENTREL(PRODUCTS_HIERLIST 'STANDARD')
INHIERARCHY PRODUCTS_INHIER FAMILYREL
PRODUCTS_PROD_TOP_LVLDSC
, PRODUCTS_CATEGORY_LVLDSC
, PRODUCTS_SUBCATEGOR_LVLDSC
, PRODUCTS_PRODUCT_LVLDSC
FROM PRODUCTS_FAMILYREL(PRODUCTS_LEVELLIST 'PROD_TOP')
, PRODUCTS_FAMILYREL(PRODUCTS_LEVELLIST 'CATEGORY')
, PRODUCTS_FAMILYREL(PRODUCTS_LEVELLIST 'SUBCATEGORY')
, PRODUCTS_FAMILYREL(PRODUCTS_LEVELLIST 'PRODUCT')
LABEL PRODUCTS_LONG_DESCRIPTION
ATTRIBUTE UNIT_OF_MEASURE FROM PRODUCTS_UNIT_OF_MEASURE
ATTRIBUTE SUPPLIER_ID FROM PRODUCTS_SUPPLIER_ID
ATTRIBUTE PRODUCT_ID FROM PRODUCTS_PRODUCT_ID
ATTRIBUTE PRODUCTS_LEVEL FROM PRODUCTS_LEVELREL
ATTRIBUTE PRODUCTS_LDSC FROM PRODUCTS_LONG_DESCRIPTION
ATTRIBUTE PRODUCTS_SDSC FROM PRODUCTS_SHORT_DESCRIPTION
ATTRIBUTE PACK_SIZE FROM PRODUCTS_PACK_SIZE
ATTRIBUTE WEIGHT_CLASS FROM PRODUCTS_WEIGHT_CLASS
It maps the AW objects to the columns referenced within the view. Having the limit map defined within the AW as a text variable makes it easier to modify the data that can be returned into the view by the analytic workspace.
The same techniques can be used to create a view to return data form a cube. Making a view over the sales cube, which contains 35 measures and attributes relating to 4 dimensions (Time, Channel, Geography, Products), would look something like this:
TIME VARCHAR2(4000)
CHANNELS VARCHAR2(4000)
GEOGRAPHIES VARCHAR2(4000)
PRODUCTS VARCHAR2(4000)
TIME_LEVEL VARCHAR2(4000)
TIME_LDSC VARCHAR2(4000)
TIME_CAL_MONTH_LVLDSC VARCHAR2(4000)
TIME_CAL_QTR_LVLDSC VARCHAR2(4000)
TIME_CAL_YEAR_LVLDSC VARCHAR2(4000)
TIME_CALENDAR_PRNT VARCHAR2(4000)
CHANNELS_LEVEL VARCHAR2(4000)
CHANNELS_LDSC VARCHAR2(4000)
CHANNELS_CHANNEL_LVLDSC VARCHAR2(4000)
CHANNELS_CLASS_LVLDSC VARCHAR2(4000)
CHANNELS_TOP_LVLDSC VARCHAR2(4000)
CHANNELS_STANDARD_PRNT VARCHAR2(4000)
GEOGRAPHIE_LEVEL VARCHAR2(4000)
GEOGRAPHIE_LDSC VARCHAR2(4000)
GEOGRAPHIE_COUNTRY_LVLDSC VARCHAR2(4000)
GEOGRAPHIE_SUBREGION_LVLDSC VARCHAR2(4000)
GEOGRAPHIE_REGION_LVLDSC VARCHAR2(4000)
GEOGRAPHIE_WORLD_LVLDSC VARCHAR2(4000)
GEOGRAPHIE_STANDARD_PRNT VARCHAR2(4000)
PRODUCTS_LDSC VARCHAR2(4000)
PRODUCTS_PROD_TOP_LVLDSC VARCHAR2(4000)
PRODUCTS_STANDARD_PRNT VARCHAR2(4000)
PRODUCTS_LEVEL VARCHAR2(4000)
PRODUCT_ID VARCHAR2(4000)
PRODUCTS_PRODUCT_LVLDSC VARCHAR2(4000)
PRODUCTS_SUBCATEGOR_LVLDSC VARCHAR2(4000)
PRODUCTS_CATEGORY_LVLDSC VARCHAR2(4000)
REVENUE BINARY_DOUBLE
SR_MA_12M BINARY_DOUBLE
SR_MA_6M BINARY_DOUBLE
SR_PC_PP BINARY_DOUBLE
SR_PC_YA BINARY_DOUBLE
GEOGRAPHY_SHARE_PARENT NUMBER
GEOGRAPHY_SHARE_TOTAL BINARY_DOUBLE
PRODUCT_SHARE_PARENT NUMBER
PRODUCT_SHARE_TOTAL BINARY_DOUBLE
CHANNEL_SHARE_TOTAL BINARY_DOUBLE
CHANNEL_SHARE_PARENT NUMBER
With a view over the cube it is now possible to select values from the cube from any level without having to issue an aggregation function or worry about using a GROUP BY command. For example, to create a report that shows the revenue, 12 month moving average, 6 month moving average, % growth from prior period, % growth from prior year for 1999, 2000 and 2001, for products at the Category level, for all channels and for all geographies the query could be written as follows:
COLUMN TIME_LDSC FORMAT A7
COLUMN PRODUCTS_LDSC FORMAT A35
COLUMN REVENUE FORMAT 999,999,999.99
COLUMN SR_MA_12M FORMAT 999,999,999.99
COLUMN SR_MA_6M FORMAT 999,999,999.99
COLUMN SR_PC_PP FORMAT 99.99 COLUMN
SR_PC_YA FORMAT 99.99
break on PRODUCTS_LDSC skip 1
select PRODUCTS_LDSC
, TIME_LDSC , REVENUE
, SR_MA_12M
, SR_MA_6M
, SR_PC_PP
, SR_PC_YA
from SALES_CUBEVIEW
where GEOGRAPHIE_LEVEL = 'WORLD'
AND TIME IN ('1803', '1804', '1805')
AND CHANNELS IN ('1')
AND PRODUCTS_LEVEL = 'CATEGORY'
ORDER BY PRODUCTS_LDSC , TIME_LDSC;
CLEAR COLUMN
The process for creating these special SQL views is now very simple thanks to a new plug-in for Analytic Workspace Manager. This has been created by OLAP product management team (Marty Gubar) and is an example of how to extend AWM using the new extensibility interface. Once you have downloaded and installed the addin you create your OLAP schema in the normal way. It is possible to create a relational view over both dimensions and cubes with the process being the same for both objects. Once you have loaded data into your analytic workspace right-mouse click on a dimension or cube and the “Create Relational View…” option should be visible at the bottom of the menu:
The first step of the wizard allows you to select the attributes and measures to make visible via the view. Be warned, you may have to deselect some attributes and/or measures to make this work. The reason being there is a SQL limit (4000 characters I think) within the process of defining a view, therefore, you may have to deselect some measures and/or attributes to successfully create the view. Unfortunately, no error message is displayed if things go wrong during the definition, however, when you try to select from the SQL view you may get an error message saying the LIMIT MAP does not exist. This implies you selected too many columns.
I am sure this issue will be resolved in a later build. For the moment it is not a major issue and the problem is quickly and easily resolved. Expand each node in the tree and deselect items that are not actually required:
Ideally when thinking about which views to create and the contents of those views you want to try and create views that minimize the need to use joins to create a result within the reporting tool such as BI EE. Therefore, it may be necessary to do some research first to determine which measures and attributes users actually need to allow them to create their reports. In other words do not try and expose every single attribute and measure.
This new addin to Analytic Workspace Manager (thanks to Marty Gubar) makes it very easy for BI EE customers to include data contained within a 10g OLAP cube as part of a BI EE report.
Useful Links
Analytic Workspace Manager
http://www.oracle.com/technology/software/htdocs/devlic.html?=http://download.oracle.com/otn/java/olap/AWM102030_Win.zip
Relational View Generator software
http://www.oracle.com/technology/products/bi/olap/viewGenerator_1_0.zip
Documentation: http://www.oracle.com/technology/products/bi/olap/ViewGenerator.html