Business Intelligence – Oracle

Archive for July, 2007

Customizing OBI EE – SOAP API

Posted by Venkatakrishnan J on July 31, 2007

As I was writing my blog yesterday on using URL parameters as a customizing option, it made me think about the availability of open API’s for OBI EE. As luck would have it, I had some time yesterday to work on the API’s and this blog is a result of that. In this article, we would be seeing how to use SOAP API of OBI EE from JDeveloper.  One of the major advantages of OBI EE is that it opens itself for developers in the form of standard API’s. Almost all the functionality is exposed and it would enable customers to use these API’s within their application code sets and thereby enabling complete integration. Here, we would basically be seeing how to go about using the SawSession service and the HtmlViewService to view a simple report. The SawSession service helps is maintaining sessions & authentication. The HtmlViewService helps in actually extracting the layout of a report. The SOAP API works on the WSDL implementation within OBI EE. If you navigate to http://localhost:port/analytics/saw.dll?WSDL one can obtain the WSDL schema information.  

One can use the Mindreef SoapSCOPE evaluation copy to go through the WSDL.

                                  obiee_soap.jpg

                                  obiee2_soap.jpg

One can test each of the invoking methods using this. But the only major problem with this evaluation copy is that you cannot maintain session state. Hence it would be difficult to test all the methods available. Now lets look at the various services that are available. 

1.                  Security Service

2.                  Ibot Service

3.                  Web Catalog Service

4.                  Replication Service

5.                  Metadata Service

6.                  ReportEditing Service

7.                  HTMLView Service

8.                  XMLView Service

9.                  SAWSession Service 

Security Service:          This service helps in identifying the user priveleges. One can assign, revoke privileges using Security Services. 

Ibot Service:            As the name suggests, this is a service for invoking Ibots. 

Web Catalog Service:          This service is for managing the web catalog.

Replication Service:                  This service is used for replication. Export/Import of catalogs can be done using this. 

Metadata Service:          This is for managing the BI Server metadata. 

ReportEditing Service:          This service is used to merge arguments and Oracle BI Web Services data to create and return the results. 

HTMLView Service:                  This service is used to embed Oracle BI HTML results in third-party dynamic Web pages, such as Active Server Pages (ASP) or JavaServer Pages (JSP), and portal frameworks. The embed process merges Oracle BI Web Services content with the content of third-party Web pages. 

XMLView Service:                  This is used for retrieving the data from Oracle BI EE Server in the form of XML. 

SAWSession Service:          This is the service that would enable users to login, logout and maintain sessions. Now lets see how we can go about using these services from within Jdeveloper to create a sample custom report. 

1.       Open Jdeveloper and create a new application. (With no templates)

                                    obiee3.jpg 

2.       Create a new Package within the Applcation.

                                    obiee4.jpg

Use Web Service Proxy technology to create it.

                                    obiee5.jpg  

Invoke all the methods that you need. In our example, we will be including the SAWSession service, the HTMLView Service and the XMLView Service.  

3.       The next step is to create an object for SAWSession Service and invoke the logon method.           

For example,

soaptest.SAWSessionServiceSoapClient myPort = new soaptest.SAWSessionServiceSoapClient();                   

sessionID = myPort.logon(“Administrator”,”Administrator”);          

This would create a session. 

4.       Once a session is created, the next step is to create a pageID for that session. A page is nothing but an entire html page that might contain multiple reports. This uses the HTMLView Service.          

For example,

soaptest.HtmlViewServiceClient htmlClient = new soaptest.HtmlViewServiceClient();                   

pageID = htmlClient.startPage(newPage,sessionID); 

5.       After creating the pageID, create multiple report Ids that you would like to associate with  a pageID.          

For example,                   

htmlClient.addReportToPage(pageID, reportID, newreportRef, null, null, null, sessionID);                    

Here, newreportRef uses a Reference structure like this                    

ReportRef newreportRef = new ReportRef();           

newreportRef.reportPath = reportPath;                     

newreportRef.reportXml = reportXML;               

For specifying a report one would have to specify 2 values. One is the report path and the other is the report definition. Both can be obtained from the report definition properties.

 

 

                               obiee6.jpg

 

5. Once this done the next step is to generate html output for the report.          

For example,

htmlOutput = htmlClient.getHtmlForReport(pageID, reportID, sessionID); 

For future reference, I have uploaded the entire code here. One can use this for reference. This just shows the extensibility of OBI EE. Also, one can call BPEL processes from within BI EE to make educated business decisions. Mark Rittman has already blogged about that here. Interesting post considering the fact it is gaining more and more traction in the customer environments. I just had a customer who had this query and i was more than happy to direct them to Mark’s blog just to give them an idea and of course the documentation. The power of community is so good in this regard and it has helped me out a lot of times. If you want to know more about the open API’s they are available here. Just in case you want to do the same with BI Publisher they are available here. You might want to check out this blog by Vlamis and his team for giving out excellent articles on OLAP and DW in general. Also, if you are passionate about discoverer check out Michael Armstrong-Smith‘s blog.

Advertisements

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

Customizing OBI EE – GO URL Parameters

Posted by Venkatakrishnan J on July 30, 2007

While I was working on the data mining pieces, I got a request from a customer who basically wanted to completely customize OBI EE. Well, it got me thinking about the various customization options that we have with OBI EE. In my customer’s case, they wanted to integrate OBI EE into their web application. Let’s see what options we have with OBI EE customizations.

Using OBI EE GO URL parameters

Almost most of the functionality that a customer would need or expect out of OBI EE is available for them via URL parameters. One can leverage these URL parameters into their application. What this would do is that rather than having OBI EE as a separate reporting tool, it would enable an end customer to call various reports via iframes into their application.
 
 A sample URL with the parameters for OBI EE would look like this

 http://hostname:port/analytics/saw.dll?GO&NQUser=Administrator&NQPassword=Administrator&
Path=/Users/administrator/GEC_DW/Regional+Sales/Profit+per+Category+Pie+Chart&Options=md

When one needs to use the URL parameters the fundamental part of the URL should include http://hostname:port/analytics/saw.dll?GO and following that options are to be entered. Lets look at the various URL parameters for OBI EE

        1. &NQUser – Username for logging into Answers

        2. &NQPassword – Password for login. One must realize that entering password directly into the URL is not very secure. Its recommended to use    the post method wherein the password is not explicitly shown. (People who know discoverer, entering password, as a parameter is no more   available. Only post method is supported)
 
        3. &Options=mdfr – Each of the letters in mdfr have a specific function.
            m – To include the modify report link under the report
            d – To include the download link under the report
            f – To include the printer friendly link under the report
            r – To include the refresh report link under the report

  All the above can be interchangeably used. For example &Options=md for including modify and download links alone.

        4. &Action – This allows the developers to request specific formats for the reports. For example, &Action=print (can also have excel etc)
 
        5. &ViewName – This argument allows one to specify a view. For example, &ViewName=Chart1

        6. &Style – Specify a style sheet For example, &Style=Lime

        7. &Format – Specify the format of the output, html or xml
 
        8. &Path – Specify the path for the report. For example, &Path=/Shared/administrator/testReport

        9. &SQL – Logical Sql – One can even issue logical sql via the URL parameter. For example,  &SQL=select+region+from+Sales. Here Sales is the   subject area.

The above are some of the parameters that you might find useful. But for more detailed and some parameters refer the Documentation here.

 
The above picture is actually a simple application that calls out 2 different reports based on the data entered. This kind of integration is very strong and infact the entire OBI EE is integrated into the Oracle Siebel CRM using this methodology. One can use Post methods; execute scripts etc using this type of methodology.

Following are some examples for simple HTML form elements using post method.

 Access to Answers:
     <form action=”http://hostname:port/analytics/saw.dll?” method=”post”>
     <input type=”hidden” name=”Cmd” value=”Answers”>
     <input type=”hidden” name=”nqUser” value=”Administrator”>
     <input type=”hidden” name=”nqPassword” value=”Administrator”>
     <input type=”submit” value=”Answers”>
     </form>

To open a report in Answers (for edit), include the Path parameter:
     <form action=”http://hostname:port/analytics/saw.dll?” method=”post”>
     <input type=”hidden” name=”Cmd” value=”Answers”>
     <input type=”hidden” name=”nqUser” value=”Administrator”>
     <input type=”hidden” name=”nqPassword” value=”Administrator”>
     <input type=”hidden” name=”Path” value=”/Shared/SH/Category analysis”>
     <input type=”submit” value=”Open a Report for editing in Answers”>
     </form>

To open BI Publisher use “AdvancedReports” as the Cmd value
     <form action=”http://hostname:port/analytics/saw.dll?” method=”post”>
     <input type=”hidden” name=”Cmd” value=”AdvancedReports”>
     <input type=”hidden” name=”nqUser” value=”Administrator”>
     <input type=”hidden” name=”nqPassword” value=”Administrator”>
     <input type=”submit” value=”BI Publisher”>
     </form>
 

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

Oracle Data Mining and Business Intelligence

Posted by Venkatakrishnan J on July 25, 2007

I believe Oracle Data Mining is one of the most under utilized tools/database options in the BI space. There are so many Oracle Data Warehouse implementations out there but hardly few use Oracle Data Mining (but yes the market share is growing recently). Is there any reason for such a low adoption rate? Is it due to the lack of capabilities of Oracle Data Miner? Or is it due to the perceived notion that Data Mining is for statisticians and the like? Or is it because the management does not believe in the value of Data Mining?

I think it’s primarily due to the perceived notion rather than the lack of features because ODM indeed has a very rich set of functionality (a lot!!!). Of course statisticians can much appreciate the value of a data-mining tool. But again, as a person who has been involved in some DW implementations, I believe we understand the data better than anyone else. To use data mining Statistical knowledge would be an absolute plus but not mandatory. I am planning to put together a series of articles that would elucidate the various models that are used in Oracle Data Miner with examples. Its more of an education for me too since I myself would be getting my hands dirty for the first time with this tool. It is a bit too ambitious but let me see how far I can go. I will try to put together some relevant day-to-day examples as and when I get time (I will try as much as possible to deviate from the Beer bottle and Baby diaper example J). For more detailed analysis and inputs we always have the documentation and the ODM PM Marcos Campos’s blog. Actually the ODM PM is Charlie Berger. Thanks for correcting me Charlie. But some usual caveats here. Data Mining is a huge and a vast topic. I plan to just cover the models that might make sense for all those who use Business Intelligence. If you have any feedback do let me know since I think I am being too opinionated here!!!

Before we get into the details, lets go through an overview of datamining types. There are 2 types of datamining.

       1. Supervised Data Mining
       2. Un-Supervised Data Mining

Supervised is also called as Predictive data mining wherein the process is used to predict some outcomes. For example, a credit card company can use this to predict whether a customer is going to default on his payments.

Un-Supervised Datamining, also known as descriptive datamining involves finding out intrinsic patterns within the data. Again, how the pattern is identified depends on the model.

Following are some of the models supported by Oracle Data Miner.

Supervised Data Mining:

        1. Classification
        2. Regression
        3. Attribute Importance
        4. Anomaly Detection

Un-Supervised Data Mining:

        1. Clustering
        2. Association
        3. Feature Extraction

With that I would like to close this with some interesting articles on datamining

        1. Crime Detection
        2. Credit Card Fraud Detection

Posted in All Posts, Data Mining | 2 Comments »

BI Publisher 5.6.2 and VPD

Posted by Venkatakrishnan J on July 24, 2007

In my last article, i had written about leveraging Proxy Authentication for VPD in BIP 10.1.3.2. But in earlier releases of BIP especially for people who are still on 5.6.2 version, there was no proxy authentication. Also, if a customer wants to leverage VPD within an Oracle database that is of earlier releases like 9i/8i then Proxy Authentication will not work. So in this article i would be focussing on 2 methods of using VPD within BIP 5.6.2. I will talk about 2 cases here. To be honest, the first one does not leverage VPD within the database. But i have included it here just for the sake of completeness.

1. If the idea is just to pass the username & get the data based on the user, this can be done directly from the BIP itself without going back to the database (without setting up VPD in the database).

Consider there are 2 enterprise users Executive & Analyst. We have to create a report from a table that has a column called userlogged. The idea is when Analyst logs in one must see the Analyst user’s (userlogged = ‘ANALYST’ in the sqlquery) data. Similarly, for the executive user one must see the Executive User’s data. 
               To implement this we would be creating an XMLP report with DataTemplate as the data source.
For more on Templates refer Tim Dexter’s blog and of course the BIP 5.6.2 User Guide

xmlp1.jpg

xmlp2.jpg
Use the following script for Data Template.

<dataTemplate name=”JobDemo” description=”Job Demo” dataSourceRef=”BIP_VPD” Version=”1.0″>
    <input id=”P_USERNAME” value=”${SYSUSER.username}”/>
    <properties>
     <property name=”showControls” value=”false”/>
     <property name=”online” value=”true”/>
     <property name=”parameterColumns” value=”3″/>
     <property name=”openLinkInNewWindow” value=”true”/>
    </properties>
    <parameters>
     <parameter dataType=”character” name=”P_USERNAME”/>
    </parameters>
    <dataQuery>
     <sqlStatement name=”Q1″>
 select EMPLOYEE_ID, JOB_TITLE, FIRST_NAME, EMAIL, HIRE_DATE, SALARY from employees a, jobs b where a.job_id = b.job_id  and UPPER(ADMIN_USER) = upper(:P_USERNAME)
</sqlStatement>
    </dataQuery>
    <dataStructure>
     <group name=”G_EMPLOYEE” source=”Q1″>
      <element name=”EMPLOYEE_ID” value=”EMPLOYEE_ID”/>
      <element name=”JOB_TITLE” value=”JOB_TITLE”/>
      <element name=”FIRST_NAME” value=”FIRST_NAME”/>
      <element name=”EMAIL” value=”EMAIL”/>
      <element name=”HIRE_DATE” value=”HIRE_DATE”/>
      <element name=”SALARY” value=”SALARY”/>
     </group>
    </dataStructure>
   </dataTemplate>

Here, the ${SYSUSER.username} variable would substitute the actual usernames into the query & hence would return different results for each logged in user. For complex queries one can use nested case statements to use this username variable & implement the logic accordingly. It is important to note the positioning of the parameter variable tags since placing anywhere else would not return the valid data. The other option would be is to have the where clause statement itself returned back to the bip by using lexical references. This way one need not implement vpd at the database level.

2. If the idea is to have some complex queries based on the username passed then VPD at the database level can be used.

 2.1.In the following example, the 2 users analyst & executive would be mapped within the database as admin & admin1 respectively. These users would be accessing the data under the HR schema which would be the VPD owner

 2.2.   The VPD policy would then be applied on a table called hr.countries over which the column COL_USER would be the column that maintains the user details.

 2.3.   Before creating the policy one would have to create application context package using the following script. create or replace context HR using HR.Context_Package_XML;create or replace PACKAGE Context_Package_XML AS
FUNCTION BeforeReportTrigger return boolean;
P_USERNAME VARCHAR2(100);
END;
create or replace PACKAGE BODY Context_Package_XML IS
FUNCTION BeforeReportTrigger RETURN BOOLEAN IS
v_ouser VARCHAR2(30);
BEGIN
DBMS_Session.Set_Context(‘HR’,’SETUP’,’TRUE’);
v_ouser := SYS_CONTEXT(‘USERENV’,’SESSION_USER’);
IF upper(P_USERNAME) = ‘ANALYST’ THEN
BEGIN
DBMS_Session.Set_Context(‘HR’,’USER_NAME’, ‘ADMIN’);
END;
ELSE
IF UPPER(P_USERNAME) = ‘EXECUTIVE’ THEN
BEGIN
DBMS_Session.Set_Context(‘HR’,’USER_NAME’, ‘ADMIN1’);
END;
ELSE
DBMS_Session.Set_Context(‘HR’,’USER_NAME’, ‘HR’);
END IF;
END IF;
DBMS_Session.Set_Context(‘HR’,’SETUP’,’FALSE’);
RETURN TRUE;
END;
END Context_Package_XML;

Create the vpd function

create or replace FUNCTION user_only (
p_schema IN VARCHAR2 DEFAULT NULL,
p_object IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2
AS
BEGIN
if sys_context(‘HR’,’USER_NAME’) = ‘HR’ then
RETURN ‘1=1’;
else
IF upper(SYS_CONTEXT(‘HR’,’USER_NAME’)) = ‘ADMIN’ THEN
return ‘upper(con_user) = ”ADMIN”’;
ELSE
IF upper(SYS_CONTEXT(‘HR’,’USER_NAME’)) = ‘ADMIN1’ then
return ‘upper(con_user) = ”ADMIN1”’;
else
return ‘1=0’;
end if;
end if;
end if;
END;

Add a vpd policy using dbms_rls.add_policy procedure

BEGIN
DBMS_RLS.add_policy
(object_schema => ‘HR’,
object_name => ‘COUNTRIES’,
policy_name => ‘COUNTRY_SEL_POL’,
function_schema => ‘HR’,
policy_function => ‘USER_ONLY’,
statement_types => ‘SELECT’);
END;

This application context package would set the context depending on the username (Analyst or Executive). This package would be run by using a before report trigger that’s available in BIP. Once this is done, the next step is to create a report using Data Templates. One can use the following Data Template code.

<dataTemplate name=”JobDemo” description=”Job Demo” dataSourceRef=”BIP_VPD1″ defaultPackage=”Context_Package_XML” Version=”1.0″>
                                <input id=”P_USERNAME” value=”${SYSUSER.username}”/>
    <parameters>
                                        <parameter dataType=”character” name=”P_USERNAME”/>
                                </parameters>
    <dataQuery>
     <sqlStatement name=”Q1″>
 select COUNTRY_ID, COUNTRY_NAME, REGION_ID,CON_USER from countries
</sqlStatement>
    </dataQuery>
                                <dataTrigger name=”beforeReport” source=”Context_Package_XML.BeforeReportTrigger”/>
    <dataStructure>
     <group name=”G_COUNTRIES” source=”Q1″>
      <element name=”COUNTRY_ID” value=”COUNTRY_ID”/>
      <element name=”COUNTRY_NAME” value=”COUNTRY_NAME”/>
      <element name=”REGION_ID” value=”REGION_ID”/>
                                                <element name=”CON_USER” value=”CON_USER”/>
     </group>
    </dataStructure>
</dataTemplate>

What this basically does is that it calls a before report trigger(a Pl/SQL package wherein we pass the username of the user logged in using the ${SYSUSER.username} variable. The Pl/SQL package basically sets the context thus enabling the report to be executed as the user based on the context.

Posted in Bi Publisher | 1 Comment »

BI Publisher 10.1.3.2 and VPD

Posted by Venkatakrishnan J on July 23, 2007

I was going through an interesting post by Jonathan Lewis on Proxy Authentication that is available for end users via Sql Plus in 10g R2. This post made me think about the availability of the same in BI Publisher 10.1.3.2 wherein one can leverage the proxy authentication(for database connections that are on 10g) to do VPD or row level security. This article would demostrate how to use Proxy Authentication to do row level security within BI Publisher. Of course, this applies only if you are 10.1.3.2 version of BIP and 10g of database. For users who are on earlier releases, I would follow this one up with another article for showing how to go about doing the same.Assume that there are end users Analyst and Executive within BI Publisher(Analyst and Executive are also users within the 10g R2 database). There is one table under Analyst that has all the Employee details. Assume that the Analyst is working on the Employee table but would like Executive end user to see only those data wherein the salary is greater than 7000.

Create a Proxy user bipubapp_user that would be used to proxy authenticate both Analyst and Executive. The following commands are performed as a user Sys(any admin user)

Create user bipubapp_user identified by welcome1;

grant create session to bipubapp_user;

create user Analyst identified by welcome1;

grant create session, create table to Analyst;

alter user Analyst grant connect through bipubapp_user;

create table Analyst.Employees as select * from HR.Employees;

create user Executive identified by welcome1;

grant create session to Executive;

alter user Executive grant connect through bipubapp_user;

The next step is to set up the VPD policy within the database. I have a user called VPD_ADMIN to source the function and the VPD policy.

1. Create the following VPD function.

create or replace FUNCTION Executive_Apply 
                       RETURN VARCHAR2
AS
BEGIN
if USER = ‘ANALYST’ then
   RETURN ‘1=1’;
else
IF USER = ‘EXECUTIVE’ THEN
   return ‘salary > 7000’;
ELSE
return ‘1=0’;
end if;
end if;
END;

2. Add a VPD policy with the above function

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

3. Check whether VPD is working

SQL> conn bipubapp_user[executive]/welcome1
Connected.
SQL> show user
USER is “EXECUTIVE”
SQL> select count(*) from analyst.employees;

  COUNT(*)
———-
        44

SQL> conn bipubapp_user[analyst]/welcome1
Connected.
SQL> show user
USER is “ANALYST”
SQL> select count(*) from analyst.employees;

  COUNT(*)
———-
       107

The next step is to go to the BI Publisher admin console and create 2 users Analyst and Executive.

bip1.jpg

Create a datasource that would use the bipubbapp_user as the login username. Make sure to enable proxy authentication while creating the datasource.

bip2.jpg

Give the necessary privileges to Analyst and Executive. Log out and log in as Analyst and create a report which would extract the data from the Employees table.

bip3.jpg

View the report. I have not created any templates here. This is just to show you how Proxy Authentication works in BIP.

Log out and log in as Executive. View the same report.

bip4.jpg

Ofcourse, this works only with databases that are on 10g and the BIP version has to be 10.1.3.2 or above. For the earlier releases, i would follow up with another article that would show you how to set application contexts in order to use VPD. On the same lines, Mark Rittman has an interesting article on setting up VPD in OBI EE. Interesting read.

I have not gone into details of Proxy Authentication. One can use secure application roles to have one more layer of authentication. For more information on Proxy Authentication, would request you to go through the docs here.

Posted in Bi Publisher | 5 Comments »

Creating a Hyperion Essbase(Analytic Server) Cube

Posted by Venkatakrishnan J on July 21, 2007

In this example, I would be using Sales Warehouse data that comes in Oracle SH schema. It would make a lot of sense if one creates a cube out of transactional data. But for the sake of simplicity I would be focussing on the cube creation process with a simple warehouse schema.

Before you start, ensure that you have all the components (Analytic Server and Analytic Integration Services up and running)

Open up the Analytic Integration Services Console. As soon as you start, it would prompt you to create an OLAP catalog.

 

essbase1.jpg

Enter connection details for a schema that you generally use for storing metadata (recommended to create a separate schema in order to store the metadata). Once that is done it would automatically take us to the following screen

 

essbase2.jpg

 

On to the left is the OLAP metadata connection. Enter the details that you entered in the previous screen. On to the right is the Analytic Administration Services connection. Enter those details here. The next step is to create an OLAP Model.

essbase3.jpg 

Next is the screen to establish a connection to the data source. As I said, I would be connecting to the Sales Warehouse schema.

 

 essbase4.jpg

 

There are two types of files that we would be dealing here. They are

ü      Olap Model

ü      MetaOutline

In Olap Model one can establish all the joins of the tables that they want in their schema. It requires a fact table and their corresponding dimensions. Hierarchies should be specified here.

 

essbase5.jpg

 

Metaoutline is a file type wherein one can create the dimensions and specify the measures. Once the Olap model is created the next step is to design the Metaoutline.

 

essbase6.jpg

 

The next step is to load the data.

essbase7.jpg

essbase8.jpg

Then one can use the excel add in to view the data.

This is one of the ways to create a cube. One can also create the cube directly from the Analytic Administration Services. Also, I have not gone into lots of other features that would make essbase stand part. This was just to show an overview for people would like to start with Essbase but do not know where to begin from.

Posted in Hyperion Essbase | 16 Comments »

Hyperion Essbase Analytic Server – First Impressions

Posted by Venkatakrishnan J on July 20, 2007

Hyperion Essbase (as it was formerly called) is one of the market leaders in the MOLAP space. Prior to the acquisition there have been many cases wherein Oracle competed head on head with Hyperion Essbase using Oracle OLAP. Both of these products have their own advantages. In this article let’s try to understand the working of Hyperion Essbase vis-à-vis Oracle OLAP.

Hyperion System 9 BI was the latest and last offering from Hyperion before the acquisition. There have been quite a few name changes for many of the well-known products like Essbase, Brio etc. Also, there were quite a few changes in the architecture front. System 9 is based on a 3-tier architecture. In System 9 Hyperion Essbase has been re-branded as the analytic server. There are 4 components that form the entire MOLAP engine. These components are as follows

  1. Analytic Services
  2. Analytic Administration Services
  3. Analytic Integration Services
  4. Analytic Provider Services

One must realize that even though Essbase (I will stick with this name to refer to all the above 4) is a MOLAP engine, it’s considered as a database in itself. It’s more like a multi-dimensional database. It is one of the fundamental components of Hyperion Planning and Financial Management.

Following would be installation sequence that one would have to follow to get this up and running.

1.                  Install License Server

2.                  Install Shared Services

3.                  Install Analytic Services

4.                  Install Analytic Administration Services

5.                  Install Analytic Integration Services

6.                  Install Analytic Provider Services

7.                  Install the Excel Add in which is under BI + Analytic Services Client

After the installation one would have to startup the analytic server and the integration server. Following is the architecture diagram for the analytic server

Architecture

 

Let us try to understand the 4 components in detail.

Analytic Server:        

This is where the MOLAP cube is stored. The server acts as a shared resource handling all data storage, calculatioo, sorting etc. It also contains the Outlines (just assume that outline is a file that stores the dimension and measure specifications), rules (one can define rules for data load) etc.

            Analytic server has 2 kinds of storage

ü      Block Storage (Easy to use and implement but does not scale)

ü      Aggregate Storage (Has certain limitations but can scale)

 Analytic Administration Services:     

            Analytic Administration Services—the database and system administrators’ interface to Analytic Services—provides a single-point-of-access console to multiple Analytic Servers. Using Analytic Administration Services you can design, develop, maintain, and manage multiple Analytic Servers, applications, and databases. You can preview data from within the console, without having to open a client application such as Spreadsheet Add-in. You can also use custom Java plug-ins to leverage and extend key functionality.

 Analytic Integration Services: 

            This is a very important component wherein one designs the dimensions and fact tables if one wants to leverage different data sources like Oracle, DB2, SQL Server etc. It basically uses ODBC to connect to different data sources. It also provides a drill through feature wherein one can drill down from the database (MDB) to relational database.

 Analytic Provider Services:

            This is for enabling clustering of the database (essbase cube) across multiple machines.

Posted in Hyperion Essbase | 2 Comments »

Hyperion System 9 BI – Overview

Posted by Venkatakrishnan J on July 19, 2007

Before Oracle acquired Hyperion, their BI stack was called as System 9 that included a lot of components ranging from BI to Performance management. After the acquisition, Oracle has come out with a slightly modified bundle called as OBI EE Plus. The BI bundle was earlier called as OBI EE. OBI EE+ = OBI EE + 4 Hyperion Components. Following are the components of OBI EE Plus

1. Oracle Dashboards
2. Oracle Answers
3. Oracle BI Publisher
4. Oracle Delivers
5. Oracle Disconnected Analytics
6. Financial Reporting
7. Production Reporting and SQR
8. Web Analysis
9. Smartview for Office

Now that we know the bundling of OBI EE+, lets try to understand the Hyperion System 9 components. These components lend further support to the OBI EE and makes it a complete offering.

Financial Reporting: (Like BI Publisher with the ability to natively connect to Essbase, SAP BW and Office integration with PowerPoint, Excel, Word etc)

·        BI + module primarily utilized for highly formatted financial and operating reports against OLAP, planning and HFM sources.

·        Can do scheduling

·        Can do report bursting

·        Was known as Hyperion reports

·        Primarily used for creating compliant financial reports

·        2 interfaces – Desktop client and web client

·        Desktop Client – Financial Reporting Studio

·        Web Client – Part of System 9 Workspace

·        Can have SAP BW as a data source

·        Has MS Office integration

 Financial Reporting

Production Reporting: (More like BI Publisher without bursting capabilities. One would have to use Financial reporting for that feature)

·        Web-based delivery of high volume, presentation quality reports

·        Cross function reporting like creating reports based on data from CRM and financial planning data

·        Connection to DB2, Oracle, SQL Server, Essbase, SAP R/3, SAP BW

·        Pixel-Perfect reporting

·        Formerly known as SQR

·        No Bursting capabilities

Production Reporting

 

Web Analysis: (Like Discoverer for OLAP but with multiple OLAP sources and ability to create dashboards and other features)

·        BI + module primarily utilized for advanced analysis capabilities against OLAP data (not restricted to Essbase alone)

·        Analysis focused

·        Adhoc querying

·        Dashboard creation

·        Was known as Hyperion Analyzer

·        2 interfaces – Java Client and Web Client

·        Java Client – Web Analysis Studio

·        Web Client – Part of System 9 Workspace

Web Analysis

Also, Mark Rittman has a writeup on Hyperion components here. This would give more details on the components. The above post is just to give you an overview. It would be interesting to see how these components would be integrated in the future. But for now, one would have to go through a seperate download for the OBI EE and Hyperion to use OBI EE Plus. But again, that would be worth it for the features this tool offers :-).

Posted in OBI EE Plus | 6 Comments »

My First Post

Posted by Venkatakrishnan J on July 18, 2007

This is my first post on this blog. I intend to blog about various BI tools like Oracle BI EE+, Hyperion Essbase and Cognos. I have had experiences in using MOLAP tools like Cognos, Hyperion(essbase) and Oracle OLAP. You can expect some postings on the above 3 MOLAP tools and how they differ from each other. Also, i have had some experiences in using Discoverer, BI Publisher, OBI EE. Any feedback with regards to the content would be much appreciated.

Posted in General | Leave a Comment »