Oracle9i Warehouse Builder User's Guide -- Index

ABAP scripts, 8-32

access level, Oracle Workflow Builder client, 10-10

add objects with Metadata Import Utility, 11-7

administration environment, 2-7

administration tree, 2-7

agents

transparent gateways, 5-4

aggregation, mixed levels, 4-6

aggregator operator, 7-25

analyze statistics and targets, 8-21

application

generic file based, 5-14

Archive/Restore

archiving a project, 11-47

restoring a project, 11-50

setting up preferences, 11-45

using, 11-44

attribute sets

creating, 3-16

default, 6-4

Auto-Mapping dialog

copy, 6-20

Match by Name, 6-20

Match by Position, 6-20

binding operators, 6-4

bound name, 8-11

bridges

business areas, 4-39

versions, 11-36

bridge-type attribute set, 3-16

bulk processing, 8-21

business area, 4-38

bridges, 4-39

Business Tree tab, 4-39

creating a, 4-39

business tree

business area, 4-38

character set

flat file, 5-31

check constraint, 3-14

CHECK/INSERT, 8-11

cleansing data, 7-34

clipboard, 2-10

code viewer, 8-37

column

attribute sets, 3-16

default order, 3-11

generated, 4-9

reorder, 3-11, 3-12

commit, 2-25

commit frequency, 8-21

configuration

flat file physical properties, 8-26

flat file sources, 8-26

flat file targets, 8-26

loading properties, 8-15

mapping, 8-9

mapping operator attributes, 8-14

mapping physical properties, 8-16

mapping sources and targets, 8-21

mapping table operators, 8-9

Oracle Reports, 12-2

runtime parameters, 8-19

runtime parameters, SAP files, 8-32

SAP file physical properties, 8-32

table operator, 8-10

configuration parameters

dimension, 9-6

materialized views, 9-8

rules for export, 11-4

sequences, 9-10

warehouse module, 9-3

Workflow Queue Listener, 9-4

configuration properties

analyze statistics and targets, 8-21

attributes, 8-15

bound name, 8-11

bulk processing, 8-21

commit frequency, 8-21

conditional loading, 8-12

constraint management, 8-23

default audit level, 8-20

default operating mode, 8-19

default purge group, 8-21

delete (use for matching), 8-16

deployable, 8-19

Field Names in the First Row, 8-13

flat file operators, 8-13

hints, 8-22

insert (use for loading), 8-15

keys, 8-12

loading type, 8-11, 8-13

maximum number of errors, 8-21

partition exchange loading, 8-22

SQL*Loader parameters, 8-24

step type, 8-19

transformation operators, 8-14

update (operation), 8-16

update (use for loading), 8-16

update (use for matching), 8-16

configuring

data warehouse, 9-2

database links, 9-12

dimensions, 9-6

hash partitions, 9-21

materialized views, 9-8

new physical objects, 9-3

partitions, 9-17

physical instance, 9-2

range partitions, 9-21

range with hash subpartitions, 9-22

sequences, 9-10

views, 9-11

warehouse module, 9-3

connection information

non-Oracle database system, 5-3

Oracle Designer Repository, 3-3

remote function call (RFC), 5-16

SAP application, 5-16

source modules, 5-2

updating, 5-28

warehouse module, 3-3

connection type, 5-16

console

administration tree, 2-7

menu, 2-3

project tree, 2-5

toolbar, 2-3, 2-4

transformation tree, 2-6

tree view buttons, 2-3

utilities button, 2-3, 2-7

views, 2-5

wizard buttons, 2-3

constant operator, 7-9

constraint

change, 3-12

change UK to PK, 4-25

check, 3-14

DEFAULTIF and NULLIF, 5-32

fact table foreign key references, 4-3, 4-20

generated unique key constraints, 3-13

unique key constraint on levels, 4-5

view, 3-25

constraint management, 8-23

container

database sources, 5-5, 5-9

flat file sources, 5-13

flat files, 5-13

Oracle Designer Repository, 5-8

SAP application, 5-15

source module, 5-2

warehouse modules, 3-2

create definition

delimited flat file, 5-43

dimension, 3-7, 4-4

fact table, 4-19

flat file, 5-30

flat file sources, 5-30

hierarchy, 4-12

materialized view, 3-19

Oracle Workflow process, 10-10

transformation, 4-33

view, 3-23

creating

database links, 5-4, 12-48

diagram of source definitions, 5-29

dimensions, 3-7, 4-7

indexes, partitions, database links, 9-3

physical objects, 9-3

source modules, 5-5, 5-9

time dimension, 4-15

warehouse module, 3-2

data generator operator, 7-6

data source, 5-1

data transformation, 4-27, 7-1

data type

flat file fields, 5-32

portable, 5-32

data warehouse

configuration parameters, 9-2

database links

configuring, 9-12

creating, 5-4, 9-3

New Database Link dialog, 5-3

Oracle database system, 5-4

SAP, 5-16

source module, 5-2

warehouse module, 3-4

deduplicator operator, 7-24

default audit level, 8-20

default operating mode, 8-19

row based, 8-20

row based (target only), 8-20

set based, 8-19

set based fail over to row based, 8-20

set based fail over to row based (target only), 8-20

default purge group, 8-21

definition

attribute, 4-10

business area, 4-38

delimited file, 5-42

delimited flat file, 5-43

fixed-length file, 5-33

flat file, 5-30

materialized view, 3-19

transformation, 4-33

valid and invalid, 9-25

validate a set of definitions, 9-23

warehouse key column, 4-12

DELETE, 8-11

delete (use for matching), 8-16

DELETE/INSERT, 8-11

delimited file

definition, 5-42

deployable, 8-19

deploying

PL/SQL mappings, 9-29

scripts, 9-33

tables, 9-30

Designer Repository

import definitions into Warehouse Builder repository, 3-18, 4-26

desktop view, 3-23

diagram source definitions, 5-29

dimension

attribute definition, 4-10

basic definition, 4-3

create definition, 3-7, 4-4

define a level of aggregation, 4-9

denormalized dimension table, 4-6

dimension object, 4-3

generated scripts, 9-28

hierarchies, 4-11, 4-12

levels, 4-5

mapping, 6-5

New Dimension Wizard, 3-7, 4-4

products

create, 3-7, 4-7

property sheet, 3-10, 4-14

table property sheet, 3-10, 4-15

time dimension, 3-10, 4-15

update, 3-9, 4-12

validate example, 9-23, 9-24

dimension table

default column order, 3-11

physical aspects, 4-3

reordering the columns, 3-11

dimensions

creating, 3-7, 4-7

directives

Metadata Export Utility, 11-16

Metadata Import Utility, 11-18

Discoverer bridge

business areas, 4-39

display sets

default attribute sets, 6-4

display welcome page, 2-20

DISTINCT, 7-24

drawer, utilities, 2-7

editing

dimensions, 3-9, 4-12

invalid objects, 9-25

editor

dimension, 3-9, 4-12

materialized view, 3-23

object, 2-23

source module

generate diagram, 5-29

warehouse module, 3-6

environments

administration, 2-7

project, 2-5

export

log file, 11-39, 11-43

metadata export file, 11-3

metadata with Metadata Export Utility, 11-3

subset of objects, 11-3

exporting metadata, 11-39

Express bridge

business areas, 4-39

expression operator, 7-57

external process operator, 7-52

extract transformation, 8-2

fact table

create definition, 4-19

default column order, 3-11

define foreign key reference, 4-20

physical aspects, 4-3

property sheet, 4-23

reordering the columns, 3-12

update definition, 4-22

filter data with a transform, 4-33

Filter operator, 7-28

Find, 2-24

fixed-length file

definition, 5-33

flat file

character set, 5-31

configuration, 8-26

create definition, 5-30

data sample, 5-34

definition, 5-30

delimited

create format, 5-43

update format, 5-47

delimited format, 5-42

field constraint, 5-32

field mask, 5-32

field type, 5-32

fixed-length, 5-33

create format, 5-34

format, 5-38

generic file based application, 5-14

mapping sources, 8-26

mapping target, 8-26

multiple physical records per logical record, 5-31, 5-42

physical record length, 5-31

source module, 5-13

update format definition, 5-47

Flat File Sample Wizard, 5-30, 5-33

folder-in-use lock, 2-17

gateways

transparent, 5-4

generated

column name, 4-9

unique key constraints, 3-13

generated scripts

dimension, 9-28

general description, 9-23

mapping, 9-29

materialized view, 9-29

sequences, 9-30

tables, 9-30

types, 9-40

generating upgrade scripts, 9-40

generic file based application, 5-14

GROUP BY, 7-25

group by operation, 7-25

hash partitions, 9-17, 9-21

HAVING, 7-25

heterogeneous source, 5-2

hierarchies

create definition, 4-12

dimension, 4-11

hints, 8-22

ID column

rename as warehouse key column, 4-12

import

definition

database source, 5-19

database systems, 5-19

Import Metadata Wizard, 5-23

re-import, 5-23

definitions into a warehouse module, 3-18, 4-26

Metadata Import Utility, 11-7

selected modules, 11-19

validation rules, 11-20

Import Metadata Wizard, 5-33

invoking from navigation tree, 5-50

invoking from New Module Wizard, 5-18

importing metadata, 11-7, 11-9, 11-35

inbound reconcile, 8-2

indexes

creating, 9-3

INI file for SAP Integrator, 5-17

init.ora, 5-5

input roles, 7-36

INSERT, 8-11

insert (use for loading), 8-15

INSERT/UPDATE, 8-11

integrators, 5-2

intermediate results, 8-37

intersect, 7-14

job dependencies, 10-12

job scripts, 10-15

joiner operator, 7-16

key lookup operator, 7-10

keys, 8-12

library

transformation, 4-28

loading properties, 8-15

loading type, 8-11, 8-13

check/insert, 8-11

delete, 8-11

delete/insert, 8-11

insert, 8-11

insert/update, 8-11

merge, 8-12

none, 8-11

truncate/insert, 8-11

update, 8-11

update/insert, 8-11

log file

export to OMG CWM, 11-39, 11-43

logical constraints

view, 3-25

logical name

creating, 2-20

maximum length, 2-21

requirements, 2-21

logon to a repository, 2-2

mapping, 6-1

adding or removing operator groups, 6-22

Auto-Mapping dialog, 6-19

binding operators, 6-4

code viewer, 8-37

connecting attribute groups, 6-19

create, 6-7

data flow operators, 6-16

definitions, 6-2

dimension, 6-5

edit DML Type, 8-10

extract transformation, 8-2

flat file sources, 8-26

flat file targets, 8-26

generate, 8-35

generated scripts, 9-29

intermediate results, 8-37

operator attribute groups, 6-3

operator attributes, 6-3

operator display set, 6-3

operator properties, 6-3

operator property inspector, 6-6

SAP source, 6-14

selecting flat file operators, 6-13

selecting operators, 6-10

transformations, 7-1

triggers, 8-2

validation, 8-33

Mapping Editor

about, 6-5

keyboard operations, C-10

menu bar, C-2

mouse operations, C-7

toolbar, C-5

Toolbox, C-5

mapping flat file operators

sources, 8-26

mapping input parameter operator, 7-46

mapping output parameter operator, 7-49

mapping sequence operator, 7-5

materialized view, 4-4

configuring, 9-8

create definition, 3-19

default column order, 3-11

generated scripts, 9-29

performance, 4-4

refreshing, 9-10

reordering the columns, 3-12

update definition, 3-23

materialized view editor, 3-23

maximum number of errors, 8-21

menu, 2-3

MERGE, 8-12

metadata

detailed reports, 12-17

implementation reports, 12-19

lineage and impact analysis diagrams, 12-21

lineage and impact analysis reports, 12-20

summary reports, 12-16

Metadata Export Utility

directives, 11-16

Metadata Import Utility

directives, 11-18

Metadata Loader

Export Utility, 11-3

Import Utility, 11-7

Loader Utilities in MS-DOS window, 11-15

metadata loader (MDL), 11-2

command line utility, 11-15

exporting metadata, 11-5

import options, 11-10

import searching, 11-7

importing, 11-7, 11-9

large files, 11-2

multiple user considerations, 11-2

migrating a repository, 11-2

minus, 7-14

mixed levels of aggregation, 4-6

module

New Module Wizard, 5-16

source module, 2-17, 5-2

database definitions, 5-5, 5-9

flat files, 5-13

unique names in project, 2-20

warehouse module, 3-2

creating a business area, 4-39

module editor

source module

generate diagram, 5-29

warehouse, 3-6

multi-user access, 2-17

read-only mode, 2-18

read/write mode, 2-18

synchronization, 2-18

name

creating, 2-20, 2-21

default naming mode, 2-22

policies, 2-20

renaming an object, 2-21

name and address operator, 7-34

named attribute sets, 3-16

naming mode

default naming mode, 2-22

preferences, 2-22

naming policies, 2-20

navigation

search tree, 2-24

warehouse module navigation tree, 3-6

wizards, 2-19

navigation tree, 2-3

New Database Link dialog, 5-3

New Fact Table Wizard, 4-19

New Materialized View Wizard, 3-19

New Module Wizard, 5-14, 5-16

New Time Dimension Wizard, 4-15

New Transform Wizard, 4-33

New View Wizard, 3-23

NONE, 8-11

non-Oracle database systems, 5-3

object

children of first-class objects, 11-9

import selected modules, 11-19

import with Metadata Import Utility, 11-7

open_links, 5-5

Operator Property Inspector, 6-6

operators

aggregator, 7-25

constant, 7-9

data generator, 7-6

deduplicator, 7-24

definition of, 6-2

expression, 7-57

external process, 7-52

Filter, 7-28

joiner, 7-16

joiner, full outer join, 7-20

key lookup, 7-10

mapping flat files, 8-26

mapping input parameter, 7-46

mapping output parameter, 7-49

mapping sequence, 7-5

name and address, 7-34

post-mapping process, 7-44

pre-mapping, 7-42

set operation, 7-14

sorter, 7-31

splitter, 7-21

transformation, 7-54

optimizer hints as a transformation, 8-9

Oracle Designer, 9-12

import definitions into Warehouse Builder repository, 3-18, 4-26

source module, 5-8

Oracle Discoverer

business areas, 4-39

Oracle Express

business areas, 4-39

Oracle Portal, 12-15

Oracle Reports

configuration, 12-2

Oracle Transparent Gateway, 5-3

Oracle Workflow

client access level, 10-10

define process, 10-10

Deployment Wizard, 10-5

server, 10-5

ORDER BY, 7-31

outbound reconcile, 8-6

output components, 7-37

parameter

schedule job with parameters, 10-15

transformation, 4-27

partition exchange loading, 8-22, 9-22

partitions

configuring, 9-17

creating, 9-3

hash, 9-17, 9-21

range, 9-17, 9-21

range with hash subpartitions, 9-22

performance

materialized views, 4-4

warehouse keys, 4-3

physical aspects

dimension table, 4-3

fact table, 4-3

physical name

creating, 2-20

syntax, 2-20, 2-21

uniqueness requirements, 2-20

post-mapping process operator, 7-44

predefined time dimension elements, 4-15

Preferences

Clipboard/Recycle, 2-10

display welcome page on all wizards, 2-20

naming mode, 2-22

utility, 2-8

add a utility, 2-9

remove a utility, 2-10

update a utility, 2-10

Warehouse Builder Browser, 12-2

pre-mapping process operator, 7-42

print report, 12-4

Project, 2-15

project

unique module names, 2-20

project environment, 2-5

project tree, 2-5

property sheet, 2-23

default column order, 3-11

dimension, 4-14

object, 3-10, 4-14

table, 3-10, 4-15

fact table, 4-23

materialized view, 3-23

table, 3-10

query rewrite, 4-4

range partitions, 9-17, 9-21

with hash subpartitions, 9-22

RECNUM, 7-6

reconciliation

inbound, 8-2

outbound, 8-6

recycle bin, 2-10

reference materials

distributed database systems, 5-4

non-Oracle database systems, 5-4

Oracle8i Data Warehousing Guide, 4-2, 4-4

Ralph Kimball books, 4-2, 4-4

refreshing materialized view, 9-10

REMAINING_ROWS output group, 7-21

remote function call (RFC)

RFC connection, 5-16

SAP RFC connection, 5-17

rename warehouse object

view, 3-27

renaming, 2-21

reorder columns, 3-11

warehouse objects, 3-12

Reports

creating custom, 12-38

detailed, 12-17

implementation, 12-19

lineage and impact analysis, 12-20

lineage and impact analysis diagrams, 12-21

printing, 12-4

summary, 12-16

repository

logging on, 2-2

reserved words, B-2

ROW BASED, 8-20

ROW BASED (TARGET ONLY), 8-20

row sets

definition of, 1-7

rows

definition of, 1-7

rules

dimension object, 4-5

running scripts, 9-32

Runtime Audit Viewer, 10-22

purging runtime entries, 10-27

refreshing the view, 10-27

viewing by date range, 10-24

viewing the job audit, 10-17

viewing the job instance audit, 10-18

viewing the task audit, 10-19

viewing the task details audit, 10-21

runtime parameters, 8-19

sample

data sample for flat file, 5-34

SAP application

source module, 5-15

SAP file physical properties, 8-32

SAP Integrator

create definitions, 5-15

objects supported, 5-50, 5-51

required files, 5-17

SAPRFC.INI file, 5-17

search navigation tree, 2-24

secondary source

view, 3-25

semi-colon, 3-21

SEQUENCE, 7-7

sequences

configuring, 9-10

generated scripts, 9-30

services

heterogeneous services, 5-3

SET BASED, 8-19

SET BASED FAIL OVER TO ROW BASED, 8-20

SET BASED FAIL OVER TO ROW BASED (TARGET ONLY), 8-20

set operation operator, 7-14

intersect, 7-14

minus, 7-14

union, 7-14

union all, 7-14

short name

defining, 1-8

reserved words, B-2

software integrators, 5-2

SORTED INDEXES, 8-25

sorter operator, 7-31

source

data source, 5-1

source definition

update, 5-26

source module, 5-2

connection information, 5-2

SAP source, 5-16

create definition, 5-14

creating, 5-5, 5-9

database definitions, 5-5, 5-9

database links, 5-2

flat files, 5-13

import definitions, 5-19

Oracle Designer Repository, 5-8

SAP application, 5-15

Source Module Editor

generate diagram, 5-29

splitter operator, 7-21

SQL optimizer hints as a transformation, 8-9

SQL*Loader parameters, 8-24

star schema

basic example, 4-2

step type, 8-19

summarize data with a transformation, 7-25

synchronization, 2-18

syntax errors

extra semi-colon, 3-21

SYSDATE, 7-7

table

attribute sets, 3-16

default column order, 3-11

generate and deploy, 9-30

generated scripts, 9-30

reordering the columns, 3-12

table properties

dimension table, 3-10, 4-14

fact table, 4-24

tablespace, 9-8

testing deployed scripts, 9-32

time dimension

create, 3-10, 4-15

toolbar, 2-3, 2-4

commit, 2-25

property icon, 2-23

Transfer Wizard

log file

export, 11-39, 11-43

version, 11-36

transformation, 7-1

create definition, 4-33

custom example, 4-33

extract, 8-2

filter data, 4-33

group by operation, 7-25

library, 4-28

overview, 4-27

parameters, 4-27

unique names, 2-20

transformation operator, 7-54

transformation tree, 2-6

transparent

agents, 5-4

transportable tablespace, 9-17

tree view buttons, 2-3

trigger

general discussion, 8-2

TRUNCATE/INSERT, 8-11

union, 7-14

union all, 7-14

unique key

constraint on levels, 4-5

Universal Identifiers (Universal IDs), 11-7

UPDATE, 8-11

update

connection

data source, 5-28

flat file

format, 5-47

operations on specific columns, 8-16

source definitions, 5-26

fixed format file, 5-47

update (operation), 8-16

update (use for loading), 8-16

update (use for matching), 8-16

update definition

view, 3-26

UPDATE/INSERT, 8-11

user-defined attribute set, 3-16

using

Properties inspector, 9-3

Workflow, 10-5

utilities button, 2-3, 2-7

utilities drawer, 2-7

add a utility, 2-9

configuration, 2-8

remove a utility, 2-10

validating, 9-23

editing invalid objects, 9-25

validation codes, 9-25

validation messages, 9-25

validation

mappings, 8-33

rationale and general discussion, 9-23

rules for imported definitions, 11-20

versions

bridges, 11-36

Transfer Wizard, 11-36

view

configuring, 9-11

conventional, 4-4

create definition, 3-23

default column order, 3-11

logical constraints, 3-25

reordering the columns, 3-12

viewing

validation details, 9-25

Warehouse Builder Browser

administration, 12-45

browsing the favorites, 12-33

contents tab, 12-26

creating custom reports, 12-38

creating database links, 12-48

customizing the favorites pages, 12-36

detailed reports, 12-17

dropping database links, 12-51

editing database links, 12-50

implementation reports, 12-19

lineage and impact analysis diagrams, 12-21

lineage and impact analysis reports, 12-20

properties tab, 12-25

purging stale user information, 12-53

QA user role, 12-57

registering a custom report, 12-46, 12-52

registering a repository, 12-46

related tab, 12-27

reports tab, 12-28

roles, 12-55, 12-56

setting up preferences, 12-2

summary reports, 12-16

unregistering a repository, 12-52

viewing database links, 12-49

warehouse developer role, 12-57

warehouse user role, 12-57

Warehouse Builder MDL File Upgrade Utility, 11-1

Warehouse Builder Repository Assistant, 11-1

Warehouse Builder Runtime Assistant, 11-1

Warehouse Builder Transfer Wizard, 11-34

warehouse keys

column, 4-12

performance, 4-3

warehouse module, 3-2

connection information, 3-3

contents, 2-17

creating, 3-2

creating a business area, 4-39

database links, 3-4

displaying, 3-6

import definitions, 3-18, 4-26

navigation tree, 3-6

rename objects

materialized view, 3-23, 3-27

unique object names, 2-20

warehouse module editor, 3-6

warehouse object, 3-2

rename objects, 3-23

materialized view, 3-23

view, 3-27

welcome page, display, 2-20

WHERE, 7-28

wizard

display welcome page, 2-20

Flat File Sample Wizard, 5-30

Import Metadata Wizard

invoking from navigation tree, 5-50

invoking from New Module Wizard, 5-18

New Dimension Wizard, 3-7, 4-4

New Fact Table Wizard, 4-19

New Materialized View, 3-19

New Module Wizard, 3-2, 5-14, 5-16

New Time Dimension Wizard, 4-15

New Transform Wizard, 4-33

New View Wizard, 3-23

Oracle Workflow Deployment Wizard, 10-5

wizard buttons, 2-3

wizard navigation, 2-19

Workflow

defining the process, 10-10

deploying mappings, 10-5

process example, 10-12

Workflow Queue Listener

configuration parameters, 9-4

write lock, 2-17

Index

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W