Unit-I

Introduction to Data Warehousing: Introduction, Necessity, Framework of the datawarehouse, options, developing datawarehouses, end points.

Data Warehousing Design Consideration and Dimensional Modeling:

Defining Dimensional Model, Granularity of Facts, Additivity of Facts, Functional dependency of the Data, Helper Tables, Implementation manyto-many relationships between fact and dimensional modelling.

Unit-II

 An Introduction to Oracle Warehouse Builder: Installation of the database and OWB, About hardware and operating systems, Installing Oracle database software, Configuring the listener, Creating the database, Installing the OWB standalone software, OWB components and architecture, Configuring the repository and workspaces.

Defining and Importing Source Data Structures: An overview of Warehouse Builder Design Center, Importing/defining source metadata,

Creating a project, Creating a module, Creating an Oracle Database module, Creating a SQL Server database module, Importing source metadata from a database, Defining source metadata manually with the Data Object Editor, Importing source metadata from files.

Unit-III

Designing the Target Structure: Data warehouse design, Dimensional design, Cube and dimensions, Implementation of a dimensional model in a database, Relational implementation (star schema),Multidimensional implementation (OLAP),Designing the ACME data warehouse, Identifying the dimensions, Designing the cube, Data warehouse design in OWB, Creating a target user and module, Create a target user, Create a target module, OWB design objects.

Creating the Target Structure in OWB: Creating dimensions in OWB,

The Time dimension, Creating a Time dimension with the Time Dimension Wizard, The Product dimension, Product Attributes (attribute type),Product Levels, Product Hierarchy (highest to lowest),Creating the Product dimension with the New Dimension Wizard, The Store dimension, Store Attributes (attribute type), data type and size, and (Identifier),Store Levels,

Store Hierarchy (highest to lowest),Creating the Store dimension with the New Dimension Wizard, Creating a cube in OWB, Creating a cube with the wizard, Using the Data Object Editor

Unit-IV

Extract, Transform, and Load Basics: ETL, Manual ETL processes, Staging, To stage or not to stage, Configuration of a staging area, Mappings and operators in OWB, The canvas layout, OWB operators, Source and target operators, Data flow operators, Pre/post-processing operators.

Designing and building an ETL mapping:  Designing our staging area,

Designing the staging area contents, Building the staging area table with the Data Object Editor, Designing our mapping, Review of the Mapping Editor, Creating a mapping.

Unit-V

ETL: Transformations and Other Operators: STORE mapping, Adding source and target operators, Adding Transformation Operators, Using a Key Lookup operator, Creating an external table, Creating and loading a lookup table, Retrieving the key to use for a Lookup Operator, Adding a Key Lookup operator, PRODUCT mapping, SALES cube mapping, Dimension attributes in the cube, Measures and other attributes in the cube, Mapping values to cube attributes, Mapping measures’ values to a cube, Mapping PRODUCT and STORE dimension values to the cube, Mapping

DATE_DIM values to the cube, Features and benefits of OWB.

Validating, Generating, Deploying, and Executing  Objects: Validating, Validating in the Design Center, Validating from the editors, Validating in the Data Object Editor, Validating in the Mapping, Editor, Generating, Generating in the Design Center, Generating from the editors, Generating in the Data Object Editor, Generating in the Mapping Editor, Deploying, The Control Center Service, Deploying in the Design Center and Data Object Editor, The Control Center Manager, The Control Center Manager window overview, Deploying in the Control Center ,Manager, Executing, Deploying and executing remaining objects, Deployment Order, Execution order.

Unit-VI

Extra Features: Additional editing features, Metadata change management, Recycle Bin, Cut, copy, and paste, Snapshots, Metadata Loader (MDL) exports and imports, Synchronizing objects, Changes to tables, Changes to dimensional objects and auto-binding, Warehouse Builder online resources.

Datawarehousing and OLAP: Defining OLAP, The Value of Multidimensional data, OLAP terminologies, Multidimensional architectures, Multidimensional views of relational data, Physical Multidimensional databases, Data Explosion, Integrated relational OLAP, Data sparsity and data explosion.

 

Practicals:

1.  Importing the source data structures in Oracle.

2.  Design the target data structure using Oracle

3.  Create the target structure in OWB (Oracle Web Builder)

4.  Designed  and build the ETL mapping 

5.  Perform the ETL process and transform it to data marts.

6.  Create the cube and process it in OWB.

7.  Generate the different types of reports in using Oracle.

8.  Perform the deployment of Warehouse

9.  Create the Pivot table and Pivot chart using some existing data or create the new data. 

10.  Import the cube in access and create Pivot table and chart.

Books:

Data Warehousing by Soumendra Mohanty, Tata McGrawHill  (Unit I: Chapter 1, 2 Unit VI: 14 ).

Oracle Warehouse Builder 11g, Getting Started by Bob Griesemer, PACKT Publishing, SPD.

(Unit II: Chapter 1,3 Unit III: Chapter 3,4 Unit IV: Chapter 5,6 Unit V: Chapter  7,8  Unit VI: Chapter 9) References:

DW2.0 The architecture for Next Generation of Datawarehousing by W.H. Inmon, Derek Strauss, Genia Neushloss, ELSEVIER.

Data Warehousing Fundamentals by Paulraj Ponnian, John Wiley.

Building the data warehouse by, W.H.Inmon, third Edition, John  Wiley. The Data Warehouse Lifecycle toolkit by Ralph Kimball, John Wiley.

Download Ebook – Data Warehousing [PDF]