Czink óczki László oktató laszlo.czinkoczki@oracle 2008.04.17

1. Using the Oracle Warehouse Builder Czinkóczki Lászlóoktató laszlo.czinkoczki@oracle.com 2008.04.17

2. Data Warehouse VersusTransactional Systems • A data warehouse is an enterprisewide database that is: • Multisubject oriented • Integrated across subjects on an enterprise base • Time variant • Nonvolatile • Historical • Capable of handling unpredictable workloads

3. PRODUCT • prod A • prod B • prod C • prod D • prod E TIME • Year • Quarter • Month • Week • Day CUST • cust A • cust B • cust C • cust D • cust E Dimensional Data Model withRelational Data Types Dimension table Dimension table SALES • Prod • Cust • Time Dimension table Dimensional concepts • Dimension • Hierarchy • Level • Attribute

4. Multidimensional Data Types SALES cube Time Product Customer Sales dimensioned by product, customer, and time Data is stored in multidimensional cubes in the analytic workspace.Analytical workspace is stored in BLOB column of relational table

5. What Is Oracle Warehouse Builder? • Oracle Warehouse Builder (OWB) is an extensible framework for designing, deploying, and managing enterprise data warehouses, data marts, and e-business intelligence applications.

6. Producing Quality Information • Warehouse Builder enables the • Extraction, Transformation, and Loading of data • to produce quality information in the Oracle database. Businessintelligence Action Transformation Data Information Knowledge (Qualityinformation)

7. BI Environment from an OWB Perspective OWB Model, extract, transform, load, and manage BI data Create analytic content Share Business Intelligence OWB repository Relational Portal page Disco Plus BI Beans Sources and/or Disco Viewer Multi-dimensional(OLAP) • Relational • Flat files • Applications • Mainframe • SAP Spread-sheet add-in Reports BI Beans application Oracle database Tools using BI data

8. OWB 10g R2 Default Installationwith a Single Repository Created by the OWB Repository Assistant or Logon dialog box’s Get Started button Created by the repository owner Target schemas Repository owner Highly privileged database user • Create database objects: • - Tables - Dimensions, cubes - Mappings - Packages, … Repository user • - Audit tables - Run-time and deployment statistics - Physical location info - Design metadata - Possible target Can be a single repository Repository users Repository owner Target users

9. Projects • A project is the highest-level object in Warehouse Builder. • It is best to create projects after identifying the functional areas for the projects. Project A Staging Mapping Warehousemodule Transformations Source module Project B Operational data sources Data Mart Mapping Warehousemodule Warehousemodule Transformations

10. Design Center Menus Toolbar Project explorer Connection explorer Project explorer: Holds all information related to a single project Connection explorer: Holds all connections and control centers for the repository Global explorer: Holds all objects that are shared among users of the repository; this is a great place to share reusable components across projects Global explorer

11. Objects Within a Project Oraclemodules

12. Objects Within a Project Files Applications

13. Objects Within a Module Module node expanded to show its objects Dimensions Cubes Tables

14. Objects Within a Module Mappings Transformations Data auditors

15. Process Flow of Design and Deployment Design target object metadata. 1 Map source to targetwith transformations. 3 OWB client 2 Extract sourcemetadata: Validate, generate, and deploy the code. 4 • Relational databases • Flat files • Applications • Mainframe • SAP Derive and deploy BI reportinginfrastructure. OWB repository 5 Oracle 8i/9i/10g • OLAP • Flat files • BI tools • Non-Oracle databasesvia gateways Sources Target warehouse and data marts Extract and transform data. 6 Define A.Define B.Map A to B. Capture knowledge using “experts.” 7

16. Multiple Named Configurations: Preview • Named configurations for multiple deployments • No need to write scripts to change tablespace names, and so on (Multiple named configurations and locations covered in the Part 2 course) Logicaldesign PhysicalconfigurationDevelopment PhysicalconfigurationProduction • No logging • Nonparallel • Tablespace DEV_DATA • Table EMP Location <abc> • No logging • Parallel • Tablespace PROD_DATA • Table EMP Location <abc>

17. Flat files Operational External Server logfiles The ETL processes Extract Transform/Load Publish Subscribe Dependentdata marts TL TL B2C Staging areas Transformations E Access layers Portal B2B RDBMS Enterprise model(atomic data) Metadata repository

18. OMB*Plus tcl Scripting Within Design Client You can run OMB*Plus tcl scripts to perform any function available in the Design Center graphical interface. (For example, create, modify, delete, import, or examine your OWB metadata.)

19. Creating a Project

20. Creating an Oracle Source Module

21. Create Module Wizard: Connection Information

22. Editing the Oracle Database Location

23. Import Metadata Wizard: Filter Information

24. Selecting the Tables for Import

25. Viewing Data Using the Data Viewer

26. OWB Mapping • Extract, transform, and load (ETL) involves the movement and transformation of data from your sources to your targets. • Use OWB mappings to specify which source data objects provide data to which target data objects. • Mapping definitions reside in target modules. • Use OWB mappings to specify which source data objects provide data to which target data objects. Relational or flat file module Warehouse ordata mart module Table Column Column Map

27. Creating the Warehouse Target Module

28. Designing the LOAD_STG_GEOG Mapping

29. Drag the table operator onto the canvas. Creating a Staging Table

30. Adding an Attribute New attribute

31. Using a Filter Operator 1 3 2

32. Using the Expression Builder to Definea Filter Condition

33. Using a Joiner

34. Set Operation Operator

35. Using the Create and Bind Option to Create the Repository Table

36. Synchronizing Operatorsand Repository Objects Mapping operator Synchronize from an operator to a repository object. Synchronize from a repository object to an operator.

37. Validating the Mapping

38. Generating the Mapping

39. 3 2 1 Deploying a Mapping • Before you deploy a mapping, you must perform the following steps: 1. Deploy the source object used in the mapping. 2. Deploy the target object used in the mapping. 3. Deploy the mapping.

40. Executing a Mapping

41. Viewing the Data Using Data Viewer The result of your work!

42. Defining a Relational Dimensional ModelClassifying the Data Descriptors Time Metrics Dimensions Cubes

43. Dimension Tables • Dimension tables have the following characteristics: • They contain textual information that represents the attributes of the business. • They contain relatively static data.

44. Determining Granularity Year? Quarter? Month? Week? Day?

45. Dimensional Design Using OWB Defining Implementing Deploying Using wizard ROLAP MOLAP Using editor Implementing a dimension consists of specifying how the dimension and its data are physically stored.

46. Creating a Dimension Using the Wizard

47. Create Dimension Wizard: Storage Type

48. Create Dimension Wizard:Dimension Attributes

49. Use the arrow keys to change the order. Create Dimension Wizard: Levels • Specify levels in the default hierarchy (STANDARD). • The highest level appears at the top.

50. Create Dimension Wizard: Level Attributes • By default, ID, NAME, and DESCRIPTION attributes are selected for all levels. • For the lowest level, all the listed attributes are selected.