Business Objects Data Integrator Designer XI ETL Example

I recently received extensive On the Job Training in the Extract, Transform and Load of ASCII text files, SQL Server 2000 and Informix Databases, Data Warehouses and Data Marts using the Business Objects Data Integrator XI software package. I have found that this software is very dynamic and powerful. People who have a moderate level of Database and SQL Experience should be able to start coding ETL processes rather quickly.

The source code for all of the ETL Components are maintained in a Proprietary SQL Server 2000 Database Server Repository. The basic structure of the Data Integrator ETL Components are: Projects, Batch Jobs, Work Flows, Data Flows, Transformation Queries/Scripts/Mappings and Data Stores. The Data Stores use native SQL Server Data Server Names or ODBC DSNs for connecting to Input Source and Target Destination Database Tables.

The executable code can be run via the Data Integrator Designer IDE or by Exporting Execution Command Line Arguments to either Unix Shell Scripts or Windows NT Batch Command Files. These batch jobs are then run by an Apache Web Service Application Job Server called the Business Objects Data Integrator Web Administrator.

The original idea of the Example ETL Process Job_SalesOrg was taken from the Data Integrator XI Tutorial. The data in the sales_org.txt file and the local Microsoft SQL Server database server SHOWES(Windows NT) Target Database Table salesorg_dim were generated specifically for this example and contain bogus data fields.

I can provide copies of the Exported Job_SalesOrg.ATL file which is a Business Objects Proprietary formatted XML type of ASCII Text file, the Job_SalesOrg.BAT and Job_SalesOrg.TXT files to show how the executables work(upon request).

Step 1: Run Data Integrator Designer XI Software After Creating a SQL Server Repository

Notice below, that I created a local machine SQL Server database server showes DIRepo Repository and granted a ditest userid full dbo permissions. You can log into the software with a trusted userid or with Windows Authentication. After logging into the new showes DIRepo, I then created a Project Job_SalesOrg.

I created a local SQL Server server showes and DIRepo database.

I created a Project Job_SalesOrg.

Step 2: Create a Batch Job Job_SalesOrg.

I created a Batch Job Job_SalesOrg.

Step 3: Create a Work Flow WF_SalesOrg.

I created a Work Flow WF_SalesOrg.

Step 4: Create a Data Flow DF_SalesOrg which will contain the ETL Logic.

Notice below, that I created a sales_org.txt(input source format file), Query and a Target SALESORG_DIM table(destination table).

I created a Data Flow DF_SalesOrg which will contain the ETL Logic.

Step 5: Create/Edit the Formatted ASCII Text file sales_org.txt.

Notice below, that the sales_org.txt(input source format file) does not contain any data yet. There are four columns of data: SalesOffice, Region, DateOpen and Country.

Create/Edit the Formatted ASCII Text file sales_org.txt.

sales_org.txt

Create/Edit the Formatted ASCII Text file sales_org.txt.

Can actually view contained data.

Step 6: Create/Edit the Transformation Query.

Notice below, that we mapped SalesOffice, Region, DateOpen and NOT Country. This was intentional to show differences in mapped fields.

Create/Edit the Transformation Query.

Step 7: Create/Edit the Target Datastore Target_DS.

Create/Edit the Target Datastore Target_DS.

Create/Edit the Target Datastore Target_DS.

Step 8: Login to Data Integrator Web Administrator for Creating Batch Job.

Login to Data Integrator Web Administrator for Creating Batch Job for Repository.

Should see All Repository and Job Server Administration for specific userid.

Export Execution Command Line to create NT Batch Command File.

Step 9: Run the Batch Job from Web Administrator or from the Data Integrator Designer.

Execute batch job from the Data Integrator Designer.

Read Job_SalesOrg_Log Contents.

Should see all records written from the sales_org.txt file to the Target SALESORG_DIM Table from SQL Server view.

Should see all records written from the sales_org.txt file to the Target SALESORG_DIM Table from SQL Server view.

Should see all records written from the sales_org.txt file to the Target SALESORG_DIM Table from Data Integrator Designer view.

Go Back to:
http://Howes-IT-Going.com

This Webpage and Contents Created by Steven C. Howes © Howes-IT-Going 2003-2009 All rights reserved.

Steven C. Howes and Howes-IT-Going are NOT Responsible for Usage or Reproduction.
This Web Page, Business Objects Data Integrator Designer ETL Tool, Microsoft SQL Server 2000 Database and Word Document were for Educational and Discussion Purposes ONLY. Terms of use.