Data Warehouse Design

AUDIENCE: Data Architects, Database Administrators, Data Analysts, Application Developers, and System Designers. PREREQUISITES: Participants should have a reasonable knowledge of data modelling and database design. DURATION: 3 days. Workshops and Lecture based. OBJECTIVES: Data Warehousing has evolved into a unique and key component of IT strategy for many organisations. Dimensional modelling is the proven technique for developing understandable, high-performance data warehouses and data marts. This course introduces data warehouses, demonstrates the techniques involved in developing dimensional models and shows how these models fit in to the process of building data warehouses. All techniques are illustrated by real world examples drawn from a range of industries. After taking this course, students should be able to: - Define the constituent parts of a data warehouse. - State the limitations of classic entity-relationship models in data warehouse design. - State the strengths of dimensional models and understand how such models are used in the development of data warehouses and data marts. - Gather relevant requirements and place these requirements into a proper context for use in dimensional model development. - Develop appropriate dimensional models using the approach given in the course. - Understand what metadata is and its importance in data warehouse design. - Make decisions regarding the implementation of dimensional models to produce efficient data marts and data warehouses. COURSE CONTENT: Introduction to Data Warehousing What is a data warehouse? Characteristics of a data warehouse. Constituent parts of a typical data warehouse, basic processes involved. Data marts. Online Analytical Processing (OLAP), ROLAP and MOLAP. Data Modelling Classical entity/relationship modelling, definitions, notation. What data models are used for. Relational databases. Limitations of relational databases. Introduction to Dimensional Modelling What is dimensional modelling? Star Schema. Relationship between dimensional modelling and entity/relationship modelling. Why is dimensional modelling used in data warehouse design? How dimensional models are used in enterprise data warehouse design. Integrating data marts, shared dimensions. Basic Dimensional Modelling Techniques Facts and dimensions. Snowflaked schema. Attributes of dimensions. Types of dimension - slowly changing, rapidly changing, large. Primary and foreign keys. Defining fact tables, granularity. Aggregation. Advanced Dimensional Modelling Many-to-many dimensions. Dimension roles. Hierarchies within dimensions. Deciding on the number of dimensions. Fact tables – further work on granularity. Times and different units of measure. Developing Dimensional Models Requirements gathering. Identifying facts, dimensions and data marts. Fact and dimension table detail. Validating the dimensional model. Identifying data sources. Mapping data from source to target. Aggregation. Metadata What is metadata? Source system metadata. Data staging metadata. DBMS metadata. Metadata catalogue. Architecture and Physical Design Deciding on the data warehouse architecture. Data, technical and infrastructure architecture. Moving from logical to physical. Data staging and loading.