Business Intelligence – Oracle

Archive for the ‘OLAP’ Category

Oracle Data Integrator 10.1.3.5 and Oracle OLAP – RKM and IKM – Using Oracle OLAP as a Target – Part 2

Posted by Venkatakrishnan J on January 6, 2009

In the blog entry here, we had seen how the new RKM for Oracle OLAP works. Today, we shall look at the other KM(IKM) which would be used for loading data directly into Analytic Workspaces. I had noted earlier that, the Oracle OLAP RKM just reverses the OLAP cube to its underlying source tables. So, basically, it is not Oracle OLAP aware per se and it does not support all kinds of data extract and load. This is the major difference in the Oracle OLAP RKM and Essbase RKMs. Essbase RKMs treats Essbase itself as multidimensional data store. So, all data extract through MDX, Report Scripts etc are supported. We shall look at how ODI uses the Essbase RKMs in a later blog entry. Lets proceed with our test on the Oracle OLAP IKM which would be used to load an Oracle OLAP AW Cube. To test this out we shall start out with a new cube in the Global Analytic Workspace. This cube mapping is provided below as a reference.

In our case, the UNITS_FACT_TGT is a table that is used as a source for the cube. We shall create another table UNITS_FACT_SRC which would be used as source to populate data into this target table. Our idea is to simulate an environment wherein the target table would be populated temporarily before loading into Oracle OLAP. This target table would contain only the incremental data.

CREATE TABLE UNITS_FACT_SRC AS SELECT * FROM UNITS_FACT;
CREATE TABLE UNITS_FACT_TGT AS SELECT * FROM UNITS_FACT WHERE 1=0;

Now, lets create an interface which would be using the above 2 tables (one as a source and the other as a target).

In the flow control, ensure that you are choosing the “IKM Oracle AW Incremental Update”.

This is very similar to the normal Oracle to Oracle data load. The only addition are the 4 options which are specific to AW loading. The 4 options are

1. REFRESH_CUBE – This will enable the automatic start of data load into Oracle OLAP at the end of data load into the target table.
2. AW_OWNER – Owner of the Oracle OLAP AW
3. AW_NAME – The Analytic Workspace name
4. CUBE_NAME – The cube whose data needs to be refreshed.

This IKM assumes a straight data load into the Oracle OLAP. The data load cannot be customized. The only way to do that is to call custom OLAP specific Pl/SQL packages. But again, i was expecting that to be available in the IKM itself. Now lets execute the interface and see what the Interface does under its covers.

Let us take a look at the Olap Refresh option to see the exact code.

declare
xml_clob clob;
xml_str varchar2(4000);
isAW number;
v_awname varchar2(50);
v_owner varchar2(50);
v_cube varchar2(50);
v_dimension varchar2(50);
v_line varchar2(250);

cursor c1 is select DIMENSION_AW_NAME from
olapsys.all$olap2_aw_cube_dim_uses
where
aw_owner = v_owner
and aw_name = v_awname
and aw_logical_name = v_cube
and DIMENSION_AW_NAME<> 'TIME';
begin

v_awname := 'GLOBAL';
v_owner  :=  'GLOBAL';
v_cube   :=  'UNITS_FROM_ODI';

sys.dbms_aw.execute('AW ATTACH '||upper(v_owner)||'.'||upper(v_awname)||' RW');
DBMS_LOB.CREATETEMPORARY(xml_clob,TRUE);
dbms_lob.open(xml_clob, DBMS_LOB.LOB_READWRITE);
v_line := '  <BuildDatabase  Id="Action7" AWName="'||upper(v_owner)||'.'||upper(v_awname)||'" BuildType="EXECUTE" RunSolve="true" CleanMeasures="false" CleanAttrs="true" CleanDim="false" TrackStatus="false" MaxJobQueues="0">';
dbms_lob.writeappend(xml_clob, length(v_line),v_line );
dbms_output.put_line(v_line);
open c1;
loop
fetch c1 into v_dimension;
exit when c1%notfound;
v_line := '    <BuildList XMLIDref="'||v_dimension||'.DIMENSION" />';
dbms_output.put_line(v_line);
dbms_lob.writeappend(xml_clob, length(v_line),v_line );
end loop;
close c1;
v_line := '    <BuildList XMLIDref="'||v_cube||'.CUBE" />';
dbms_output.put_line(v_line);
dbms_lob.writeappend(xml_clob, length(v_line),v_line );
dbms_lob.writeappend(xml_clob, 18, '  </BuildDatabase>');
dbms_lob.close(xml_clob);
xml_str := sys.interactionExecute(xml_clob);
dbms_output.put_line(xml_str);
dbms_output.put_line('finish');
sys.dbms_aw.execute('AW DETACH '||upper(v_owner)||'.'||upper(v_awname));
end;

The above is a java code and you would see calls to the DBMS_AW package (for attaching and detaching the AW). The actual data load is done through a custom XML load parser. This can be customized according to our needs. But again this is not something that can be done from the front end. This would have to be changed in the backend class files of ODI.

After i saw how the RKM worked, i was pretty sure how the IKM would work. The above confirmed my guess. In a future blog entry i would be comparing this with the Essbase specific RKMs and IKMs.

Update: John Goodwin has an excellent series of articles on ODI and Essbase,Planning connectivity. I would recommend everyone to check that out. Thanks John for letting me know.

Posted in All Posts, OLAP, Oracle Data Integrator | 2 Comments »

Oracle Data Integrator 10.1.3.5 and Oracle OLAP – RKM and IKM – Using Oracle OLAP as a Source – Part 1

Posted by Venkatakrishnan J on January 4, 2009

As you might probably be aware, the latest version of ODI 10.1.3.5(and also the prior release) supports a new set of KM’s which can extract and load data into Oracle OLAP Cubes. We shall try to understand how this connectivity works in this release. There are 2 aspects to this connectivity. They are

1. Using Oracle OLAP as a data source – This is supported by a Reverse Knowledge Module called “RKM Oracle Olap (Jython)”.
2. Incremental data load into Oracle OLAP – This is supported by a Integrating Knowledge Module called “IKM Oracle AW Incremental Update”

Both the above KM’s are available in the default install of ODI. One would have to manually import the KM’s from the {ODI_HOME}\oracledi\impexp directory. We shall be using the Global Analytic Workspace for all our testing. This Global AW contains 4 dimensions

1. Channels Dimension – Containing one normal Level Based Hierarchy
2. Customer Dimension – Containing one normal Level Based Hierarchy
3. Product Dimension – Containing a value based hierarchy
4. Time Dimension – Containing the normal level based fiscal time hierarchy

Also this AW contains 2 cubes, one having one measure each. The below screenshots would give you an understanding on how my AW is structured.

To use this AW as our data source in ODI, we need to create the physical connection in the Topology Manager of ODI.

Once the physical connection is created, lets create a logical connection to denote the usage of this connection as a source.

Also tie the physical and logical connections together by means of the Global context as shown above. This will ensure that the logical connection will use the physical connection defined above. Once this is done, let us login to Designer and import all the out of the box KMs provided by ODI.

As a next step lets create a model and import this AW using the RKW imported above. While creating the model, we would have to use Oracle as the Technology and then assign the Logical connection that was created above.

And in the Reverse tab, choose the context and use the customized option as shown below.

Ensure that you are choosing the RKM properly in the dropdown. Also, enter all the the Analytic Workspace related connection details as shown above. Now for the Reversing operation to work there are 2 jar files that are needed. They are

1. olap_api.jar
2. awxml.jar

These 2 files need to be copied from the Oracle database home i.e Copy the 2 files from {ORACLE_HOME}\olap\api\lib to {ODI_HOME}\oracledi\drivers. The documentation states that awxml.jar alone is sufficient. But i was not able to get the Reverse functionality to work with just that single jar. Only after i copied over the olap_api.jar file over to the drivers directory, the reverse started to work. Once the files are copied over, a restart of the designer is required. After the designer restart, just click on reverse from the Model that we created. The reversing operation is in itself treated as an execution and can be tracked using the Operator.

Once the reverse operation is successful, we should be having the imported objects in the Model as shown below.

If you observe closely, what we have basically obtained is not actually the OLAP object itself. Instead, what the RKM has done, is that it has used the XML API (using awxml.jar) of the Global AW to reverse engineer all the dimension and fact tables from the mappings defined in the AW. If you see, we have obtained 3 dimension tables and 2 fact tables. Also, one dimension table is missing which is the value based product dimension. I believe the current version of RKM does not support reverse engineering Value based hierarchies. So, the question now is if we incorporate these imported dimension/fact tables into our interface, will we be extracting data out of the cube or from the fact tables? Unfortunately, i believe the current version does not support direct extract of data from OLAP using these 2 KM’s (if data is required directly from the cubes one would have to create custom SQL Views on top of the AW and then use them in the interface). To test this out, lets create a sample dummy fact table with no rows as shown below

CREATE TABLE UNITS_FACT_TEST AS SELECT * FROM UNITS_FACT WHERE 1=0

We shall be using the above table as our target in the interface. For the source, we shall use the imported UNITS_FACT table. The screenshots of the interface is shown below

As you see, we are treating this as a normal oracle table in our interface. So, when we execute this interface the data extract would happen only from the underlying fact tables instead of the Cube AGGMAPs. I was expecting a bit more advanced form of the KMs to support Oracle OLAP natively. But considering the fact that the cube level data can be exposed through SQL Views this is not much of a hinderance as it is possible to use them to extract data directly from the cube. Neverthless, it would be interesting to see what the Incremental Update IKM does. My hunch is that this will support data load into the fact tables and in turn will kick start the cube aggregation. We shall get into more details on the AW data load using ODI in a future blog entry.

Posted in All Posts, OLAP, Oracle Data Integrator | 2 Comments »

OBI EE 10.1.3.3/2 and Oracle OLAP 10g

Posted by Venkatakrishnan J on September 7, 2007

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 :-).

Posted in All Posts, OBI EE Plus, OLAP | 1 Comment »

Oracle 11g – CUBE_TABLE function for Oracle OLAP

Posted by Venkatakrishnan J on September 6, 2007

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.

Posted in All Posts, OLAP, Oracle 11g | 3 Comments »

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

Posted by Venkatakrishnan J on August 30, 2007

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)

               snapo1.jpg

And the plan for the above query would be like this

              snapo2.jpg

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.

Posted in All Posts, OLAP | 2 Comments »

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

Posted in All Posts, OBI EE Plus, OLAP | 6 Comments »

Oracle Olap 11g – First Impressions

Posted by Venkatakrishnan J on August 17, 2007

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.

 olap1.jpg

 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.

 olap2.jpg

 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.

olap3.jpg

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.

 

olap4.jpg

 

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

olap5.jpg

olap6.jpg

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

olap7.jpg

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

olap8.jpg

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.

Posted in All Posts, OLAP | 2 Comments »