Warehouse Architecture and Design Principles
SAS Education
SAS Education
Course Learning Paths
e-Learning (UK)
e-Learning (Ireland)
Live Web
Business Knowledge Series
Training Formats
Discounts and Offers
Project Based Training
Certification Bootcamps
SAS Professionals

Warehouse Architecture & Design Principles - WADPRIN

This 3-day course provides a broad coverage of the architecture and detailed physical design of a data warehouse - the storage, management and exploitation of data in an integrated information architecture. Anyone attending should already have an understanding of systems analysis and practical knowledge of the relevant SAS system features (see pre-requisites).

Download course description as PDF  Register now 


3 days - Classroom


After attending this course attendees will be able to create architecture and designs for an integrated information architecture,including:

  • the Enterprise storage layer of a data warehouse, based on the most common physical models used
  • OLAP data marts
  • data mining data marts
  • processes (ETL - Extract, Transform and Load) used in loading the Enterprise layer and in maintaining data marts
  • management and publishing of metadata
  • managing the environment, including archival and backup of data
  • basic principles of planning the infrastructure (hardware and software)
  • considerations for optimal performance in large data warehouses

Prerequisite Skills


  • familiarity with a wide range of SAS software used in data warehousing, SAS data management and data access features, SAS OLAP viewing/reporting tools
  • data warehouse or data mart project experience as an implementer (WCO or SAD) or analyst (WAN)
  • ability to read entity-relationship diagrams
  • knowledge of the basic principles of designing applications and data

Useful but not essential is experience with:

  • SAS/Warehouse Administrator software
  • databases and/or SPDServer
  • client/server SAS features
  • data mining
  • web technology
  • data warehouse applications
  • projects as warehouse architect (WAC)
  • entity-relationship modeling techniques and tools. SAS System Modules Used

Course Topics

The course will consist of three parts:

1. Presentations
2. Demonstrations of practical data warehouse techniques
3. Exercises based on a case study

The presentations cover the following:


  • Concepts of an integrated information architecture
  • Role of the 'Data Warehouse Architect'
  • Evolution of a data warehouse environment

Subject Modelling

  • Data models used in planning a date warehouse
  • Subject models and how to define them
  • Case study exercise: defining a Subject Model

Designing the Enterprise Layer

  • Definition of Logical modeling versus Physical modeling
  • Preparing a Logical model
  • Preparing a Physical model, choosing the model type
  • Defining and using Normalised data models
  • Defining and using Dimensional data models
  • Specific features of Dimensional models: facts and measures, conforming dimensions, time and history, querying a star schema
  • Case study: Dimensional modeling

Process Architecture and Design

  • Planning the Process Architecture
  • Considerations for Process Design: source identification, business rules, data quality, security
  • ETL – acquiring, preparing and loading data
  • Extraction – business rules, changed data capture
  • Transformation – validation & cleansing, integration, enrichment, transformation, transfer
  • Load – load techniques including 'slowly changing dimensions'
  • Case Study: Extract processing, Load processing
  • ETL administration
  • Use of 'standard techniques' in ETL

Security Considerations

  • Defining Security requirements
  • Architecture considerations, main types of Security
  • Design considerations for Security


  • What is metadata
  • Defining and using metadata

Data Marts

  • Role of the data marts in an integrated information architecture
  • Structure of data marts
  • Data mart physical models
  • Aggregates in data marts
  • Data mart design

Optimising the Enterprise Layer of the Data Warehouse

  • Scalability impacts, performance trade-offs
  • Optimisation strategy
  • Common optimisations used in the data warehouse
  • Case study: optimisation considerations

Process Architecture and Design part 2: Data Warehouse Management

  • Considerations for the end-to-end process
  • Scheduling
  • Distribution of data
  • Aging, Archiving and Recovery
  • Backup and Restore


  • Considerations for hardware choices, software choices
  • Estimating size, capacity planning

Summary and Close

  • The exercises are based on a case study and include group activities
  • Attendees work on requirements derived from real projects to create physical data models and design warehouse processes

Examples demonstrated during the class include a complete working ETL process for an enterprise-style data warehouse with slowly changing dimension history.

0845 402 9902

Delegate Testimonials

Terms & Conditions
Public Courses
Custom Training Courses