Posted in Belajar, Oracle

Working with Flat File Data – Data Warehousing

Creating Source Module from flat files :

  • First create a flat file source module
    • Use Create Module Wizard
  • Create Flat file definitions by importing Metadata from the flat files
    • Use import metadata wizard to import flat files
    • Use flat file sample wizard to sample flat files

Source and Target Modules

  • Modules are storage objects that logically group source and target object definitions
  • Source modules are for data warehouse center or partner application to read data from.
  • Target modules are for the Data Warehouse center or partner application to write data to.

Defining a Target Warehouse Module

  • Create Target User, Target Module and Target schema
  • Creating external tables
  • Designing dimensions
  • Designing cubes

ETL (Extract, Transform, Load) Data Flow Mappings

Steps :

  • Examine an existing map
  • Use of mapping editor tool in warehouse builder
  • Design mapping between external table and dimensions
  • Design mapping between external table and cubes
  • And finally generate code for these mapping using mapping editor

Deploying Objects in Target Warehouse

Deployment : Final step in creating physical instance of the data warehouse target modules :

  • Deployment process generates scripts for creating data objects such as tables
  • Deployment process also generated scripts that load data into these objects

Use Control center Manager provided in Oracle Warehouse builder to

  • Deploy the external table
  • Deploy the cube and the referenced dimensions
  • Deploy and execute the ETL mappings

Check Metadata for Dependency

Inter Row Calculations using SQL model Clause

  • SQL model clause is a Business Intelligence tool
  • Query ouputs can be processed with inter row calculations
  • Reduces the need for importing database query outputs to spreadsheets to get business results
  • Increases performance, scalability and manageability of enterprise data warehousing applications

Filling Gaps in Sparse Data

  • Often a row may not have data for a particular column or dimension
    • Example : daily sales data with no sales on a particular day
  • This is known as sparse data
  • Difficult to run business analytics
  • Partitioned Outer join is an oracle feature that fills these gaps in sparse data
    • This is called densification of sparse data
  • This makes it easier to apply data warehousing applications to sparse data

High speed data loading adapted to data warehousing applications

  • Load flat flies as external tables
  • Loading in parallel is allowed
  • Faster than the corresponding SQL *Loader available in previous Oracle Versions

Rolling windows operations using oracle partition

  • Segregate past data using oracle partition
  • Use rolling window to
    • Add new data to new partition
    • Remove old partition
  • Reduces cost by
    • Improved disk utilization
    • Reduced impact on end user

Oracle ETL Infrastructure features

Oracle Application Express



Teman yang baik, teman yang memaksa anda untuk terus berkembang...

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s