Designing a Data Warehouse (BI)

Exceptional SQL Server Training

Designing a Data Warehouse (BI)

Course Facts

3 day course
Our Trainers are Authorised Professionals
Live Online or Classroom-based
Certificate of completion
Maximum class size of 6 for scheduled courses
Unlimited support after your course
Courses at all levels: Introduction, Intermediate, Advanced and Masterclasses

Course Outline

Introduction To SQL Server Analysis Services (SSAS)
First Look at Multidimensional BISM
Understanding MDX
Data Sources and Data Source Views
Dimension Design

FULL OUTLINE

Related Courses

Private One-to-One Training

One-to-one tuition tailored to your exact requirements. Your choice of duration, dates and content.

Enquire Now

Team Training for Companies

Group bookings provide you with the most cost-effective way to train your team.

Enquire Now

Team Training and Bespoke Sessions

Booking a private training session allows you to focus on your exact needs on dates that work best for you or your team.

Sessions can be run online or in person, on your site or at our London training centre.
We manage training for many of the UK’s largest companies, taking care of all the admin, working with you to ensure the perfect course.
Our expert trainers will first understand your requirements and then tailor each session to your exact specification.
We offer a free consultation service to work out every detail of your course.

Prices for bespoke sessions with our expert trainers start at £525 + VAT.

Overview

This course is aimed at data warehouse and business intelligence designers, implementers and managers, but will also serve as a good basis for business and data analysts who will be involved in working with data warehouses and business intelligence deployments. All individuals who will be involved in some element of working with a data warehouse will benefit from gaining an understanding of the dimension modelling concepts that influence the final design of a data warehouse.

Delegates will be introduced to the basic terminology and concepts of a Kimball-based Data Warehouse architecture. The course covers Dimension Modelling Techniques introducing the basics of Fact Tables, Dimension Tables, Dimension Hierarchies and more.

Case studies are used throughout to demonstrate the concepts taught and to introduce the need for some of the more advanced modelling techniques such as Factless Tables, Enterprise Data Warehouse Bus Architecture, handling Slowly Changing Dimensions and Snapshots, Bridge Tables, Recursive Hierarchies.

As well as introducing the concepts and terminology of the data warehouse design, delegates will learn about the design cycle and processes, the ETL requirements and processes and ensuring that the model is fit for both current and future business requirements.

This course is generic, but use of SQL Server and its business intelligence service and tools (DBMS, SSIS, SSAS and SSRS) will allow for some hands on elements of data warehouse design to be carried out to re-enforce concepts.

This course can be followed by the SQL Server Business Intelligence courses to address the implementation of a data warehouse, multidimensional cube and reporting deployment in a SQL Server environment: (SSAS) SQL Server Analysis Services, (SSIS) SQL Server Integration Services, (SSRS) SQL Server Reporting Services.

Course Outline

Introduction To SQL Server Analysis Services (SSAS)

Business Intelligence Semantic Model (BISM) Multidimensional Mode
BISM Tabular Mode
SQL Server Analysis Services 2012/2014

First Look at Multidimensional BISM

Development, Administrative and Client Tools
Upgrading to SSAS 2012/2104
Using SQL Server Data Tools (SSDT - 2012/2014)
Using Business Intelligence Development Studio (BIDS - 2008)
Multidimensional Applications
The Visual Studio Environment
Creating a Project
Creating an Analysis Services Database
Creating a Data Source
Creating a Data Source View
Creating a Cube
Using the Cube Wizard
Deploying Browsing a Cube
Using SQL Server Management Studio (SSMS)
Querying the Cube in the MDX Query Editor

Understanding MDX

What is MDX
MDX Concepts
Measures and Measure Groups
Hierarchies and Hierarchy Levels
Members
Cells
Tuples
Sets
MDX Queries
MDX Expressions: Operators and Functions

Data Sources and Data Source Views

Supported Data Sources
Creating Data Source Views: DSV Wizard and DSV Designer
Data Source Views in Depth
Multiple Data Sources Within a DSV

Dimension Design

Working with the Dimension Wizard
Working with the Dimension Designer: Attributes, Attribute Relationships, User Hierarchies
Browsing the Dimension
Sorting Members of a Level
Optimising Attributes
Defining Translations in Dimensions
Creating Snowflake Dimensions
Creating Parent-Child Hierarchies

Cube Design

The BISM Multidimensional Mode
Creating a Cube Using the Cube Wizard
Cube Dimensions
Relationship Types: No Relationship, Regular, Fact, Many-to-Many, Referenced
Measures and Measure Groups
Calculated Members: Calculations
Creating Perspectives
Creating Translations
Browsing Perspectives and Translations

Advanced Dimension Design

Custom Rollups
Unary Operators
Naming Levels In Parent-Child Hierarchies
Customising Dimension Properties: Ordering, All Member, Default Member, Unknown Member, Error Configurations, Storage Mode, Grouping Members
Dimension Intelligence: Account, Time and Dimension
Server Time Dimension
Dimension Writeback