1 / 61

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

Using the Oracle Warehouse Builder. Czink óczki László oktató laszlo.czinkoczki@oracle.com 2008.04.17. Data Warehouse Versus Transactional Systems. A data warehouse is an enterprisewide database that is: Multisubject oriented Integrated across subjects on an enterprise base Time variant

kirk
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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.

More Related