Oracle Warehouse Builder (OWB) is an ETL(Extract Transform Load) tool produced by Oracle.
OWB’s main use is the consolidation of heterogeneous data sources from legacy systems in data warehousing and data migration. It offers capabilities for relational, dimensional and metadata data modeling, data profiling, data cleansing, and data auditing.
Here is an overview of the steps to set up Oracle Warehouse Builder:
- Listener Configuration
- Configuration or Creation of Database
- Workspace and Repository Configuration
- Login into the Design Center
In Oracle, all network connections are done through the Listener. It is a named process which runs on the oracle server.
It receives a connection request from the client to connect to the oracle database. It also receives a connection request from the client and manages the traffic this request to the database server.
Below are the steps to Configuration of the Listener :
Step 1: Run the Oracle Net Configuration Assistance to Configure the Listener.
Step 2: The first screen is the Welcome screen which gives the option to select Listener Configuration from it and then Click on the Next Button.
Step 3: After clicking on the next button the second screen allows us to Add, Reconfigured, Delete, or Rename a Listener. In between, we have to select the Add option from the given option to configure a New Listener and click on Next button.
Step 4: The third screen will ask you to enter the Name of the Listener and by default name of Listener is “LISTENER”.
We can enter a new name or continue with the default name and click on the Next button to proceed.
Step 5: Fourth screen which shows the selection of the protocol. By default TCP/IP protocol is selected in this screen. TCP is the Standard Communication Protocol for internet and local network. We have to select the TCP protocol and click on the Next button.
Step 6: The last and the final step is the TCP/IP port number for the Listener to run. The default port number is 1521 and we can continue with the default port number and click on the Next button.
Step 7: After selecting the port number the next screen will ask us to Configured another Listener. We have to select No option and click on Next Button.
(If you want to configure another listener then you can select yes.)
Step 8: Finally the last screen will show the message is Listener Configured Complete.
Step 1: Click on the Database Configuration Assistant then the welcome screen will appear. We just need to click on the Next button to configure the Database and we have to select the option -> Create a Database and click on the Next button.
Step 2: The next screen will ask you to select the Data Warehouse option and click on the Next button.
Step 3: In the next step we have to enter the Global database name and SID (System Identifier) Name to the Database.
We have to enter the database name whatever we want and click on the Next button.
Step 4: In the next screen, we have to just select the Management Option of the Database.
Step 5: In the below Database Credentials step we have to enter the password for the database and click on the Next button.
Step 6: In the Storage Option we have to select File System Storage for the database to store the database and click on the Next button.
Step 7: After selecting the database storage option we have to select the Database File Location Option and click on the Next button.
Step 8: After selection the File Location option we just need to Configured the Recovery Area for the Oracle Database.
where you have to Checkmark the Specify Flash Recovery Area and specify the Flash Recovery Area Size in a byte and click on the Next button.
Step 9: Now in this step we can add the Sample Schemas (Database) to checkmark on the Sample Schemas option and click on the Next button.
Step 10: This steps only shows the Initialization Parameter of the database. If we want to change it we can change the size of the memory accordingly and click on the Next button.
Step 11: In this step select Revert to pre-11g default settings for Security Settings.
Step 12: In this below step you just have to enable the automatic maintenance tasks.
Step 13: After enabling the automatic maintenance tasks the next screen will show the Database Storage Information.
Step 14: In this step, you just have to Checkmark the Create database Option to create a database and click on the Finish button.
Step 15: After clicking on the Finish button after that it will show you the Confirmation message of the created database.
Step 16: After that click on the Ok button then it will show the process of Clone database creation in Progress.
Step 17: After completing the Clone database you can see the Global Database Name and System Identifier Name and in that screen.
You will get one button that is Password Management, you just need to click on that to set the password to the database for security purpose.
Step 18: After clicking on Password Management button you will get the below screen in that we have to Uncheck the option as OWBSYS and set the Password for the database and click on OK button.
Step 19: Finally We can see the database created as shown below.
Step 1: Run the Repository Assistance to Configure the Workspace and Repository.
Step 2: Welcome screen will appear and then click on Next to go to the next steps.
Step 3: Now enter the Host Name, Port Number, and Service Name for the Oracle Self-Service E-Billing OLAP database, and click Next.
Step 4: Select Manage Warehouse Builder Workspaces, and click Next.
Step 5: Select Create a new Warehouse Builder Workspace, and click Next.
Step 6: Select create a workspace with a new user as workspace owner and click Next.
Step 7: Enter repository owner User Name and a Password, and click Next.
Step 8: Enter the Username, Password, and Workspace and click Next.
Step 9: Enter the Password and Click on Next.
Step 10: Enter the following Tablespace information:
- Tablespace for Data: USERS
- Tablespace for Indexes: USERS
- Tablespace for Temporary Data: TEMP (Default)
- Tablespace for Snapshots: USERS
Step 11: Select the language required for the repository. The default is American English. Click Next.
Step 12: Click Next to skip selecting a user.
Step 13: The Repository Assistant Summary displays all the information related to the repository. Click Finish.
Step 14: The progress bar shows the installation status. When it finishes, click OK on the Installation Successful dialog.
Step 1: Select Design Center under warehouse builder as shown in below screenshot and it will display a login screen.
Step 2: Design Center is the main OWB client in which you design sources, targets, ETL mapping, and transformations.
In the below screen we have to enter the configuration details like User Name is acmeowd and Password as a bright, Host Name as localhost and the port number is default port number that is 1521 and click on OK button.
Step 3: After clicking on OK button we will get Logon Screen to logon the Design Center where you have to enter Username and Password and Click on OK button.
Step 4: After clicking on the OK button you will see the Design Center as shown in the below screen.
If this tutorial helped you AT ALL, please leave a comment and I’ll be happy to answer any of your questions in the comment section below!