Faculty of Information Technology, BUT

Course details

Data Warehousing in Oracle

IOW Acad. year 2017/2018 Summer semester 5 credits

Course is not open in this year
Close
Data warehousing concepts in Oracle, logical and physical model of a data warehouse, structures for efficient creation if data warehouses, process of extraction, transformation and loading, SQL constructions for data warehousing (aggregation, analysis, reporting, models), Oracle Warehouse Builder, efficiency of data warehouses, moving data between data warehouses.

Guarantor

Language of instruction

Czech

Completion

Classified Credit

Time span

26 hrs lectures, 12 hrs pc labs, 14 hrs projects

Assessment points

40 half-term test, 60 projects

Department

Lecturer

Instructor

Subject specific learning outcomes and competences

Students learn the basic terms and principles of data warehousing and becomes acquainted with process of extraction, transformation and loading. Then, students get to know the Oracle environment for data warehousing and learn to use it for creating of real data warehouses. 

Generic learning outcomes and competences

Students will be able to design a data warehouse and to create it in the Oracle tools. 

Learning objectives

The aim is to understand concepts of creating and using of data warehouses in the Oracle 11g environment; to become acquainted with tools and processes of data warehouse creation; to understand the ETL process from OLTP databases into a data warehouse; to learn analytic SQL constructions and usage of Oracle Warehouse Builder tool for definition of ETL process.

Prerequisite kwnowledge and skills

Knowledge of relational databases and the SQL language. 

Fundamental literature

  • Griesemer, B.: Oracle Warehouse Builder 11g: Getting Started. Packt Publishing, 2009.
  • Documentation of Oracle Warehouse Builder 11g.

Syllabus of lectures

  1. Data warehousing concepts in Oracle 11g - basic terms: data warehouse, ETL, OLTP and OLAP databases, approaches of data warehouse creation, facts and dimensions.
  2. Logical and physical model of a data warehouse (1) - problem of data modeling in data warehousing, terms of business, logical a physical model.
  3. Logical and physical model of a data warehouse (2) - physical models of data warehouses (star schema, snowflake schema), facts and dimension characteristics, transformation of models.
  4. Structures for efficient creation of data warehouses - size estimation, data partitioning, indexing, optimization, parallelization, data security.
  5. Process of extraction, transformation and loading (1) - ETL process and its parts, tools for ETL process, data extraction: data sources selection, mapping, data extraction methods.
  6. Process of extraction, transformation and loading (2) - Data transformation: anomalies in data, problems of transformation and their solution, tools and techniques, data quality.
  7. Process of extraction, transformation and loading (3) - data loading: data transmission techniques, loading process definition, data loading techniques, post-processing.
  8. SQL constructions for data warehousing - aggregation in data warehouses, analytical queries in SQL, regular expressions in SQL.
  9. Oracle Warehouse Builder (1) - tool description and definition of steps of the ETL process.
  10. Oracle Warehouse Builder (2) - accessing various data sources, metadata management, data security.
  11. Efficiency of data warehouses - efficiency of ETL process, performance tuning, parameters setting, use of materialized views.
  12. Optimization in data warehouses - optimization at various levels, optimization of analytic queries.
  13. Support for data warehousing in the Oracle DBMS - various Oracle tools to maintain data warehouses.

Syllabus - others, projects and individual work of students

The project includes design and creation of a data warehouse from a given OLTP database sample in Oracle Data Warehouse Builder, including the design of extraction, transformation and loading process and the analysis of data. 

Progress assessment

  • A project according to its specification (60 points).
  • Final written test (40 points).

Controlled instruction

  • A project of creation a data warehouse in the Oracle 11g. 
  • Written test at the end of a semester. 

Exam prerequisites

It is necessary to get at least 50 points from all ranked activities. 

Course inclusion in study plans

  • Programme IT-BC-3, field BIT, any year of study, Elective
  • Programme IT-BC-3, field BIT, 2nd year of study, Elective
Back to top