SQL Server Analysis Services or SSAS supports tabular modeling, which is a columnar database used to store data for analysis. Access to the data in a tabular model provides fast performance. It is easy for users to access and use because Data Analysis Expressions or DAX queries are used to gain access to the data, which is similar to Excel calculations in a spreadsheet. This course will discuss and show how to implement tabular models in SSAS, including how to create and manage the tables, create calculations to access the data stored, and how to manage the database. This course is one in a series of Skillsoft courses to help learners prepare for the 70-466 Implementing Data Models and Reports with Microsoft SQL Server certification exam.
Learning Objectives
Manage Tables
- start the course
- use a project template to create a new tabular model and describe the Tabular Model Designer Interface
- import data into a tabular model using the Table Import Wizard
- define relationships between tables using the model designer
- create hierarchies to define relationships between columns in a table
- hide and freeze columns to manage their visibility
- sort and filter columns
- create perspectives to define viewable subsets of a model
Implement Business Logic
- create calculated columns to aggregate data, including using Data Analysis Expressions or DAX
- create measures using a table's measure grid, including using Data Analysis Expressions or DAX to create the formula
- create Key Performance Indicators or KPIs
- create Data Analysis Expressions or DAX to implement tabular objects into a tabular model
- implement time intelligence using different functions to support Business Intelligence or BI analysis, including specifying a Mark Table
- implement context modification
Implement Data Access
- create partitions using Partitions Manager
- manage partitions using Partitions Manager
- process partitions using different modes
- configure the mode for data access for a tabular model
Manage Security and Backups
- configure server roles and SQL Server Analysis Services or SSAS database roles to implement security
- configure roles to grant permissions to a tabular model database
- implement dynamic security for custom security approaches
- implement row-level permissions
- back up a tabular database
- restore and import a PowerPivot model into an SSAS instance
- define a Multidimensional Expression or MDX script to import a partial PowerPivot model
Practice: Build a Tabular Model
- implement a tabular model, business logic, and data access as well as manage security