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.
Learning Objectives
Designing Partitions
- start the course
- describe partitions in a multidimensional model, including the types Local and Remote, aggregations, and design strategy
- 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
- describe proactive caching for partitions
Creating Partitions
- create a local partition by filtering a fact table
- create a local partition using a table, view, or named query
- 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
- create a remote partition
- enable writeback on a partition in Cube Designer and SQL Server Management Studio or SSMS
- browse, delete, disable, and convert writeback data
- design aggregations for a partition using the Aggregation Design Wizard
- edit and delete partitions in a multidimensional model
- merge partitions in a multidimensional model
Implementing Custom Logic
- describe and display KPIs
- create calculated members using the Cube Designer
- create named sets using the Cube Designer
- define time intelligence calculations using the Business Intelligence Wizard
- create relative measures, including using growth, YoY, same period last year
- implement rank, percentile, and percentage of total using MDX
Practice: Implementing Partitions and Custom Logic
- design and create partitions and implement custom logic into a multidimensional model