Overview
SQL Server Analysis Services (SSAS) is the technology from the Microsoft Business Intelligence stack to develop Online Analytical Processing (OLAP) solutions. Over the years, SSAS has evolved and has emerged as a leader in the area of analytical solutions. SSAS generally uses a data warehouse as a data source and contains data objects like cubes, dimensions, measures, aggregations hosted in a SSAS database. SSAS also offers options to select a variety of server modes like Multi-dimensional, Tabular, and PowerPivot. Multi-dimensional expressions (MDX) and Data Analysis Expressions (DAX) are the two primary query and expression languages, which are used to query SSAS database objects.
A typical analytical data model development process using SSAS involves the following steps:
- Reading data from data source and configuring a schema for the data model in SQL Server Data Tools (SSDT)
- Developing SSAS database objects using the schema
- Deploying and processing the database objects hosted in the data model
- Exploring, Testing, and Reporting the data hosted in the data model
Explanation
In this tutorial, we will explore this life cycle for Multi-dimensional as well as Tabular SSAS data models. The tutorial is structured in the following order:
- SSAS Installation
- SSAS Evolution – What’s new in SSAS 2016
- Basic Terms and Concepts
- Installation – Multi-dimensional and Tabular modes
- Configuring sample data
- Multidimensional Models
- Fundamentals
- Developing a multi-dimensional data model
- Deploying and Processing a multi-dimensional data model
- Querying the data model using MDX
- Tabular Models
- Fundamentals
- Developing a tabular data model
- Deploying and Processing a tabular data model
- Querying the data model using DAX
- Reporting
- Browsing SSAS data using Microsoft Excel
- Browsing SSAS data using Powerview
We will be using a sample ready-to-use multi-dimensional as well as tabular model as a base solution to explore different aspects of SSAS data models. Now that we understand the structure of this tutorial, let’s get started with the basic terms and concepts of SSAS.
Siddharth has more than 14 years of experience in the IT Industry, with more than a decade of experience in Business Intelligence and Analytics, for clients banking, logistics, government, Media Entertainment, products, life sciences and other domains. He has been a lead architect for a portfolio of 40+ apps, containing apps in web, mobile, BI, Analytics, data warehousing, reporting, collaboration, CMS, NoSQL and other technologies. He has several certifications and is a published author for online and print-media publications, as well as the MSDN Library.
In his present role, he remains responsible for architecture design, technology stack selection, infrastructure design, 3rd party products evaluation and procurement, and performance engineering. These applications use technologies like Elasticsearch / Lucene, MongoDB, SharePoint 2013 and 2010, jQuery-based framework like Highcharts and GoJS, SQL Server and the Microsoft Business Intelligence stack (SSIS, SSAS, SSRS, MDX, PowerPivot, PowerView), jQueryMobile, Bootstrap, iOS xCode framework, and many others.
- MSSQLTips Awards: Champion (100+ tips) – 2018 | Author of the Year – 2017 | Author Contender – 2016, 2018-2019