Microsoft SQL Server 2014 – Designing BI Solutions: Data Models

This course is included in our On-demand training solution.


SQL Server Analysis Services enable the storage and analysis of data using data models that employ the Business Intelligence Semantic Model, BISM framework. Using a data model allows value to be added to the data being presented to the business users by using key performance indicators and aggregating the appropriate data. In this course, you will explore how to design multidimensional and tabular data models and how to create the different elements to make meaningful data for the business user reports. This course is one of a series in the Skillsoft learning path that helps individuals prepare for the Designing Business Intelligence Solutions with Microsoft SQL Server exam (70-467).

Target Audience

Database and business intelligence administrators and developers who are responsible for designing a BI infrastructure, IT professionals interested learning how to design BI infrastructures and how they’re related to data systems, and individuals interested in taking the Designing Business Intelligence Solutions with Microsoft SQL Server exam (70-467)



Expected Duration

150 min.

Course Objectives

Course Introduction

Business Intelligence Semantic Models

  • describe the two types of BISM data models and how they can be used to create a unified Analysis Services platform
  • Choosing the Appropriate BISM

  • describe the considerations for choosing an analytical data model for an SSAS solution
  • Choosing the Appropriate Schema for a Data Model

  • choose between a star and snowflake schema for a multidimensional data model
  • Designing a Relational Model for a Data Mart

  • identify how to design a relational model for a Data Mart
  • Designing Proactive Caching within SSAS

  • determine the appropriate Proactive Caching method within SSAS for different scenarios
  • Designing Multidimensional Cubes

  • describe how to plan for a multidimensional cube
  • Designing Data Sources for Analytical Models

  • design data sources for analytical models, including the types of data source and the considerations
  • Creating a Data Source for Analytical Models

  • create data sources and data source views, including relationships, for analytical models
  • Creating a Cube for a Multidimensional Model

  • use the cube wizard to create a cube for a multidimensional model
  • Defining Measures

  • configure measures, including identifying the aggregation method
  • Defining Measure Groups

  • define measure groups
  • Designing and Configuring Dimensions

  • design and configure dimensions in a multidimensional model
  • Configuring Relationships between Tables

  • design and configure relationships between tables
  • Designing and Creating Aggregations

  • design and create aggregations to improve cube performance
  • Designing Cubes for Drillthrough

  • design and configure cubes for drill-through
  • Designing Indexes and Index Views for Processing

  • design indexes, index views, and order by statement for SSAS processing
  • Creating Tables in a Tabular Model (Part 1)

  • import tables and configure relationships and measures for a tabular model
  • Creating Tables in a Tabular Model (Part 2)

  • design and configure attributes and dimensions for a tabular model
  • Designing Partition Strategies for a Cube

  • choose the appropriate partitioning strategy for a cube
  • Designing Aggregations for Partitions

  • design aggregations strategies for separate partitions
  • Creating Partitions for a Cube

  • create partitions for a cube, including specifying the aggregations and storage
  • Configuring Binding Options for Partitions

  • configure binding options for partitions
  • Configuring Data Compression on Fact Table Partitions

  • configure data compression options on fact table partitions
  • Designing and Configuring Write Back for a Cube

  • design and configure write back for a cube
  • Exercise: Designing Data Model and an SSAS Solution