slide1
Download
Skip this Video
Download Presentation
Czink óczki László oktató [email protected] 2008.04.17

Loading in 2 Seconds...

play fullscreen
1 / 61

Czink óczki László oktató [email protected] 2008.04.17 - PowerPoint PPT Presentation


  • 136 Views
  • Uploaded on

Using the Oracle Warehouse Builder. Czink óczki László oktató [email protected] 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Czink óczki László oktató [email protected] 2008.04.17' - kirk


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

Using the Oracle Warehouse Builder

Czinkóczki Lászlóoktató

[email protected]

2008.04.17

data warehouse versus transactional systems
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
dimensional data model with relational data types

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
multidimensional data types
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

what is oracle warehouse builder
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.
producing quality information
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)

bi environment from an owb perspective
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

owb 10 g r2 default installation with a single repository
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

projects
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

design center
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

objects within a project1
Objects Within a Project

Files

Applications

objects within a module
Objects Within a Module

Module node expanded to show its objects

Dimensions

Cubes

Tables

objects within a module1
Objects Within a Module

Mappings

Transformations

Data auditors

process flow of design and deployment
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

multiple named configurations preview
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>
the etl processes

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

omb plus tcl scripting within design client
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.)

owb mapping
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

adding an attribute
Adding an Attribute

New attribute

synchronizing operators and repository objects
Synchronizing Operatorsand Repository Objects

Mapping operator

Synchronize from

an operator to

a repository object.

Synchronize from

a repository object

to an operator.

deploying a mapping

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.

viewing the data using data viewer
Viewing the Data Using Data Viewer

The result of your work!

dimension tables
Dimension Tables
  • Dimension tables have the following characteristics:
    • They contain textual information that represents the attributes of the business.
    • They contain relatively static data.
determining granularity
Determining Granularity

Year?

Quarter?

Month?

Week?

Day?

dimensional design using owb
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.

create dimension wizard levels

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.
create dimension wizard level attributes
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.
defining a cube using the wizard

SALES cube

Dimensions

Products

Promotions

Channels

Customers

Times

Measures

Amount

Quantity

Cost

Defining a Cube Using the Wizard

Products

dimension

Promotions

dimension

Sales_Products_FK

Sales_Promotions_FK

Sales_Channels_FK

Sales_Customers_FK

Channels

dimension

Customers

dimension

Sales_Times_FK

Time

dimension

mapping to load the sales cube
Mapping to Load the SALES Cube

Join condition:

INGRP1.ID = INGRP2.ORDER_ID And INGRP1.CUSTOMER_ID = INGRP3.CUSTOMER_ID And INGRP1.PROMOTION_ID = INGRP4.ID

ad