Relational Database Design using Oracle Warehouse Builder

1
Relational Database Design using Oracle
Warehouse Builder
1

• Jan Cordtz
• Product Manager Business Intelligence
• Oracle Denmark

2
Agenda

• Introduction
• Business Intelligence
• Logical design and physical design
• Creating Dimensions and Facts
• Implementing this Data Model
• The ETL process
• Putting Data into the Data Model using a mapping
• Quering the Datamodel

3
ltInsert Picture Heregt
Introduction to Dimensional Design
4
Turning Data into Profit.
3 NF
DW Model
OLAP Model
5
The Data ? Information Transformation

• Data becomes useful information when
• You understand its content and structure
  (metadata)?
• Its secure (unbreakable)?
• You can access it in a timely manner (grid)?
• You can trust it (quality)?

6
ETL Mappings

• Graphical, declarative modeling of Data Flows
• Map from Source to Target
• Integrated Data Quality
• NA standardization
• Match/Merge
• Profiling
• Generates SQL PL/SQL
• Merge, transportable tablespaces, sqlloader,
  table functions, streams, xml data types,
  BLOBS/CLOBS,
• Leverage custom data transformations
• Pluggable maps for reusability of logic

7
Metadata Services

• Dependency Management
• Data Lineage
• Impact Analysis
• Metadata Snapshots
• Change Management (diff, merge and reconcile)?
• Security (roles)?
• Multi-language Support (MLS via XLIFF)?
• Extensibility - user defined properties, objects,
  and associations
• Reporting (browser)?
• APIs (Scripting, SQL, PL/SQL)?
• Exchange (import/export)?

8
What about the 3NF Data Model ?

• Foreign Keys
• Primary Keys
• Normalization
• No Duplicates

9
ltInsert Picture Heregt
Introduction to Dimensional Design
End of section