Oracle Warehouse Builder OWB 11g New Features Part 2!

This is a continuation from Part 1: Part 1. Again, due credit to Oracle for providing some of this information to us on OWB 11g!

Let's recap on the unified repository addition. The single repository owner for OWB 11g is named OWBSYS, which owns the actual repository. This user does not functionally own objects but owns them physically from a schema perspective. The single unified repository enables maintaining a single copy of OWB database objects in OWBSYS (tables, views, PL/SQL packages, and so on).
Benefit of Pre-seeded OWBSYS Unified Repository Schema?

Previously, each Oracle database that was utilized as an OWB repository required a user with SYSDBA privileges. Beginning with OWB 11g, SYSDBA privileges are no longer required. An OWBSYS schema is created during installation of Oracle Database 11g, release 1. OWBSYS holds the metadata, which is divided into workspaces. To start using Warehouse Builder, you merely need to create a new workspace. You do not need SYSDBA privileges. HOORAY!!

Accessing Workspaces Instead of Repositories

Previously, users accessed the repository as a whole. Therefore, users were referenced as repository users and repository owners. Beginning with OWB 11g, the term repository is replaced with workspace (from the user connection perspective). Thus, instead of granting access to a repository, you grant access to a workspace. Because of the use of workspaces in a single schema, creating workspaces is simplified.

When defining the repository, an administrator creates one or more workspaces, with each workspace corresponding to a set of users working on related projects. A common practice is to create separate workspaces for development, testing, and production. Using this practice, you can allow users (such as your developers) to access the development and testing workspaces but restrict them from the production workspace.

Default OWB Install Uses Database Oracle Home

Default install of the OWB set goes into the database Oracle home. However, you can still install OWB into its own Oracle home.

When you install the OWB 11g server to the Oracle database home as a database option during installation of Database 11g, the OWBSYS schema is automatically created.

When you install OWB 11g to its own Oracle home other than the Database 11g home, or to an environment using Database 10g, release 2, an OWB script is provided for you to manually create the OWBSYS schema.

There is now a SINGLE Control Center Service!
Instead of a Control Center Service per repository (as required in OWB 10g), OWB 11g requires only a single Control Center Service for the database instance, serving the OWBSYS schema.The single Control Center Service has the following characteristics:

• Services requests from multiple workspaces.
• Executes as the OWBSYS user.
• Maintains Control Center log data, including the workspace label.

A workspace owner is an OWB administrator who manages a particular workspace and registers users of that workspace. In the diagram above, workspace owner 1 owns workspace 1 and workspace owner 2 owns workspace 2.

If a workspace is created with a new database user as its owner, the workspace must be created in the OWB Repository Assistant using database DBA credentials.

A workspace is equivalent to an OWB 10.2 repository. There can be multiple workspaces in a repository.

There are minor security changes to support workspace user management. Beginning with Oracle Warehouse Builder 11g, release 1, the preferred method of implementing metadata security is through the user interface available in the Design Center (as described in the Oracle Warehouse Builder User’s Guide). If, in a previous release, you implemented security by using a PL/SQL package, Oracle Warehouse Builder 11g, release 1, supports that implementation.

Workspace users connect to a workspace after the workspace owner grants them access. These users work essentially in the same way as repository users work in OWB 10g. However, workspace users operate at what is now called the workspace level (rather than the repository level).

Next time we will discuss new connectivity options and the OWB connector to Siebel.

Oracle Warehouse Builder OWB 11g New Features!

OWB 11g has a myriad of new features that make this tool more and more accessible to new ETL technicians. The differences in installation and repository structure in the 10gR2 release were significant enough to make the 'understanding' of the architecture easier. Well now OWB 11g has taken this to another level. Not only that but the new features or enhanced architectural features make this product very attractive. Over the next few weeks I will be blogging a bit about the new features and enhancements. (due credit to Oracle Corp for providing some of this information and diagrams).
This is part1. My blog also has a part 2,3,4,5 as well as 6.

The first thing to mention is what Oracle felt were the goals for OWB11g. Why have a new release?
A few reasons:

• Better integration with the Database
• Easier installation because OWB is a database option.
• The SYSDBA requirement removed in a repository installation (very nice IMO)
• Reduced need for DBA credentials
• Database 11g as a source AND target.

Packaging remains the same basically as 10gR2 (core features and options).

• Siebel ERP/CRM connector is new though!
  

Repository Structure has changed from 10gR2 to 11g:

• The illustration below represents the OWB architecture prior to OWB 11g.

• OWB 10g requires one OWB schema per OWB repository. Multiple OWB repositories on an Oracle Database instance require maintaining multiple OWB schemas for multiple OWB repositories on that instance.
• Furthermore, OWB 10g requires one Control Center Service per OWB repository (This means multiple control Center Services).
• OWB 10g Does Not Install to the Database Oracle Home
• OWB 10g and earlier releases cannot install to the Database Oracle home; these versions of OWB require their own Oracle home. Because OWB 11g is integrated as an option in the Oracle database, the default OWB 11g installation is to the database Oracle home. OWB 11g architecture resembles the following:
• A major feature of the new OWB 11g architecture is the single unified repository (above) for each database instance, with a single Control Center Service.
• The single repository owner for OWB 11g is named OWBSYS, which owns the actual repository. This user does not functionally own objects but owns them physically from a schema perspective. The single unified repository enables maintaining a single copy of OWB database objects in OWBSYS (tables, views, PL/SQL packages, and so on...

Next time I'll explore more about repositories, workspaces, and the OWBSYS user, and Control Center Services.

Nifty little OBIEE Blog for beginners - OBIEE 101

I saw this blog today that I felt was worth sharing for beginners. Looks like real-world experience and good shots of various issues. not bad! link

APEX and BI Publisher Integration

We're working on a big client project that has quite a few technologies, including APEX and BI Publisher. We don't have them working together actually, but I have done some APEX work and lots of BIP, so I wanted to direct you to Dimitri Gielis's blog and this post specifically: integration

Oracle OBIEE and pre-built Analytics (Siebel) Architecture

This posting won't focus on the build it vs buy it thought process (which is valid considering these nice pre-built analytics solutions really cost a bundle!). Instead I wanted to first discuss OBIEE and Oracle E-Business suite or Oracle Apps integration. Now, there should be no mystery that a huge amount of operational data is flowing through your Oracle apps database. It should also be no mystery that you'd probably like to get as much value out of that data and see what it contains...forecasting, trending, historical data...you name it. That's where BI and OBIEE really come in.

Now with OBIEE, you can have a world class operational system with APPS and a world class BI and data warehousing solution using OBIEE. The big sticky here is what do we do? Custom develop the whole underlying architecture to get OBIEE up and running? We're definitely not going to report off of the apps database with our analytic reports! We need a solid data warehousing environment, data marts, and strong ETL to get the right data out of apps, and into our Data Warehouse. THEN we can consider OBIEE and its reporting capabilities.

You might however consider using the pre-built analytics that can be purchased. These can roll-up lots of reports, pre-built ETL, and many DW structures that are already there for your deployment. The typical client will be able to immediately use 60-80% of this pre-built package right out of the box. So what does this all look like from a bird's eye view? Well let us JUST look at the concept of the pre-built analytics below:

High-level analytical queries, like those commonly used in Oracle BI, scan and
analyze large volumes of data using complex formulas. This process can take a long time when
querying a transactional database, which impacts overall system performance.
For this reason, the Oracle Business Analytics Warehouse was constructed using dimensional
modeling techniques to allow for fast access to information required for decision making. The Oracle Business Analytics Warehouse derives its data from operational applications, and uses Informatica’s data integration technology to extract, transform, and load data from the transactional database into the Oracle Business Analytics Warehouse.
The Oracle Business Analytics Warehouse architecture comprises the following components:

DAC client. A command and control interface for the data warehouse to allow for set up,
configuration, administration, and monitoring of data warehouse processes.

DAC server. Executes the instructions from the DAC client. The DAC server manages data
warehouse processes, including scheduling, loading of the ETL, and configuring the subject areas
to be loaded. It dynamically adjusts its actions based on information in the DAC repository.
Depending on your business needs, you might incrementally refresh the Oracle Business
Analytics Warehouse once a day, once a week, once a month, or on another similar schedule.

DAC repository. Stores the metadata (semantics of the Oracle Business Analytics Warehouse)
that represents the data warehouse processes.

Informatica Server. When the server is loading or refreshing the Oracle Business Analytics
Warehouse, it is recommended that the server be dedicated to that activity. However, when the
ETL server is not loading or refreshing the Oracle Business Analytics Warehouse, you can use it
for other purposes.

Informatica Repository Server. Manages the Informatica repository.

Informatica Repository. Stores the metadata related to Informatica workflows.

Informatica client utilities. Tools that allow you to create and manage the Informatica
repository.

OBIEE post install OC4J and Windows Services

I wanted to download the Oracle Business Intelligence Enterprise Edition PLUS 10.1.3.3 recently. I didn't want to upgrade from my previous version since I had done so much work on it, it had become a war-zone after all the experimenting I had done.

Part of my post install was to get rid of the annoying automatic OC4J startup on Windows. The other part was to create simple .BAT files to startup and shutdown the multiple services that OBIEE uses.
If you've done the install as a simple non-Application Server install that uses the standalone OC4J container, read on:

After you have installed BI EE, you are asked to reboot your computer (this is for a Windows install). After you reboot and login, you will notice a DOS window which shows OC4J starting. Even if you set all the BI Services to "Manual", this particular command is still fired at startup.
This is because the command to start OC4J is set in the Windows Registry.
If you go to the Windows registry (type "regedit" in your "Start --> Run" textbox) navigate to "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Run" you will notice that an entry named 'oc4j' is created during the installation, which starts up the OC4J window you'll see every time you startup.

So I found that entry in the registry, I selected it, and I deleted it. We STILL have the ability to start and stop the OC4J process through our OBIEE start menu link:

What one can do is to create a batch file for Windows that will actually start and stop this OC4J process that we deleted out of the registry, AND while we are at it, we can also add the startup and shutdown actions for the other OBIEE processes too. This way, we have a master start and stop for all necessary services through one initiation.

To do this, open a Windows program like 'notepad' and enter the following for the startup.bat file:
This is how my "Start_BI" file looks like:
net start "Oracle BI Server"
net start "Oracle BI Presentation Server"
net start "Oracle BI Java Host"
C:\OracleBI\oc4j_bi\bin\oc4j.cmd -start

I then saves this file from notepad as Start_Oracle_BI.bat and chose the option for 'Save As Type' as 'All Files'.
Now the shutdown.bat file:
net stop "Oracle BI Server"
net stop "Oracle BI Presentation Server"
net stop "Oracle BI Java Host"
"C:\Program Files\Java\jdk1.5.0_08\bin\java.exe" -jar C:\OracleBI\oc4j_bi\j2ee\home\admin.jar ormi://localhost:23791 oc4jadmin oc4jadmin -shutdown force

One thing to note is that in the above .bat file scripts is that some of these locations are hardcoded.
Make SURE you have the necessary files in the right place, OR change the locations in the script to point to the right path. ALSO note in the shutdown script that the word oc4jadmin appears 2 times. This is because we assume that oc4jadmin is both the login and password. If you registered a different password, you'll need to change that in the shutdown .bat file as well. If you don't Oracle will not authenticate and allow you to shut down the oc4j process!

Now all I need to do is to click on my .bat files to execute them, which starts and stops all the important services!

MIX at Oracle

You can now find me and some colleagues at MIX, Oracle's idea site to put people in touch with one another. Just search for me by name. Mix is here: link