Microsoft SQL Server: Implement Partitions and Custom Logic

This course is included in our On-demand training solution.


In SQL Server Analysis Services or SSAS, multidimensional model partitions are used to provide the physical storage for the fact data that is in a measure group. Custom logic can also be implemented into a model to provide for more granular aggregation on the data and better results for the user. This course will demonstrate how to create partitions, including local and remote partitions, and how to implement custom logic into a multidimensional model using calculated members, time intelligence, and named sets. 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.

Target Audience

Business intelligence developers who create reporting and analysis solutions and individuals interested in taking the Implementing Data Models and Reports with Microsoft SQL Server certification exam



Expected Duration

150 min.

Course Objectives

Course Introduction

Designing a Partition Strategy

  • describe partitions in a multidimensional model, including the types Local and Remote, aggregations, and design strategy
  • Understanding the Storage Modes for Partitioning

  • describe the storage modes used for partitions, including multidimensional online analytical processing or MOLAP, relational online analytical processing or ROLAP, and hybrid online analytical processing or HOLAP
  • Understanding Proactive Caching for Partitions

  • describe proactive caching for partitions
  • Creating a Local Partition by Filtering a Fact Table

  • create a local partition by filtering a fact table
  • Creating a Local Partition Using Tables or Queries

  • create a local partition using a table, view, or named query
  • Setting up for Remote Partition Creation

  • set up the configuration for remote partitions, including specifying valid server names, creating and deploying a secondary database, and enabling features in SQL Server Management Studio or SSMS
  • Creating Remote Partitions

  • create a remote partition
  • Enabling Writeback in a Partition

  • enable writeback on a partition in Cube Designer and SQL Server Management Studio or SSMS
  • Browsing and Managing Writeback Data in a Partition

  • browse, delete, disable, and convert writeback data
  • Designing Aggregations for a Partition Using the Wizard

  • design aggregations for a partition using the Aggregation Design Wizard
  • Editing and Deleting Partitions

  • edit and delete partitions in a multidimensional model
  • Merging Partitions

  • merge partitions in a multidimensional model
  • Using KPIs in a Multidimensional Model – Part I

  • describe and display KPIs
  • Using KPIs in a Multidimensional Model – Part II

  • describe and display KPIs
  • Creating Calculated Members in a Multidimensional Model

  • create calculated members using the Cube Designer
  • Creating Named Sets in a Multidimensional Model

  • create named sets using the Cube Designer
  • Defining Time Intelligence Calculations

  • define time intelligence calculations using the Business Intelligence Wizard
  • Creating Relative Measures

  • create relative measures, including using growth, YoY, same period last year
  • Using Ranking, Percentile, and Percentage of Total

  • implement rank, percentile, and percentage of total using MDX
  • Exercise: Creating Partitions and Custom Logic