My OBI Implementation Experience

OBIEE 11.1.1.5 is available for download

2011-05-05T22:08:00.000-07:00

Oracle released the latest version of the OBIEE Platform. This version is available for Solaris OS. Installer is not yet available for AIX but is expected to be available soon. This version addresses problems with OBI Applications on 11g, 10g to 11g upgrade issues, support for iPad and iPhone etc..

Software can be downloaded at:

http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/biee-111150-393613.html

I am yet to see the documentation to get the full list of enhancements. Oracle hasn't published the documentation for this release yet.

Marketwire announcement here:

http://www.marketwire.com/press-release/new-releases-oracler-business-intelligence-software-enable-enterprises-improve-timely-nasdaq-orcl-1509102.htm

OBIEE 11g hasn't treated me well!

2010-11-29T09:38:00.000-08:00

Every blogger in OBIEE area has posted about the installation steps and quite a few posts on new features, configuration, architecture etc. after the much anticipated and publicized OBIEE 11g launch in July. I have been thinking about getting it installed and review it myself for quiet a bit of time and it took a lot of effort due to poor planning. Anyway, I am here to put all that effort here so that everybody can learn from it.

My system configuration - Dell Studio 1558, Intel i3 CPU M330 @ 2.13 GHz, 4 GB RAM, Windows 7 Home Premium 64-bit

Attempt 1

Couple of weeks back, I was talking to a colleague of mine and heard that any program can be run as if the OS is XP Professional 32-bit by simply changing the compatibility mode in setup.exe properties. I installed OBIEE 11g R2 Database using compatibility mode and was able to connect to it just fine. I also ran rcu and it just ran great. OBIEE 11.1.1.3 installer wasn't doing anything (no changes in log) for hours at step 11 of 12. I checked couple of OTN posts and it became clear that Windows 7 wasn't an option.

Attempt 2

Then came this noble idea of VMware and thought that RedHat would be fun to install. I used to have Windows and Mandrive on my previous Dell Inspiron 700m. Also, RedHat is becoming popular at work and that also contributed for the decision to install RHEL.OBIEE 11g R2 database, RCU and OBIEE 11g got installed successfully except for few rpms missing in RHEL. It was straight forward except few minor issues. But, Administration Tool isn't available on RHEL. So, checked the platform compatibility xls and found that client is available only for Windows based OS.

Attempt 3

My elder brother uses my 6 year old Dell 700m (1.2 GB RAM, XP Professional), which I used over the weekend to install the OBIEE 11g software. The whole install took the whole Saturday night and the monitor went off in the morning before the installation was fully done and the monitor is not powering on anymore. I tested the installation successfully but I took off one entertainment option for my brother.

Attempt 4

I removed RHEL Virtual machine and installed XP Professional with 2 GB memory, 2 processors and 60 GB Hard Disk dedicated for VM. I got all of it installed on the VM successfully and trying to dig through to make sense of everything. It took about 6-7 hours for the installation, which itself is a lot of time.

Here are my first set of comments(not really complaints).

OBIEE 11g install is a huge footprint. 11-12 weeks were considered enough for simpler OOB Analytics applications such as Service and Order Management where as with this kind of platform install, that kind of timelines cannot be met.
Documentation is about 1 GB, which is just too much to even carry around.
Limited Platforms in Release 1 is another thing. When you cannot have software for all platforms, you always get a feeling that it is not stable. Next release is supposed to address this and the next release is expected in early 2011.
Only application server available to support OBIEE is Weblogic, which is a huge reason for not many upgrades planned.
Installer cannot install client tools alone. I am surprised how Oracle could let this out without addressing this.
OBI Presentation looks really great and I liked the way the samples are presented. Users will have to be trained all over on 11g due to significant differences and more complex presentation may loose its USP.

At a time when IT budgets are tightly managed, investments in technology upgrades will be evaluated more from value perspective. But, as economy picks up pace heading north east, upgrades will get kick started . I expect next year to be huge in terms of upgrades, trainings and lot more projects in OBIEE 11g. So, it is better to be ahead of the curve for everybody than fall behind.

I would like to hear from people about experiences with RPD and Catalog upgrade. I will certainly take sometime in the next few weeks on the upgrade part and post my observations.

Essbase as a data source in OBIEE 10.1.3.4.1

2010-09-24T06:50:00.000-07:00

I came across many resources on web about using Essbase as a data source in OBIEE but all the content is around configuring cube once the cube is imported into RPD. I had problems with OBIEE connecting to Essbase service and with no experience on Essbase, it just makes it difficult to debug where the problem is. Below is the error that I was getting.

Network error [146]: Unable to connect to [:1423]. The client timed out waiting to connect to Essbase Agent using TCP/IP. Check your network connections. Also make sure that server and port values are correct

The very fact that there is not much content on issues like this on web confirms my belief that there are not that many implementations hosted on Solaris. Anyway, here are the steps that should be followed to fix the issue, if the Essbase server installation is default installation. Essbase agent can be run on a different port; check with your Administrator or run netstat to identify what ports are used.

Chcek what your Essbase server version is and install the same version of client on BI Server machine.
Open port # 1423 (Essbase agent port) and port range 32768-33768 (ports on which Essbase clients connect to Essbase agent) in Essbase server for BI server to connect
Run BI Server in 64-bit mode after adding environment variables that maintain paths to Essbase client. Changes in user.sh are pasted below.
Import cubes in Admin Tool, check consistency and save the RPD
Run Answers reports and validate data.

OBI uses client libraries to connect to Essbase, which means client should be installed. Below is the list of variables that should be added to user.sh in //OracleBI/setup directory.

# Essbase Parameters
#---------------------------
ARBORPATH=/Essbase/EssbaseClient
ESSBASEPATH=/Essbase/EssbaseClient
HYPERION_HOME= (up to Hyperion directory)
ESSLANG=English_UnitedStates.Latin1@Binary
export ARBORPATH
export ESSBASEPATH
export HYPERION_HOME
export ESSLANG
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ARBORPATH/bin
export LD_LIBRARY_PATH
LD_LIBRARY_PATH_64=$LD_LIBRARY_PATH_64:$ARBORPATH/bin
export LD_LIBRARY_PATH_64
#---------------------------

Oracle created this as a documentation enhancement.

Upgrade OBIEE from 10.1.3.4.0 to 10.1.3.4.1 !! - Issues and Resolution

2010-06-27T16:26:00.001-07:00

Hi All,

It was a long time since we posted on this blog !! Many things changed in life since my last post !! Changa moved to India recently leaving rest of the team alone !! We had a great time with him and badly missing his company in office and especially during long weekend trips !! :)

In the last 2 months, our team upgraded 16 OBIEE Environments from 10.1.3.4.0 to 10.1.3.4.1. Lets have a short discussion on the approach and achievements :

Why we took the decision to upgrade when there are no issues with 10.1.3.4.0?

OBIEE 10.1.3.4.0 is the most stable product with absolutely no issues when it comes to usage of Dashboards and Delivers. You will come across many issues when you want to implement BI Publisher on 10.1.3.4.0 especially when you are on Solaris platform and using IBM Websphere instead of OC4J. After an extended follow up with Oracle on the BI Publisher issues on 10.1.3.4.0, Oracle suggested to upgrade OBIEE to 10.1.3.4.1 which will fix maximum BI Publisher issues.

Also Oracle claimed that only BI Publisher issues are fixed in 10.1.3.4.1 and no improvement / issues in Dashboards.

So, we finally made a decision to upgrade OBIEE environments to 10.1.3.4.1.

Are there any issues identified in 10.1.3.4.1 post upgrade ?

There are 3 issues identified in 10.1.3.4.1

Multi Select Prompts : If you select multiple values in prompts and click on 'Go', it will wipe off all the values selected and report won't run.

Date Prompt : If you select a date in the date prompt and the format is 'mm-dd-yyyy', it will fire the query to database in the format 'yyyy-mm-dd' which will result in 'Not a valid month' error.

Refresh Link : If you click on the 'Refresh' button down to any report, it will not refresh the report. (This issue is first identified by our team. If you already upgraded to 10.1.3.4.1, dig for this issue and apply patch as mentioned below)

What are the patches to fix this issue ?

Oracle provided one single patch for all these. Patch # 9394038 did the magic. We got all the above issues fixed.

How about BI Publisher, the major target ?

The issues are still open and all the 10.1.3.4.0 issues exist in 10.1.3.4.1 too. We are currently working with Oracle to fix those issues and will come up with some fix soon.

That's it for now !! Will come up with a continuation for this post soon once we progress in implementation of BI Publisher.

Hope this will help OBIEE Administrators creating / upgrading to 10.1.3.4.1 environments. While discussing with Admin's I found that these issues are not identified (post testing) and asked for the related Patch. So this will help them.

Also in parallel we are implementing BI Management Pack which will be out of validation soon. Will share those details as well !!

Bye for now,

Aneel Kanuri

Warm Welcome to Aneel!

2010-04-30T20:07:00.000-07:00

I have been bit busy for last few months due to some recent changes in my work activities. I have been trying to get some of my colleagues to take up the task of blogging or contributing to this blog but didn't get any positive responses. I was afraid at sometime that the blog will become inactive in a while as I get busy with my new role but thankfully, Aneel agreed to become a contributor and I am more than happy to have him contribute to this blog. His unique skills and areas of expertise add not just more technical value but also more variety to this blog and I am sure readers will be greatly benefited.

Aneel has experience with Discoverer Configuration and Administration for quite a bit and in his current role, he administers a huge OBIEE & OBIA Infrastructure. Aneel's posts will speak for his experience than me. I sincerely thank Aneel for doing this and for the kind words about me in his own introduction post. I wish Aneel all the best and I am sure readers will see great benefits from the posts as always.

You will see posts more on OBI Applications and OBI Administration hereafter. Happy reading!

Configuring iBots

2010-03-16T22:10:00.000-07:00

Here goes my first post...

I was asked to configure iBots in our Staging environments last week. I came across a error while configuring iBots and it was observed that our paths are not set in Solaris. I believe this is a common error and might occur in many of your servers too.

The following steps will let you configure the iBots in minutes.

Issue : I got the following error when I ran ../OracleBI/Server/Bin/schconfig :

libnqsutilitygeneric.so is not found in specific path.

Resolution :

Export the following paths :

1. export LD_LIBRARY_PATH=../OracleBI/server/bin (give full path)
2. export PATH=../OracleBI/server
3. export ANA_INSTALL_DIR=../obiee/OracleBI
4. export SAROOTDIR=${ANA_INSTALL_DIR}
5. export SADATADIR=../obiee/OracleBIData
6. export SATEMPDIR=../obiee/OracleBIData/tmp
7. export SAWROOTDIR=$SAROOTDIR/web

P.S: Test the paths randomly by running the echo command.

Exporting only the LD_LIBRARY_PATH also worked for me but it is recommended to export all the above paths. Also, you can find these paths in the common.sh of BI Presentation Services box.

You are now set to configure your iBots.

Now run the schconfig.

1. Configure the database by providing the valid TNS Name, Username and Password.

2. Configure the mail server and SMTP Ports.(SMTP server configuration is needed for scheduler to deliver report content to mail).

3. Restart the Scheduler.
./run-sch.sh stop
./run-sch.sh start

Create an iBot in Delivers and run it to verify whether the iBot is working. Hope, that helps!

Aneel Kanuri

Hi All

2010-03-16T21:51:00.000-07:00

I am Aneel Kanuri working as OBIEE Administrator. I feel it was a great opportunity to work with such a genius, Changa Reddy, in my career. I thank Changa for giving me this opportunity to share my knowledge with you all !!

Here, I would like to share some of my research work on Oracle Products focusing more on OBIEE.

Hope our work will help you all !!

Aneel Kanuri

DAC Full Load vs Incremental Load

2009-09-28T15:12:00.000-07:00

I have been getting few queries around what determines DAC to do an incremental load or Full load. Though DAC Guide has it under Refresh Dates section, I thought I will simplify it for the benefit of everybody.

If you ever want to run a Full Load of an execution plan, reset Data Warehouse in DAC by navigating to Tools -> ETL Management -> Reset Data Warehouse. The warning is - "This action will cause all tables that are loaded during next ETL to be truncated". This means that refresh dates in Physical Sources will be set to NULL both on Source and Target tables and if both refresh dates are NULL, ETL truncates the target table and run Full Load command.

Full load is a straight forward exercise but most often, we encounter doing something that is not straight forward such as running full load only on certain tasks but not the whole of the execution plan. Reasons are plenty and this post is limited to provide a reasonable explanation on how DAC determines whether to run an incremental or full load.

The Refresh Dates in Physical Data Sources determine whether to run the command for incremental or full load. I am pasting a graphic that explains what happens when.

Based on what you need, determine what refresh dates are to be populated/modified in Physical data sources.

DAC Synchronize Tasks failing

2009-08-19T12:17:00.000-07:00

MESSAGE:::Error while inserting a record!
EXCEPTION CLASS::: com.siebel.etl.gui.core.RecordManipulationException

com.siebel.analytics.etl.client.core.DACMessage.convertToRME(DACMessage.java:31)
com.siebel.analytics.etl.client.data.model.UpdatableDataTableModel.upsertNewRecord(UpdatableDataTableModel.java:141)
com.siebel.analytics.etl.infa.fileParsing.InfaDacWriter.insertTableList(InfaDacWriter.java:459)
com.siebel.analytics.etl.infa.fileParsing.InfaDacWriter.insertNodeTables(InfaDacWriter.java:397)
com.siebel.analytics.etl.infa.fileParsing.InfaDacWriter.insertNodeTables(InfaDacWriter.java:314)
com.siebel.analytics.etl.infa.fileParsing.TaskSync.sync(TaskSync.java:168)
com.siebel.analytics.etl.client.action.TaskSynchronizationAction.doOperation(TaskSynchronizationAction.java:123)
com.siebel.etl.gui.view.dialogs.WaitDialog.doOperation(WaitDialog.java:53)
com.siebel.etl.gui.view.dialogs.WaitDialog$WorkerThread.run(WaitDialog.java:85)
------------------------------------------------------------------------------------
We have a custom mapping that has been tuned to use DB Links and also commented the line that was checking Last Update Date in Oracle EBS tables. The commented line is as below.

-- AND (AP_INVOICE_DISTRIBUTIONS_ALL.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE', 'MM/DD/YYYY HH24:MI:SS') OR AP_INVOICES_ALL.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE', 'MM/DD/YYYY HH24:MI:SS'))

After this, DAC task is not synchronizing. It is throwing the error - "MESSAGE:::Error while inserting a record". After removing the line, it is just synchronizing fine.

It looks like DAC has problems avoiding the commented line in SQL Query. When I run DAC Client in interactive mode, it throws this error. So, DAC is treating MM/DD/YYYY as a table name for some reason. I can't think what the logic is but definitely wrong.

FROM W_ETL_TABLE A_W_ETL_TABLE
INNER JOIN W_ETL_OBJ_REF OBJ_REF ON OBJ_REF.OBJ_TYPE='W_ETL_TABLE'
AND OBJ_REF.SOFT_DEL_FLG='N'
AND OBJ_REF.APP_WID='PGBU_R12'
AND OBJ_REF.OBJ_WID=A_W_ETL_TABLE.ROW_WID
WHERE
((A_W_ETL_TABLE.NAME=''MM/DD/YYYY'
))
Aug 10, 2009 9:43:32 AM com.siebel.etl.engine.core.ETLUtils logException
SEVERE: MESSAGE:::ORA-00907: missing right parenthesis

EXCEPTION CLASS::: java.sql.SQLException

How to change the connection pool passwords with each migration?

2009-08-07T12:57:00.000-07:00

I have been depending on my DBA to type the passwords in the connection pools every time we have an RPD that needs to be migrated to pre-production or Production. It is not much of an effort but it is just nuisance to bring him to my desk and let him type the password while I turn my head away.

I have been thinking it can be done by UDML and even heard from a colleague who has successfully done it but never really had all that time to try it myself. My colleague, Pradip, gave me some pointers on this and I thank him for that. Today, I got sometime to try it out for myself and I certify that it works best.

I had RPD with pre-production connections and new RPD with development database connections that need to be moved to pre-production. First, I copied the connection pool from my pre-production RPD and pasted in a notepad. Right click on the connection pool and click Copy and then Paste in a notepad. Just a reminder - Copy and Paste menu items in RPD are actually UDML generator and UDML executor respectively.

Once the UDML is pasted in notepad, you will see the password, user name and all the other connection pool properties. You can add all connection pools to notepad, if you have more than one connection pool that needs to be changed with every migration. Once done, run the below command at command line (you should have OracleBI client installed on the machine where you execute the command).

D:\OracleBI\server\Bin>nQUDMLExec.exe -U Administrator -P SADMIN -I D:\UDML_Stg.txt -B "D:\Repository\OracleBIAnalyticsApps_Dev.rpd" -O "D:\Repository\OracleBIAnalyticsApps_Stg.rpd"

Input RPD - D:\Repository\OracleBIAnalyticsApps_Dev.rpd
Output RPD - D:\Repository\OracleBIAnalyticsApps_Stg.rpd

Once the command is fully executed, you should find output RPD with changed connections. You can specify any path you may wish to have the RPD saved.

Contents of D:\UDML_Stg.txt is -
-------------------------------------------------------------------------------
DECLARE CONNECTION POOL "Oracle Data Warehouse"."Oracle Data Warehouse Connection Pool" AS "Oracle Data Warehouse Connection Pool" UPGRADE ID 2327604
DATA SOURCE {VALUEOF(OLAP_DSN)}
TIME OUT 600
MAX CONNECTIONS 100
TYPE 'Default'
USER 'VALUEOF(OLAP_USER)'
PASSWORD 'D7EDED84BC624A917F5B462A4DCA05CDCE256EEEEEDC97D54E960C7A19ECF4EFE0BDF057F3860BD2C8F3BEE6EAF9FC34'
SHARED LOGIN
ASYNCHRONOUS
CONNECTIONS TO SAME URI 10
OUTPUT TYPE XML
HEADER PATH {D:\\OracleBI\\server\\config\\NQSQueryHeader.xml}
TRAILER PATH {D:\\OracleBI\\server\\config\\NQSQueryTrailer.xml}
ISOLATION LEVEL DIRTY READ
BULK INSERT BUFFER SIZE 32768 TRANSACTION BOUNDARY 10
TEMP TABLE PREFIX {TT} OWNER {}
DESCRIPTION {User name is VALUEOF(OLAP_USER)
Data source name is VALUEOF(OLAP_DSN)}
PRIVILEGES ( READ);
-----------------------------------------------------------------------------

Synchronize Tasks in DAC

2009-04-10T20:31:00.000-07:00

I had this error recently in DAC Client while trying to Synchronize Task.

MESSAGE:::Failed to create folder/workflow/session cache file
EXCEPTION CLASS::: com.siebel.analytics.etl.infa.interaction.PmrepUtilsException

This happens if you have not created/changed INFA_DOMAINS_FILE and PATH variables to point to domains.infa and path to bin directory respectively.

Data truncation with OBIEE Applications 7.9.5 OOB ETLs

2009-01-16T19:30:00.000-08:00

This post is applicable only for OBIEE 7.9.5 applications configured to run with data movement type as UNICODE. After the recent upgrade to 7.9.5 (Informatica 8.1), in development, some ETLs (Bank, GL Account, Employee and User dimensions) started failing with either Unique constraint error or duplicate keys found. After looking at data, it was clear that OBIEE is getting loaded with duplicate values in INTEGRATION_ID column causing duplicate keys found error while creating unique index, which is a composite key on columns - DATASOURCE_NUM_ID, INTEGRATION_ID and EFFECTIVE_FROM_DT.

When I exported the source data and stage table data to a spreadsheet and started ro by row comparison with VLOOKUPs in Excel, it was clear that Informatica while loading stage table, it either truncated a character in INTEGRATION_ID or added some junk characters in some VARCHAR columns. Because the truncated values in INTEGRATION_ID matched already existing values, duplicate keys found error was thrown.

After raising with support and spending some time on it, I figured that it is Default Buffer Block Size setting in Informatica that is causing the issue. Defaukt Buffer Block size is configured as 1280000 in all of the ETL sessions that are failing. I reverted the value to 128000 and the sessions started to run with no truncation and no junk characters.

Informatica support directed me to the Performance tuning guide, which has details on 2 very important session level parameters (Default Buffer Block Size and DTM Buffer Size), confuguring which without understanding the implications can lead to data loss without even realizing the problem. I am putting dowm the explanation in brief.

Informatica Integration Service process starts the Data Transformation Manager (DTM) when you start a session and DTM allocates buffer memory to the session at runtime based on the DTM Buffer Size setting in the session properties. The DTM divides the memory into buffer blocks as configured in the Default Buffer Block Size setting in the session properties. The reader, transformation, and writer threads use buffer blocks to move data from sources to targets. The buffer block size should be larger than the precision for the largest row of data in a source or target.

The Integration Service allocates at least 2 buffer blocks for each source and target partition. Use the following calculation to determine buffer block requirements:
[(total number of sources + total number of targets)* 2] = (session buffer blocks)

Session Buffer Blocks = No. of Partitions * (DTM Buffer Size / Buffer Block Size)

In OOB ETLs, Buffer Block Size is increased to 1280000 leaving DTM Buffer Size as 32000000. Session Buffer Blocks = (32000000/1280000), which is 25 blocks and that seem to be not enough to move data from source to taget, which case it should have failed rather than truncating characters. But, my first test with 128000 for Default Buffer Block size worked, which resulted 250 Memory Blocks and this seem good enough to move data. I was not sure why this fixed but support directed me to right document.

Here is some additional information that may be useful.

You configure buffer memory settings by adjusting the following session parameters:

DTM Buffer Size. The DTM buffer size specifies the amount of buffer memory the Integration Service uses when the DTM processes a session. Configure the DTM buffer size on the Properties tab in the session properties.

Default Buffer Block Size. The buffer block size specifies the amount of buffer memory used to move a block of data from the source to the target. Configure the buffer block size on the Config Object tab in the session properties.

Refer to Power Center Workflow Administration Guide or Performance Tuning Guide for more detailed explanation.

Registering Informatica 8.1.1 Integration Service in DAC

2008-10-31T15:06:00.000-07:00

Registering Informatica Integration service in DAC took a lot of time in the evening the other day. The documentation says that the ServerPort variable in Custom Properties in Informatica Admin Console will be the port on which Informatica Integration service will listen to. Refer to section 4.7.3 in Oracle® Business Intelligence Applications Fusion Edition Installation and Configuration Guide Release 7.9.5 document. But, after creating ServerPort variable with value 4006 as a custom property in Repository properties did not help. lsof -i|grep 4006 on Solaris returned nothing on Solaris, which indicates that the port is not in use. After shutting down and restarting of Informatica services multiple times, I thought I missed something in documentation. I was able to connect to Integration serviec and Repository service with pmrep and pmcmd respectively from the command prompt on the machine where DAC client is running.

After lot of unsucessful attempts, I looked at all of the ports in use by the Solaris user and 6009 stood out to be something of interest. Boom!! Here I go. DAC threw a message "connection successful but passwords not verified" when the port changed to 6009 from 4006. I do not think we ever configured 6009 anywhere during or post install of Informatica 8.1.1 OEM. I am wondering what if we want to create another Informatica integration service on the same box with a different user. I will post the answer as I come out of that situation.

Here is the way to fix the Port issue with Informatica Integration Service

After going through the documentation once more, it is clear that the custom property should be created in Informatica Integration Service properties NOT on the repository properties. I restarted Infaservices today and the port is different now. So, Informatica is assigning different port each time for Integration service. After adding the ServerPort variable with value as 6010 in Informatica Integration service custom properties, Informatica integration service is always running on the static port(6010). Now I am confident that we can run multiple Informatica Integration services on a Solaris machine, if required.

ParameterfileOLTP.txt in DAC

2008-10-30T21:12:00.000-07:00

I had many problems with DAC 7.9.4 not picking right values from parameterOLTP.txt file while generating the individual parameter files at the run time. Read the full post here... Dylan Wan talked about an enhancement then and a colleague of mine reminded me today of testing that while we were planning on modifying parameterfileOLTP.txt as we upgrade from 7.9.4 to 7.9.5. It is pretty cool now as we get to define parameters at Task level for different source system containers and requires no restart of DAC, no assemle of subject areas and no build of execution plans required after adding the parameters at task level, which is really cool. If parameterfileOLTP.txt has same parameter, what you define in DAC takes precedence over the definition in parameterfileOLTP.txt.

I was suspicious of the fact that Informatica expects the parameters to be prefixed with mapplet name in case the parameter is a mapplet parameter. DAC is able to generate the parameter as mapplet.parameter at the run time though we do not specify in DAC that it is a Mapplet parameter. I am thinking DAC queries OPB tables (Informatica Repository tables) to identify whether the parameter is Mapplet or mapping.

7.9.4 DAC in fact had same parameters feature at task level in DAC Client and in fact it is documented that we should use parameters at task level instead of attempting to change parameterfileOLTP.txt. It is clear now that I missed to read this piece of the DAC 7.9.4 documentation, while I configured OOB Order Management. To be fair to myself, applications configuration document mentions to change the parameter values in parameterfileOLTP.txt, which should have at least had a line on configuration steps in DAC. I spent lot of time trying to fix the issue with DAC not passing right values from parameterfileOLTP.txt and I ultimately created my own parameters in 7.9.4. Anyway, not bad to realize now than never.

What to do when oc4jadmin password is forgotten?

2008-10-01T20:59:00.001-07:00

I installed 10.1.3.3.2 long time back on my personal laptop but have not used it in a while. I wanted to install 10.1.3.4 and 7.9.5 to check some stuff. When I tried to uninstall 10.1.3.3.2, installer asks for oc4jadmin password and so many of my attempts failed. I did some research on Oracle documentation and found this resource useful.

This is what you need to do reset the password.

1) Stop OC4J, if it is already running.

2) Open system-jazn-data.xml C:\OracleBI\oc4j_bi\j2ee\home\config\

3) Locate Credentials property. It will appear something like {903}4L50lHJWIFGwLgHXTub7eYK9e0AnWLUH=

4) Change the credentials property value to desired password by prefixing the password with !.
!Admin123

5) Start oc4j and the password will be encrypted in the file.

I could uninstall OBIEE with the new password. I will try to download the software over the next week and have the installation done.

Noetix Generator for Oracle Business Intelligence

2008-08-30T16:09:00.000-07:00

Oracle Apps is not known to for providing a good feasible solution for great demand for operational and transactional reports though OBIEE is the solution targetted for providing both detail level reports and analytical reports. Few people may debate but detail level reports are not something that OOB Applications spent time on and customers have been looking to build a custom solution but building a custom BI solution with Oracle Apps source isn't as easy because of the complexity of the data model, which a BI architect cannot easily design. Noetix Corporation has been operating successfully in this space providing their solutions catering to customers on OBIEE, Discoverer and Business Objects.

I just want to highlight what Noetix offers for OBIEE and also highlight little success that we had with this solution in our user community. Noetix with great understanding of the data model of Oracle Apps generates database views in Oracle Apps database. It generates same database views for all operating units with slightly different names. "Noetix Generator for OBI" software generates UDML script and generates all 3 layers of RPD with Noetix specific security groups for different roles selected. RPD does not have any dimensions or facts as such but users will have to generate reports from one view at a time, which means users will not be able to build reports coming from different views.

As OBIEE Supply Chain Analytics OOB Solution does not have anything at this point to cover the analytics related to manufacturing process, Noetix solution has been looked at and our business users have responded positively so far. I posted this just to let my fellow OBIEE devloper community to know that there is a solution out there in the market. Noetix solutions may not help all the customers but it is definitely worth looking at if the size of Oracle Apps implementation is moderate or large implementation. Also, Noetix has EUL Generator for catering to Discoverer customers.

Help on UI Changes in OBIEE..

2008-07-15T11:32:00.000-07:00

I had few requirements around changing the logo and other things in OBIEE OOB Dashboard. Though I got an unofficial documentation on this from a colleague of mine, I was curious to find something that can help to change and see how it affects the look and feel of the dashboard. The basic requirements can be fulfilled by changing bg_banner.jpg and siebelbug.gif in res/s_Oracle10/b_mozilla10 and in res respectively. Absolute path depends upon the web server used. If it is IIS, you can find the files in OracleBI/web/res/.. and if it is OC4J, the files will be in $ORACLEBI_HOME/oc4j_bi/j2ee/home/applications/analytics/analytics/res/..

I have got Firefox version 3.0 installed on my machine and I got Firebug 1.2 installed as an add-on. Firebug allows to change lot of the properties in CSS files and add additional attributes and see the effect right away. You do not have to upload CSS files to validate the look and feel. Also, it helps to identify the actual CSS file that has the attributes, properties and etc. Try for yourself to learn more. It is really interesting to play with. I will be using this to change my blogs sometime when I find some good time.

Type-II SCD Logic in OBIEE OOB ETLs

2008-07-15T09:39:00.000-07:00

I have been thinking about blogging about evolution of Type-II SCD in OBIEE Applications in different versions of Applications for quite sometime now but haven't had all the time. With previous versions of Analytics applications, our ability was very limited in handling SCD logic. I remember having a requirement in one of my earlier implementations to look at capturing changes as the change occur in source (SIEBEL) system, which means tracking history of all changes that occur even during the day. After much of deliberations, customer thought it is not worth putting all that effort to solve a business problem that did not exist. Let me explain what the business problem we were trying to solve.

In a contact center, team leads and team managers are changed in the source system during the work day very often than anybody can imagine. We had ETLs running at top of the half hour to gather the telephony data at CSR level and most reports roll up the data to team lead and team manager level in SIEBEL Analytics. We proposed to make the position hierarchy dimension a Type-II SCD but when the logic of SCD was explained, IT management raised that the SCD should capture intra-day changes as well. I started to put some effort back then to come up with some custom SCD logic that would accomplish this. But, business dropped that with an argument that the CSRs do not report to more than one Team Lead on any day thouh the changes may be done in the system during the work day.

I have reviewed 7.9.4 ETLs and one of the things that impressed me most is the SCD logic and how it has been implemented. It definitely deserves a post and credits to Engineering team for design and build of this logic in Informatica. I am not going to write all the logic down here but will mention at high level what is happenning in ETLs with respect to TYpe2 SCD. This may be helpful to those folks, who haven't had a chance to look at 7.9.X apps as yet. In the latest OBIEE Apps, ETL_PROC_WID is not a constant anymore. The definition of ETL_PROC_WID column is more explainable now than before. ETL_PROC_WID is different in each ETL run and is incremented by 1 for each ETL run. OBIEE ETLs leveraged this column in handling failure cases. Though there are multiple exceptions handled, when it comes to Type-II SCD logic, OOB ETLs evaluates 2 ports;
1) SYSTEM_COLLS_DIFF checks for any changes in date (Audit) columns in Stage and Target tables and marks it 'Y', if there is a change in values of all the audit columns between satge and target table.
2) TYPE2_COLLS_DIFF checks all the columns for which the history of changes need to be tracked and mark it 'Y', if there is a change in values of all the columns between stage and target table.

Once the above ports are evaluated, UPDATE_FLG is evaluated whether is an update or SCD or insert or nothing to do. Source System Change Date is one column that is compared while the record is inserted with the Effective From Date of the target record while the SCD record in inserted. I had used ETL run date so far for populating SCD Start Date and SCD End Date. Source system change date will help because lot of systems are modified after the fact and they would like to go with either change date or a date that the source system captures as effective from date. OO ETLs have another mapping to update the Effective To Date of the history record for all the records that are modified for Type2.

I am pasting a small graphic that explains the difference in older reeases and new release of OBIEE Applications.

Running DAC Service in Background mode

2008-06-16T18:28:00.000-07:00

I have been planning about running DAC Server in background mode in Solaris but did not work with nohup. I first had to change the way the confog.sh and dac_env.sh are called ("source ./config.sh" to ". config.sh") in order to start DAC Server. When I use "nohup startserver.sh &" at command prompt, it errors out immediately. nohup.out file has the error as - LD_LIBRARY_PATH=......... : is not an identifier. I mentioned in one of my earlier posts that it is one issue that I have to fix as doing Ctl+c on the Solaris session does not release the connections DAC made with the database, causing lot many Inactive sessions on the database. Also, having a windows machine just to run DAC Service on the Solaris command prompt is simply not convincing, acceptable and sounds odd.

I found the other day when I had to restart DAC Server multiple times as I was debugging an issue with parameters not getting correctly populated by DAC during the run-time, I figured that the call to run config.sh in startserver.sh is throwing the "invalid identifier" error. I commented config.sh in startserver.sh and tried "nohup startserver.sh &" after running ". config.sh" in foreground at command prompt, it just ran fine and I exited the Solaris session and logged back in to see if the service is still alive. It is indeed alive!! The conclusion is that EXPORT cannot be run in background mode.

Venkat has blogged about how to create this as a Windows service for a oc4j service and I am pretty sure that the same can be implemented for DAC Server. Visit Venkat's blog post for more details, if your DAC Server is hosted on a Windows machine.

Customization of Time Dimension

2008-05-29T20:25:00.000-07:00

The first and foremost customization that came up was Time (Day) Dimension. In Contact Center Telephony Analytics, Time Dimension always relates to Half Hour Interval or Hour Interval and Fact tables are populated with a key to Time dimension. But, in OM Analytics, OOB calls Day(Period) Dimension as Time dimension in Presentation. It is understandable given that in OM area, Time is actually measured in days. In a business like the one that I am trying help now, Time is really not even days given that some large products take near about 2 years to manufacture and deliver.

Coming back to Time dimension, first thing that was pointed out was that Fiscal Calendar is not Company specific calendar. I am surprised why Oracle has not made an attempt to lookup GL_PERIODS table in Oracle EBS to populate the Day dimension. I have created two Custom mappings that updates Day dimension for Calendar that is defined in Oracle EBS. There are 2 ETLs and the logic is as below.

Mapping 1 - Lookup GL_PERIODS and extract PERIOD_YEAR, PERIOD_NUM, QUARTER_NUM, YEAR_START_DATE, QUARTER_START_DATE, ADD_MONTHS(GL_PERIODS.YEAR_START_DATE, 12)-1 as YEAR_END_DATE, START_DATE as MTH_START_DATE, END_DATE as MTH_END_DATE.
Lookup BOM_CAL_WEEK_START_DATES for Week Number and Week Start date. Week Number is not captured in BOM_CAL_WEEK_START_DATES; so, an expression is created to calculate the week number for the given date.

Mapping 2 - End Dates are calculated and populated for each date in W_DAY_D.

ETLs fail with error - ORA-12537

2008-05-29T19:43:00.000-07:00

I have 10 set in No. of Connections field in DAC Physical Data Sources both for OLTP and OLAP. The error is intermittent and upon restart, ETL completes successfully with no issues. I understood that the connections are somehow exceeding but couldn't guess that DAC is the problem creator. I closed DAC by hitting Ctl+c in Solaris session many a times when I wanted to restart thinking it would release all the connections but to my surprise it just keeps the connections alive. The only way to get the connections released is to run stopserver.sh in Solaris session to stop DAC Server. Stopping DAC gracefully is so very important.

Coming to running DAC Server in background mode in Solaris is still a challenge here. nohup ./startserver.sh & logs an error - ./dac_env.sh: LD_LIBRARY_PATH=/xxx/yyy/oracle/10.2.0/lib:/xxx/yyy/zzz/Informatica/Repositoryserver:/xxx/yyy/zzz/Informatica/Server:/xxx/yyy/zzz/app/oracle/10.2.0/lib32:/xxx/yyy/zzz/Informatica/Server: is not an identifier.

I am still working with our Admin team on this issue but given all the other activities, it isn't in my high priority list. But, we will have to find a solution by the time we move into Production.

Generation of parameter files by DAC at runtime

2008-05-29T19:17:00.001-07:00

I was very curious on how actually DAC reads parameterfileOLTP.txt and generates individual parameter files that are passed runtime as a parameter in pmcmd command. I became more curious after I observed that DAC was not taking right values from parameterfileOLTP.txt file. I changed CATEGORY_SET_ID value in different sections to different values as per the documentation but DAC generated one parameter file correctly but the other one was incorrectly populated. It populated one of the parameter file with CATEGORY_SET_ID=27 where as parameterfileOLTP.txt has no such value anywhere in the file.

I am still not very clear on the logic and DAC still maintains default values somewhere thought the value does not exist in parameterfileOLTP.txt. I wonder where. OOB ParameterfileOLTP.txt has additional parameters though the parameters are not defined in the mapping. When I removed those unwanted ones from the file, DAC is passing the correct values. Let me give an example of what I am talking about.

ParameterfileOLTP.txt in \\OracleBI\DAC\Informatica\parameters\input\ has been modified to have $$CATEGORY_SET_ID=1100000023. The entry is pasted here.

[SDE_ORA_InventoryProductDimension_Derive]
$DBConnection_OLAP=PARAM_OLAP
$DBConnection_OLTP=PARAM_OLTP_ORA11I
$$CATEGORY_SET_ID=1100000023

\\informatica\server\SrcFiles has the parameter file is definitely modified (timestamp changed) but $$CATEGORY_SET_ID value is set to ' 27'. DAC is passing the file in \\informatica\server\SrcFiles\SDE_ORA11510_Adaptor.SDE_ORA_InventoryProductDimension_Full.txt in the pmcmd command.

I changed [SDE_ORA_ProductDimension_Full] section to remove mplt_BC_ORA_ProductDimension.$$CATEGORY_SET_ID as the mapping does not a mapplet and the DAC is passing the value that is there in parameterfileOLTP.txt.

I am still searching for the logic but when I remove the ones that are not required, it does work as expected. I will be glad if someone can explain me the logic.

Multiple instances of OBIEE components in Solaris

2008-04-23T19:29:00.000-07:00

We have been running 7.9.3 applications on 10.1.3.3.1 platform. We raised with Oracle on margin related metrics not being available in 7.9.3 and we got a response that it is enhanced in 7.9.4 apps. We decided to install 7.9.4 applications with 10.1.3.3.2 platform on the same development machine but on a different mount point. With 7.9.3 installation experience, we decided that we will install 7.9.4 and at the same time have 7.9.3 running as long as we want and scrap 7.9.3 sometime when we are happy with 7.9.4. We installed 7.9.4 apps and platform from the new media on a different mount point with the default ports but did not start any services after install. I modified all the different required ports in 7.9.3 and started 7.9.4 services. After some debugging, investigation and researching, we are finally up with 7.9.3 and 7.9.4 on the same machine. I would like to thank Borkur for some initial help and direction.

I thought to put all the changes in a consolidated place. You may ask why is this required to have multiple instances running on same machine. Learning environment is a need but having a Solaris box for learning environment will not sell. Imagine a hypothetical case; Production will have to be upgraded with no downtime. We can upgrade in a different mount point and after validation, the old environment can be dropped. In fact, we already made a decision to not install the Production BI apps on the default ports.

Starting 64 bit OBI Service in Solaris

2008-04-23T17:56:00.000-07:00

I tried to restart OBI service after a long time today.

yyy:/xxxx/OracleBI/setup>./run-sa.sh start64

yyy:/xxxx/xx>tail -f /xxxx/xxx/OracleBI/server/Log/NQServer.log

2008-03-21 10:19:06
[nQSError: 43059] Init block 'LAST_SYND_DS_YTD_QTD': Dynamic refresh of repository scope variables has failed.
ld.so.1: nqsserver: fatal: /pgobiee/app/oracle/10.2.0/lib/libclntsh.so.10.1: wrong ELF class: ELFCLASS64
[nQSError: 46029] Failed to load the DLL /pgobiee/app/obiee/OracleBI/server/Bin/libnqsdbgatewayoci10g.so. Check if 'Oracle OCI 10G' database client is installed.

If you read see, OBI is trying to use the file in lib directory, which is a 64 bit file. I checked the LD_LIBRARY_PATH variable and added an entry for Oracle client lib directory.

yyy:/xxxx/OracleBI/setup>export $LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

I restarted the sevice by issuing

yyy:/xxxx/OracleBI/setup>./run.sa.sh start64
yyy:/xxxx/xx>tail -f /xxxx/xxx/OracleBI/server/Log/NQServer.log
2008-03-21 10:19:06
[nQSError: 43059] Init block 'LAST_SYND_DS_YTD_QTD': Dynamic refresh of repository scope variables has failed.
ld.so.1: nqsserver: fatal: libclntsh.so.10.1: No such file or directory
[nQSError: 46029] Failed to load the DLL /pgobiee/app/obiee/OracleBI/server/Bin64/libnqsdbgatewayoci10g64.so. Check if 'Oracle OCI 10G' database client is installed.

I created a soft link to libclntsh.sl in Oracle\lib32 in Bin64 directory.

yyy:/xxxx/OracleBI/server/Bin64>ln -s $ORACLE_HOME/lib32/libclntsh.so libclntsh.so.10.1

The service starts with no complaints.

I could not find any noticeable differences with 64 bit service running. So, I removed the soft link and started the 32 bit service. Make sure LD_LIBRARY_PATH has $Oracle_HOME/lib32 included.

OBIEE Installation on Solaris 10

2008-03-20T20:17:00.001-07:00

I have not installed anytime all the components in Solaris but was sure that the instructions pretty much help to complete the install. It is pretty much a smooth install but we struggled with few issues and I am putting down the details here for the benefit of others.

One thing that we struggled was to get Informatica up and running.

Issue 1:

When we tried to restore the OOB repository, we got an error - Unable to create Repository. Please check the Activity Log for more detailed messages". But, in activity log, there was absolutely no messages.

When we tried to create the repository from the command line with pmrepagent, we see an error message - "sh pmrepagent: not found".

In pmrepserver.log, below is the error message that we are seeing.
============================================
ERROR : RS_39194 [Thu Mar 06 12:46:58 2008] : (18998|15) Failure in running comm
and-line request type[100405] [pmrepagent restore -r Oracle_BI_DW_Unix -t "Oracl
e" -u pw_center_repos -c test.world -d "UTF-8" -h 1.1.2.3 -l -o 9999 -i "Backup/Oracle_BI_DW_Base.rep" -s 100 -b -j -a Oracle_0BI
_0DW_0Unix-es.lic -H "1.1.2.3" -O 5001 -K 2082340862]. Erro
r is [].
===========================================
Just to add to this, pmrepagent was not starting after starting pmrepserver.

Solution:

Add RepositoryServer directory path to PATH variable and restart pmrepserver. Start the restore from Repository Administration Colsole client.

Issue 2:

After successfully restoring the repository, when we try to connect from Informatica client, we were getting an error - "[REP_12782] The repository Oracle_BI_DW_Base's code page (UTF-8 encoding of Unicode) and workflow Manager's code page (MS Windows Latin 1 (ANSI), superset of Latin1) are incompatible".

Solution :

Set LC_ALL=C in Solaris environment and re-register Informatica Server in Informatica Server Administartion Console. We set LC_ALL=C and tried multiple things but re-registering the repository in repository administration console is the most important step that we tried after a long struggle. We were able to see more code pages in the code-page drop-down of create repository window. We selected "ISO 8859-1 Western European" and created the repository. We now are able to connect to Informatica from client and everything is working just fine now.

Installation of Oracle on Windows XP..

2008-03-17T21:40:00.000-07:00

I was asked to take a brief training session for some folks who have been working on SIEBEL CRM. I thought it would be a good time to install OBI on my laptop and demonstrate what the tool can do. I tried installing Oracle 9i thinking it would utilize less resources but had problems starting listener. My next stop was to try 10g. While I tried to install 10g, it gave me an error that I could understand little bit and fully understood that there is something that I have to do apart from hitting Next, Next to install. Microsoft Loopback adaptor has to be installed in order to install Oracle on Windows XP Professional. I read that even XP Home has to have loopback adaptor but I know for sure XP Professional needs the adaptor installed.

It seems 9i wasn't throwing a readable message but would have just worked with a loopback adaptor. I successfully installed 10g and OBIEE 10.1.3.3.2. I did demonstrate the paint.rpd to the audience and also explained them on how to configure sample fact, dimensions and hierarchies by utilizing the tables that are created by 10g install with DW Option added to the install. It went just fine and I now have OBIEE to try few things at home if office isn't enough.

My Analytics Experience...

2008-03-16T20:49:00.000-07:00

I have worked on 3 fresh implementations (2 major & 1 medium) and maintained 2 medium sized Analytics applications. My first 3 implementations had just SIEBEL CRM as data source and it included Reatil, Consumer Product Goods, Trade Funds and Service Analytics. My last implementation for a Financial services firm was well ahead of its time. We built real-time reporting, deployed ETLs to run every 30 Minutes and dealt with massive data. Read Many and Write Many data warehouses are talked about now but this was designed and delivered a year ago in my previous implementation. RTD systems are the feature of the BI tools today but a custom real-time report that gets refreshed every 3 Seconds was delivered a year back.

Last implementation was more on Contact Center reporting and less on Service Analytics modules. We had 10 data sources to extract data from and ETLs designed to run every 30 Minutes, hourly, daily, weekly, monthly and yearly. So, we covered all the frequencies that one can possibly think of. Some may ask - why will one need to run ETLs every 30 minutes? In a contact center, daily operations are dependent heavily upon the latest data available to the team leaders and managers. The telephony systems are proprietary and cannot be used as data sources in a reporting tool like Analytics. The only option left with is to build ETLs that can run every 30 Minutes. There are lot of things that need to be considered while designing ETLs to run at such short intervals. Aggregates need to be thought about and multiple options are to be explored to reduce the amount of time for aggregating data.

I have been using custom incremental aggregation and has been working great. I join Fact and Fact Stage table to identify the DISTINCT of columns that are used in GROUP BY clause in aggregate query and load a custom change capture table. A PL/SQL is created to delete records in aggregate table by joining aggregate table and change capture table. Aggregate query will be modified to join with the change capture table so that the changes are only aggregated. I call these steps Incremental Aggregation.

OOTB ETLs use a Lookup on target table to determine whether the record needs to be inserted/updated. The Lookup query should be changed to join the fact table with staging table and staging table should have an index on the columns that are used in join. In addition to this, it is also advised to comment the ORDER BY Clause that Informatica generates automatically, if the volume of data is expected to be huge.

These are not the only steps though but something that not many people implement. There are so many other things that most people implement - Index Hints in Source Qualifier query, Lookup query, Analyzing tables and Indexes weekly, avoiding Bit Map indexes etc..

One more thing that I implemented in my last implementation that really helped is a custom ETL runtimes subject area in Analytics. We created a ETL that actually pulls Session, Start Time, End Time, No. of Rows and load the data into a table which is configured as a fact table. We created another dimension table that has Session, Data Source. We had multiple reports built in Analytics to do trending analysis on data volume, run times and concentrate our efforts more on those ETLs that need to be tuned.

E-mail me at changa.reddy@gmail.com if you need any further information on any of the techniques.

Why this blog and why now?

2008-03-15T19:26:00.000-07:00

I have been toying with this idea of creating a blog to record my day-to-day experience somewhere so that this can be referred back sometime not only by me but others who are struggling with something. If this blog can help someone technically or functionally, that would be what this was thought of in the first place. I will try to capture my experiences, tips and tricks not just technical but from functional point of view as well. There is a good history written by Mark in his blog on OBI. I have been working from 2005 on multiple versions of SIEBEL Analytics and now OBIEE.

My related experience in the OBI space so far -
1) SIEBEL Analytics 7.7, 7.8
2) Informatica 5.0, 6.4, 7.1
3) OBIEE Platform 10.1.1.2, 10.1.3.3
4) OBIEE Applicatons 7.9.4

I passed Introduction to Oracle 9i after I was out of my first project that was n Lotus Notes, DomDoc, Domino Workflow and etc.. I chose to join an Analytics implementation thinking that my SQL skills would be a decent value add to the project. In the very initial days, it did help to some extent. I was writing PL/SQLs and tuning Informatica mappings for almost 6 months. I was also formally trained in the mean time on Analytics by a colleague in 4 hours. While I was working on tuning of ETLs, I developed lot of interest in the way the query engine works and security works. I have been working on Analytics implementations for long enough to see myself as someone who can talk end-to-end of SIEBEL Analytics implementation.

I am particularly impressed with the way Oracle has been able to integrate SIEBEL Analytics into the Oracle Product family. It gained lot of momentum in last few months especially after the launch of OBIEE 10. I suddenly see many blogs and Google brings many more pages now for any queries on OBI. It is definitely capturing the market and Oracle has been able to convince its customers that this has potential to meet all their future BI needs. I will make sure to blog more frequently and see to it that this gets ranked by GOOGLE. Let us see how it goes!