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

Database and business intelligence developers and architects who want to learn how to plan for a BI infrastructure


Expected Duration
192 minutes

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).


BI Infrastructure Elements

  • start the course
  • describe the elements of a BI infrastructure and explain data sources
  • describe a data warehouse and ETL for a BI infrastructure
  • describe analytical data models and the reporting and analysis element in a BI infrastructure
  • describe the role Windows Server, SQL Server, SharePoint, and Office applications play in a BI infrastructure
  • describe the considerations and guidelines for updating an existing BI solution

Plan a BI Project

  • describe the key features in a BI project
  • describe the applications, development, and test infrastructure as well as the personnel to build the BI project
  • plan system sizing and workload for a BI infrastructure
  • describe the different types of server topologies for a BI solution
  • plan for scaling out a BI solution
  • plan for high availability for a BI solution

Data Warehouse Design

  • determine the MCR, CPU, and RAM requirements for a data warehouse database
  • determine the storage requirements for a data warehouse database
  • describe the key tasks and steps in designing a data warehouse database in a BI project
  • design a dimensional model for the business process using a matrix design and document it

Dimension and Fact Table Design

  • design dimension keys, attributes, and hierarchies, given a scenario
  • design slowly changing data and unknown values for a dimension
  • design time dimension tables based on a scenario
  • design self-referencing dimension tables based on a scenario
  • describe the types of columns in a fact table and how to determine the level of grain
  • describe the types of measures in a fact table and the types of fact tables commonly used

Data Warehouse Physical Design

  • describe causes of data warehouse I/O activity and the considerations for placement of database files
  • describe table partitioning, including benefits, and create a partitioned table
  • describe the considerations for using indexes and the types of indexes
  • create indexes and assess their performance
  • describe data compression and its benefits
  • implement compression
  • describe the guidelines for planning views and create views based on them

Practice: Designing BI Solutions

  • understand the considerations involved in BI solution design





Multi-license discounts available for Annual and Monthly subscriptions.