Business Intelligence – Oracle

Archive for June, 2009

Time for a Change – Upcoming Announcements – Millionth Hit

Posted by Venkatakrishnan J on June 15, 2009

Well, as the saying goes “Change is the only Constant”, there are quite a few changes that are coming up on this blog(well not blog alone!!!) in the near future. I would be in a position to make an announcement in a week or so. And i am very much looking forward to that. One thing that i can say for sure is the fact that you can expect more of my blog entries in the future :-). More on that next week.

And as luck would have it, while i was writing this, the blog registered its first Millionth hit (of a total of 302 blog entries). I would have to express and extend my thanks to anyone and everyone who have been visiting this blog ever since its inception on 18th of July 2007. I believe the blog has come a long way since then. I have written at least two blog entries every week since i started, barring a couple of months when i did not even write a single one. When i started to write on BI EE there were only a couple of people writing about it like Mark(who was very well known in the Oracle BI Community even at that time) and Adrian(actually myself and Adrian were discussing this in the BI Forum). Then came along John who was also very active on the BI Forums. And then came people like Alex(Siebel + BI EE) , Christian (BI EE + Essbase) and others who have been working on these products for long but just now started to blog about them.

In the coming future, i would be primarily focusing on Hyperion Essbase(i would say this has been a tool that has been really close to my heart that i have not blogged much about), EPM Integration, Hyperion Planning/EPMA integration, BI EE – Essbase Integration (more use cases). Hopefully you have found this blog useful and thanks for stopping by.

Posted in All Posts, General | 16 Comments »

Oracle Data Integrator 10.1.3.5 – Connectivity to Open LDAP of Shared Services

Posted by Venkatakrishnan J on June 9, 2009

One of the features of Oracle Data Integrator is its ability to connect to a lot of disparate data sources using JDBC. One such feature is its ability to expose any LDAP directory as a relational source. If you are on earlier releases of Hyperion EPM like 9.3, where there is no out of the box SSO and authentication/authorization capability to BI EE with open LDAP, one approach is to configure BI EE to authenticate against OpenLDAP and then get the user-group information from some other custom table(or by using the DBMS_LDAP package). I had shown how to configure BI EE to authenticate against OpenLDAP here. Since BI EE cannot automatically pick up the groups directly from OpenLDAP in prior releases, one way is to get the user-group related information from OpenLDAP and then populate that into a set of custom tables. Then BI EE can be used to get these groups from the custom tables. The architecture would look something like this

image

Lets look at what it takes to setup the OpenLDAP connectivity from ODI. As a first step lets first log into Topology Manager and create a new LDAP connection. Choose the “Sunopsis JDBC Driver for LDAP” as the JDBC driver

image

And then choose the JDBC URL.

image

To enable the connectivity to any LDAP directory, the password would have to be passed in an encoded format. To encode the password, run the below command from a command prompt.

 
java -cp {OracleDI}\oracledi\drivers\snpsldapo.jar 
com.sunopsis.ldap.jdbc.driver.SnpsLdapEncoder
<the of password root openldap>

image

Copy the above encoded password. In the JDBC URL, enter the below URL

 
jdbc:snps:ldap?ldap_url=ldap://localhost:28089/ &amp;ldap_password=KILAKMNJKKLHKJJJDDGPGPDB
&amp;ldap_basedn=dc=css,dc=Hyperion,dc=com 

image

The basedn above is what would be used for searching all the users, groups, roles etc. In the Data Server definition, enter the username as root user who has traversing access to the entire OpenLDAP directory

image

You should be able to test the connection to the LDAP from here. The root user of OpenLDAP is different from the admin user. In fact, the admin user’s original cn is not admin. It is 911. admin is the givenName attribute of the 911 user. The root user password is by default root. One behavior that i noticed across the releases, was the fact that in 9.3 release admin user had the traverse directory privilege. But in EPM 11, 911 user does not have the traverse directory privilege. In my case, the default root password did not work. So, i had to reset the root user password from shared services.

image

As a side note, if you feel that shared services web console does not give you the actual LDAP directory structure, i would recommend a free LDAP client like JXplorer. The screenshot of shared services OpenLDAP using this free client is given below

image

Now, if you go to the Designer and reverse engineer this data source using selective reverse.

image

image

This should convert the entire directory structure to a relational format. From this point onwards, its a matter of building the interfaces and loading the custom user-group tables. Though the setup of the above is pretty straight forward, this can come in very handy especially when you are trying to consolidate/report against multiple user sources.

Posted in All Posts, EPM, Hyperion Shared Services, OBI EE Plus, Oracle Data Integrator | 1 Comment »

Oracle Data Integrator 10.1.3.5 – Using OBI EE as a Data Source – Using BI EE JDBC Driver

Posted by Venkatakrishnan J on June 7, 2009

In a prior blog entry here, i had shown how to use BI Server as a data source for loading data from Essbase into custom tables. That approach basically used the Sun ODBC-JDBC bridge which in most cases has bad performance. Today we shall see how to use the native BI Server JDBC driver to connect to BI Server and in-turn use that for loading Essbase Data into a custom table.

One of the major advantages of ODI is the fact that it can be used to connect to any data source that has a valid jdbc driver. In order to make ODI to use the BI EE JDBC driver, we need to make the JDBC driver (jar file) of the BI Server to be accessible by ODI. The jar file(bijdbc14.jar) of BI Server can be obtained from the BI Publisher web server installation folder {OracleBI}\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib. The main reason why we are going to the BIP folder structure is because BIP uses the JDBC driver of BI Server to do reporting on BI EE metadata.

image

The above screenshot basically provides the database driver class and the connection string of the JDBC driver of BI EE. Now copy this driver jar file to {OracleDI}\oracledi\drivers directory.

image

Now open up Topology Manager and create a new Universal Data Server. Use the above driver class and connection in the JDBC URLs.

image

Ensure that you are able to test the connection using Administrator username and password.

image

Now, in the physical schema choose any subject area in the Presentation Layer.

image

Now once this is done, go to the Designer and create a Project. And import the necessary Knowledge Modules that i had shown in the last blog entry. Since we are using JDBC, we can do a selective reverse of all the necessary tables.

image

But be aware that, BI EE supports multiple presentation tables with the same name across subject areas. But ODI currently does not support multiple table import with the same name (as there is no BI EE specific RKM. This can be resolved if you build a separate RKM. Also, though you might be able to reverse objects with special characters, they would not work within an Interface. So, its better to create aliases with proper ODI supported naming conventions. ). Lets choose just 4 such tables, Basic, Market, Scenario and Year.

image

image

image

Now lets build a very simple interface wherein we would be inserting Gen2, Market and the Measure value into a dummy table. Remember in BI EE, whenever you are bringing in multiple tables from a single presentation subject area, no joins are needed i.e. a cross join would have to be configured across multiple tables.

image

If you execute this interface you would notice that BI Server would generate the MDX accordingly and would then insert the output of the MDX to the database table.

image 

This should give you an idea of how to go about using multiple BI EE subject area tables in an ODI interface and then using that interface to load data into a database table. Also, this method should typically be as fast as your BI Server is since it uses the JDBC connectivity as opposed to the JDBC-ODBC bridge.

Posted in All Posts, EPM, Hyperion Essbase, OBI EE Plus, Oracle Data Integrator | 3 Comments »

Oracle BI EE 10.1.3.4.1 – Handling Complex String Manipulations – Using Regular Expressions (RegEx) and Evaluate

Posted by Venkatakrishnan J on June 4, 2009

One of the requirements that i recently came across involved some complex string manipulations that cannot be solved easily by using BI EE specific functions. Just to illustrate the requirement, take a look at the screenshot below

image

As you see, the above is a simple BI EE report containing the customer related details. But the major problem with this report is that, it contains all the details of a customer in a comma separated  format. Basically the source application has inserted the data into this table in such a fashion. One key aspect to this is the fact that the comma delimited customer details follow a specific pattern. That is the details column is nothing but a concatenation of the below customer attributes in the same order

1. Customer First Name
2. Customer Last Name
3. Customer Gender
4. Customer Email Address
5. Customer Phone Number
6. Customer Street Address
7. Customer City
8. Customer State
9. Customer Country

The requirement is to get a report containing the Customer ID, Country ID, Customer Phone Number and the Customer Email address. Normally the best place for handling this would be in the ETL stage wherein individual attributes can be split into multiple columns and then used for reporting. Unfortunately in most cases we would have to work with what we have. To do this in the reporting layer requires some complex INSTR, SUBSTR logic. Today we shall another approach for handling such requirements. Instead of using normal string manipulation functions, we shall be using a new feature of Oracle 10g called as the Regular Expressions.

Regular Expressions or RegEx make string manipulations easy and in fact provides quite a few advanced formatting, filtering options. The regular expressions come as 4 different types of functions

1. REGEXP_SUBSTR – Advanced Sub-String function
2. REGEXP_INSTR – Advanced String position evaluation function
3. REGEXP_LIKE – For doing advanced filtering on a result set
4. REGEXP_REPLACE – For doing advanced multiple-iterative string replace

In order to achieve the above requirement, we need to use these database functions. And of course, we need to use EVALUATE to call these database functions. To split the above comma separate customer attributes, we need to use RegEx functions given below

1. Customer Phone – REGEXP_REPLACE(CUSTOMER_DETAILS,'(.*),(.*),(.*),(.*),(.*),(.*),(.*),(.*)’,’\4′)
2. Customer Email – REGEXP_REPLACE(CUSTOMER_DETAILS,'(.*),(.*),(.*),(.*),(.*),(.*),(.*),(.*)’,’\3′)

Now use EVALUATE and pass down the above RegEx functions back to the database.

image

If you look at the report now, you should basically have the individual attributes in a separate column as shown below.

image

The above was one requirement. Lets look at another requirement now. In some cases, when you are reporting against transactional applications, you would have columns which get entered as free flowing text. The requirement is to classify these columns into 3 types. One is numeric, the other is free text without numbers and the third is alphanumeric. The requirement is to basically give a provision to the end users to choose a specific type and then that column should only the corresponding text i.e. for example if numeric is chosen only the text where numbers were entered should be displayed. In normal sql terms, filtering for numerals and alpha-numerals require separate logic and is complicated as well. We shall see how we can achieve this using RegEx.

Lets first take a look at the report

image

Now, lets create a prompt which will produce just 3 values (Numeric, Alphabets, Alpha-Numeric).

image

 
SELECT 'Numeric' FROM RegEx WHERE CUSTOMERS_DETAILS.COMMENTS IS NOT NULL 
UNION ALL 
SELECT 'Alphabets' FROM RegEx WHERE CUSTOMERS_DETAILS.COMMENTS IS NOT NULL 
UNION ALL 
SELECT 'Alpha-Numeric' FROM RegEx WHERE CUSTOMERS_DETAILS.COMMENTS IS NOT NULL 

Make this prompt to set a presentation variable called AlphaNum.

image

Now go to the original report and create a filter as shown below

 
CASE WHEN '@{AlphaNum}{Numeric}' = 'Numeric' 
THEN EVALUATE('REGEXP_SUBSTR(%1,''^\d+$'')',CUSTOMERS_DETAILS.COMMENTS) 
WHEN '@{AlphaNum}{Numeric}' = 'Alphabets' 
THEN EVALUATE('REGEXP_SUBSTR(%1,''^[a-zA-Z]+$'')',CUSTOMERS_DETAILS.COMMENTS) 
ELSE EVALUATE('REGEXP_SUBSTR(%1,''^[a-zA-Z0-9]+$'')',CUSTOMERS_DETAILS.COMMENTS) END IS NOT NULL 

image

Now include this report in the main dashboard along with the prompt.

image

image

As you see, the RegEx functions are pretty powerful. And of course, with the advent of EVALUATE, we now have the capability to leverage them from BI EE itself. This saves time and effort in implementing complex string based logics.

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

Oracle Data Integrator 10.1.3.5 – Using Oracle BI EE as a Data Source – Loading Essbase data into custom tables – Using JDBC-ODBC Bridge

Posted by Venkatakrishnan J on June 2, 2009

In the blog entry here, i had basically shown multiple ways of loading data into Essbase. Today we shall see an approach wherein one can extract data from Oracle BI Server using the JDBC-ODBC bridge. The idea is to use BI Server to import Essbase metadata. Then Oracle Data Integrator would be used to extract the Essbase metadata through MDX via the BI Server and then load into a custom table. Though there are no out of the box RKM’s to reverse engineer BI Server metadata, we can work around them using some other functionality. If you want to extend this, you can create your own custom RKM to reverse-engineer BI Server metadata tables.

To start with lets first import a sample Demo->Basic cube into our BI EE repository. While creating the subject area of this cube, ensure that you do not have spaces/special characters in the column names. If you need the special characters for Answers, then add aliases with new no space/special characters name. For example, if you have Gen1, Year as one of your column names, add an alias as Year.

image

image

The idea behind doing this is to ensure that we can treat these tables as normal tables using the AnalyticsWeb ODBC connection. Of course, we can make ODI to append double quotes if special characters are present in a column or a table name. But for now lets go with this approach.

Once the aliases have been added, go to the Topology Manager and create a universal Data Server using the SUN JDBC-ODBC bridge.

image

image

image

Create a physical schema. Ensure that you are entering the correct subject area name in the schema.

image

Ensure that you are able to test the connection. Once this is done, create a logical schema and assign it to the Physical Data Server through the Global Context.

image

After this go to the designer and create a project. Import the the following Knowledge Modules in to the Project.

1. LKM SQL to SQL
2. CKM SQL

Then create a model. Since we do not have a RKM to reverse engineer the data sources automatically (though you can actually extend other RKM’s for this purpose). So, for now lets manually enter the tables and columns in the Model as shown below

image

Use the Aliases as the column names.

image

image

Now ensure that you are able view the data directly from Essbase using this.

image

image

If you look at the screenshot above, BI Server would be showing the data against the actual names (Gen1, Year etc). So, you can be sure now that the aliases can be accessed even outside of BI EE using the ODBC connection. Now, to make this simple lets create a straight forward table called YEAR_DIM_ESS in any database schema (this will act as our target)

 
CREATE TABLE 
YEAR_DIM_ESS ( 
YEAR VARCHAR2(100), 
QUARTER VARCHAR2(20), 
MONTH VARCHAR2(20)); 

Import this table to another Model. And then create an interface mapping the columns from the source to the target. Ensure that you are using the SUNOPSIS_MEMORY_ENGINE as the staging area.

image

image

image

Now, if you execute the interface, you would have essbase data loaded inside the database table.

image

This is one other good option to consider if you have full set of end to end oracle tools like ODI, Essbase, BI EE etc. In this case today, i have used the JDBC-ODBC bridge. Later i would be covering how we can use the JDBC jar of BI Server directly within ODI.

Posted in All Posts, EPM, Hyperion Essbase, OBI EE Plus, Oracle Data Integrator | 3 Comments »