BusinessObjects XI R1/R2: Data Integrator: Extracting, Transforming and Loading Data

Scheduled Dates (sort by: location | date)

Course Overview

Using Data Integrator you create projects that Extract, Transform and Load (ETL) data from disparate sources to deliver more timely and accurate data that end users at your organization can trust. In this 4-day course you learn about data transformation jobs, techniques for capturing changes in data, handling errors, multi-user environment tasks, administering server and migration basics. You also learn how to use tools to audit, profile data, and manage metadata to assist you in the design and implementation of your ETL project. Initial activities in this course guide you through creating a few dimensions and a fact table.

These smaller activities focus on the tools and features discussed within each lesson. You will have a chance to apply different concepts learned from a few combined lessons in two 1-hour comprehensive workshops. At the end of the course, you will be able to put into practice what you have learned in terms of data flow design in a final workshop.

As a business benefit, by being able to create efficient ETL projects, your end users can use this transformed data to help improve operational and supply chain efficiencies, enhance customer relationships, create new revenue opportunities, and optimize your return on investment from enterprise applications.

Audience

This course is designed for individuals responsible for implementing projects involving the extraction, transformation and loading of data in batch jobs, administering and managing projects that involve Data Integrator.

Skills Gained

BusinessObjects Release 2 customers.This icon has been placed throughout the guide to identify features that are specific to BusinessObjects XI Release 2.These features include:

• Data profiling - this feature allows you to confirm the use of trusted data by providing you the ability to evaluate the source data before you design your ETL project

• Enhanced metadata reports - this feature is a new graphical user interface that allows you to view impact and lineage analysis, operational statistics metadata reports, and job execution dashboards. You can also use Auto Documentation to create a document outlining all objects used in Data Integrator project.

• XML_Pipeline transform - when using XML data, this transform enables you to process large XML files in your ETL project.

• Support for ANSI SQL-02 varchar behavior

Prerequisites

erience with these products or technologies will be helpful:

• Knowledge of data warehousing and ETL concepts
• Experience with Microsoft SQL Server
• Knowledge of normal forms of data and SQL language
• Experience using functions, elementary procedural programming and flow-ofcontrol statements, for example: If then Else, and While Loop statements

Course Outline

Lesson 1 - Datawarehousing concepts

• Describe normal forms of data
• Explain dimensional modeling

Lesson 2 - Understanding Data Inegrator

• List Data Integrator's benefits and key performance functions
• Describe standard Data Integrator components and management tools
• Explain object characteristics and relationship between object
• Describe the Data Integrator development process
• Explain and describe key areas in the Data Integrator Designer

Lesson 3 - Defining source and target metadata

• Explain what a datastore is
• Create a database datastore
• Change a datastore definition
• List types of metadata
• Capture metadata information from imported data
• Import metadata by browsing
• Explain what a file format is
• Create a new file format
• Handle errors in file formats

Lesson 4 - Validating, tracing, and debugging jobs

• List operations that Data Integrator pushes down to the database
• View SQL generated by a data flow
• Use descriptions with objects
• Use annotations to describe job, work, and data flows
• Validate jobs
• Trace jobs
• Use log files
• Use View Data with sources and targets
• Use the Interactive Debugger
• Set filters and breakpoints for a debug session

Lesson 5 - Creating a batch job

• Create a project, job, work flow, and data flow
• Add, connect, and delete objects in the workspace
• Explain source and target objects
• Add source and target objects to a data flow
• Explain what a transform is
• Understand the Query transform
• Describe the Query editor window
• Use the Query transform in a data flow
• Understand job execution
• Use template tables

Lesson 6 - Using built-in transforms and
nested data

• Describe and use the most commonly used built-in transforms in a data flow
• Understand hierarchical data representation
• Import metadata from XML documents
• Describe how transforms handle nested data
• Explain uses of nested data and the Query transform
• Unnest data
• Use the XML_Pipeline in a data flow

Lesson 7 - Using built-in functions

• Explain what a function is
• Differentiate between functions and transforms
• List the types of operations available for functions
• List the types of functions you can use in Data Integrator
• Use functions in expressions
• Use date and time functions and the date_generation transform to build a dimension table
• Use lookup functions to look up status in a table
• Use match pattern functions to compare input strings to patterns in Data Integrator
• Use database type functions to return information on data sources

Lesson 8 - Using Data Integrator Scripting Language and variables

• Understand variables
• Describe the variables and parameters window
• Explain differences between global and local variables
• Create global variables
• View global variables
• Set global variable values
• Explain language syntax
• Use strings and variables in Data Integrator scripting language
• Determine when to use custom functions
• Create a custom function

Lesson 9 - Capturing Changes in Data

• Explain what Changed Data Capture (CDC) is
• Using Changed Data Capture (CDC) with time-stamped sources
• Create an initial and delta load job
• Explain what history preservation is
• Identify history preserving transforms

Lesson 10 - Handling errors and auditing

• List levels of data recovery strategies
• Recover a failed job using automatic recovery and marking recovery units for executing jobs
• Run a job in recovery mode when automatic recovery fails
• Use Try/Catch blocks to specify alternate work flow options in the event of job errors

Lesson 11 - Supporting a multi-user environment

• Explain the stages of Data Integrator's development process in a multi-user environment
• Describe terminology used in a multi-user environment
• Explain repository types in a multi-user environment
• List repository maintenance guidelines
• Create a central repository
• Define a connection to the central repository
• Activate the central repository
• Add objects to the central repository
• Add objects with filtering to the central repository
• Check in objects
• Check out object
• Undo a checkout
• Get objects
• Label and compare objects
• View object history
• Delete objects

Lesson 12 - Migrating Projects

• Prepare for migration
• Describe migration mechanisms and tools
• Choose a migration mechanism
• Create multiple configurations in a datastore
• Use the Rename Owner tool to rename database objects
• Create a system configuration
• Distinguish between a phased and a versioned model for multi-user migration
• Add a project to a central repository
• Get the latest version of a project
• Update the project
• Copy contents between central repositories
• Import and export objects to a repository
• Export objects to a file
• Export a repository to a file
• Explain central repository security
• Create a secure central repository
• Define a connection to a secure central repository
• Implement group permissions
• View and modify permissions

Lesson 13 - Using the Administrator

• Log into the Administrator
• Describe the Administrator interface
• View available repositories
• Add a repository and user roles
• Set the status interval for displaying job executions
• Set the log retention period
• Execute, schedule, and monitor batch jobs
• Understand architecture, load balance index, and job execution in server groups
• Work with server groups and Designer options
• Add a server group
• Edit and remove server groups
• View Job Server status in server groups

Lesson 14 - Profiling data

• Explain what data profiling is
• Set up a Data Profiler repository and configure profiling tasks
• Add Data Profiler users
• Connect the Data Profiler Server to the Designer
• Submit a profiling task and view generated profile information
• Monitor profiling tasks in the Administrator

Lesson 15 -Managing Metadata

• Explain what metadata is
• Import and export metadata using the Metadata Exchange feature
• Use reporting tables and views to enable you to analyze metadata information

BusinessObjects XI R1/R2: Data Integrator: Extracting, Transforming and Loading Data / DM370R2