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