Business Intelligence – Oracle

Oracle Olap 11g – Reporting using OBI EE

Posted by Venkatakrishnan J on August 21, 2007

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

Advertisements

6 Responses to “Oracle Olap 11g – Reporting using OBI EE”

  1. Rama said

    Do we need to create dimensions again in bussiness model layer to do drilling, isn’t there any option to import them from OLAP database.
    With 10.1.3.3 we can import dimensions for XMLA compliant OLAP databases why this option does not exist for oracles own database.

  2. Venkatakrishnan J said

    Yes currently, one would have to manually create those dril paths from within OBI EE. One of the main reasons for this is that Oracle OLAP is not XMLA compliant. You are right in saying that for XMLA compliant OLAP datasources, 10.1.3.3 has a new feature to natively identify the hierarchies. One must understand that Oracle OLAP provides more flexibility in exposing the entire OLAP cube in the form of SQL Views. But i hear you. It would indeed be interesting to see how OBI EE handles Essbase and Oracle OLAP in the coming versions. Something is already on the works now i think.

  3. Ralph said

    So how do you model dimensions for drilling again from those new views in OBI EE? Can you give an example?

  4. Max said

    sorry for my english. please, correct my question.

    Oracle introduced AWM plugin :
    “The Oracle Business Intelligence Enterprise Edition (OBIEE) Plug-in for Analytic Workspace Manager (AWM) allows you to quickly create an OBIEE repository that will allow the OBIEE Server (and therefore any OBIEE client, including as Dashboards, Answers, Delivers and the MS Office Plug-in) to query Oracle Database 11g OLAP cubes.”

    it is work great, but the plugin have serious limitations: Only the default hierarchy in a dimension is exported to the OBIEE repository.

    I tried to create the other hierarchies in OBIEE repository, but my project failed.

    can you give me professional advice: How to create the others hierarchies of my cube?

  5. Somnath said

    Hope you are still working in Bangalore.

    I have found one white paper that describes the whole thing you have listed here. I am working in a project and we are using Oracle Olap 10g for out calculated agg measures. The only problem that we are facing is we have a requirement to show the base data from the relational tables as well and there is no document available in Oracle that tells me about this. Do you have any idea how to resolve this problem.

    Cheers,
    Somnath

  6. Leo said

    Hi

    How can i identify hierarchy level in answers….. If you could provide me steps to do it, i would be very greatful to you.

    Thanks & Regads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: