Metadata reporting on the OWB 11gR1 repository- options?

There are quite a few clients, coworkers and managers I've met with that feel the documentation capabilities of OWB are, shall we say....lacking a bit. Sure, there is the OWB online repository browser which is quite handy and gives you a lot of information. There is the metadata lineage and impact analysis diagramming which again is quite handy. There are various tricks one can do with OMB*PLUS or experts to whip up some simple documentation. Of course when you want to get specific, then it becomes a challenge.

Say you want a report, with minimal work, that is in a CSV or excel spreadsheet showing all sorts of values that you figure you can grab from the repository somehow. Again, you can accomplish some great things with OMB and experts, and I have done this, but many times it is too time intensive and overkill. Sometimes you might consider UDOs (User Defined Objects) which can be created with OMB scripting, which give you additional fields and properties to use on your objects. This can make things easy when you are in the beginning of a project and you can tackle documenting things as you go along.

What if you're 50% or even 90% done with your project, and someone demands something - fast. I say, look at the repository quickly, write some SQL, and get it out there fast.

Well in OWB 11gR1, the repository is broken down into things called workspaces. These workspaces have separate owners, and they have users themselves. So if you want to access the OWBSYS schema's many views that hold important metadata, you'll need to set some context for the user accessing the data- namely the various workspace owners. Below are a few steps that have been published in forums and the owb blog as well that outline what you should do if you cannot see any data in OWBSYS's views.....

First read this.

Then....


THEN, here are 2 sample scripts that are in the oracle forums as well. These should get you started, and get your mind thinking i terms of what other views you'd like to look at, and what other columns are you interested in...as well as the various ways to join the views!


--This script focuses more on mapping contents...
select
distinct 'TARGET',
comp.map_name,
comp.data_entity_name,
comp.operator_type
from
all_iv_xform_map_components comp,
all_iv_xform_map_parameters param
where
lower(operator_type)
in ('table', 'view', 'dimension', 'cube')
and param.map_component_id = comp.map_component_id
and param.source_parameter_id is not null
UNION
select
distinct 'SOURCE',
t1.c1,
t1.c2,
t1.c3
from
(select
comp.map_name c1,
comp.data_entity_name c2,
comp.operator_type c3,
max(param.source_parameter_id) c4
from
all_iv_xform_map_components comp,
all_iv_xform_map_parameters param
where
lower(operator_type) in
('table', 'view', 'dimension', 'cube')
and param.map_component_id = comp.map_component_id
group by
comp.map_name, comp.data_entity_name, comp.operator_type) t1
where t1.c4 is null
order by 2,1

--This script focuses more on source to target tables and column mappings

SELECT
S_TBL_LOC ,
S_TBL_name,
S_FLD_name,
T_TBL_LOC ,
T_TBL_name,
T_FLD_name
FROM
(
SELECT
parms.map_component_id S_COMP_id ,
parms.parameter_id S_PARM_id ,
colms.entity_name S_TBL_name ,
colms.column_name S_FLD_name ,
tbls.schema_name S_TBL_LOC
FROM
all_iv_xform_map_parameters PARMS,
all_iv_xform_map_components COMPS,
all_iv_columns COLMS ,
all_iv_tables TBLS
WHERE
source_parameter_id IS NULL
AND parms.map_component_id = comps.map_component_id
AND UPPER(comps.operator_type) IN ('TABLE','DIMENSION')
AND parms.data_item_id =colms.column_id
AND colms.entity_id =tbls.table_id
)
,
(
SELECT
parms.map_component_id T_COMP_id ,
parms.parameter_id T_PARM_id ,
parms.source_parameter_id T_Sid ,
colms.entity_name T_TBL_name ,
colms.column_name T_FLD_name ,
tbls.schema_name T_TBL_LOC
FROM
all_iv_xform_map_parameters PARMS,
all_iv_xform_map_components COMPS,
all_iv_columns COLMS ,
all_iv_tables TBLS
WHERE
source_parameter_id IS NOT NULL
AND parms.map_component_id = comps.map_component_id
AND UPPER(comps.operator_type) IN ('TABLE','DIMENSION')
AND parms.data_item_id =colms.column_id
AND colms.entity_id =tbls.table_id
)
WHERE
S_PARM_id=T_Sid

Data Warehousing Training

I just got done custom creating an advanced data warehousing class, along with updating our beginner course to utilize Oracle 11g technologies. Whew, talk about a lot of work and only a short time to get it done! To summarize, the course that I teach is a full 5 day class on the following topics:

-Beginner Course Topics-
Beginning Data Warehousing
Dimensional Modeling
Oracle 11g Database options for Data Warehousing
Oracle Warehouse Builder OWB 11gR1 introduction
OWB11g Architecture and Installation
OWB11g and OWB 10gR2 differences, similarities
Exploring OWB11g Interface
Common setup of typical scenarios with remote machines and schemas
Building ETL Mappings from source to staging to target
Deep-Dive into OWB Mapping best practices and tips with the operators
Understanding Data Quality
Data Profiling and Data Rules Option within OWB11g
Advanced operators- Match & Merge, pre-post mapping operators, table functions, etc.
Understanding Workflow installation and Process Flow Basics

-Advanced Course Topics-
Oracle OLAP
Understanding Oracle's Position on OLAP & Hyperio Essbase
How to use OWB to construct full MOLAP cubes and how it differs from ROLAP
Extensive ETL Lab (2-3 hour lab encompassing many advanced techniques)
Pluggable Mappings
The Metadata Manager (Impact, Dependency, and Propagation)
Lifecycle Management (MDLs, Snapshots, Signatures, importation, exportation, comparison of
snapshots, etc.)
Security in OWB
Extra detail on Process Flows and Scheduling
OMB*PLUS scripting
OWB Experts
Advanced Tips and Tricks for project management, and high Performance


...and that's all. I built out about 90% of this class and taught it recently to pretty good reviews... Let's hope there's more students in store as I prepare to build out a new offering to my students!

Cheers
-Greg

Oracle Warehouse Builder and SAP Sources

I saw a nifty link that Antonio Romero, one of Oracle's OWB product managers, gave out. When I sat in the Publisher's and Author's round table event at the last Open World, I was lucky to sit with the head of Oracle's documentation and discuss a bit on the online documentation structure, and changes coming. As well, I heard from OWB PMs that the OWB docs would be filled out more. Here is the start of that effort, in chapter 7 - Retrieving Data From SAP Sources.
Antonio kindly brought this to attention recently.

OWB Interesting Warnings and Errors #1 PL/SQL: ORA-00907: missing right parenthesis

(Please remember you may click on any picture for a larger view!)

I thought while I am writing away on the new Oracle Warehouse Builder 11g Handbook for Oracle Press / McGraw-Hill that I would still put out a few simple and useful gotchas on my blog for beginner and intermediate users. The most interesting things that seem to pop up are the odd and slightly off-target errors you may receive if you do something wrong. So here is interesting warning and error #1. Relatively straight forward, but can still trick any beginner and some intermediate users.

Let us say that we have a situation that calls for using a key lookup operator. Sometimes, when the lookup results in no matching row for the input data, we have the option to tell the operator what we would like to be output (instead of plain old NULL). For example, below, I might not have a match for my incoming data for the column tgt_unit_name. If I do not, I would output a 'default value' and not simply NULL. In the case below is where my error or warning actually arises. Note that I entered 'N/A UNIT' for my default value. If I had entered N/A UNIT without the ' ' marks, this would cause OWB to complain and issue an error that might not make perfect sense to the beginner. Let us now assume that I did NOT enter the opening and closing ' ' tick marks.
If I go ahead and try to validate the mapping that this key lookup operator appeared in, it would validate perfectly. Furthermore, if I decided to generate the code, and give it a once-over, it might look fine as well. However, when i go to my control center to deploy this mapping...the warnings will fire. So the culprit is PL/SQL: ORA-00907: missing right parenthesis. If you have a nice code tool like pl/sql developer, or toad, or sql developer, you'd likely generate this code from your mapping, and throw it into your development environment and look for problems. A beginner might go right ahead and look for some missing parenthesis. Right? Wrong! This is one area in which you have to think about something. OWB creates code. Usually syntactically correct code. It will not normally and purposely create code that has missing parenthesis. The problem is somewhere else, and it deals with the fact that we did NOT put the opening and closing ' ' around N/A UNIT. See below:

We can clearly see the right number of parenthesis are there, but one is not interpreted due to N/A UNIT not being formed correctly! Let's go back to the mapping, and the key lookup operator and make sure we enter 'N/A UNIT'. (Remember, the way you interact with OWB literally is writing code.) We make the change, and deploy the mapping, and get no errors or warnings!

So, what does the working code snippet look like now?
Note that with a nice IDE, like SQL Developer, we can see that the moment the ticks are in place and the 'N/A UNIT' value is handled correctly in the NVL, we have success. This is to illustrate to the beginner or intermediate user that everything you do in OWB is writing code under the hood. Simply forgetting to use the tick marks to form a literal can result in an error that might make you spend many minutes hunting down a missing parenthesis! Remember that OWB generates PL/SQL, but will listen to you, the user (almost too much) and do exactly as you tell it!

FULL Outer Joins FOJ in OWB

See the owb blog: here

OWB patching part 2

I should have mentioned this in my first patching post a few days back. But...test test and test your patching and what it does to your system before deploying it into a secure production system.

See this OTN forums link...a few people (only 2) are having peculiar problems already with the OWB patch. This is not terribly surprising...things take a while to get perfectly right even with a patch!

OWB patching

Just to let you all know that are still using OWB 10gR2 (quite a few I bet) it looks like there is a new patch out there that came out in July, so if you're having as issue with one of the 120 bugs it supposedly fixes, get on over to metalink to investigate the new patch!

What Does Oracle have in store for OWB and ODI? Here is a Roadmap!

Check out the link direct from Oracle on their plans with Oracle Warehouse Builder and Oracle Data Integrator - OWB and ODI.
check it out here.

OWB can be a bit 'blind' when Oracle RAC is involved!

I will update this bit soon I hope. I'll post a little nasty bit that tricked us and cause our mappings to run horribly slow on a RAC environment. Hopefully when I get a little more information on RAC from Dan Norris, I'll update this with a clear explanation!

OWB 11g R2 beta and book

Good news! I have been accepted into the OWB11gR2 beta, and I am putting the new features though the testing ringer to see just how well everything will turn out. I will likely not be able to mention or discuss much of it, so please don't ask. If I have been given consent, I will share at the approved time.

I am also in the early stages of bringing a massive amount of experience, projects, data, and writing manuscripts together for an even bigger project. I'll be starting very soon to write the first Oracle Warehouse Builder book ever published. It will be with Oracle Press (McGraw Hill) and will be taking advantage of all the beta experience that will be gained from the OWB11gR2 beta.

In the coming months I will update here on anything that is allowed and not confidential.

Oracle Warehouse Builder OWB vs. Oracle Data Integrator ODI

Here's a snippet from a bigger article written by Mark Rittman over on OTN.

Oracle Data Integrator in Relation to Oracle Warehouse Builder

At this point, regular users of Oracle Warehouse Builder are probably wondering how Oracle Data Integrator relates to it and how it fits into the rest of the Oracle data warehousing technology stack. The answer is that Oracle Data Integrator is a tool that’s complementary to Oracle Warehouse Builder and can be particularly useful when the work involved in creating the staging and integration layers in your Oracle data warehouse is nontrivial or involves SOA or non-Oracle database sources.

For those who are building an Oracle data warehouse, Oracle Warehouse Builder has a strong set of Oracle-specific data warehousing features such as support for modeling of relational and multidimensional data structures, integration with Oracle Business Intelligence Discoverer, support for loading slowly changing dimensions, and a data profiler for understanding the structure and semantics of your data.

Where Oracle Data Integrator provides value is in the initial preparation and integration of your source data, up until the staging area of the data warehouse.

Oracle Data Integrator can integrate and synthesize data from numerous disparate datasources, including Web services and event-based architectures, and, as shown in the figure above, provides a handy graphical interface on top of Oracle Database-specific features such as Oracle Change Data Capture. Once data has been integrated and copied into your data warehouse staging area, Oracle Warehouse Builder can take over and create and populate your operational data store and dimensional warehouse layers.

Oracle Warehouse Builder OWB 10gR2 Tutorial for you!

Here's a set of slides with some basics of OWB for beginners to look over. I know Oracle has some Oracle By Example tutorials that are 'OK' at best. This is another look at the tool for you to learn from. Hopefully these will fill in some gaps as the documentation is not so great, there are no OWB books, and there is not as much useful information about this product compared to other Oracle products.

Oracle Warehouse Builder OWB 11g New Features Part 6!

This is a continuation from part 5. In part 6, we will discuss a bit more about the installation of OWB 11g, and the install options. Again, thanks to Oracle for providing some of this information to make my posting easier!

Server Install Provides Full Control Center Functionality

The Oracle home of the Oracle Database 11g installation includes the components needed to perform the following tasks for OWB:

• Deployment and execution of all standard objects
  (tables, dimensions, cubes, maps, and so on)
• Deployment of process flows
• Warehouse upgrade
• Run-time scripting
• Installation of Oracle Workflow

The OWB server installation includes a small set of files needed to get the Control Center running in the database Oracle home. This allows a “fully functioning” OWB install directly with the database install.

The directory structure looks like this:

• Oracle Home/owb
• Oracle Home/owb/wf

Discoverer deployment is the only task not supported by the OWB server install. For Discoverer deployment, you must perform the stand-alone installation described later.

Stand-Alone..When Do You Need Stand-alone Installation?

The OWB 11g stand-alone installation is required only if you must:

• Deploy to an Oracle Database 10g, release 2 target
• Perform Discoverer deployment

The stand-alone DVD is actually bundled with the 11g database pack.

For full functionality, OWB 11g stand-alone must be installed on the database server machine (10g R2 or 11g) into a new Oracle home (separate from the database home).

Using OWB 11g with DB 10g R2

OWB 11g is architected on the assumption that it will be used with Oracle Database 11g. This tight integration includes pre-seeding of the OWBSYS schema in the default database and placing OWB in the same Oracle home as the database.

To use OWB 11g with Oracle Database 10g R2, you must perform a few simple steps:

1. Run SQL script to create OWBSYS.

2. Run SQL script to identify OWB home to Oracle Database 10g R2.

3. Unlock OWBSYS.

4. Enable access to workspaces.

1. Run SQL script to create OWBSYS.

When using Oracle Database 10g R2, you must run a SQL script to create the OWBSYS repository schema that is needed by OWB 11g. (OWBSYS is automatically created during Oracle Database 11g installation, so this step is not needed when using OWB 11g with Database 11g.)

The script /OWB/UnifiedRepos/cat_owb.sql installs the OWBSYS database user that is otherwise automatically created when installing Database 11g.

Note: The method by which you invoke SQL*Plus is important. Do not invoke SQL*Plus from the Start > Programs > Oracle database home folder or the Warehouse Builder folder. Instead, invoke SQL*Plus from Start > Run > cmd.exe. Type the Path command and press Enter.
Verify that Warehouse Builder is near the front of the path statement so that the SQL*Plus session invokes from the Warehouse Builder installation. If not, you may need to temporarily set the path (such as Path=\bin). Then invoke SQL*Plus as SYS with SYSDBA privileges. For example, at the command line you can type the following:
sqlplus sys/ as sysdba

Connect to SQL*Plus as SYSDBA and issue the following command to create OWBSYS:

@/owb/UnifiedRepos/cat_owb.sql;

Press Enter. You are prompted for a tablespace for the OWBSYS user. Type a table such as users and press Enter.

When this command finishes successfully, you are prompted: “If you are NOT using an OWB installed in the Oracle database home, please now run reset_owbcc_home”.

2. Run SQL script to identify OWB home to Oracle Database 10g R2.

Earlier, you were instructed to install Warehouse Builder in an Oracle home other than the database Oracle home if you are using OWB 11g with Oracle Database 10g R2. To ensure access to the Control Center on the Oracle Database 10g R2 database, run the reset_owbcc_home.sql script and pass in your Oracle home for Warehouse Builder.
Run the script as a system privileged user such as SYS or SYSTEM. For example, type the following command at the command prompt:

SQL> @c:\oracle\OWB_home_11g\owb\UnifiedRepos\reset_owbcc_home;

Press Enter. You are prompted for the full path of the Oracle home for the OWB Control Center install. First read the following note.

Note: In the following example, observe the required use of the forward slashes (/) even when using a Windows machine. Note also that your response with the full path must be case sensitive. Be sure to enter the drive letter in uppercase (such as C:) and follow the case sensitivity of the folder names in the path of the Oracle home for OWB. Your response should be similar to the following example:

C:/oracle/OWB_home_11g

Press Enter.

Important note: If you specify the path to the OWB Oracle home incorrectly (using backward slashes or incorrect case sensitivity), the reset_owbcc_home.sql script finishes without apparent errors. However, when you later use the Repository Assistant to create a workspace and users, the process fails.

3. Unlock OWBSYS.

After creating OWBSYS, you must unlock the OWBSYS account and reinstate its password if the strong password option is enabled on the database. Proceed with the SQL*Plus commands to unlock the OWBSYS account and assign a password (also named OWBSYS):

alter user OWBSYS account unlock;

alter user OWBSYS identified by OWBSYS;

The requirements explained above are also mentioned in the OWB 11g Installation Guide documentation, in the section titled “Hosting the Repository on Oracle Database 10g Release 2.”

For each Warehouse Builder client installation, enable access to the workspaces hosted on your Oracle Database 10g R2 database.

4. Enable Access to Workspaces

By default, Warehouse Builder 11g, release 1 clients are set to connect to workspaces hosted on an Oracle 11g database. That is, the Warehouse Builder repository is assumed to be hosted on an Oracle 11g database.

To enable access to an Oracle Database 10g R2 repository and its workspaces, alter the file /owb/bin/admin/Preference.properties. If the file does not exist, you can create it based on the example file available in the same directory, Preference.properties.tmp.

In the preference properties file, add the REPOS_DB_VERSION_ALLOWED property and set its value to Oracle 10g, Oracle 11g. (This property may already be present.)

And that's all for part 6. Hopefully, this allows users to get OWB 11g installed in their preferred format! Thanks again to Oracle for providing some of the info contained above. If you have any questions, feel free to drop me a line. Part 7 will cover Repository Assistant Workspace Changes, and how logging in, managing users and targets, and other features have changed!

Oracle Warehouse Builder OWB 11g New Features Part 5!

This is a continuation from part 4. In part 5, we will discuss a bit about the installation of OWB 11g, and how it is easier and differs from the previous counterparts. Again, thanks to Oracle for providing some of this information to make my posting easier!

Overall, the installation of OWB 11g is greatly simplified in an Oracle Database 11g environment. When you install the core Oracle Database 11g, you get the OWB back end: a pre-seeded repository schema and workflow.

Without the requirement of SYSDBA privileges (as we previously mentioned in other postings), you can make a sandbox repository and start exploring the OWB tool immediately! This was not necessarily so in the 10g versions!

Three types of OWB 11g installation possibilities can be:

• Server installation as part of the Oracle Database 11g installation
• Stand-alone installation using the stand-alone DVD bundled with the 11g database pack
• Stand-alone installation for Oracle Database 10g R2 using special scripts
• Note: Installing to Oracle Database 10g R2 requires running two special SQL scripts and a few extra steps

OWBSYS Is Part of Database 11g Installation

As part of the database installation scripting, the OWBSYS schema is seeded as part of these starter databases:

• General Purpose
• Transaction Processing
• Data Warehouse

During installation, all of the necessary system privileges, object privileges, and roles are granted to the OWBSYS schema. The script that creates OWBSYS is located at: ../owb/UnifiedRepos/cat_owb.sql

Why?

So after database installation, OWB is ready to use. Because the necessary privileges and roles are granted, no DBA or SYSDBA credentials are required when creating workspaces.

Note: The schema does not hold a complete repository/workspace in its pre-seeded form. You must still define your workspaces, workspace owners, and users. Also, OWBSYS is locked by default. As will be explained later, you must unlock it if you are using OWB 11g with Oracle Database 10g R2.

OWB as a Custom Database Option

For custom databases, OWB is an option in the Database Configuration Assistant user interface. With the OWB option selected, these database options are enabled:

• Oracle OLAP
• Oracle JVM
• Oracle XML DB
• Oracle Intermedia
• Oracle Enterprise Manager Repository

Now that we have a brief overview of the install options, next time we will look more in depth at the OWB 11g server install, the OWB 11g standalone install, and if the post doesn't get TOO long, we'll also discuss using OWB 11g on a 10gR2 Oracle Database and all the gotchas that come with that! I know that will be heavily requested just simply because some people want to use OWB 11g but don't want to upgrade their database just yet from 10gR2 to 11g! Until next time....

Oracle Warehouse Builder OWB 11g New Features Part 4!

This is a continuation from part 3. We will continue to explore some new features this time of OWB 11g. Last time we touched on the Siebel Connector and some general connectivity options.

First, you should know that there are a few new features in OWB 11g that were rolled out in the latest OWB 10g R2 patch version 10.2.0.3. As follows:

• Version support for type 2 SCDs.
• Set based Delete in mappings code generation.
• Availability of a merge optimization property for table operators.
• DML error logging to handle error tables.

So what are these and how are they 'new'?

Versioning of Type 2 SCDs (slowly changing dimensions)
Hierarchy versioning supports multiple hierarchies. When the non-leaf level (think of a tree or maybe a bush...the leaves are the furthest from the root or middle) of a dimension contains versioned attributes, the versioning of this non-leaf level results in the versioning of its corresponding child records if they have effective date and expiration date attributes and if you have enabled hierarchy versioning. In essence, what can happen to 'Dad' will happen to his kids too.

Hierarchy versioning is not enabled by default for type 2 SCDs (slowly changing dimensions). When you create a type 2 SCD by using the Create Dimension Wizard, hierarchy versioning is disabled. You must use the Data Object Editor to enable hierarchy versioning.

So what is a Type 2 Slowly Changing Dimension?

In Type 2 Slowly Changing Dimensions, a new record is added to the table to represent the new information. Therefore, both the original and the new record will be present. The new record gets its own primary key.

For example, say we have the following table:

Customer Key	Name	State
1001	Christina	Illinois

After Christina moved from Illinois to California, we add the new information as a new row into the table:

Customer Key	Name	State
1001	Christina	Illinois
1005	Christina	California

Advantages:

- This allows us to accurately keep all historical information.

Disadvantages:

• This will cause the size of the table to grow fast. In cases where the number of rows for the table is very high to start with, storage and performance can become a concern.
• This necessarily complicates the ETL process.

Usage:

• About 50% of the time.

When to use Type 2:

• Type 2 slowly changing dimension should be used when it is necessary for the data warehouse to track historical changes.

Set-based DELETE in Mapping Code Generation
Beginning in Warehouse Builder 10.2.0.3, you can design a mapping with the loading type set to DELETE and code generation set to SET BASED.

Merge Optimization for Table Operators
Beginning in Warehouse Builder 10.2.0.3, you can enable the Merge Optimization property for table operators. When set to true, this property optimizes the invocation or execution of expressions and transformations in the MERGE statement. There is QUITE a bit of information and tips and tricks out there on using the merge operator to the full extent.

DML Error Logging to Handle Error Tables
Prior to release 10.2.0.3, OWB already generated error-handling code for row-based mode.
Beginning in Warehouse Builder 10.2.0.3, DML error logging is available for set-based maps.

Thats is all for now, soon we'll move on to more new features and how OWB11g is easier to install and maintain.

Oracle Warehouse Builder OWB 11g New Features Part 3!

This is a continuation from part2. Here is part 3 of the new features and enhancements to Oracle Warehouse Builder - OWB 11g. It is in these posts that you can make the educated decision whether or not to pursue OWB 11g, or stay with OWB 10gR2. It is also here you can compare and contrast the differences between the 2 releases. Once again, props to Oracle for providing some of the info!

The new OWB 11g connectivity options....

• Now there is database support for database 11g to be both the source and target.
• Oracle Database 11g is supported as both an OWB source and an OWB target. Oracle Database 11g support is closely linked to the simplified install of OWB 11g as part of the Oracle Database 11g install.
• Now there is also Gateway 11g support
• The ability of OWB to connect transparently to non-Oracle systems continues with OWB 11g support for Gateway 11g.
• Also, the new optional Siebel Connector fo Siebel Analytics

OWB Connector to Siebel Analytics...
The Warehouse Builder Connector to Siebel enables you to connect to—and extract data and metadata from—Siebel applications. The connection to the Siebel applications uses database users that have the appropriate privileges set by the DBA. After you import metadata from Siebel applications, you can use Siebel objects in mappings, create process flows containing Siebel objects, and generate SQL code.

You can use this connector with non-Oracle databases after you establish a connection to those databases. The table above lists the functionality available in the Warehouse Builder Connector to Siebel.

Next time in part 4 we will discuss more new features and enhancements!

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.

Oracle Warehouse Builder OWB R2 Process Flows

I recently was contacted by one of my former clients that had a pretty good question. I actually had this questions asked a few times by various parties, so here is a summery and an answer!

"Quick OWB question for you: Is it possible to execute process flows from outside of OWB once they have been created. Wondering if there is a template provided by oracle(like “Sql_exec_template for OWB mappings) to execute Process flows…"

Well, OWB does contain a pl/sql package called WB_RT_API_EXEC with a function inside it called RUN_TASK that submits execution requests to the OWB runtime engine. The requests could be for mappings, process flows or scheduled jobs. The function spec looks like:

function run_task

( p_location_name in varchar2
, p_task_type in varchar2
, p_task_name in varchar2
, p_custom_params in varchar2 default null
, p_system_params in varchar2 default null
, p_oem_friendly in number default 0
, p_background in number default 0
) return number;

This is actually the same function used by the sqlplus_exec_template.sql script in OWB 10gR2 that you mentioned in your email. It can be called using (assuming a repository owner connection exists):

(this is an example…)

declare
result_num number;
begin
result_num:= wb_rt_api_exec.run_task('PFMOD_LOCATION1', 'PROCESS', 'MY_PKG/MY_PF', ',', ',', 0 ,0);
end;

The full documentation of the function can be found in /owb/rtasst/wb_rt_api_exec.pl

As usual, if you have further good questions never hesitate to checkout Oracle OWB Blog and Oracle Technology OWB Forum .

The OWB Repository Upgrade Assistant!

Just what in the world is this little hidden Wizard tool doing? Let's find out more about the OWB Repository Upgrade Assistant, shall we?

First, what does it do, and why do we use it?

Next, how do we use it; exactly where do we find it?

Any issues we know of?