Business Intelligence – Oracle

Archive for August, 2007

Oracle Warehouse Builder 11g/10g – Ebusiness Suite Connector

Posted by Venkatakrishnan J on August 31, 2007

I was speaking to one of the users of OWB yesterday and the conversation drifted towards the Ebusiness Suite connector that OWB 11g/10g provides to the end users. The user wanted to know how is the ebusiness connector different from a normal connection to an oracle database? It made a lot of sense to me considering the fact that OWB connector does not provide out of the box ETL mappings (based on business modules in ebusiness suite like BI Applications) to a target warehouse. The only way to find that out was to use the connector and see what it does differently. I did exactly that and thought that this deserves a blog entry in itself. Ebusiness suite connector does a lot of things other than connecting to the ebusiness database. It logically groups all the tables according to the business functions.

The first step i did was to create a connection to an ebusiness suite database using the Application module. The connection details would be exactly same as what you would give for an oracle database.




Once the connection details are specified, the connector gives a window that would look like this


As you see above, the connector provides 2 options. First one is an option where in one can choose a business domain and the 2nd one is an option similar to an ordinary table import wizard from a schema. The 1st one is the one that is unique to the connector i.e identifying and segregating the tables based on the business modules. The business modules would look like this


In our illustration i would choose the Order Entry (OE) module and import all the tables under that.



As you see above, the connector helps a developer in working on the tables that are specific to his module. It reduces a lot of effort, considering the fact that ebusiness suite has a huge number of tables. Apart from making the life of a developer easy by segregating the tables and views, the connector can also write back to an ebusiness suite instance. It can deploy objects to the concurrent manager. One can use the ebusiness suite objects both in the mappings and also in the process flows. One can access the documentation about the connectors here

Posted in All Posts, OWB | 9 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.



      Hierarchy:   All      ->      Customer Gender      ->      Customer Name


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


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


      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

a.CUST_ID = b.CUST_ID and
a.PROD_ID = c.PROD_ID and


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,

a.CUST_ID = b.CUST_ID and
a.PROD_ID = c.PROD_ID and

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

a.CUST_ID = b.CUST_ID and
a.PROD_ID = c.PROD_ID and

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 »


Posted by Venkatakrishnan J on August 29, 2007

I had one of the customers the other day asking for a step by step documentation for implementing VPD using OBI EE. I thought i would compile one here since this was the first time i myself was implementing VPD in OBI EE. There are many ways to implement VPD or role-based row level access from within OBI EE. They are

1.   Setting the context when a connection is established and then using that context for implementing VPD.
2.   Using proxy authentication
3.   Using variable username and passwords in the connection pools.
4.   Varying the where clause from within BI Server.

The above 4 are some of the ways that i could think of. Let me know if you come across some more methods. In this article, we would be going through the steps for implementing VPD by setting the contexts.

If you had gone through my previous article here on implementing VPD using proxy authentication from BI Publisher, i would have blogged about a scenario of 2 users trying to access the same employee table. We would be using the same scenario here. User ANALYST can see all the records in the employee table and user EXECUTIVE can see only those records wherein Salary > 7000. The first step is to create a context. Context is something that logging applications can use to identify themselves. I have a schema called VPD_ADMIN which would be the owner of all the contexts and functions. ANALYST schema would hold the employees and the departments table.



For the sake of security, contexts cannot be set directly. They can be set only via a procedure. In my case, i have created a context called VPD_CONTEXT that would be set by the procedure SET_CONTEXT_PROCEDURE. The next step is to create the procedure that would set the context.

create or replace procedure set_context_procedure
v_user in varchar2
end set_context_procedure;

Here, the VPD_CONTEXT context has one attribute called USER_NAME that would be set from within OBI EE. Once the procedure is created, the next step is to call this procedure from OBI EE. Unfortunately, OBI EE cannot execute oracle procedures directly. In order to overcome this, we will create a function that would call this procedure and then call the function as sql from within OBI EE.

create or replace function set_context_function(v_user in varchar2)
return varchar2 is

The next step is to create the function for implementing VPD. We will use the same EXECUTIVE_APPLY function that we used earlier and modify it a little bit.

create or replace FUNCTION Executive_Apply (
RETURN ‘1=1’;
return ‘salary > 7000’;
return ‘1=0’;
end if;
end if;

And then, add this function to the VPD Policy.

(object_schema => ‘ANALYST’,
object_name => ‘EMPLOYEES’,
policy_name => ‘EMPLOYEE_SEL_POL’,
function_schema => ‘VPD_ADMIN’,
policy_function => ‘EXECUTIVE_APPLY’,
statement_types => ‘SELECT’);

Once this is done, check whether the VPD is functioning properly.


Once this is done, the next step is to import both the employees and department tables into OBI EE.


After that, we would have to create 2 users called Analyst and Executive using the OBI EE security.


After that, in the connection pool, change the username and passwords to that of VPD_ADMIN. Also, enable the check box to use the fully qualified table names.


Double click on the connection pool and go to the Connection scripts tab. Then enter the select script.


The next step is to log into BI Answers as ANALYST and EXECUTIVE and view the create reports out of the employees table.



As you see, the data would vary based on the logged in user though we are getting the data out of the same table. But better be aware of shared cache. Modify the query whenever you feel that the cache is coming in your way. You would have to make the cache VPD aware. I have not tried this. I would update this blog as soon as i try that one out. To make it VPD aware you would have to use security sensitive repository variables. Thanks to bryan for his comments. There are also other ways of implementing VPD. I believe Mark Rittman has already blogged about the 4th one in our list here. Another interesting way of implementing VPD wherein the predicates would be changed by the BI Server rather than the database.

Posted in All Posts, OBI EE Plus | 7 Comments »

Oracle BI EE and Mapviewer Maps

Posted by Venkatakrishnan J on August 23, 2007

As you must be knowing OBI EE now officially supports Mapviewer Maps. This is one more reason why users must look into OBI EE. One can have visual maps of locations that can change based on data. Mapviewer is actually one of those products that is actually tightly integrated with Oracle Spatial. What this means is that if you have topographic data about your warehouses, store locations etc, all you need to do is load them up into Spatial and use Mapviewer to create the maps for you. Once you have the maps, you can integrate your maps with OBI EE. For example, while analyzing the sales for a particular region, Mapviewer maps can provide additional topographic information about the location. It also supports drilling down from the maps to the OBI EE reports. Installing mapviewer is pretty straightforward. All one would
need to do is deploy the ear file in to a OC4J container. To test this out, i deployed it on the OC4J that comes along with the OBI EE installation. It just worked like a breeze. Below is a sample screenshot.

One can get all details about Mapviewer from here

Posted in All Posts, Mapviewer, OBI EE Plus | 9 Comments »

Oracle BI EE – Metadata Dictionary Statistics

Posted by Venkatakrishnan J on August 23, 2007

One of the new features of Oracle BI EE is the ability to view the Metadata Dictionary Statistics from within BI Answers.
“In this release, this Metadata Dictionary can
now be accessed directly from the Oracle BI Answers selection pane where specific metadata information will be shown to guide report construction. Once this Oracle BI
Answers-based functionality has been enabled as described in this section, an icon will appear next to each Subject Area, Table name, and Column shown in the selection
pane for that Subject Area. The Subject Area icon is always displayed, whereas the Table name and Column name icons are only displayed on mouse-over. Clicking on the
icon will open up a specific Metadata Dictionary page to show information for that element and links to related repository information.”
– This is what the documentation says about this feature.

Lets see how to go about doing the same here. The first thing you would have to do is to open your repository in offline mode using the Administrator. Then go to
Tools->Utilities->Generate Metadata Dictionary and click on Execute.


Then it would prompt you to specify the directory wherein you would like to have the dictionary exported to. Specify any directory here. We can later on copy the files later on. Once executed,
utility would create dictionary under a new folder with the name same as the repository. In my case i have create the dictionary for the repository called Paint. It would look something like this below


The dictionary is nothing but a set of static xml files that gives lineage information of the metadata. Once the dictionary has been created the next step is to bundle this and deploy it into the App server.
If you have a OC4J instance copy all the entire paint directory to the directory where you would find the saw.dll file (this is where the analytics war file would have been deployed). Once this is done
restart the presentation services and ensure that you can access the xml files directly. Your URL would look similar to this http://localhost:port/analytics/paint/NameIndex.xml .


Ensure that you do get the xml back after entering the URL. Once this is done, the next step is to make BI Answers aware of the presence of the dictionary. To do this open InstanceConfig.xml file.
It would be under {BIEEDataHome}/web/config. Then add the following to the file


Save this file and restart your presentation services. Now if you open Answers, you can find a small icon next to the subject areas and the columns that would directly take us to the corresponding metadata dictionary.


This is available in the documentation here. But the doc does not specify the details for OC4J or Oracle App server.
I believe this should help you out in case you are planning to implement it in your org.

Posted in All Posts, OBI EE Plus | 9 Comments »

Oracle Reports to BI Publisher – Conversion Utility

Posted by Venkatakrishnan J on August 22, 2007

Oracle BI Publisher has a very interesting feature. It comes along with a small utility that can convert Oracle Reports to BI Publisher reports. I just thought i would blog about the same, since a lot of Oracle Report users are now migrating towards BI Publisher. This utility would come in handy for a lot of people. In fact getting this utility to work is very simple. Lets see how this utility works in this article. First i started with creating a sample Oracle Report.


The report is based on 3 columns of Geography table. Those 3 are Region Name, Country Name and City Name. This report would primarily consist of grouping by region and then displaying a tabular report of the coutries and cities for that region. The report looks like this


The next step is to export/save the report in the form of XML from within Oracle Reports. Remember that Oracle Reports stores both the layout and data queries in the same file. Once the report is saved update your CLASSPATH to point to the $BIPUB_HOME\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib directory. This directory would contain 4 import jar files. They are

1.   Collections.jar
2.   xmlparserv2-904.jar
3.   xdocore.jar
4.   aolj.jar

The above 4 files are required for the utility to work. The utility accepts the following parameters

                     -source — (required) Source directory for Oracle Reports files. All reports must be in the same format – either RDF or XML.

                     -target — (required) Target directory to create Oracle BI Publisher report objects. This includes the Oracle BI Publisher Report file (.xdo), the layout template file (.rtf), the PL/SQL package, and log file.

                     -oraclehome — (optional) If your reports are in Oracle Reports XML format do not specify this parameter. If your reports are not in Oracle Reports XML format, specify the Oracle home path where Oracle Report Designer (9i or later version) is                         installed. BIPBatchMigration assumes that rwconverter is contained in the bin directory beneath the Oracle Home path.

BIPBatchConversion requires rwconverter from Oracle Reports to convert the report from RDF format to XML format.

                     -debug — (optional) To run the utility in debug mode and write debug statements to the log file.

Command Line Usage:

                     java … BIPBatchConversion [-debug] -source SourceDirectory -target TargetDirectory [-oraclehome OracleHomePath]


Once the command is executed, it will create the layout, sample xml, xdo file and others in a same directory.


The next step is to compile the package that is created once the utility is executed.


Lets open the template to see whether the grouping and others are intact.


Copy the entire folder to the xmlp reports directory. Once the copy is done, refresh the report repository. We would get the report within BIP Enterprise as shown below.


View the report. But ensure that you have data source of the name default that would connect to the source from which you have created the report. Else change the name of the datasource in the date template definition.


 All of the above and more, is available in the documentation here.

Posted in All Posts, Bi Publisher | 15 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)


Level1 – Product Pack Size

Level2 – Product Name

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


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
—————————————– ——– —————————-


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
—————————————– ——– —————————-

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 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

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

Oracle BI Enterprise Edition

Posted by Venkatakrishnan J on August 20, 2007

As you might know, release of OBI EE is out for download. There have been quite a lot of features that have been added to this release. Especially, in the MS Office integration front and BI Publisher front. There is a new BI office add for office 2003. Also, there is an out of the box integration available between BI Publisher and Discoverer. Like in the earlier release, where in one can source OBI EE reports from BI Publisher, one can source this time from Oracle Discoverer. I won’t be going too much into details about this since we already have a great resource about them in Abhinav’s blog here, here and here. I would rather focus on the BI Publisher features in this article.




There are more download options in this release from within BI Publisher. In the earlier release, if you had noticed, there was no explicit way to install the excel analyzer. The only way was to click on the Analyzer for Excel button (one would have to reduce the excel security level to get this installed). But now, there is an external button that you can leverage to install this directly.



Also, there is an option wherein one can create a template automatically from a data set.

This makes a lot of sense for users who are pretty new with this tool and do not how to start. Atleast this can act as a bridge.




There are more download/viewing options now. Powerpoint, CSV support have been added into this new release.


And ofcourse, there is no need for anyone to remember the URL parameters (basic functionality is exposed. For more advanced, the only way is to get them from the user guide) to get parts of the report. This has been added as links to the report in the BI Publisher Enterprise.


Another major addition to this release of BI EE is that, BI Publisher supports flash templates. One can view flash reports from with BI Publisher and ofcourse can expose them to BI EE. Quite an interesting feature if one needs highly customized flash reports.


The last noticeable feature from within BI Publisher is that, it has option now to store the files within a directory structure (file system) or within XML DB.


And yes, Tim Dexter has earmarked some good new features in BI Publisher here.


Posted in All Posts, Bi Publisher, OBI EE Plus | Leave a Comment »

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.


 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.

Posted in All Posts, OLAP | 2 Comments »

Usage Tracking in OBI EE

Posted by Venkatakrishnan J on August 14, 2007

I have had quite a few customers asking for ways to determine who is accessing what in OBI EE. As a matter of fact, OBI EE provides an easy way to do usage tracking. There are 2 methods to achieve this.

1.                  Having a usage tracking table and OBI EE would do direct inserts into this table whenever someone is accessing any of the components within OBI EE.

2.                  Enable tracking via writing to log files.

The 1st one is the most recommended option since all that one would need is a schema to hold this table. And this is very lightweight and does not consume too much of resources.

In order to set this up, one would have to go through a small set of configuration steps. They are


1.                  Run the create table scripts under the {BIInstallDir}/server/schema directory eg. SAACCT.Oracle.sql

2.                  Check whether the schema from which you ran the above script indeed has the table of name S_NQ_ACCT.

3.                  Open the NQSConfig.ini and go to the usage tracking section and enable it. For example, use the following as a sample


ENABLE                         = YES;

DIRECT_INSERT                  = YES;


CONNECTION_POOL                = “BIDB”.”BIDB” ;

BUFFER_SIZE                    = 10 MB ;


NUM_INSERT_THREADS             = 5 ;


There are 2 parameters that need explanation.

PHYSICAL_TABLE_NAME parameter requires 3 values to be specified.

BIDB (eg)                        Points to the name of the Database module within the Physical layer of   the current repository.

BISE1_SALESWH                        Points to the schema of the database in which we ran the script in step 1.

S_NQ_ACCT                                    Table that collects the statistics.

One would have to import the S_NQ_ACCT table within one of the database modules for the usage tracking to work. The above is the specification for an Oracle database. For other databases, do refer the documentation.

The CONNECTION_POOL parameter requires 2 values to be specified.

“BIDB” (eg)                        Points to the name of the Database module within the physical layer of   the current repository.

“BIDB”                        Points to the name of the connection pool within BIDB database module.





Of course all of this is documented and one can access those from here. The S_NQ_ACCT table has the following structure.


Quite a lot of stats are available out of the box for end users to report on. There is also a usage tracking repository under BIHome}/Server/Samples/Usage Tracking. One can use this repository and merge it to the work repository so that one can do usage-tracking analysis on the dashboards.

Posted in All Posts, OBI EE Plus | 11 Comments »