Microsoft SQL Server 2014 – Designing BI Solutions: BI Infrastructure Design

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


Implementing a business intelligence solution is becoming a primary requirement in many companies, no matter the size. A BI solution provides a storage solution for data that’s gathered for analysis and reporting on how to improve business performance. In this course, you will learn how to design and build a BI solution and what elements are involved. You will also learn how to design the data warehouse database for the project. 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 developers and architects who want to learn how to plan for a BI infrastructure



Expected Duration

180 min.

Course Objectives

Course Introduction

The BI Infrastructure Data Sources

  • describe the elements of a BI infrastructure and explain data sources
  • Data Warehouse and ETL Solution for a BI Infrastructure

  • describe a data warehouse and ETL for a BI infrastructure
  • Analytical Data Models and Reporting

  • describe analytical data models and the reporting and analysis element in a BI infrastructure
  • Microsoft Product Roles in the BI Infrastructure

  • describe the role Windows Server, SQL Server, SharePoint, and Office applications play in a BI infrastructure
  • BI Infrastructure Upgrade Considerations

  • describe the considerations and guidelines for updating an existing BI solution
  • Overview of Building a BI Project

  • describe the key features in a BI project
  • The Infrastructure and Personnel to Build a BI Project

  • describe the applications, development, and test infrastructure as well as the personnel to build the BI project
  • Planning for the Size and Workload of an Infrastructure

  • plan system sizing and workload for a BI infrastructure
  • SQL Server BI Topologies

  • describe the different types of server topologies for a BI solution
  • Designing Scaling for a BI Solution

  • plan for scaling out a BI solution
  • Designing for High Availability

  • plan for high availability for a BI solution
  • Estimate MCR, CPU, and RAM Requirements

  • determine the MCR, CPU, and RAM requirements for a data warehouse database
  • Determining Storage Requirements

  • determine the storage requirements for a data warehouse database
  • Data Warehouse Database Design

  • describe the key tasks and steps in designing a data warehouse database in a BI project
  • Designing Dimension Modeling and Documentation

  • design a dimensional model for the business process using a matrix design and document it
  • Designing Dimension Keys, Attributes, and Hierarchies

  • design dimension keys, attributes, and hierarchies, given a scenario
  • Designing Slowly Changing Data and Unknown Values

  • design slowly changing data and unknown values for a dimension
  • Designing Time Dimension Tables

  • design time dimension tables based on a scenario
  • Designing Self-referencing Dimension Tables

  • design self-referencing dimension tables based on a scenario
  • Types of Columns for a Fact Table

  • describe the types of columns in a fact table and how to determine the level of grain
  • Types of Measures Stored and Common Fact Tables

  • describe the types of measures in a fact table and the types of fact tables commonly used
  • Planning Database File Storage

  • describe causes of data warehouse I/O activity and the considerations for placement of database files
  • Planning Table Partitioning

  • describe table partitioning, including benefits, and create a partitioned table
  • Planning Indexes for Queries

  • describe the considerations for using indexes and the types of indexes
  • Creating Indexes and Assessing their Performance

  • create indexes and assess their performance
  • Planning Data Compression

  • describe data compression and its benefits
  • Implementing Data Compression

  • implement compression
  • Planning Views

  • describe the guidelines for planning views and create views based on them
  • Exercise: BI Solutions Design Considerations