The Master's Program: Business Intelligence (BI) Curriculum

Below is a description of SetFocus' Microsoft Business Intelligence Training curriculum for The Master's Program.

Utilizing SetFocus customized curriculum, we have organized a comprehensive and integrated Microsoft Business Intelligence training program which thoroughly covers the key technologies that you need to make the transition to this new world of Microsoft Business Intelligence implementation.  In addition to the outline below SetFocus has developed labs, projects, and exams to test appropriate knowledge transfer.

For a detailed description of the labs, projects, and exams incorporated into SetFocus' Master's Program: Business Intelligence Track curriculum, please contact a Recruiting Specialist today.

Introduction and Advanced Transact-SQL Programming

  • SQL Server Management Studio navigation and use
  • SQL Server 2008 Data Types
  • Retrieving Data
  • Grouping Data
  • Joining tables
  • Subqueries, Common Table Expressions
  • DML Statements for Inserting/Updating/Modifying Data
  • Implementing Data Integrity by Using Constraints and Triggers
  • Implementing Views
  • Implementing Stored Procedures
  • Implementing Transactions and Error Handling
  • SQL 2005 new features workshop
  • SQL 2008 new features workshop
  • Hierarchies and recursive querying
  • Working with dates/querying temporal data
  • Reading execution plans and optimizing queries
  • Audit trail processing with Database Triggers or Change Data Capture
  • Concurrency and locking

SQL Server Integration Services

  • Introduction to SSIS
  • SSIS Control Flow Operations
  • SSIS Data Flow Operations
  • Integrating SSIS with T-SQL code and stored procedures
  • SSIS Variables and Configurations and Logging
  • SSIS Event Handlers
  • SSIS Scripts
  • SSIS Package Management and Deployment
  • Running SSIS scheduled Jobs using SQL Server Agent
  • Integration of T-SQL Stored procedures with SSIS
  • Using Merge functionality in ETL Scenarios

SQL Server Integration Services Project

Data Warehousing and Dimensional Modeling Fundamentals

  • Study the Ralph Kimball methodology for Data Warehousing
  • Study differences between OLTP, Data Warehouse, and OLAP databases
  • Discuss uses of surrogate keys
  • Look at many different dimension and fact table patterns, across different industries:
  • Star and Snowflake schemas
  • Study different dimension relationships/types:
  • Slowly Changing Dimensions (and steps to implement)
  • Role Playing dimensions
  • Many to many relationships (we look at 4 examples, because this is the most difficult to grasp)
  • Bridge tables (a specific instance of many-to-many relationships)
  • Dimension hierarchies, multiple hierarchies and Dimension attributes
  • Common business dimensions (product, date, account/customer)
  • Dimension outriggers
  • Multiple fact tables at different levels of dimension granularity
  • General patterns and practices for loading dimension tables and fact tables
  • Degenerate Dimensions
  • Factless Fact Tables
  • Periodic Snapshot fact Tables
  • Junk Dimensions
  • Fully-additive and semi-additive facts

SQL Server Analysis Services

  • Basics of using SSAS to build OLAP cubes
  • Applying the different dimension models from the Data Warehousing/Dimension Modeling week to create actual OLAP databases
  • The SSAS Cube Editor
  • The SSAS Dimension Editor
  • MDX Calculations and Analysis Services KPIs (which we cover further in our MDX week)
  • Drillthrough Report Actions
  • Creating OLAP perspectives
  • OLAP storage methodologies (MOLAP, HOLAP, ROLAP)
  • Creating OLAP partitions and OLAP aggregations
  • Integrating SSAS with SSIS
  • XMLA to manage an OLAP environment
  • OLAP Security Roles
  • SSAS Data Mining
  • Backing up and restoring OLAP databases

MDX Programming

  • General MDX query syntax
  • MDX context
  • Hierarchical functions
  • MDX usage of dimension attributes versus dimension hierarchies
  • Date-based functions (ParallelPeriod, PeriodsToDate, LastPeriods, etc.)
  • Permanent Calculations and Named Sets
  • Ranking and TopN retrieval
  • Aggregation functions
  • KPI Programming

SSAS/MDX Project

SQL Server 2008R2 Reporting Services

  • Learn the basics of the SSRS report writer
  • Learn how to create reports with groups, parameters, subtotals, running totals, calculations, etc.
  • Learn how to create matrix (pivot table) reports
  • Learn how to leverage the latest charting enhancements in SSRS 2008
  • Learn SSRS Expressions
  • Learn how to incorporate SQL queries and stored procedures into reports
  • Learn how to schedule reports for automatic execution and deliver through report subscriptions
  • Learn the basics of integrating SSRS with .NET
  • Use subreports for more advanced output formats
  • Reporting against either relational or OLAP databases

Business Intelligence w/SharePoint 2010

PerformancePoint Services:

  • Creating KPI scorecards and analytic charts/grids
  • Creating Dashboard pages and dashboard filters
  • Create KPI scorecard hotlinks to related charts/grids
  • Incorporating content from Excel and SSRS
  • Publishing dashboards to SharePoint 2010

SharePoint 2010

  • Creating Business Intelligence Site Collections
  • Configuring SharePoint to receive Excel content (Trusted site locations)
  • Configuring a site collection to store document libraries for PPS, SSRS, and Excel Content
  • Scheduling SSRS reports for automatic deliver to SharePoint SSRS document libraries

Excel Services 2010

  • Creating Pivot Tables and Pivot Charts against OLAP data
  • Publishing Excel content to a SharePoint document library

PowerPivot

  • Creating Excel content using PowerPivot against different data sources
  • Integrating SharePoint 2010 with PowerPivot, and publishing PowerPivot content to SharePoint

Reporting Services

  • Additional SSRS advanced visualizations (creating maps in SSRS)
  • Integrating SharePoint 2010 with Reporting Services

SSRS/Business Intelligence w/SharePoint 2010 Project

Customized Final Team Project



Note: This curriculum is subject to change without notice.