Call Us: +32 2 466 00 16
Email: info@u2u.be
Follow Us:

Microsoft SQL Server Analysis Services

3 days
USQLAS
3 days

Upcoming Sessions

Date:

Format:

Price:

Location:

Book now

Date:

Format:

Price:

Location:

Book now

Interested in a private company training? Request it here.

Introduction

Before building Analysis Services cubes we must first learn why and when cubes are a better alternative than data warehouses. This introduction also discusses the difference between the tabular and the multi-dimensional version of Microsoft Analysis Services.

  • The need for OLAP and Analysis Services
  • Business Intelligence in SQL Server
  • Tabular versus multi-dimensional

Loading the relational data

An Analysis Services cube pulls its data from relational data sources. This modules shows how we can connect to those sources, how we can specify which tables should be accessible, and how we can make modifications to the source tables via data source views. Al this is done via Visual Studio, which is introduced in this module as well.

  • Creating Analysis Services projects in Visual Studio
  • Data Sources and Impersonation
  • Data Source Views
  • Exploring the data
  • LAB: Getting started with SSAS in Visual Studio

Building and deploying dimensions

Dimensions are the most important building blocks for creating cubes. Dimensions consist of all sorts of objects such as attributes and hierarchies. This module first introduces that terminology, then creates basic dimensions via the wizard and then goes and refines these via the editor.

  • Dimension terminology
  • Building dimensions with the wizard
  • Fine-tuning dimensions in the editor
  • Handling attribute-relationships
  • LAB: developing SSAS dimensions

Building and deploying cubes

Cubes are the only objects that are directly queried by the users. This makes them the most important object in the Analysis Services product. In this module we first learn about cube specific terminology, such as measures and measure groups, then we build a basic cube via the wizard and we see some fine-tuning of these cubes with the editor. All the more advanced cube features are discussed in the following modules.

  • Cube terminology
  • Using the cube wizard
  • Additive, semi-additive and none-additive measures
  • Building and fine-tuning cubes in the cube editor
  • Deploying cubes and dimensions
  • LAB: Building and deploying SSAS cubes

Browsing the data

Before we start enriching our cube with more advanced features we learn in this module how cubes can be accessed from typical business intelligence tools such as Excel, Power BI and Reporting Services.

  • Browsing from within Visual Studio and Management Studio
  • Browsing from Excel
  • Using cubes in Power BI
  • Browsing with Reporting Services
  • Access cubes from a custom .Net application
  • LAB: Querying cubes from within Excel

Processing Cubes and Dimensions

Cubes can be seen as a sort of cache on top of a data warehouse. But when the data warehouse changes the cache needs to be refreshed. This is what processing does. But when the cube needs to be processed frequently or the data volumes grow large we cannot simply reload all the data each time. That's where this module kicks in: it shows the different options available for refreshing a subset of the data in the most optimal way.

  • Different types of processing
  • Processing dimensions
  • Processing cubes
  • LAB: Full and incremental processing

Aggregation design

Aggregations are to a cube roughly what indexes are to a relational databases: They can speed up the querying... if the right aggregations are made. But without aggregations or with the wrong aggregations even simple queries can become horribly slow. But before this module dives into the details of how to setup aggregations it first covers two related topics: How is Analysis Services storing its data (ROLAP, HOLAP and MOLAP) and how can we store the factual data in smaller units (partitioning)?

  • MOLAP, ROLAP and HOLAP storage modes
  • Partitioning the cube
  • Creating aggregations
  • Usage based aggregation design
  • Pro-active caching
  • LAB: Aggregation design

Administration

As any server, also SSAS needs backups, security configuration, performance monitoring etc.

  • Installing Analysis Services
  • Backup and Restore
  • Securing Analysis Services
  • Monitoring
  • LAB: SSAS Administration

When analyzing business data, two challenges pop up frequently: the complexity of writing and maintaining queries which retrieve the proper data, and the performance issues which might surface when querying large amounts of data. OLAP (OnLine Analytical Processing) cubes provide fast aggregation querying over large amounts of data in a user-friendly way. In this course, you learn how to build, maintain and query OLAP cubes with Microsoft SQL Server Analysis Services 2019 (or earlier).

After completing the course, students will be able to create and manage Analysis Services cubes. They will also be able to process cubes and design aggregations.

This course is intended for developers and administrators who want to learn the skills to develop Analysis Services cubes on SQL Server 2019 or earlier. It can also be attended by administrators who want to acquire a deeper knowledge of the server they are managing.

Contact Us
  • Address:
    U2U nv/sa
    Z.1. Researchpark 110
    1731 Zellik (Brussels)
    BELGIUM
  • Phone: +32 2 466 00 16
  • Email: info@u2u.be
  • Monday - Friday: 9:00 - 17:00
    Saturday - Sunday: Closed
Say Hi
© 2021 U2U All rights reserved.