Introduction
MDX is an elegant and powerful language, but also has a steep learning curve.
The goal of this chapter is to use some simple examples to demonstrate the fundamental MDX concepts, features, and techniques that are the foundations for further exploration of the MDX language.
The chapter begins with several basic techniques: putting multi-dimensional data onto query axes, cube space restriction, empty cell removal, and the important concept of unique names for members, tuples, and sets. From there, we shall turn our attention to a few more advanced features, such as using the MDX functions, creating calculations in the cube space, manipulating strings, writing parameterized queries, and conditionally formatting cell properties. This will form the basis for the rest of the chapters in this book.
SSAS 2016 provides a sample Analysis Services database, the Multidimensional Adventure Works DW. All the MDX queries and scripts in this book have been updated for Analysis Services 2016, and verified against the 2016 Enterprise Edition of the Adventure Works DW Analysis Services database. The majority of the MDX queries and scripts should also run and have been tested in SSAS 2008 R2 and also SSAS2012.
The Query Editor in SQL Server Management Studio (SSMS) is our choice for writing and testing MDX queries. SQL Server 2012 and 2016 come with a free tool: SQL Server Data Tools (SSDT) for cube developers. Just as the Business Intelligence Development Studio (BIDS) was the tool that we used for cube design and MDX scripting in SSAS 2008, SSDT is the tool we will use in this cookbook for cube design and MDX scripting for SSAS 2016.
