[OWB] Overview Of Warehouse Builder

An overview of Warehouse Builder Design Center

The Design Center is the main graphical interface that  

we will be using to design our data warehouse, but we also use it to define our data sources. So let’s take some time at this point to go over the user interface and familiarize ourselves with it. Release 11gR2 of the Warehouse Builder has a completely updated interface, using the Fusion Client Platform style and presents an Integrated Development Environment (IDE) that is the same core IDE prevalent in other Oracle applications such as Oracle JDeveloper and SQL Developer. Anyone who has used either of those Oracle products, will immediately recognize the overall design. It is a much improved, more intuitive interface than the previous one from release 10gR2 and 11gR1 and provides such improvements as automatic layout, dockable panels, and zoom capabilities in editors within the IDE. The screenshots throughout the topic have all been updated to reflect the new interface. We launch Design Center from the Start menu under the Oracle menu entry, as shown in the following image:

The Design Center must connect to a workspace in our repository.  

To review briefly, we discussed the architecture of the Warehouse Builder in topic 1. This included the repository in which we created a workspace and a user, who would be the owner of the workspace. We used the Repository Assistant application to configure our repository and create that user. The repository is located in the OWBSYS schema that was the pre-installed schema the database installation provided for us. The user name chosen was acmeowb and the workspace name was acme_ws. Now it’s time to make use of this user and workspace. 

The first screen we’ll be presented with is the Logon screen,

which will appear in front of the main interface screen as the first task to perform when launching the Design Center: 

The first time we use this application,

the Logon dialog box comes up all blank. But after we fill in our information for the first time, it will remember the User Name and Connection details on subsequent executions of the Design Center. Also, it will present us with a smaller version of the dialog box with just User Name and Password, so that we can just enter the password and don’t have to re-enter the connection details. The button above the connection details that now displays Hide Details << will display Show Details >>. If we need to change the connection details in that case or to just see what they are set to, click on the Show Details >> button and it will display the full dialog box as above. 

As this is our first time,  

we have to enter all the details. The User Name and Password are what we specified in the Repository Assistant for the workspace owner, and the Connection details are the Host, Port, and Service Name we specified when we used the Database Configuration Assistant to create our database. We’ll enter acmeowb as the username and acmedw as the service name.

The Workspace Management button to invoke  

Repository Assistant from the Design Center Logon dialog box is gone in this new version of the logon screen. In its place is the Getting Started button which launches a Help center window. Depending on your operating system version the help screen may be blank when it first comes up. There were a couple of bugs affecting the first released version of 11gR2 on Windows that were resolved in a subsequent patch. 

The main Design Center window will  

be displayed next upon a successful log on. An example is shown in the following image, which depicts the default appearance of the Design  

Center:

A project called MY_PROJECT appears, which is the default project that the Warehouse Builder will create in every workspace. 

We referred to MY_PROJECT back when we discussed the final results screen of the Repository Assistant, which showed this project name even though we hadn’t specified one. 
Before discussing the project in more detail, let’s talk about the three tabs in the left window of the Design Center screen. They are as follows:
 
• Projects
• Locations
• Globals Navigator 

 
The Projects tab is where we will work on the objects that we are going to design for our data warehouse. It was the old Project Explorer window in the previous Warehouse Builder release. It has nodes for each of the design objects we’ll be able to create. It is not necessary to make use of every one of them for every data warehouse we design, but the number of options available shows the flexibility of the tool. The objects we need will depend on what we have to work with in our particular situation. In our analysis earlier, we determined that we have to retrieve data from a database where it is stored. 

So, we will need to design an object under the  

Databases node to model that source database. If we expand the Databases node in the tree, we will notice that it includes both Oracle and Non-Oracle databases. We are not restricted to interacting with just Oracle in Warehouse Builder, which is one of its strengths. We will also talk about pulling data from a flat file, in which case we would define an object under the Files node. If our organization was running one of the applications listed under the Applications node (which includes Oracle E-Business Suite, PeopleSoft, Siebel, or SAP) and we wanted to pull data from it, we’d design an object under the Applications node. 

The Projects tab isn’t just for defining our source data, it also holds information about targets. Later on when we start defining our target data warehouse structure, we will revisit this topic to design our database to hold our data warehouse. So the Projects tab defines both the sources of our data and the targets, but we also need to define how to connect to them. This is what the Locations tab is for. 

The Locations tab is where the connections  

are defined to our various objects in the Projects tab. The workspace has to know how to connect to the various databases, files, and applications we may have defined in our Projects tab. As we begin creating modules in the Projects tab, it will ask for connection information and this information will be stored and be accessible from the Locations tab. Connection information can also be created explicitly from within the Locations tab. 

Multiple projects can be defined in the Projects tab, but connection information is not displayed project-wise in the Locations tab. Connections are applicable for the entire workspace, and not just the project we are working on. 

There are some objects that are common to all projects in a workspace. The Globals Navigator is used to manage these objects. It includes objects such as Public Transformations or Public Data Rules. A transformation is a function, procedure, or package defined in the database in Oracle’s procedural SQL language called PL/ SQL. Data rules are rules that can be implemented to enforce certain formats in our data.