Deploying and Reporting on ETL Jobs

1. Deploying and Reporting on ETL Jobs

2. Objectives • After completing this lesson, you should be able to do the following: • Use the Control Center Manager to deploy objects and mappings • Execute the mappings to load data into tables • Use the Repository Browser

3. Lesson Agenda • Logical versus physical implementation • Locations, Control Centers, Configurations • Deployment concepts • Control Center Manager • Interface • Deploy preferences • Execute mappings and view loaded data • Repository Browser • Start the listener • Start the HTML interface • Reports

4. Data Warehouse Implementation: Typical Steps 1. Define the source metadata. 2. Define staging area metadata. 3. Define a relational dimensional model. 4. Create process flows. 5. Deploy the mappings. 6. Execute or run the mappings. 7. View the data. Part 1 Part 2

5. Logical Versus Physical Logical model (Created metadata at a logical-design level) Physical implementation (Creates physical database structures)

6. Physical Implementation Configure for physicalimplementation. Fix errors. Validate (optional). Control CenterManager For laterdeployment Generate build andextraction scripts. To files Deploy warehouse scripts. To database Build and populate warehouse tables.

7. Configuring for Physical Implementation • Specify physical characteristics of generated objects, for example: • Indexes • Tablespace • Partitions • You can configure, validate, and deploy objects individually in succession or all objects simultaneously.

8. Setting Object Configuration

9. Deployment Concepts • The deployment step creates code from the logical models and stores this code in the Oracle database. • The code is also audited in the OWB runtime tables, so there is an audit trail for it.

10. Registering Locations After you have deployed the objects for a location or explicitly registered the location, you cannot edit or delete it. You can only update its password. You must unregister the location if you want to edit or delete it.

11. Unregistering Locations

12. Configurations and Control Centers • Each configuration maps to a control center. • Each control center maps to a workspace. • To work in a particular control center, ensure that the configuration associated with that control center is set to Active. Default configuration DEFAULT_CONFIGURATION DEFAULT_CONTROL_CENTER DEV_WORKSPACE Active configuration PROD_CONFIGURATION PROD_CONTROL_CENTER PROD_WORKSPACE

13. Active Configuration • Two ways to set a configuration as active configuration: Option 1 Option 2 Status bar shows the active configuration/control center

14. Control Center and Locations • A control center manages many locations. • A target location can be managed only by a single control center. • Source locations, by contrast, can be used by multiple control centers. 1 Control center Locations n Grey means that the locations are registered.

15. Quiz • At a time, only one configuration can be active. • True • False

16. Lesson Agenda • Logical versus physical implementation • Locations, Control Centers, Configurations • Deployment concepts • Control Center Manager • Interface • Deploy preferences • Execute mappings and view loaded data • Repository Browser • Start the listener • Start the HTML interface • Reports

17. Invoking the Control Center Manager

18. Control Center Manager Components Deployment tree is organized on locations available in the Control Center.

19. Control Center Manager Interface 3 Click Deploy on the toolbar. Click Default Actions to change the Deploy Action from None to Create. 2 1 Hold [Ctrl] to select multiple objects for deployment.

20. Various Deploy Actions If a mapping's design has changed after deployment, when you click Default Actions, the Deploy Action changes to Replace You can select Drop to drop an existing table. You can also upgrade an already deployed object.

21. Deploy Options and Preferences • The default deployment settings allow for the lowest number of user interactions. • You can customize your deployment environment, that is, the Control Center Manager user interface. • Either use the View menu in Control Center Manager to enable/disable various prompts. • Or set the Deployment preferences from the Tools menu in Design Center.

22. Deployment Preferences 1 2

23. Control Center Jobs A job is a deployment or execution submitted to the control center. Status in the Control Center Jobs panel Run Generate Completed successfully Errors Warnings You can double-click in the Status column to see the job details.

24. Job Details Window If the deployment is successful, you can click the Script tab to view the generated code. Scroll down here to view the warnings.

25. OWB Automatically Creates a Connector • Connectors are deployed automatically. You do not need to deploy it explicitly. Connector Already deployed

26. Quiz • You can set the following deploy actions for the objects depending on their current deploy status: • Upgrade • Drop • Create • Replace • Truncate

27. Lesson Agenda • Logical versus physical implementation • Locations, Control Centers, Configurations • Deployment concepts • Control Center Manager • Interface • Deploy preferences • Execute mappings and view loaded data • Repository Browser • Start the listener • Start the HTML interface • Reports

28. Data Warehouse Implementation: Typical Steps 1. Define the source metadata. 2. Define staging area metadata. 3. Define a relational dimensional model. 4. Create process flows. 5. Deploy the mappings. 6. Execute or run the mappings. 7. View the data. Part 1 Part 2

29. Executing Mappings Start

30. Viewing Data

31. Practice 9-1 Overview: Deploying, Loading,and Viewing Data • This practice covers the following topics: • Deploying the target tables and mappings • Loading the target • Viewing the data

32. Lesson Agenda • Logical versus physical implementation • Locations, Control Centers, Configurations • Deployment concepts • Control Center Manager • Interface • Deploy preferences • Execute mappings and view loaded data • Repository Browser • Start the listener • Start the HTML interface • Reports

33. Repository Browser • An HTML-based interface to view and generate reports on all repository metadata objects and the relationships between those objects.

34. Common Repository Browser Tasks • Identify a recently run mapping or process flow • Identify the latest deployments • Identify data objects deployed to a specific location • Remove old deployment or execution audit details • Re-execute, abort, or monitor a process run

35. Starting OWB Browser Listener • Run ./startOwbbInst.sh located in the [ORACLE_HOME]/owb/bin/unix folder • Set the oc4jadmin user password This message indicates you are ready to start the Repository Browser.

36. Starting the Repository Browser 1. Open a terminal window. 2. Change to the [ORACLE_HOME]/owb/bin/unix folder. 3. Run ./openRAB.sh -b /usr/bin/firefox. • It opens the browser with the login page:

37. Logging In to the Repository Browser

38. Browsing Design Center Reports Click here to open the Reports page

39. Browsing Design Center Reports

40. Browsing Control Center Reports

41. Types of Control Center Reports

42. Object Summary Report Click here to view its deployment history.

43. Deployment Report

44. Execution Summary Report Click here to view the execution report.

45. Execution Report This is the execution report of a process flow. Note that all the activities included in the process flow are listed here. Click the activity to get its execution details.

46. Quiz • You have the following two types of reports in the Repository Browser: • Design Center • Logical reports • Control Center • Physical Configuration Reports

47. Summary • In this lesson, you should have learned how to: • Use the Control Center Manager to deploy objects and mappings • Execute the mappings to load data into tables • Use the Repository Browser

48. Practice 9-2 Overview: Viewing Design and Control Center Reports • This practice covers the following topics: • Administering the warehouse by using the repository browser