Business Intelligence – Oracle

Archive for September, 2007

Oracle BI EE 10.1.3.3 – Customizing look and feel – Styles and Skins

Posted by Venkatakrishnan J on September 12, 2007

OBI EE provides options to do customizations to the look and feel of dashboards and answers. One can change the images and put in custom images depending on what they need. I had one of the customers today saying that it does not work in the same way as it has been documented. Before we go into that, lets first explore the customization options. There are 2 types of customizations. One is by using Styles and the other is by using Skins. Both of them have their own purposes and have to be used in coordination with one another.
   
   1.   Styles
   2.   Skins

A style controls how dashboards and answers are displayed to an end user at run time. Where Skins are assigned to an end user while logging in. Generally, styles can be changed and skins consist of objects that are non-alterable like corporate logos etc. One can find all the styles and skins under the {ORACLEBI}/web/app/res folder. All the folder under this folder that start with s_ are styles and those that start with sk_ are skins. Now lets see how to modify the styles for an end user. Log into BI Dashboards and go to the edit dashboard page and click on dashboard properties.

   

   

Lets try modifying the style to siebel77. This would give the old siebel look and feel to the reports that we have created and also for the dashboards. If you are an user who likes the siebel look and feel, its still there but has been pushed to the background by the sleek new Oracle style. Lets choose the siebel style and see how it looks like.

   

Now, that we know how to change the styles, lets start with creating a new style. Just to be clear here, changing styles and skins might vary from on App server to another. The one that i am demonstrating is for the default OC4J. So, if you are on the default install you can go ahead and follow the list of steps. If not, you can still try and let me know whether it works since i have not tried. I am not sure whether there is a documentation bug since it somehow does not seem to work when you follow the documentation. Lets start creating a new style from out old siebel77.

1.   Lets copy the s_Siebel77 folder under {ORACLEBI}/web/app/res under the same directory.
2.   This would result in a copy of the style which we shall rename to s_newSiebel. For styles, it is mandatory to have the folder name starting with s_.
3.   Now lets copy the new renamed s_newSiebel folder to {ORACLEBI}/oc4j_bi/j2ee/home/applications/analytics/analytics/res folder.
4.   Lets make some changes to the style like changing the logo etc. I have changed the bg_banner.gif (logo in dashboard).
5.   Remember, the changes you make have to be done to the folder under the oc4j directory.
6.   And you would have to restart the OC4J for this to get reflected in your dashboards.

   

The above is way different from what has been stated in the docs. Either the older docs have not been updated or i might be missing something. The customizations that you have made above would go when you do an upgrade since all the styles which are created under {ORACLEBI}/web/app/res would be overwritten during the upgrade.

Advertisements

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

Oracle BI EE 10.1.3.3 – Support for Native database Functions and Aggregates

Posted by Venkatakrishnan J on September 10, 2007

I was going through the new features guide in OBI EE 10.1.3.3. I happened to notice one other killer feature that everyone would like(me included). OBI EE 10.1.3.3 now natively supports the use of database functions directly from answers or from Metadata Repository. It supports the use of both one-to-one functions and also aggregate database functions. So if you want to use database specific functions directly from within answers, you can do that to your heart’s content now :-). Let’s see how to go about doing that here in this article. There are 3 types of functions that you can call within a database.

1.   Functions that just do some data modification. These are one to one functions. For example, substr, instr etc are all one to one functions.

   In order to call these functions from BI Answers, a new function called EVALUATE has been added to the function dictionary of OBI EE. This EVALUATE function would take your database specific function as its argument and can also pass specific columns into the function. Lets see the syntax of EVALUATE(from the docs).

Syntax: EVALUATE(‘DB_Function(%1)’, <Comma seperated Expressions>)

As you see above, the columns are passed into the database specific functions like parameters in DOS. If you have more than 1 parameter to your function then you can use arguments like %2, %3 etc depending on how many parameters that you pass. But i believe currently it does not support parameters of different data types.

      

In above report, i am creating a new column called dense rank that would basically do a dense rank and order by the Amount.

      

As you see above, FACT.Amount would be passed as %1 to the dense rank function of the oracle database.

      

One can also call user-defined functions directly from the database. For example, i have created a very simple function test1 that would basically do a summation of the Amount and Effort.

      

      

2.   Functions that do some aggregation. For example, one can call functions to do a group by on the resultant query like sum, count etc.

Syntax: EVALUATE_AGGR(‘DB_Aggregate_Function(%1)’, <comma separated Expressions>)

      

      

3.   Functions that return a boolean value.

Syntax: EVALUATE_PREDICATE(‘DB_Function(%1)’, <comma separated Expressions>)

These functions are used in the where clause of a function. These are evaluated and help in filtering the resultant recordset.

But be aware that this supports only sql functions. It still does not support MDX or MOLAP data sources. For more information one can get the details here

Posted in All Posts, OBI EE Plus | 6 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 11g – PIVOT and UNPIVOT

Posted by Venkatakrishnan J on September 5, 2007

If you had noticed, one of the most important new features of Oracle Database 11g from a SQL perspective is the introduction of PIVOT and UNPIVOT operators into SQL constructs. So, now one can have cross tab views, that we are so used to in reporting tools (like OBI EE), from sqlplus. Also, it would really be great if the reporting tools can take advantage of this construct while building the queries. I will demonstrate PIVOT and UNPIVOT with a small example here. The following picture shows the output of a simple crosstab from OBI EE.

      

What this crosstab does is, it analyzes the amount_sold (Sales table of the SH schema) across 2 dimensions countries and channels. Lets see how a normal query would have been to get this data in 10g or before (simple unpivoted query).

SELECT COUNTRY_NAME COUNTRY, CHANNEL_NAME CHANNEL, SUM(AMOUNT) AS AMOUNT
FROM
SALES A,
GEOGRAPHY B,
CHANNELS C
WHERE
A.GEOGRAPHY = B.DIMENSION_KEY AND
A.CHANNELS = C.DIMENSION_KEY
GROUP BY
COUNTRY_NAME, CHANNEL_NAME

The result of the above query would look something like this

      

Now, our aim is to build a cross tab out of this. Lets try creating a crosstab using the PIVOT operator directly on the above aggregate query. But before that let me give the syntax of the PIVOT operator (taken directly from the docs) here.

Syntax:

SELECT ….
FROM

PIVOT
(
aggregate-function()
FOR IN (, ,…, )
) AS
WHERE …..Here, table-expr would be our aggregate query that we specified above. Aggregate-function would require the measure which we would like to aggregate (in our case AMOUNT). pivot-column is the column that we would like to pivot (pivot the rows into columns). Alias is optional. Now lets build our query.SELECT *
FROM ( SELECT COUNTRY_NAME COUNTRY, CHANNEL_NAME CHANNEL, SUM(AMOUNT) AS AMOUNT
FROM
SALES A,
GEOGRAPHY B,
CHANNELS C
WHERE
A.GEOGRAPHY = B.DIMENSION_KEY AND
A.CHANNELS = C.DIMENSION_KEY
GROUP BY
COUNTRY_NAME, CHANNEL_NAME ) A
PIVOT
(
SUM(AMOUNT)
FOR CHANNEL IN (‘Direct Sales’, ‘Partners’, ‘Internet’)
)
ORDER BY COUNTRY      As you see above, we have pivoted the first query into a crosstab. To unpivot this, we would have to create a view on top of the above query so that we can unpivot that view.

CREATE OR REPLACE VIEW
PIVOT_AMOUNT_VW
AS
SELECT *
FROM
(
SELECT COUNTRY_NAME COUNTRY, CHANNEL_NAME CHANNEL, SUM(AMOUNT) AS AMOUNT
FROM
SALES A,
GEOGRAPHY B,
CHANNELS C
WHERE
A.GEOGRAPHY = B.DIMENSION_KEY AND
A.CHANNELS = C.DIMENSION_KEY
GROUP BY
COUNTRY_NAME, CHANNEL_NAME) A
PIVOT
(
SUM(AMOUNT)
FOR CHANNEL IN (‘Direct Sales’ AS DS, ‘Partners’ AS PARTNER, ‘Internet’ AS INTERNET)
)
ORDER BY COUNTRY

         

Lets unpivot the above view.

SELECT *
FROM PIVOT_AMOUNT_VW
UNPIVOT INCLUDE NULLS
(
AMOUNT
FOR CHANNEL IN (DS, PARTNER, INTERNET)
)
ORDER BY COUNTRY

      

Quite an interesting new feature considering the fact that pivoting is an operation that is done in almost all customer scenarios. There are a number of options for PIVOT and UNPIVOT. They can be obtained from the documentation here

Posted in All Posts, Oracle 11g | 1 Comment »

OWB 10g/11g – Heterogeneous Data Sources – Excel, MySQL and SQL Server

Posted by Venkatakrishnan J on September 4, 2007

As you would be knowing, Oracle Warehouse Builder can connect to heterogeneous data sources like Excel, MySQL and SQL Server. In this article, we would be seeing how to go about setting the connection to these datasources via ODBC. The steps are uniform and infact it requires a bit of knowledge on Heterogeneous Connectivity of Oracle Database. My example is based on a ODBC connection to an Excel file. But the steps should be same for any other datasource which you are trying to import via ODBC. The list of steps are

1. Create a System DSN pointing to your Excel (SQL Server, MySQL etc) data source.

2. Create a Listener entry in the listener.ora of the database. This step is to make the listener to listen for new connections to this DSN. In my case, the listener.ora would look like this

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = EXCEL)
(ORACLE_HOME = D:\oracle\BISE1\db)
(PROGRAM = hsodbc)
)

(SID_DESC =
(SID_NAME = orcl)
)
(SID_DESC =
(SID_NAME = bise1db)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)

I have added the entry (marked in red) above to my listener.ora file

3. Create a tnsnames.ora entry. In my case, the entry would be like this

EXCEL =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) (CONNECT_DATA = (SID = EXCEL) ) (HS = OK) )

As you note above, the SID is the DSN name (EXCEL) that we created in step 1.

4. The next step is to go to the folder ORACLE_HOME/hs/admin and create a new file of the name initEXCEL.ora (init.ora). The file would contain the following

HS_FDS_CONNECT_INFO = EXCEL
HS_FDS_TRACE_LEVEL = 1

The HS_FDS_CONNECT_INFO points to the DSN name that we created in step 1.

5. Restart the listener. Connect to sqlplus and test whether the Heterogeneous connection has been properly setup. To do this, log in as a known user. In my case, i would log in as SH.

Conn SH/SH

Create database link EXCEL using ‘EXCEL’ -> This is to create a database link using the tnsnames entry that we created in step 3.

select table_name from all_tables@EXCEL -> This should return all the tables that you have connected via the DSN. In Excel files it would list all the worksheets.

6. Now that the HS has been setup in the database, the next step is to import the tables via OWB. Open OWB design center and connect to the repository. Go to the Non-Oracle databases tab and create a new connection using ODBC.

7. Enter the connection details of the DSN that we created. Remember to use the DSN name as the service name.

8. Then import the tables/worksheets that you want.

As you see here, the connection setup is uniform for all the databases. The only step that would vary is the step 1. In 11g one can explicitly use an external database gateway to connect if one does not want to use ODBC. The documentation for this can be found here.

Posted in All Posts, OWB | 17 Comments »

Hyperion Essbase and JAPI – Custom Applications using JDeveloper

Posted by Venkatakrishnan J on September 3, 2007

I had a question from one of the Hyperion users yesterday with regard to the open APIs of Essbase. The user wanted to know whether a Hyperion cube can be exposed to custom applications. In short, the customer wanted to know whether there is any Hyperion equivalent of Oracle OLAP API. I did some digging around and found that Hyperion Essbase has 3 different types of APIs. They are

1.   C API
2.   Microsoft Visual Basic API
3.   JAPI for Java

In this article, i would be showing how to use the JAPI from a client machine to access the analytic server data. To do this, i created a sample cube on the Essbase server. If you are new to Hyperion, you can get a short writeup of how to go about creating a cube in one of my previous blog entries here. My sample cube consisted of 2 dimensions. They are

1.   Times
         Levels:   Year->Quarter->Month->Day

2.   Products
         Levels:   Product Name

The OLAP Model and the Meta Outline are given below in the pictures.

         olapmodel1.jpg

         olapmetaoutline2.jpg

In order to use the JAPI in a client machine, one would have to download and install the analytic server runtime component (analytic server client). This client would give the necessary jars which we can include in our project. The entire JAPI of Hyperion Essbase is split into 6 main packages. They are

1.   com.essbase.api.base
2.   com.essbase.api.metadata
3.   com.essbase.api.dataquery
4.   com.essbase.api.datasource
5.   com.essbase.api.domain
6.   com.essbase.api.session

As the name suggests, each of these packages have their own importance. For example, com.essbase.api.session and com.essbase.api.base help in creating a session. These package consists of methods that expose the entire functionality of Essbase to end users. In our example, we would be using all these packages to get a value from one of the cells in the Essbase. To do this, i will give an overview of the steps that we would have to take.

1.   Establish an Essbase session by calling the signOn() method.

2.   Specify the Olap Server that you want to connect by using the getOlapServer() method.

3.   Connect to the Olap Server using the connect() method.

4.   Open the Cube (database in Essbase terminology) by specifying the application name and the database (use the openCubeView method)

5.   Perform member selection. Select the members that you would like to display.

6.   Get the cell value from the cube for the specified member(s).

The code to do this would look like this.

package essbasetest;

import java.lang.Object;
import com.essbase.api.base.*;
import com.essbase.api.datasource.*;
import com.essbase.api.dataquery.*;
import com.essbase.api.metadata.*;
import com.essbase.api.domain.*;
import com.essbase.api.session.IEssbase;

public class test1
{
public test1()
{

String olapServerName;
String userName = “analyticservices”;
String password = “welcome1”;
String providerURL = “http://localhost:13080/aps/JAPI&#8221;;
String analyticServerName = “incq065bb”;
String appName = “LevelTi”;
String dbName = “LevelTi”;

IEssbase ess = null;
IEssOlapServer olapSvr = null;
try
{
ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);
IEssDomain dom;
dom = ess.signOn(userName, password, false, null, providerURL);
//System.out.println(dom.PROP_COUNT_OLAP_SERVERS) ;
olapSvr = (IEssOlapServer)dom.getOlapServer(analyticServerName);
olapSvr.connect();
IEssCube cube = olapSvr.getApplication(appName).getCube(dbName);
System.out.println(cube.getApplicationName());
//getMemberFromCube(cube);

IEssCubeView cv = null;
cv = dom.openCubeView(“Cube Query”, analyticServerName, appName, dbName);

performMemberSelection(ess, cv);
performCubeViewOperation(ess, cv, “pivot”);

} catch (EssException ex){
System.err.println(“Error: ” + ex.getMessage());
}
}

static void performCubeViewOperation(IEssbase ess, IEssCubeView cv,
String opStr) throws EssException
{
IEssGridView grid = cv.getGridView();
grid.setSize(3, 5);
grid.setValue(0, 2, “Product”);
grid.setValue(0, 3, “Market”);
grid.setValue(1, 2, “Jan”); ;
grid.setValue(1, 3, “Feb”);
grid.setValue(1, 4, “Mar”);
grid.setValue(2, 0, “Actual”);
grid.setValue(2, 1, “Sales”);
IEssOperation op = null;

op = cv.createIEssOpPivot();
((IEssOpPivot)op).set(0, 3);

cv.performOperation(op);

int cntRows = grid.getCountRows(), cntCols = grid.getCountColumns();
System.out.print(“Query Results for the Operation: ” + opStr + “\n” +
“—————————————————–\n”);
for (int i = 0; i < cntRows; i++)
{
for (int j = 0; j < cntCols; j++)
System.out.print(grid.getValue(i, j) + “\t”);
System.out.println();
}
System.out.println(“\n”);
}

static void performMemberSelection(IEssbase ess, IEssCubeView cv)
throws EssException {
String fldSel = “<OutputType Binary <SelectMbrInfo (MemberName, MemberLevel, MemberGen, Consolidation, MemberFormula, MemberAlias, DimensionName, Expense, MemberNumber, DimensionNumber, ChildMemberName, ParentMemberName, PreviousMemberName, NextMemberName)”,
mbrSel = “@ichild(PRODUCTS), @ichild(TIMES)”;
IEssMember[] mbrs = cv.memberSelection(mbrSel, fldSel);
for (int i = 0; i < mbrs.length; i++) {
IEssMember mbr = mbrs[i];
System.out.println(“Name: ” + mbr.getName());
System.out.println(“Level: ” + mbr.getLevelNumber());
System.out.println(“Generation: ” + mbr.getGenerationNumber());
System.out.println(“Consolidation: ” + mbr.getConsolidationType());
System.out.println(“Formula: ” + mbr.getFormula());
System.out.println(“Dimension name: ” + mbr.getDimensionName());
System.out.println(“Child count: ” + mbr.getChildCount());
System.out.println(“Parent name: ” + mbr.getParentMemberName());
System.out.println(“Member number: ” + mbr.getMemberNumber());
System.out.println(“Dimension number: ” + mbr.getDimensionNumber());
}

mbrs = cv.memberSelection(“1999”, IEssMemberSelection.QUERY_TYPE_CHILDREN,
IEssMemberSelection.QUERY_OPTION_MEMBERSONLY, “1999”, “”, “”);
for (int i = 0; i < mbrs.length; i++) {
IEssMember mbr = mbrs[i];
System.out.println(“Name: ” + mbr.getName() +
“, Desc: ” + mbr.getDescription() +
“, Level Num: ” + mbr.getLevelNumber() +
“, Gen Num: ” + mbr.getGenerationNumber() +
“, Child count: ” + mbr.getChildCount() +
“, Dim Name: ” + mbr.getDimensionName() +
“, Dim Category: ” + mbr.getDimensionCategory().stringValue());
}
}

static void getMemberFromCube(IEssCube cube) throws EssException {
System.out.println
(“\nGetting a Member from Cube (Year): \n” +
“———————————“);
IEssMember mbr = cube.getMember(“1998”);
displayMemberProperties(mbr);

System.out.println
(“\nGetting a Member from Cube (Year): \n” +
“———————————“);
IEssDimension dim = cube.getDimension(“1998”);
IEssMember rootMbr = dim.getDimensionRootMember();
displayDimensionProperties(dim);
displayMemberProperties(rootMbr);

System.out.println
(“\nGetting a Dimension from Cube (Year): \n” +
“———————————“);
dim = cube.getDimension(“1999”);
displayDimensionProperties(dim);
}

static void displayMemberProperties(IEssMember mbr) throws EssException {
System.out.println(“\nDisplaying member properties…\n”);
System.out.println(“Name: ” + mbr.getName());
System.out.println(“Dimension Root Member: ” + mbr.isDimensionRootMember());
System.out.println(“Level Number: ” + mbr.getLevelNumber());
System.out.println(“Generation Number: ” + mbr.getGenerationNumber());
System.out.println(“Unary consolidation type: ” + mbr.getConsolidationType());
System.out.println(“Dimension Name: ” + mbr.getDimensionName());
System.out.println(“Parent member name: ” + mbr.getParentMemberName());
System.out.println(“Member number: ” + mbr.getMemberNumber());
System.out.println(“Dimension number: ” + mbr.getDimensionNumber());
System.out.println(“Next Sibling Member Name: ” + mbr.getNextSiblingMemberName());
System.out.println(“First Child Member Name: ” + mbr.getFirstChildMemberName());
System.out.println(“Previous Sibling Member Name: ” + mbr.getPreviousSiblingMemberName());

}

static void displayDimensionProperties(IEssDimension dim)
throws EssException {
System.out.println(“\nDisplaying dimension properties…\n”);
System.out.println(“Name: ” + dim.getName());
System.out.println(“Dimension number: ” + dim.getDimensionNumber());
System.out.println(“Dimension storage type: ” + dim.getStorageType());
System.out.println(“Dimension tag: ” + dim.getTag());
System.out.println(“Declared size: ” + dim.getDeclaredSize());
System.out.println(“Actual size: ” + dim.getActualSize());
System.out.println(“Attribute dimension data type: ” +
dim.getAttributeDimensionDataType());
}

public static void main(String[] args)
{
test1 a= new test1();
}
}

The output after executing the above package is shown below

         codeoutput.jpg

I have generated the above code based on an example that gets bundled along with the client. I would recommend everyone to go through that since it has a lot of examples and covers a lot of features. The above is just to give you an example and to show you the capabilities of Hyperion Essbase API. One can leverage these java classes in any application. It would be very interesting to see how Essbase gets integrated into Oracle BI stack and i believe it should not be much of a problem. Interesting times ahead indeed!!!

Posted in All Posts, Hyperion Essbase | 4 Comments »