Duration: 5 Days

Overview

This course is designed to cover the SQL Server Analysis Services BISM Tabular Model. It is aimed at professional business intelligence (BI) developers: consultants or members of in-house BI development teams who are about to embark on a project using the tabular model. It includes hands on labs allowing the students to reinforce the concepts by creating practical examples of using the BISM tabular model.

Prerequisites

We are going to start with the basics of tabular models, so no prior knowledge is required . However, it would be useful to know certain core BI concepts such as dimensional modeling and data warehouse design. Some previous knowledge of relational databases, especially SQL Server, will be important when it comes to understanding how Tabular is structured and how to load data into it.

Course Outline

Module 1: Introducing the Tabular Model

• Semantic models in Analysis Services
• Understanding Tabular and Multidimensional
• Choosing the right model for your project
• Understanding DAX and MDX
• Introduction to Tabular calculation engines
• Analysis Services and Power BI

Module 2: Getting Started with the Tabular Model

• Setting up a development environment
• Working with SQL Server Data Tools
• Building a simple tabular model
• Deploying a tabular model
• Querying tabular models with Excel
• Querying tabular models with Power BI Desktop
• Working with SQL Server Management Studio

Module 3: Loading Data Inside Tabular

• Understanding data sources
• Understanding impersonation
• Understanding server-side and client-side credentials
• Working with big tables
• Loading from SQL Server
• Opening existing connections
• Loading from Analysis Services
• Loading from an Excel file
• Loading from a text file
• Choosing the right data-loading method

Module 4: Introducing Calculations in DAX

• Introduction to the DAX language
• Measures
• Calculated columns
• Calculated tables
• Writing queries in DAX
• Formatting DAX code

Module 5: Building Hierarchies

• Basic hierarchies
• Parent-child hierarchies

Module 6: Data Modeling in Tabular

• Understanding different data-modeling techniques
• Working with dimensional models
• Using views to decouple from the database
• Relationship types
• Normalization versus denormalization
• Calculated tables versus an external ETL

Module 7: The Tabular Presentation Layer

• Setting metadata for a Date table
• Naming, sorting, and formatting
• Perspectives
• Power View–related properties
• Key performance indicators
• Translations
• Selecting culture and collation in a tabular model

Module 8: Using DirectQuery

• Configuring DirectQuery
• Limitations in tabular models for DirectQuery
• Choosing between DirectQuery and VertiPaq

Module 9: Security

• User authentication
• Roles
• Administrative security
• Data security
• Creating dynamic security
• Security in DirectQuery
• Monitoring security

Module 10: Processing and Partitioning Tabular Models

• Automating deployment to a production server
• Table partitioning
• Processing options
• Processing automation
• Sample processing scripts

Module 11: Inside VertiPaq

• Understanding VertiPaq structures
• Reading VertiPaq internal metadata
• Memory usage in VertiPaq
• Understanding processing options

Module 12: Interfacing with Tabular

• Introducing the AMO and TOM libraries
• Introducing the TMSL commands
• Defining objects in TMSL
• TMSL commands
• Creating a database programmatically
• Analyzing metadata
• Automating project deployment

Module 13: Monitoring and Tuning a Tabular service

• Finding the Analysis Services process
• Resources consumed by Analysis Services
• Understanding memory configuration
• Using memory-related performance counters
• Using dynamic management views
• Automating monitoring info and logs acquisition
• Monitoring data refresh (process)

Module 14: Optimizing Tabular Models

• Optimizing data memory usage
• Designing tabular models for large databases
• Designing tabular models for near–real-time solutions

Module 15: Choosing Hardware and Virtualization

• Hardware sizing
• Optimizing hardware configuration
• Virtualization
• Scalability of an SSAS Tabular solution

Module 16: Log Operations in Analysis Services

• Location and types of logs
• Log file configuration settings
• MSMDSRV service log file
• Query logs
• Mini dump (.mdmp) files

To Hire a proven AMS Course Author and or Subject Matter Expert who teaches this class, Call 800-798-3901 Today!