Developing SQL Data Models (20768C)

Developing SQL Data Models (20768C)

from 895.00
Date:
Delivery Method:
Quantity:
Purchase Now

SATV Voucher & GSA Checkout → SATV | GSA

Course ID: B3914 | Guaranteed to Run

Duration: 3 Days | 3-Month Access (MOC On-Demand)

Location: Flex - San Francisco or Live Online | Click here to schedule private course.

Overview:

Course 20768 is an instructor-led training (ILT) course which lasts 3 days. During this time, students will learn how to create managed enterprise BI solutions. Specifically, students will be taught:

  • How to implement both multidimensional and tabular data models

  • How to create cubes, dimensions, measures, and measure groups

This course is also available in the On-Demand delivery format with digital Microsoft Official Courseware (dMOC)

+ Who Should Attend

This course has been designed for experience database professionals, who need to operate in a BI Developer role to create enterprise BI solutions. You must have experience querying data using Transact-SQL, and your main responsibilities at work should include:

  • Implementing multidimensional databases by using SQL Server Analysis Services
  • Creating tabular semantic data models for analysis by using SQL Server Analysis Services

 

+ Course Outline

Module 1: Introduction to Business Intelligence and Data Modeling

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, you 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
  • Defining 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, you 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, you 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, you will be able to: 

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

Module 6: Customizing Cube Functionality

This module describes how to customize a cube.

Lessons 

  • Implementing Key Performance Indicators
  • Implementing Actions
  • Implementing Perspectives
  • Implementing Translations

Lab: Customizing a Cube

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

After completing this module, you 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 Power Pivot.

Lessons 

  • Introduction to Tabular Data Models
  • Creating a Tabular Data Model
  • Using an Analysis Services Tabular Data 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: Using 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

 

+ Prerequisites

You must have experience querying data using Transact-SQL.

+ Certifications

N/A

+ SQL Server Training