Running batch scripts in ODI

Okay, one of the most basic questions in ODI.
How do I convert a package into a batch script so that it can be scheduled using task scheduler, or run it without opening ODI window at all ??

Packages can be converted into a scenario and scenarios can be executed through command prompt using the startscen.bat script. It is located in ODI_HOME\oracledi\agent\bin folder.
Now, if you just type startscen.bat and hit enter in the command prompt, a full description will be displayed (just like the 'man' command in linux) . Read the instructions properly, the simplest one requires three parameters : The Scenario name, Version, Context
So it would look like : C:\......\bin\startscen.bat Scenario_name version_number Context

The only confusion is when you convert the package into a scenario it suffixes it with the version, e.g. Package named 'Trial' will be displayed as 'Trial version 001'.
When you run the startscen.bat make sure you dont add the suffixes, as an example, when you run the Trial version 001 using cmd prompt, your three mandatory parameters would be
startscen.bat Trial 001 Global

You can go to the operator window and check if it has worked successfully.

Good luck !!!

Changing Java home path in ODI

Sometimes it happens that you have to use a different Java location for ODI to work. This was more prominent in the initial releases of ODI 11g where if the system was 64 bit, ODI installer needed a 32 bit Java for the installation to run, then after the installation was complete it would need 64 bit java version to get it working.
The problem has been resolved now and you dont have to worry about Java version.
But incase you want to change the path, you should modify the odi.conf file, its available in the following folder
ODI_HOME\oracledi\client\odi\bin\odi.conf

Check the last line in odi.conf file,
SetJavaHome C:\oracle\Middleware\Oracle_ODI_EE\jrockit

You can specify the new path here ...

Enjoy !!!

No Hyperion Planning LKM in ODI and IKM Planning bug

Ouch !! that hurt

When I tried creating a flat file extract of my Planning application's Account heirarchy, I sort of hit a roadblock. There is no LKM which pulls metadata information of Planning into a staging area.
Unless somebody gives me access to the Planning application's relational database tables and unless I have the courage to meddle with the tables, it seems difficult to get it right. I wanted to automate a process which pulled metadata from Planning application and then populate the same in ASO cube

But how do I proceed now ??
The only solution I could think of was using OutlineLoad utility's extract outline method to extract metadata. But wait, why do I need to use Planning metadata extract at all when I can use its corresponding Essbase cube's metadata?, plus there is a LKM for the same. So as far as practical uses are concerned, Planning LKM, though it would be great to have one, is not necessary.

And the bug :
For once, I was frustrated to find that IKM planning is not getting reflected in the interface I was creating.
Thanks to my colleague, he said the IKM is 'bugged'.
The procedure to correct it, he told me is, as follows:

1.Open the Knowledge Module in Edit mode
2. On the 'Source Technology', which says 'Generic SQL', change it to 'Undefined' (dont worry, it is fine)
3. Open the interface again, you will see what you want to see ;)

Just as I was typing this, I thought of searching it on John Goodwin's blog and I got it as expected
http://john-goodwin.blogspot.in/2012/08/odi-11116-planning-km-bug-fix.html

Looks like there is a opatch !!!

Reversing XML file in ODI

"XML files can be used as tables, yes, that is an old story.
But have we tried doing that in ODI ? no, not until now"

Probably the best use of xml files are that they can be used to store relational table information. I tried reversing xml file which is basically a table in ODI and thus this blog post was born.

Tables in xml format have two files associated with them

• The data contained in the table is stored in the .xml file
• The metadata or column information of that table is contained in another file with the extension '.xsd' or '.dtd'

More information about how .xsd and .xml are created can be found here.

http://www.w3schools.com/schema/schema_example.asp
I will be using a simple xml file which is basically stolen from the internet, here are the contents of the file (note that it is only half of the picture that I have posted, you will have to complete the elements 'book' and 'catalog')

What this file above tells is that there is some table which contains information about books and we need to represent the same in ODI. Lets proceed.

Step 1:

Creating Data Server and Physical schema:

When specifying the JDBC url, we need to mention three things which are required for a successful reverse.
1. xml file name and location
2. dtd or xsd file name and location
3. Schema which will represent the table

In the pciture above I havent mentioned Schema yet (I will come to that later). And where did I get the book.dtd file from ? Well, ODI automatically creates a dtd file (if not specified) when you reverse the model.
All you have to do is specify only the f=../**.xml parameter initially, then create a model in designer and reverse it, this will only generate the dtd file and wont give the tables. You have to come back to topology manager and specify the details of dtd with the d=../**.dtd file

The Schema:
As ODI always does things in a proper DBMS way, it always needs a schema , that is the place holder of all tables, to be specified. When creating a Physical schema after this step, You will see that ODI has automatically created a schema as BOOK based on the DataServer name 'BOOK_TRIAL'

Now, go back to the DataServer details and enter the schema parameter like this

Test the connection after saving, if all goes well, its time to switch to the designer window

Step 2:

Creating Model :

Just like we create a model for any other source, create a model ... when reversing, use Standard reverse and and go to Selective Reverse option and check 'Objects to reverse engineer', it will give you the details of tables available

Check the desired tables and click reverse engineer button, you will soon see the two tables created under that Model

Voila !!! as you can see the contents of BOOK table are those that I showed in the xml file.
Best of luck with your integration !!!

Next, I am going to try converting Relational tables to xml using ODI. Will keep everyone posted

Get used to Calc Manager !!!

While working on preparing a training plan of Hyperion Planning 11.1.2.2, the very first question came to my mind "I don't know how to use the calc manager yet, how do I write rules in Planning now ??"

Since Business Rule component has been dumped completely by EPM system, people will now have to use Calc Manager only, even in Classic Planning Applications.
The most hilarious workaround people use is - build the rule in EAS then copy the script and paste it in Calc manager window. Though it works all the time, having tried a hand at the graphical interface made things even simple !!!

Getting my first tutorial on using the graphical interface from this link

http://ranzal.wordpress.com/2010/06/11/oracle-hyperion-calc-manager-%E2%80%93-part-1/

try it ... its very simple !

Direct Data Load - using outlineload utility to load data

Although I am not sure if this was available in Planning 11.1.2.1, but Hyperion Planning 11.1.2.2's outlineload utility does come with a functionality of loading data (You read it right !!)

There are two options Planning provides :
1. Either use the .csv data file to load the data using Outlineload.sh, this means that data will be loaded immediately and Administrators will have to ensure that Planning and Essbase metadata are in sync (refresh has been done)
2. Use the .csv file along with the utility which will create a .txt file of the same and a .rul (rule) file which can be loaded using the Essbase administration console. This means that you can create a data file when you have it ready and load it when the database has been refreshed at a fixed interval.

Needless to say, you can automate the whole damn thing !!!

Some basic points you should remember when using the data load utility

DIRECT_DATA_LOAD : This is the parameter you can either set to TRUE or FALSE in the System Properties of the application.
if TRUE :  .csv file is loaded directly, see option 1 written above
if FALSE : .csv file is converted to a text file and a corresponding rule file is created, the path where these two files are created is specified in the DATA_LOAD_FILE_PATH system property

DATA_LOAD_FILE_PATH  : This is the file location you need to specify if you choose set  DIRECT_DATA_LOAD to false


The csv file format : Imagine pushing data in Essbase using Excel addin having a Rows of a Dimension, Columns of another dimension and Point of view dimensions above it, the header of csv would be

DimensionInRows,MembersInColumnsSeparatedByComma,Point-of-View,Data Load Cube Name

Account,Jan,Point-of-View,Data Load Cube Name
acct1,12,”Local,ent1,Current,Ver1,FY08”,Plan1

or you can specify the Operation and data load cube name by putting it in between the first two column header

DimensionInRows,Operation,Data Load Cube Name, MemberNamesInColumns,Point-of-View

Entity,Operation,Data Load Cube Name,aUnspec,aSmart,aDate,aText,Point-of-View
e1, ,Plan1,77,smart1,12-22-2008,textValue,"USD,Jan,Current, BUVersion_1, FY07"

If you dont want the 'row' column at all, like a simple 1 dimensional table where you have 'value' column you will have to specify the /TR option in the OutlineLoad command, the Header would then be

Value,Driver Member,Point-of-View,Data Load Cube Name

Value,Driver Member,Point-of-View,Data Load Cube Name
14,a1,"Jan,Local,e1,Current,Version1,FY08",Plan1
sl1_value2,a2,"Jan,Local,e1,Current,Version1,FY08",Plan1 

and the command would be 

OutlineLoad /A:acpt1 /U:admin /M /I:c:\outline1data.csv /TR /L:c:/OutlineLogs/outlineLoad.log /X:c:/OutlineLogs/outlineLoad.exc 

 Note how PoV members are enclosed in double quotes and use exact same syntax when creating the file

How to do ??
For policy reasons, can't put the screenshots :(
Will see if I can do it locally on another machine.

For now I will paste the reference link
Search 'Loading Data' in it
http://docs.oracle.com/cd/E17236_01/epm.1112/hp_admin_11122/frameset.htm?launch.html.

Cheers !!

EPM 11.1.2.2 Installation : Hands on experiences

For Planning and Essbase learners, first things first, dont expect it run good on a 32 bit, 4 GB RAM laptop. Essbase Server and EAS will work just fine. Beginners who want to make their hands dirty with Planning on their basic laptops might want to install the good old 11.1.1.X.

While things have improved drastically on installation front, the new EPM system demands some good configuration server (as always). A basic installation including EPMA, CALC Manager, Planning, Essbase Server and EAS consumes around 5 gigs of RAM that too when no user except the admin is logged in.
This might tempt people to go for a 8 gb RAM, 64 bit dual core machine just to try it out. The best part is installation works smoothly but start creating a classic application on that and you will come to know your investment has gone wrong yet again. It works, but its slow.
Read the documentation carefully and just incase you want to learn Planning, I suggest you have two machines, one which hosts Essbase Server and EAS and the other hosting Planning.

Now, you will find most of those new features of EPM 11.1.2.2 all over the net, I will put down a few things I noticed while installing

- Unzipping the first 7 folders will suffice for basic installation of EPMA, CALC Manager, Planning, Essbase Server and EAS, but wont have OHS.
- No 'Install by tier' option, have to be clever enough to understand installation components now :)
- EPM instance name need not be different on different machines, use the same one
- If OHS is not used, you can deploy everything using the inbuilt Weblogic server on port 9000 and have one service for everything, real PITA for stopping individual services
- If IIS is used, it deploys everything on port 80 by default
- Import_export folder location specified on runtime, can be a shared folder on remote machine too
- Admin name can be changed, so it wont conflict with your company's active directory


Some important things
IIS component is needed for EPMA and make sure have it enabled and running or installation wont proceed.
How to do it ?
Go to Control Panel->Programs and Features -> Turn Windows features On or Off (on the left panel)
a window will open, in that search for 'Internet Information Services' and check it. You might want to check 'Microsoft .NET Framework' too just in case. This will start IIS, check if it is running by searching IIS in Start button -> search for programs and files, if it working you will be able to see IIS console and Website folder in it

The most important thing is to run the installer and configurator as Administrator, I had a bad experience where I missed it and got an error 'Service failed to deploy' while configuration, result ? uninstalling it failed, had to format the drive again.

To summarize, the installation and configuration is quite smooth now, and its easy to understand for novice too. The Web 2.0 thing is good to use in Workspace and Planning, the buttons and 'dockability' of some components are cool. The scrollable things scroll smoothly and dont feel as if there is some rust in the scroll bar. The overall feel is easy on the eyes, great going Oracle !!


Inside Story Though not confirmed yet, the configuration will undergo minor changes in upcoming 11.1.2.2 patch where you wont have to run the configurator again and again for seperate databases for each components. Like you select Foundation, Planning and Essbase while configuring and it will prompt you for new database schema for each component. Lets wait and watch !!   Sorry I am too lazy to post some snapshots here, why dont you try it out yourself ?? ;)   Cheers !!

The Nerdy stuff by Craig Stewart : Duplicating Model Structures of different technologies

Something interesting I found on one of those cool blogs of ODI, its goes deeper to explain some unexplored buttons and features of ODI11g.

What if you have reversed a model of MySQL with lots of tables and now you have a Oracle Schema in which you want the exact same model structure of MySQL to be replicated in it ?
Read how Craig Stewart does it here

http://odiexperts.com/using-one-of-odis-lesser-known-secrets-the-common-format-designer-cfd-by-craig-stewart/

Worked for me, and thats a cool thing to tell your clients just in case they want to migrate to a new database, aint it ?

Craig has done a brilliant job by recording his work and posting it on the net. Here is the link where you can get yourself some good videos on ODI
http://www.business-intelligence-quotient.com/?p=933

Cheers !!

Publishing Planning Application modifications using ODI (Just an idea)

Planning dimensions are modified every time during the development phase, and when multiple developers are working on the same Planning cube, nobody knows who is adding or deleting what. So one fine day you open up the Financial Report you had created just to find out that it doesnt work because some member that existed before is not available now.

You can use HSP_AUDIT_RECORDS table of your planning application to find out who-did-it !
Setting Audit trails in Hyperion Planning is an age old method of doing this. Read on here  http://docs.oracle.com/cd/E17236_01/epm.1112/hp_admin_11122/frameset.htm?launch.html
(search HSP_AUDIT_RECORDS)

However, whats difficult is presenting this information to users who are not given details of underlying database schema. So ODI can be used beyond this point, where you can print the records to a flat file, journalize the table for tracking additions and firing mails containing this information the moment someone modifies the Planning app.

That was one of the many things you can do with ODI and Planning tables. I will be posting more such things as I get time.

Weblogic and HTTP servers for beginners

One thing that has always been a source of confusion for all beginners is the Weblogic server. I will be coming to that later, before that I will tell you the purpose of this post. Its about the new feature of ODI 11g which can use Weblogic Server for running ODI agent. A comprehensive doc for setting up this is available here
http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/odi/odi_11g/setup_jee_agent/setup_jee_agent.htm
I will post my experiences of setting up the ODI agent later since I am still not through it.

So what is Application Server and HTTP server?
For beginners, suppose you want to buy a camera on ebay, you open the website's page on your machine (client), from where does the information that you see - about your desirable camera on your screen - come from ?
How does it calculate the monthly installments for you ? Does it use your machine's calculator app for doing that ? Definitely not.

There is a application server ( usually the big machine residing far away from your home) which accepts your (client) requests and then processes the desired results and sends it over to your machine. To standardize/streamline/secure this communication, there is a middle-man who is called the http server (HS). So your requests will be given to a http server, http server will forward it to the appropriate application server (AS) and AS after processing the request will respond to the HS with a 'reponse', this response will appear on your (client) machine as the end result, in our case the image of your desired camera and the calculated installment !

Though this is a very simple example I have given, over the period of time, the roles and responsibilities of Application Servers and HTTP servers have increased, but the core functionality remains same. For more details, you can refer to the web :)

Now tell me more about Weblogic ...
Its basically an application which runs usually on big machines which will do the server-side processes. So Weblogic Server is an Application server. What is my application ?? It can be anything that you want to host on this machine. A simple webpage or a complex site like ebay or facebook.
In our case the Application is going to be Oracle Data Integrator's component called 'Agent'.

Weblogic was originally called BEA Weblogic but when Oracle acquired BEA it became Oracle Weblogic.
Weblogic's direct competitors are Apache and Microsoft IIS. Weblogic is lightweight compared to Apache meaning it wont eat much RAM or disk space of the server machine.
Oracle Weblogic server is usually called wls or ows in AS jargon.

And what is Oracle HTTP server ?
Like I said above, it is the middle-man which will direct client requests to the Weblogic AS and respond to clients with response from WLS. It is lovingly called OHS.

Things get real messy when you dig deeper into WLS and OHS, but once you understand the way it works, its quite easy to conceptualize things.
To really get a hang of these two things, you should try installing and configuring Oracle EPM system all by yourself, face the difficulties starting and stopping services, read the log files and Oracle forums. Some things are better learnt the hard way :)

How do I install Agent using Weblogic ?
Coming back to the purpose of this post, the 'newest' and the most irritating way of installing an agent is through Weblogic. Its also called the Java EE agent. For installing this agent, you need to install all the features that come with the setup (check Java EE ) and make sure that Weblogic server is installed on the same machine. The path where you will be installing ODI must incorporate Weblogic installation path.
Find out how it is done here
http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/odi/odi_11g/setup_jee_agent/setup_jee_agent.htm
and if you are lucky, you might get the agent installed in the first shot. So best of luck !!!

How to add a new JDBC driver in ODI 11g

What is JDBC driver anyway ?
When you want to make connections between any two machines where one is acting as a database and other may or may not be a database but just a simple computer, its done by specifying connection URLs. A Driver is just code that will enable connections with different databases.
The language in which application's code is written will decide which database connection driver you will be using. Read more on JDBC drivers here http://en.wikipedia.org/wiki/JDBC_driver
Now, coming to the JDBC drivers, look at the example of a driver below
com.microsoft.sqlserver.jdbc.SQLServerDriver

The orange part tells us which technology you will be using for the connectivity, here it is the Microsoft SQL server. It means there is some code at the db side which will help bridge the connection with application.
The yellow part tells us what coding language is used to establish the connection at the application side, jdbc here means java database connectivity. The next part SQLServerDriver tells us the database with the exact driver name.

Another example of SQL driver is
weblogic.jdbc.sqlserver.SQLServerDriver
Here it states that the connectivity to MS SQL with application (ODI which uses Java) will be established through Weblogic Application Server (Sometimes it is called the middle-layer)
[ It might sound geeky for beginners, so dont ponder much on this]

Since ODI is coded in Java, it will use JDBC to connect with other databases. These driver files (codes) are stored in a particular location where ODI will look up.
So tomorrow if you come up with your own database and want to connect it to ODI, where would you put the driver ?

The location is
C:\Users\username\AppData\Roaming\odi\oracledi\userlib

And one more thing, how does odi know that there are some additional drivers (code/jars) you need to refer to ?
In the same path above, there is a file named additional_path.txt,  you specify the full path and the driver file name, see the contents I put after adding a sqljdbc4.jar driver file in that folder above.
Though sometimes ODI is clever enough to get the drivers without updating the above file.

; Additional paths file

; You can add here paths to additional libraries

; Examples:

; C:\ java\libs\myjar.jar

; C:\ java\libs\myzip.zip

; C:\java\libs\*.jar will add all jars contained in the C:\java\libs\ directory

; C:\java\libs\**\*.jar will add all jars contained in the C:\java\libs\ directory and subdirectories


C:\Users\username\AppData\Roaming\odi\oracledi\userlib\*.jar


And dont forget to restart ODI (Close ODI studio and open again) if you add your driver.
Note : For Experts
The location just doesnt specify putting Driver files, you can also put some of your own java classes if you want at this location. You can put your groovy scripts and external jars that might be used if you are going to customize anything in ODI.

ETL and E-LT ? Please Help !!

If you are going to face an interview for a job related to Data Integration, get  ready for this question. People are going to ask this question even if they themselves don't know the answers. :D
Data integration primarily involves pulling data, doing some cleanup activities and then putting it somewhere.
In DI jargon the three steps mentioned above are Extraction, Transformation and Loading. So thats ETL for you. You can read a lot about this on Wikipedia.

The question to be asked is : Where you do all the clean-up activities or where does this 'Transformation' take place ?
Once you have pulled the data from source, there has to be a temporary area to stage the information right ?
As an example,You can't change the format of the date column in your source directly because then you wont be left with the original data. It always makes sense to do all the transformations on a 'copy' of source and fire all the sql queries on that 'copy' before we put it in the target.
Now, where is this 'copy' or temporary 'staging' is created defines the approach : ETL or E-LT

The Neanderthal approach to this staging area where most DI tools didnt work well was to have their own proprietary Staging Area which would have huge storing capacity, high processing speed and could handle millions of rows of data efficiently. But the catch was, you would have to learn the syntax (query) of pulling data from  Source and putting it in the staging, learn the syntax of transforming the data in staging and then learn the syntax of putting the transformed data into your target. And with so many database technologies emerging with minor tweaks in the syntax, it became difficult to learn all things together. Besides that, these proprietary Staging areas were costly and didnt quite live up to the expectations as far as Bulk Loading capabilities were concerned.

So the geeks at Sunopsis thought 'Lets get rid of this staging area and use something else that is cost effective and efficient'. Then they thought, 'What if we use the source database/schema or target database/schema itself for transformation ?' example, I have a table called EMPLOYEE and then I create a temporary table called T$EMPLOYEE in the same schema on which I will do the transformation and load the data into target database, I can save millions of dollars by not buying a proprietary Staging area.
So, using this idea, they created this new data integrator called Sunopsis data Integrator and marketed it with the word 'E-LT' claiming that it didn't use a proprietary staging schema (I will come to that later). Sunopsis DI allowed the users to select the staging area whether it should be Source db or Target db. Another major factor that seperated this tool from others was its declarative approach using graphical UI (More on that later). This was a major hit and Oracle saw this as an opportunity of growth. Result, Sunopsis was bought by Oracle and soon it became Oracle Data Integrator.

Can the Staging Area be completely eliminated ?
The answer to this is No, though Oracle/Sunopsis Data Integrator claims to use E-LT approach there are some bitter times when you have to use a separate Staging area when both your source as well as target dont have the ability to perform SQL activities. Example: If you want to read data from a flat (text) file and put it in a text file, you will have to use a staging schema which can do sql. Sunopsis Memory engine or the new In-Memory engine is their own staging technology and can be used in such cases, along with other technologies. This basically is your JVM in action and it is known that this staging area is not able to handle large volumes of data. Personally this is the last staging area technology I would use for my integration projects.

I need a raise - Upgrading from ODI version 10 to 11g

This is one question almost every one who have worked on version 10 have an answer until they try doing it and come to know its not working.
Migrating ODI from version 10 to 11 is not as simple as zipping the repositories using 'Export' command and importing it in a newer repository, Even exporting individual objects in XML format is not going to work.

Basically you need to modify the tables in the underlying schema of Master and Work repository so that they are version 11 'compliant' and how do you do that ??
ua.bat or upgrade assistant will help you in doing this. To know how it works, odiexperts have put it nicely
Here is the link
http://odiexperts.com/11g-oracle-data-integrator-upgrading-from-10g-part-411g/
And you will find  ua.bat file here
C:\oracle\product\11.1.1\Oracle_ODI_1\bin (ODI_HOME/bin)
One point to note here is that the agents that were created in version 10 might not work right after you upgrade the repositories and import them, there are some things that you will need to do differently.
Check the other posts related to ODI agent to find what can be done.
I would say that this post is rather obsolete considering nobody uses ODI 10 now, For those who start with version 11g might want to ignore this topic.

Best of luck

Welcoming the new Agent with OPMN

Having seen how to create agent service on windows it must be clear that why Oracle has done away with this idea of Windows service using wrapper classes, the most obvious reason is to coax developers to use OPMN which stands for Oracle Process Management and Notification. You can find a lot of documentation  on this, one link is here http://docs.oracle.com/cd/B13597_05/core.904/b12057/intro.htm.
But to put it in layman's language, it is the Oracle's very own task/process manager just like windows Services (services.msc).
The very first advantage of this technique is that it eliminates the need to have expertise in creating services on different kinds of OS (Windows, Linux etc) like the one we saw in the previous post.
Besides that, OPMN is good and reliable.

To create an agent service using OPMN, you can refer to Oracle's very own yet so incomplete documentation here
http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/odi/odi_11g/odi_opmn/odi_opmn.htm
or you can refer to John Goodwin's blog where he has put it in a very fine way
http://john-goodwin.blogspot.in/2010/12/managing-odi-11g-standalone-agents.html

What can go wrong while configuring ?
If you follow the steps mentioned in John's blog as they are mentioned, everything is bound to work properly. I faced some issues starting it in the first shot, I am listing a few ...

- Please dont miss changing \ to / in agentcreate.properties file, I bet most of us will miss it for the first time

- After getting those java errors while adding the agent, I searched if the environment variables are getting set when running the script odi_opmn_addagent.bat, but for some reason they weren't getting updated so I had to add them manually using Windows->Control Panel->System and Security->System->Advanced system settings->Environment variables. However, I feel this problem is very much specific to my machine.

- The agent service will install, start and will show status as alive when using 'opmnctl status' command, but when you test it in Studio, the test fails. To debug or to find the status of the agent, you need to check the log files in the location
C:\oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\OPMN\opmn and my log file's name was
console~ODIAGENT1~odiagent~1.log.

If you are trying it out for the first time, it is advisable to have only one Work Repository associated to a Master since I had three repositories (WORK, WORKREP99 and WORK2 ) and it added to the confusion of specifying its name everywhere.

I got this error when checking the logs (test failed but opmn said the service is alive !!)

SEVERE: Agent ODIAGENT1 encountered an error: ODI-1405: Agent ODIAGENT1 start failure: the agent is not defined in the topology for master repository.
ODI-1405: Agent ODIAGENT1 start failure: the agent is not defined in the topology for master repository.
I ran the odiparams.bat again and tried my luck, this time it started but when I checked the log it had connected to another work repository named WORKREP99 whereas I had specified WORK in odiparams.bat and all other files

INFO: Scheduler started for work repository WORKREP99 on Agent ODIAGENT1.
Obviously logging into ODI using this WORKREP99 worked and the test was successful,
I will be digging deeper to find out whats going wrong and why is it taking another work repository.

Bringing back the good old Agent in ODI 11g

Most of them who have worked with ODI 10.x will know how good the agent was. Creating, starting, stopping and converting it into service was very easy in older versions.
With ODI using Weblogic to create agent services in the new version, things have become tricky.

If you have skipped master repository creation step while installing, the agent name that you give while installing ODI never shows up automatically in the 'Physical Architecture' tab in Topology, though you can see a batch file created with name 'agent_AGENT_NAME.bat' in the 'C:\oracle\product\11.1.1\Oracle_ODI_1\oracledi\agent\bin' folder and running this batch file directly will give you nothing but an error. You have to modify and run the odiparams.bat file before running the agent file.
Modifying the odiparams.bat file : File is located in the path mentioned above. Search for the below lines


REM #
REM # Repository Connection Information
REM #
set ODI_MASTER_DRIVER=oracle.jdbc.OracleDriver
set ODI_MASTER_URL=jdbc:oracle:thin:@localhost:1521:XE
set ODI_MASTER_USER=MASTER
set ODI_MASTER_ENCODED_PASS=a7yXZz0C1gPf2GxKzUNPxEJMp


REM #
REM # User credentials for agent startup program
REM #
set ODI_SUPERVISOR=SUPERVISOR
set ODI_SUPERVISOR_ENCODED_PASS=a7yabVOzj7C5oY503v.Ei6xKy
REM #
REM # User credentials for ODI tools
REM #
set ODI_USER=%ODI_SUPERVISOR%
set ODI_ENCODED_PASS=%ODI_SUPERVISOR_ENCODED_PASS%


REM #
REM # Work Repository Name
REM #
set ODI_SECU_WORK_REP=WORK
The text highlighted in yellow needs to be changed as per your Master Rep and Work rep details (just like the good old days)

This time you wont find the 'agent encode' command working, use the 'encode.bat' file to encode the passwords eg. encode The_password
Add the agent using Topology manager and relate it to a context.
This will start the agent successfully. But the moment you close the command prompt window or try to exit, it will terminate the batch script and your agent will fail.

To create a service of ODI agent in the newer versions refer to the following link

https://blogs.oracle.com/dataintegration/entry/running_odi_11gr1_standalone_a

I tried this on my laptop and it worked till the time I didn't shut down my machine. Since then the service has not started once, though I feel redoing the steps mentioned above will solve the problem. I wont be doing that since its not necessary when we can use OPMN to create agent services. Read the next blog post to find out ...

Essbase Dataload using ODI - will my dataload ever finish ??

In older versions of ODI (10.x) while loading data into Essbase, you might find that it takes eternity to load data however big or small it might be in terms of volume. This issue as Oracle claims has been resolved in version 11 but I am not sure and will give it a try when I get an opportunity.

Row by Row insertion : ODI's older version uses an outdated KM for loading data into Essbase, It will load data in bulk from staging area into target until it encounters an erroneous record (You are trying to insert a record which doesnt have a proper member in Essbase associated or data is not numeric). After that it will take each row, validate it and insert in the cube. If you have selected to ignore errors in records while loading  data, you might want to wait till doomsday to finish the dataload.
The best way out is to not ignore erroneous records and cancel the dataload on first error record itself, moreover, it doesn't make sense to ignore records with errors.

Though it remains to be validated, I personally feel that dataload speeds in ODI version 11 have improved as compared to version 10. The reason was that we had migrated from  ODI 10 to 11 while keeping the source, staging and target untouched.

Why is it necessary to start all processes as Administrator ?

If you have tried starting EPM services by double-clicking on the batch scripts in Windows (start.bat, startEssbase.bat etc), you will probably get no error and the black screen will disappear as if nothing happened.
Running the script using a command prompt will give you the same result, it will come out the batch job peacefully, but when you actually check the services if they have started you will be surprised to see that nothing has worked as planned.
The best approach is start the services as 'Run as administrator' or run the cmd prompt in administrator mode.
The installation needs to be done by an administrator or 'Run as administrator' to get the best results.

What about Linux ??
Oracle says that you need to create a new user and it is not recommended to perform installation or start any services by super user. So never sudo...

I have come across this issue once when the services were being started by a super user, but the user who installed it was different, they failed to start.
Going through the logs with help of Oracle revealed that some file attributes (Ownership) are changed on the fly every time services are stopped/started, so this will result in some files not being opened by the service trying to open it.
Solution : If the services were started by some other user and now you need to restore the file access, change the ownership using 'chown' command (recursively) of the 'Oracle' folder

Data Integration ?

Data Integration, simply put, is a way of pulling data from somewhere and putting it somewhere else. I must warn you that its easier said than done.

The most common uses of Data Integration are
1. Building a Central Data Repository : Suppose you have lots of databases spread across various geographies and across different platforms and the business needs to have a central database, which will contain all the data of all geographies in a uniform way.
2. Populating Databases used by Decision Support systems from Transaction Systems : You need to build a data warehouse which will contain information brought in from Transaction systems and then it will be used by BI tools for reporting and analysis
3. Metadata management : Updating Columns, adding new members in a multidimensional database etc can also come under data integration, although there are some disagreements on this.

There are many other cases where data integration tools can be used, the ones posted above are those which you will face most of the times.

Practical Issues of Data Integration
You can find various links regarding this on the net, I will be putting them here again in my words.

Format of Data : The date column in one table might be different from the date column in other. So while integrating both tables (Union) we need to make sure that the date format is uniform in target database.
formatting is perhaps the most common issue faced in integration. It can be as simple as date format or as complex as using particular rules to be written in SQL.

 Mapping : A source data value say INDIA needs to be put as REGION10 in target, Source value US = REGION4 in target, AUSTRALIA=REGION3 and like wise. This will involve using look up tables which will map the source value to a particular target value.

Data Integration time, size and method : The time taken for integration, method of loads (row by row or bulk loads) and the size of data to be loaded will decide the effectiveness of your integration tool.

Scheduling : Data Integration process need to be scheduled properly so that there are no inconsistencies. eg. If you start a data integration process while the source transaction systems are still being updated, the target may or may not have the correct data.

Consistency : The data needs to be consistent across source and target at all times. Changed data capture is a very important aspect in the field of Data Integration.

Why again ??

I remember my first day in the corporate world like it happened yesterday. Though I had studied about Business Intelligence and Software Development in my college years, I was totally confused after watching everything I studied happening right in front of my eyes. The meetings, the discussions, the mails all full of corporate jargon. So two years after all this, I thought - There has to be an easier way to express everything, right ??

Having conducted a few training sessions myself, the toughest task I have found is to explain the basics to freshers. And those concepts have to be explained in simplest possible ways.

My posts will be about simplifying stuff related to Business Intelligence and how to use a dreaded thing called Oracle Data Integrator, its going to take a lot of time I know, but its just a small start of a big journey ...