Data Modeling and BI with SQL Server

The focus of this course is on creating a managed enterprise BI solution; how to implement both multidimensional and tabular data models, and how to create cubes, dimensions, measures, KPIs.

The class uses a hands-on approach concentrating on the tools and techniques to quickly and effectively create Dashboards, ScoreCards, Tablular and Matrix Reports as well as using many of the new Visualization tools available through PowerBI and Office365.


Working knowledge of data concepts and structures as well as a working knowledge of Microsoft Excel is needed for this course.

Course length: 5 days, Monday - Friday. 
Delivery: Online or in-class.

Please call (475) 328-2227 with questions.

Upcoming Classes

Course Overview

Day 1 - Data Modeling Concepts

  • Data Modeling Fundamentals

  • Facts

  • Dimensions

  • Measures

  • KPIs and Trends

  • Data Relationships

  • Pivot Tables & Data Cubes

Day 2 - Building a Physical Model

  • Creating a Physical Structure

  • Data Types 

  • Indexing and Partitioning

  • ETL Techniques

Day 3 - Reporting and Visualizations In Excel

  • Excel PivotTables

  • PowerPivot

  • PowerView

  • SSAS Tabular

  • SSAS Multidimentional

Day 4 - Reporting and Visualization

With PowerBI Desktop

  • PowerQuery

  • PowerBI

  • Buidling Dashboards

  • Tabular Reports

Day 5 - Building and Automation

  • SSIS

  • SQL Jobs

  • Azure Jobs

Learn the fundamental components of a data model. Creating facts, dimensions and measures. The creation of star and snowflake schemas. Create direct and indirect data relationships. Creating keys and calculated columns.

Building the physical structure of a Data Warehouse. Index and Key configurations. Choosing data types and creating functions.

Viewing the data model with Excel Pivot Tables, using PowerPivot and PowerView. Publishing Data Models to SSAS and Power BI.

Using PowerBI Destop to create visualizations. Create complex and interactive visualizations, including TopN Queries, Images, External URLs, Infographics and more. Running Totals, Period over Period, Moving Average and other standard reports as well.

Automate the ETL and Build of a Data Warehouse using Integration Services and other SQL Tools. Extract data from SQL, CSV, Excel, Access and other external sources.