SQL Server Analysis Services 2014/2012 - Tabular model with DAX

Course:   SSASTM
Duration:   3 Days
Level:   I
On our website at:   http://www.verhoef-training.com/courses/SSASTM.html
 
Course Summary

The course teaches how to create a fast analysis of data from various data sources within minutes, using Analysis Services Tabular models. The delegates will learn to create and manage server (SSAS Tabular) and standalone (PowerPivot) data models, querying these models using the DAX language, and optimize their performance. The course also includes integration with SharePoint and Power View reporting. The delegates will also learn when it is more appropriate to use tabular model over the traditional OLAP cubes and vice versa, and whether to convert OLAP cubes into tabular model or not. The course is suitable for participants who need to quickly and easily implement technology similar to OLAP cubes in the company, and want to avoid complicated solutions.

Topics Covered In This Course

Introduction to Tabular Model

  • BI semantic model - BISM
  • BISM differences against UDM
  • OLAP Cubes vs. tabular model
  • Installing Analysis Services in tabular mode
  • Installing PowerPivot for Excel

Creating Tabular Solutions

  • Creating a tabular project for Analysis Services
  • Creating a tabular model in PowerPivot for Excel
  • Importing data from data sources
  • Setting up PowerPivot and tabular projects
  • Connecting data from multiple tables using relationships
  • Using the code lists
  • Diagram view
  • Hiding system and helper columns
  • Updating data from the underlying databases
  • The default formatting of data
  • Analysis of the data model in Excel

Advanced Functionality

  • Calculated Columns
  • Hierarchies
  • Aggregates and calculated fields
  • Creating KPIs - Key Performance Indicators
  • Error handling in the formulas
  • Perspectives
  • Converting existing OLAP cubes into tabular mode
  • Performance optimization of solutions
  • Partitioning of data
  • In-memory vs. DirectQuery mode

Custom calculations Using the DAX Language

  • Introduction to the DAX language - Data Analysis Expressions
  • Functions for working with dates and times
  • Information Functions
  • Filtering and evaluation functions
  • Functions for controlling flow of logic
  • Mathematical functions
  • Statistical functions
  • Text functions
  • Time Intelligence functions - analyzing data across time periods
  • Execution Context, Filter Context and Row Context
  • Query optimization

Deployment of Solution

  • Deploying tabular model to the Analysis Services server
  • Connecting to tabular model from SharePoint 2010/2013
  • Deploying PowerPivot to SharePoint 2010/2013
  • Converting PowerPivot to Analysis Services tabular project and back
  • Processing tabular databases, tables and partitions
  • Reporting from tabular model in Power View
  • Visual data analysis using Power View
  • Using MDX queries in DAX mode
  • Maintenance of tabular models and projects
  • Incremental deployment and processing
  • Backing up and restoring databases, detaching and attaching
  • Synchronizing databases
  • Tabular models security
  • ReadOnly and ReadWrite mode
  • Workspace database
What You Can Expect

Upon successful completion of this course, students will be able to:

Who Should Take This Course

Data analysis, BI developers and advanced data consumers (e.g. financial controlling) who need to quickly and easily implement fast analysis of the corporate data, and want to avoid complicated solutions.

Recommended Prerequisites

Basic overview in data analysis, ability to write basic Excel formulas.

Training Style

Instructor led, with hands-on workshops.

Related Courses
Code Course Title Duration Level
SSRS1
SQL Server Reporting Services with BI Tools Part 1 - Report Builder, Data Tools, Cubes, and Tabular Reporting
5 Days
I
Details
MSBI
Accelerated Microsoft Business Intelligence: From SQL 2008 through SharePoint and Office 2007
4 Days
I
Details
SSASAA
SQL Server Analysis Services 2014/2012 - Advanced DAX Analytics
2 Days
II
Details
SSASAO
SQL Server Analysis Services 2014/2012 - Advanced Data Analysis and Optimization
3 Days
II
Details
SSRS2
SQL Server Reporting Services with BI Tools Part 2 - Report Server Admin, Extensive Tabular Modeling/Reporting, and C#/VB Extensions
5 Days
II
Details

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.