Up until recently, our office installation of the Oracle BI Applications has been based around the 7.9.4 release, and as 7.9.5 is now out and comes with support for Peoplesoft HR 8.9 and 9.0, Siebel CRM 8.1 and Informatica 8.1.1, I thought I’d give this new release a try. Our existing setup was also running on Oracle Linux, but as we effectively need our Oracle BI environments to be running on Windows so that we can run them in a single VM on a Mac, I thought I’d try installing all the various products on Windows this time.
The virtual machine I set up to hold all the software was built using VMWare Fusion 1.1.3 and I created the VM using Windows XP Service Pack 2. As the iMac I’m hosting the VM on has got 4GB of RAM I allocated 2GB to the virtual machine, altered the name of the VM host from the strange name VMWare gives it to “biappsvm”, installed the Microsoft Loopback Adapter, set the virtual memory settings to operating system managed and then starting installing the software.
Before installing anything else, I first installed the Java JDK 1.5, making sure I installed it into “C:\Java\jdk1.5.0_15″ rather than the default “C:\Program Files\Java\jdk1.5.0_15″ which later on breaks the DAC installation, which tries to call Java from a batch file but would otherwise fail because of spaces in the application path (first top tip there). Once I installed Java I then installed an Oracle database (10gR2, in case there were any surprises with 11g compatibility) followed by Oracle BI Suite Enterprise Edition 10.1.3.3.3, the latest release.
At this point I had an Oracle database and the base Oracle BI software installed. Now normally you’d have a copy of Oracle E-Business Suite (11.5.8, 11.5.9, 11.5.10 or R12) handy to use as your data source, but in my case I had a subset of the 11.5.10 vision database with enough data to support Order Management analytics which I then imported into my Oracle database, using the transportable tablespace feature, so that I had some source data to play around with. After getting the data set up, I was ready to install the Oracle BI Applications.
Installing the Oracle BI Apps 7.9.5 is fairly straightforward, like all the ex-Siebel Analytics products, with the software being installed (in a simple, one-box architecture) into the same directories as your OBIEE software, like this:
Once you select the install directories you can then pick the products you want to install.
Now one thing I did find a bit confusing was matching all these product options to (a) this list of nine products that 7.9.5 appears to comprise of as listed on OTN and (b) the list of subject areas that I then could select from when working with the BI Apps admin tool (the DAC, or “Data Warehouse Administration” tool); however I then came across this spreadsheet that’s also hosted on OTN that matches the product options you get with the installer, the new packaging that’s being used for 7.9.5 (which is now officially called “Oracle BI Applications Fusion Edition (7.9.5)) which seems to simplify the product bundles, the subject areas in the DAC and the corresponding ETL execution plans which at least gives you a way to work out precisely what you’ve licensed.
Once the BI Applications are installed, you need to create a database account to hold the DAC metadata and the Informatica repository, and another one to hold the data warehouse that the BI Applications create and then populate. It seems to me that at least as this stage, you just create the data warehouse account using the normal connect, resource and create view priviledges, any tuning in terms of materialized views and so on is something you do once the initial set of data is loaded (although the load routine does seem to know about bitmap indexes, for example). Once all of the relevant database accounts are created you can install the next stage of the software, the Informatica ETL server.
Of course Informatica being part of the BI Applications is richly ironic as Oracle have spent the last five years or so rubbishing Informatica, and all row/hub-based ETL tools in favour of their own ELT-based data integration tools, OWB and now ODI. For us though being introduced to Informatica is quite an interesting prospect as (a) most of the banks and financial institutions in London seem to use Informatica for their integration, hence it’s useful for your CV and to broaden our skillset, and (b) we’re just curious what the competition have been getting up to. Informatica itself comes in Client and Server components, the server element gets installed first which sets up a “domain” first, a sort of “super-server” that co-ordinates, like the Dalek Supreme, all the other Informatica servers:
Then you create the initial repository – one thing to note here is that I initially followed the defaults and created a repository called “PowerCenter”, however the BI Apps installation guide assumes that you name it “Oracle_BI_DW_Base” which is then the repository name it connects to from the DAC and is the name of the backed-up repository, containing all the BI Apps ETL mappings that you restore later on. When you create this repository then, name it “Oracle_BI_DW_Base” rather than the default that the installer uses and your life will be a lot easier later on.
After you’ve created the repository you then create the Integration Service (I think this roughly equates to the Control Center in OWB terms) and then apply a service pack to the whole installation. One tip I’ve got here is to test at all times that the Informatica service comes up (and stays up) after each stage of the installation – I found that in one instance, the service came up and stayed up before the service pack was applied, but then always failed after the service pack was installed and I tried to access the web interface. I reinstalled all of the Informatica software again and next time it worked, but this is something I’d check actually works after you do the install before you move on to the next stage of the install and then find doesn’t work. I also found that the Informatica service also failed to start if you had the service on automatic start on Windows and the database was also starting up, my solution here was to set the Informatica service to manual start and start it after everthing else had completed its initialization.
Something that’s new in the 8.x release of Informatica is a web-based console that you can use for administering the server and the repository. Using this console, you then pick up the backed-up Oracle_BI_DW_Base repository and restore it over the empty one you created a minute ago, this then brings in all of the source-specific and file-based reference data mappings that the DAC, which you’ll configure in a moment, will call upon to load the data warehouse.
Once you know Informatica is up and running stably, the next thing is to configure the DAC tool. Bizarrely, you have to go to the Hibernate (a set of object-persistence packages) website and download the Hibernate tools and then copy a set of JAR files to the DAC installation, once you’ve done this though (and made sure you installed the JDK to a directory with no spaces in the name) you should be able to start the DAC client. On the first run it prompts you to create a connection to the DAC scheme you created earlier on in the Oracle database, then it offers to create the (empty) DAC metadata tables in the schema – these can co-exist with the Informatica repository tables in the same database schema.
Using the DAC tool, the first task you need to complete is to import the required source application list into the repository. There’s an application for each source that you can (automatically) bring into the BI Apps data warehouse, in my case I selected the Universal (required for all the sources, plus custom sources) and the Oracle 11.5.10 applications, pressed OK and then waited an hour or so whilst it brought in all the application execution routines.
Finally you need to set up your data connections in both the DAC repository and the Informatica Repository (making sure you name them identically), and finally you have to tell the DAC client which DAC server to connect to, and which Informatica servers will be running it’s ETL routines.
Assuming everything is set up and working OK (I’ve only listed out the highlights of the install steps here, the rest are detailed in the Installation and Configuration Guide) you should then be in a position where you can start your initial load into the data warehouse. More on that in a few days.
No comments:
Post a Comment