• Online, Self-Paced
Course Description

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

Learning Objectives

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

Framework Connections

The materials within this course focus on the Knowledge Skills and Abilities (KSAs) identified within the Specialty Areas listed below. Click to view Specialty Area details within the interactive National Cybersecurity Workforce Framework.