Donate to ISKME

Resources
Collections
Providers

Sign in to see your Hubs
Login
Featured Hubs
2022 OER Partner Summit
Open Textbooks
#GoOpen
Professional Learning
Climate Education
OERizona
See all Hubs

Sign in to see your Groups
Login
Featured Groups
Adult Education Open Community of Resources
OpenStax Biology 2e
PA STEM Toolkit
Pathways Project | Language Teaching Repository @ Boise State
Student Advocacy
See all Groups

About
Help Center
About Hubs
OER 101

Open Author

Create a standalone learning module, lesson, assignment, assessment or activity

Submit from Web

Submit OER from the web for review by our librarians

Learn more about creating OER

Add Link
About creating OER

Advanced Search

Sign In/Register

Please log in to save materials. Log in

Export to Google Docs
Report

Summary
Table of Contents

This text book explain data warehousing architecture design and how it is implemented using oracle

title "Data warehouse Design Using Oracle" 2024 by Girija Narasimhan
under license"Creative Commons Attribution Non-Commercial Share Alike"

Version History

Cite this work

Unit 1 : Schema Design

Unit 2: Materialized View

Unit 3: Online Analytical Processing (OLAP)

Unit 4: Partition

Unit 5: SQL Model Clause

Unit 1 Schema Design

Unit Objective:

1.2 Dimension Table

1.3 Star Schema

1.3.1 Snowflake Schema

1.3.2. Galaxy schema or Fact cancellation schema

1.4 CREATE DIMENSION statement

1.5.1 Identical Level name

1.5.2. Don't repeat the same column as level in different dimension

1.6 Hierarchies

1.6.1. Hierarchy Using Join key

1.7.1 Extended_attribute_clause

1.8 ALTER DIMENSION

1.8.1 Alter Dimension using ADD

1.8.2 Alter Dimension using DROP

1.9. DROP Dimension

1.10 Display Dimension

1.11 Dimension status

1.12 Retrieve Dimension

1.13 Validate Dimension

1.13.1 Dimension_Exceptions

1.13.2 Revalidate Dimension status

Practical Exercise

Video Tutorial Link

Unit 2 Materialized View

2.1 Introduction

2.2 Base Table vs. View Table

2.3 View vs. Materialized View (MV)

2.4 Mode of Refresh Technique

2.5 Refresh option

2.5.1 Refresh Complete

2.5.1.1. Refresh complete using refresh option "F"

2.6 Refresh Fast

2.7 Refresh Force

2.8 Never Refresh Using ALTER Refresh Complete

2.9 Never Refresh Using ALTER Refresh Fast

2.10 Never Refresh using Alter Refresh Force

2.11 Create Materialized view Log MLOG$

2.12 Materialized view Log DMLTYPE$$

2.13 Materialized view Log WITH Clause summary

2.13.1 Materialized view Log WITH Clause Primary Key

2.13.2 Materialized view Log WITH Clause ROWID

2.13.3 Materialized view Log WITH Clause COLUMN NAME

2.13.4 Materialized view Log WITH Clause SEQUENCE$$

2.13.5 Materialized view Log OLD_NEW$$

2.13.6 Materialized view Log INCLUDING NEW VALUES

2.14 COMMENTS ON Materialized view

2.14.1 Drop comments on materialized view

2.15 Materialized view Using “For Update”

2.16 Materialized view Using Aggregate Function

2.17 Join Materialized view

2.18 Materialized view Status

2.19 Materialized view Build Methods

Practical Exercise

Unit 3 Online Analytical Processing(OLAP)

Unit Objective:

3.1 DIMENSIONAL MODELING

3.2 Slice Operation

3.3 Dice Operation

3.4 Drill Down Operation

3.5 Rollup Operation

3.6 Cube Operation

3.7 Grouping Sets Operation

Practical Exercise

Video Tutorial Link

Unit 4 Partition

Unit Objective:

4.1 Partition in Data warehousing

4.3 Various types of Oracle Partition

4.4 Range Partition

4.4.1 Range Partition Using Number

4.4.2 Range Partition using Alphabet

4.4.3 Range Partition Using Date

4.5 ALTER TABLE ...PARTITION

4.5.1 ADD PARTITION

4.5.2 Rename Partition

4.5.3 Merge Partition

4.5.4 Drop Partition

4.6 List Partition

4.6.1 LIST PARTITION ADD PARTITION USING “DEFAULT” VALUE

4.6.2 ALTER TABLE MODIFY PARTITION

4.6.2.1 ALTER TABLE MODIFY..ADD

4.6.2.1 ALTER TABLE MODIFY... DROP

4.7 Hash Partition

4.7.1 HASH PARTITION Using Coalescing

4.7.2 Compare Truncate, drop, coalsec

4.8 Composite partition

Display the partition Information

Display the sub partition information

4.9 PARTITIONED INDEX

4.9.1 Local Index

4.9.2. Global Index

Practical Exercise

Unit 5 SQL Model Clause

Unit Objective:

5.1 Introduction

Partition columns

5.2 Dimension by

5.2.1 Dimension by- unique column

5.2.2 Dimension by Multiple dimension using unique keys

5.2.3 Dimension by aliasing

5.2.4 Dimension by Expression

5.4 Partition by

5.5.1 Rules create new row

5.6 RETURN UPDATED ROWS

5.7 RETURN All ROWS

5.8 Don't use same column in dimension and measure

5.9 NULL not allowed in DIMENSION BY or MEASURES clauses

5.10 Empty String (' ')

5.11 Using For Loop

5.13 Iteration using until Clause

Practical Exercise

Exercise Solution

Unit 1- Schema Design Exercise Solution

Unit 2- Materialized View Exercise Solution

Unit 3- OLAP Exercise Solution

Unit 4 - Partition Exercise Solution

UNIT 5- SQL MODEL Exercise Solution

Data warehouse Design Using Oracle

Created Feb. 7, 2024 by Girija Narasimhan

alter table..modify..drop — fig4-45_4ummKh1.jpgalter table..modify..drop

alter table..modify..drop

Sub partition values — fig4-42-display_subpartition_values_vBt0TRl.jpgSub partition values

Sub partition values

Local prefixed index — fig4-46_local_prefixed_index.jpgLocal prefixed index

Local prefixed index

Remixed from "Data warehousing Design Using Oracle" by Girija Narasimhan

Subject:: Career and Technical Education
Level:: College / Upper Division
Material Type:: Activity/Lab, Case Study, Homework/Assignment, Interactive, Lecture Notes, Lesson Plan, Textbook

Standards

No Alignments yet.

Delete Resource?

Cite this work

MLA
APA

Narasimhan, Girija. "Data warehouse Design Using Oracle". OER Commons. Institute for the Study of Knowledge Management in Education, 07 May 2017. Web. 01 May 2024. <https://oercommons.org/authoring/21861-data-warehouse-design-using-oracle>.

Narasimhan, G. (2017, May 07). Data warehouse Design Using Oracle. OER Commons. Retrieved May 01, 2024, from https://oercommons.org/authoring/21861-data-warehouse-design-using-oracle.

copy citation

History

Remixed from:
"Data warehousing Design Using Oracle" 2024 by Girija Narasimhan

Discover

Resources
Collections
Providers

Community

All Hubs
All Groups

Create

Submit a Resource

Our Services

About Hubs
About OER Commons
OER 101
Help Center

My Account

My Items
My Groups
My Hubs

Subscribe to OER Newsletter

Connect with OER Commons Facebook, Opens in new window Twitter, Opens in new window

Donate to ISKME

Powered By

Terms of Service Privacy Policy DMCA

© 2007 - 2024, OER Commons

A project created by ISKME. Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 License.

Sign in / Register

Your email or username: *

Did you mean ?

Password: *

Forgot password? Show password

or

Sign In through your Institution

Create an account Register

or

Sign In through your Institution