OBI EE 10.1.3.3/2 and Oracle OLAP 10g

If you are wondering how to go about creating reports and building the metadata in OBI EE on Oracle OLAP 10g, do not despair. Documentation(A step by step manual in the form of Oracle by Example) is out and you can access them here. It was quite due for some time. But well worth the wait. Now you can get rolling with OBI EE and OLAP 10g :-).

Oracle 11g - CUBE_TABLE function for Oracle OLAP

If you are wondering how Oracle OLAP 11g exposes itself in the form of relational views, its all because of a new function called CUBE_TABLE. This function has been added new to 11g, to enable the OLAP cubes to be a part of the SQL Optimizer. This can be used on both Dimensions and Cubes to convert them into relational data. Lets see the syntax of this new function (taken from the docs) first.

SELECT …
FROM
TABLE(CUBE_TABLE(’arg’));

As you see above, the CUBE_TABLE takes either a cube name or a dimension name as its parameter. One also has options of specifying multiple hierarchies for dimensions and cubes. Let us try an example here.

SELECT
*
FROM
TABLE(CUBE_TABLE(’sh.product’));

Here, i am trying to display the dimension product in the form of a relational table. CUBE_TABLE function does that for us automatically.

      

Now lets try specifying a hierarchy clause for the same dimension. This would be helpful if you have multiple hierarchies defined in your dimension.

SELECT *
FROM
TABLE(CUBE_TABLE(’sh.product HIERARCHY producthierarchy’));

      

Lets try the same with a cube.

SELECT *
FROM
TABLE(CUBE_TABLE(’sh.salescube HIERARCHY product producthierarchy’));

      

The only difference between the above 2 is that in case of a cube we would have to specify the dimension name and the hierarchy name after the HIERARCHY clause. All the details about the CUBE_TABLE function is available here.

Oracle 10g/11g - OLAP, CUBE and ROLLUP

If you had gone through the list of features in Oracle 11g, one of the most important advancements/features in the BI space would be the introduction of Oracle OLAP as part of the DB optimizer (in the form of MV query rewrite). But for users who do not have the luxury of using Oracle OLAP 11g (or even 11g database) in their environment, dont lose hope yet :-). CUBE and ROLLUP functions had undergone quite a few advancements in themselves while in 10g itself and they can indeed mimic an OLAP cube starting from 10g. Let me just give an example here. All the advances done in CUBE and ROLLUP functions are documented here. If you had gone through my previous blog entry here on using OLAP 11g, i had used a specific example to create a cube. I would be using the same one here.

Dimensions:

Customers:

      Hierarchy:   All      ->      Customer Gender      ->      Customer Name

Product:

      Hierarchy:   All   ->   Product Pack Size   ->   Product Name

Times:

      Hierarchy:   All   ->   Fiscal Year   ->   Fiscal Month   -> Fiscal Day

Measures:

      Quantity Sold

      Amount Sold

Oracle 10g/11g now supports Hierarchical cubes within queries. As you would know, ROLLUP is a feature wherein one can have summed up data across a hierarchy. What is new in 10g & 11g is, one can have multiple ROLLUPs (more like hierarchies) in the group by clause which in turn would take a cartesian product of all the rollups in the group by clause. In order to get a cube like data for the above measures and dimensions, the query would look like this

SELECT CUST_GENDER, CUST_FIRST_NAME||CUST_LAST_NAME, PROD_PACK_SIZE, PROD_NAME, FISCAL_YEAR, FISCAL_MONTH_DESC, DAY_NAME, SUM(QUANTITY_SOLD) AS QUANTITY, SUM(AMOUNT_SOLD) AS SALES
FROM
SALES a, CUSTOMERS b, PRODUCTS c, TIMES d
where
a.CUST_ID = b.CUST_ID and
a.PROD_ID = c.PROD_ID and
a.TIME_ID = d.TIME_ID
GROUP BY
ROLLUP(CUST_GENDER, CUST_FIRST_NAME||CUST_LAST_NAME),
ROLLUP(PROD_PACK_SIZE, PROD_NAME),
ROLLUP(FISCAL_YEAR, FISCAL_MONTH_DESC, DAY_NAME)

               

And the plan for the above query would be like this

              

The above generates the same set of data that Oracle OLAP produces. But of course, oracle OLAP provides more features like partitioning and many more. This is just to kindle your interest on the enhancements that have been made in this space. If you want to create a MV,

CREATE MATERIALIZED VIEW SALES_ROLLUP_MV
ENABLE QUERY REWRITE AS
SELECT CUST_GENDER, CUST_FIRST_NAME||CUST_LAST_NAME as CUSTOMER_NAME, PROD_PACK_SIZE, PROD_NAME, FISCAL_YEAR, FISCAL_MONTH_DESC, DAY_NAME, SUM(QUANTITY_SOLD) AS QUANTITY, SUM(AMOUNT_SOLD) AS SALES
FROM
SALES a, CUSTOMERS b, PRODUCTS c, TIMES d
where
a.CUST_ID = b.CUST_ID and
a.PROD_ID = c.PROD_ID and
a.TIME_ID = d.TIME_ID
GROUP BY
ROLLUP(CUST_GENDER, CUST_FIRST_NAME||CUST_LAST_NAME),
ROLLUP(PROD_PACK_SIZE, PROD_NAME),
ROLLUP(FISCAL_YEAR, FISCAL_MONTH_DESC, DAY_NAME)

The above is called as a hierarchical cube. It does not store all the combinations like in a cube. Instead it stores the rollup of data in the same hierarchy across different hierarchies. For example, this cube will not generate value for sales when sales is analyzed by Customer Name, Product name and Fiscal month since their actual path in the hierarchy is not specified.

 If one wants to generate an actual cube, the query would look like this

SELECT CUST_GENDER, CUST_FIRST_NAME||CUST_LAST_NAME, PROD_PACK_SIZE, PROD_NAME, FISCAL_YEAR, FISCAL_MONTH_DESC, DAY_NAME, SUM(QUANTITY_SOLD) AS QUANTITY, SUM(AMOUNT_SOLD) AS SALES
FROM
SALES a, CUSTOMERS b, PRODUCTS c, TIMES d
where
a.CUST_ID = b.CUST_ID and
a.PROD_ID = c.PROD_ID and
a.TIME_ID = d.TIME_ID
GROUP BY
CUBE(CUST_GENDER, CUST_FIRST_NAME||CUST_LAST_NAME),
CUBE(PROD_PACK_SIZE, PROD_NAME),
CUBE(FISCAL_YEAR, FISCAL_MONTH_DESC, DAY_NAME)

The above does not take care of best practices to create MVs or the queries. But this is just to show the advancements that 10g/11g provides for an end user. Can anyone let me know whether these were available in 9i? I believe in 9i, correct me if i am wrong here, one would not be able to create MVs out of the queries using CUBE clause.

Update: CUBE and ROLLUP are features that were available from 8i. Thanks for the comments Laurent.

Oracle Olap 11g - Reporting using OBI EE

As I had pointed out in my earlier article here, Oracle OLAP 11g have two modes of operation. In the 10g mode, users would have to create sql access views on top of these cubes in order to make them accessible to end users (for users who are on relational reporting tools like OBI EE). In order to create these views one would have to have a knowledge of MODEL clause, OLAP Table function and it is indeed a bit cumbersome but of course very powerful. But in the 11g mode, end users do not have to create these sql access views in order to expose them to a relational reporting tool. All one would need is the MV view that gets populated and refreshed created. Lets look at it in a bit more detail.

I have a cube called SALES that has 2 dimensions PRODUCT and CUSTOMER. Following are the details of the dimensions (both are level based)

PRODUCT:

Level1 - Product Pack Size

Level2 - Product Name

Level3 - Product ID (This would be my key for the dimension)

CUSTOMER:

Level1 - Customer Gender

Level2 - Customer Name

Level3 - Customer ID (This would be my key for the dimension)

Also, my SALES cube has only one measure that is Quantity Sold. So, it’s basically a very simple cube with 2 simple dimensions.

      

      

      

As you see above, I have one hierarchy for each of my dimensions i.e Product and Customer. Hence, when you create the cube, one would get 2 MVs views. For example, lets see the views in the product dimension. Following are the 2 views

1. Product_View - This view is basically automatically created by OLAP 11g and gives in the individual keys and their corresponding desc. It contains the following structure and data.

SQL> desc product_view

Name Null? Type
—————————————– ——– —————————-
DIM_KEY VARCHAR2(100)
LEVEL_NAME VARCHAR2(30)
LONG_DESCRIPTION VARCHAR2(100)
SHORT_DESCRIPTION VARCHAR2(100)

      

But this view does not give the complete structure. For example, one cannot determine the relationship between the levels here. In order, to get the relationship, there is the 2nd view that gives more details into the structure of the dimension. This is the Hierarchy view Product_Producthierarchy_View

2. Product_Producthierarchy_View

SQL> desc product_producthierarchy_view

Name Null? Type
—————————————– ——– —————————-
DIM_KEY VARCHAR2(100)
LEVEL_NAME VARCHAR2(30)
LONG_DESCRIPTION VARCHAR2(100)
SHORT_DESCRIPTION VARCHAR2(100)
PARENT VARCHAR2(100)
PRODUCTPACKSIZE VARCHAR2(100)
PRODUCTNAME VARCHAR2(100)
PRODUCTID VARCHAR2(100)

As you see here, DIM_KEY is the unique key for this MV and it has a parent attribute that links it up to the other levels. One would need to use the connect-by clause to get the relationship.

      

Having looked at the dimension views let us take a look at the Cube MVs.

      

As you see above, there is a view called as Salescube_view that gets automatically created when we define the cube. It has the following structure.

SQL> desc salescube_view
Name Null? Type
—————————————– ——– —————————-
SALES NUMBER
PRODUCT VARCHAR2(100)
CUSTOMER VARCHAR2(100)

Sales here is the measure. Product and Customer are the keys relating to the dimensions. Having looked at the views let’s look at how these views can be used in reporting using OBI EE. The first step is to treat the 3 main views (hierarchical views of dimensions and the cube view) as ordinary views and import them into OBI EE physical layer.

      

      

Then define all the joins in the physical and the logical layer. If need be, create dimensions(within OBI EE) to specify drill levels and then create a report out of it in answers.

      

But yes, it is always recommended that you specify proper filters within your query since this is olap data. It will have all the possible combinations of both the dimensions (cartesian product) and hence any improper query will suck the system out of
resources

Oracle Olap 11g - First Impressions

Update to the original post based on further work in OLAP 11g. 

I just managed to install Oracle 11g on one of our crash and burn servers here. Well, to be honest I was impressed with what I saw. No glitches in installation at all. It has been sometime since I last managed to do that. One of the main reasons why I installed 11g was to test out the OLAP option. And this article is a result of that. For users who are trying to get AWM 11g from OTN, it’s not available as a standalone install as yet(i am not sure whether it will be at all). It is bundled along with Oracle Client software. Infact one would also get ODBC drivers, SQL Developer from the client install. One of the first things that I observed was that the Analytic Workspaces open up really fast.

 

 As you would know, Oracle 11g support 2 options. One is the 10g mode wherein you can use the AWM similar to what you have in 10g. The other is the revolutionary 11g mode wherein you can make the cube to be part of the optimizer via MVs. I started working on the 11g mode so that I can test it out. I started with creating a sample cube from the SH schema. One of the things that I observed was, one would need more privileges to create an 11g mode cube than to create it in the 10g mode. For example, one would have to have the permission to create MVs.

 I started out with creating a Product dimension.

 

 As you see in the above diagram, as soon as you create dimension you would get the associated MVs Views. I believe the MV gets deployed only when one analyzes the dimension. The MVs are created only if one selects that option while creating the cube (In the Materialized View tab). One also has the option of either enabling the Query rewrite so that the MVs are available for the optimizer. Also the build logs for the dimensions have changed quite a bit for good.

One would also get a MV for each of the hierarchies. I have not explored more into the dimensional MVs. Maybe I will write about them later.

 

 

Similarly, the build log for the cube creation is also different. One can create MVs out of both dimensions and Cubes.

The following is the output of the MV cube view created.

The MV view contains the cartesian product of the prod_id and Cust_id and its corresponding quantity sold measure.

But one of the strange things that I noticed was when i fire a query that uses one of the columns in MV, the table gets locked out if the Workspace is open. I analyzed a cube so that it becomes available to the Optimizer via the MV. But when i close theworkspace i get the results of the query. Maybe someone can correct me if I am wrong here. Maybe there is some parameter that I would have to set.  Thanks to Chris Claterbos of Vlamis solutions. He has compiled a nice article showing how AWM 11g can be started from Windows and Mac here.