Developing SQL Data Models Training (20768)

SQL Data Modelling Course Outline

• Module 1: Introduction to Business Intelligence and Data Modelling

  This module introduces key BI concepts and the Microsoft BI product suite.

  Lessons

  • Introduction to Business Intelligence
  • The Microsoft business intelligence platform

  Lab: Exploring a BI Solution

  • Exploring a Data Warehouse
  • Exploring a data model

  After completing this module, students will be able to:

  • Describe BI scenarios, trends, and project roles.
  • Describe the products that make up the Microsoft BI platform.

• Module 2: Creating Multidimensional Databases

  This module describes how to create multidimensional databases using SQL Server Analysis Services.

  Lessons

  • Introduction to Multidimensional Analysis
  • Data Sources and Data Source Views
  • Cubes
  • Overview of Cube Security
  • Configure SSAS
  • Monitoring SSAS

  Lab: Creating a multidimensional database

  • Creating a Data Source
  • Creating and Configuring a data Source View
  • Creating and Configuring a Cube
  • Adding a Dimension to a Cube

  After completing this module, students will be able to:

  • Describe considerations for a multidimensional database.
  • Create data sources and data source views.
  • Create a cube
  • Implement security in a multidimensional database.
  • Configure SSAS to meet requirements including memory limits, NUMA and disk layout.
  • Monitor SSAS performance.

• Module 3: Working with Cubes and Dimensions

  This module describes how to implement dimensions in a cube.

  Lessons

  • Configuring dimensions
  • Define attribute hierarchies
  • Implementing sorting and grouping attributes
  • Slowly changing dimensions

  Lab: Working with Cubes and Dimensions

  • Configuring Dimensions
  • Defining Relationships and Hierarchies
  • Sorting and Grouping Dimension Attributes

  After completing this module, students will be able to:

  • Configure dimensions
  • Define attribute hierarchies.
  • Implement sorting and grouping for attributes
  • Implement slowly changing dimensions

• Module 4: Working with Measures and Measure Groups

  This module describes how to implement measures and measure groups in a cube.

  Lessons

  • Working with measures
  • Working with measure groups

  Lab: Configuring Measures and Measure Groups

  • Configuring Measures
  • Defining Regular Relationships
  • Configuring Measure Group Storage

  After completing this module, students will be able to:

  • Configure measures
  • Configure measure groups

• Module 5: Introduction to MDX

  This module describes the MDX syntax and how to use MDX.

  Lessons

  • MDX fundamentals
  • Adding calculations to a cube
  • Using MDX to query a cube

  Lab: Using MDX

  • Querying a cube using MDX
  • Adding a Calculated Member

  After completing this module, students will be able to:

  • Use basic MDX functions.
  • Use MDX to add calculations to a cube.
  • Use MDX to query a cube.

• Module 6: Customising Cube Functionality

  This module describes how to customise a cube.

  Lessons

  • Implementing key performance indicators
  • Implementing actions
  • Implementing perspectives
  • Implementing translations

  Lab: Customising a Cube

  • Implementing an action
  • Implementing a perspective
  • Implementing a translation

  After completing this module, students will be able to:

  • Implement KPIs in a Multidimensional database
  • Implement Actions in a Multidimensional database
  • Implement perspectives in a Multidimensional database
  • Implement translations in a Multidimensional database

• Module 7: Implementing a Tabular Data Model by Using Analysis Services

  This module describes how to implement a tabular data model in PowerPivot.

  Lessons

  • Introduction to tabular data models
  • Creating a tabular data model
  • Using an analysis services tabular model in an enterprise BI solution

  Lab: Working with an Analysis services tabular data model

  • Creating an Analysis Services Tabular Data Model
  • Configure Relationships and Attributes
  • Configuring Data Model for an Enterprise BI Solution.

  After completing this module, students will be able to:

  • Describe tabular data models
  • Describe how to create a tabular data model
  • Use an Analysis Services Tabular Model in an enterprise BI solution

• Module 8: Introduction to Data Analysis Expression (DAX)

  This module describes how to use DAX to create measures and calculated columns in a tabular data model.

  Lessons

  • DAX fundamentals
  • Using DAX to create calculated columns and measures in a tabular data model

  Lab: Creating Calculated Columns and Measures by using DAX

  • Creating Calculated Columns
  • Creating Measures
  • Creating a KPI
  • Creating a Parent – Child Hierarchy

  After completing this module, students will be able to:

  • Describe the key features of DAX
  • Create calculated columns and measures by using DAX

• Module 9: Performing Predictive Analysis with Data Mining

  This module describes how to use data mining for predictive analysis.

  Lessons

  • Overview of data mining
  • Creating a custom data mining solution
  • Validating a data mining model
  • Connecting to and consuming a data mining model
  • Using the data mining add-in for Excel

  Lab: Perform Predictive Analysis with Data Mining

  • Creating a Data Mining Structure and Model
  • Exploring Data Mining Models
  • Validating Data Mining Models
  • Consuming a Data Mining Model
  • Using the Excel Data Mining add-in

  After completing this module, students will be able to:

  • Describe considerations for data mining
  • Create a data mining model
  • Validate a data mining model
  • Connect to a data-mining model
  • Use the data mining add-in for Excel