Preface

Microsoft SQL Server Analysis is one of the keystones of Microsoft's Business Intelligence (BI) product strategy. It is the most widely deployed OLAP server around the world. Many organizations, both large and small, have adopted it to provide secure and high-performance access to complex analytics.

MDX (for Multi-Dimensional eXpressions) is the BI industry standard for multidimensional calculations and queries, and is the most widely accepted software language in multidimensional data warehouse. Proficiency with MDX is essential for any professionals who work with multidimensional cubes. MDX is an elegant and powerful language, but also has a steep learning curve.

SQL Server 2012 Analysis Services has introduced a new BISM tabular model and a new formula language, Data Analysis Expressions (DAX). However, for the multi-dimensional model, MDX is still the only query and expression language. For many product developers and report developers, MDX still is and will be the preferred language for both the tabular model and the multi-dimensional model.

SQL Server 2016 is the biggest leap forward in Microsoft’s data platform history. SQL Server 2016 Analysis Services has also come with some great improvements and features for Multidimensional model. The DirectQuery mode can now be used to connect directly to SQL Server, SQL Server Parallel Data Warehouse (Microsoft Analytics Platform System), Oracle and Teradata. The DirectQuery mode has also significantly improved performance compared to the previous version. The SQL Server 2012 Management Studio (SSMS) came with a graphical user interface to configure and manage Extended Events within SQL Server Database Engine. Now this is also available for SQL Server 2016 Analysis Services. The Extended Events support through SSMS GUI allows a simplified way of monitoring your Analysis Services 2016 instances, both Tabular and Multidimensional.

Despite its popularity, very few books are dedicated to MDX. MDX-related books often limit their content to explaining the concepts of multidimensional cubes, the MDX language concept and its functions, and other specifics related to working with Analysis Services.

This book presents MDX solutions for business requirements that can be found in the real business world. You will find best practices, explanations of advanced subjects in full detail, and deep knowledge in every topic. Organized around practical MDX solutions, this book provides full, in-depth treatment of each topic, sequenced in a logical progression from elementary to advanced techniques.

This book is written in a cookbook format. You can browse through the contents and look for solutions to a particular problem. Each recipe is relatively short and grouped by relevancy, so you can find solutions to related issues in one place. Related recipes are sequenced in a logical progression; you will be able to build up your understanding of the topic incrementally.

This book is designed for both beginners and experts in MDX. If you are a beginner, this book is a good place to start. Each recipe provides you with best practices and their underlying rationale, detailed sample scripts, and options you need to know to make good choices. If you are an expert, you will be able to use this book as a reference. Whenever you face a particular challenge, you will be able to find a chapter that is dedicated to the topic.

We hope that you will become confident not only in using the sample MDX queries, but also in creating your own solutions. The moment you start creating your own solutions by combining techniques presented in this book, our goal of teaching through examples is accomplished. We want to hear from you about your journey to MDX proficiency. Feel free to contact us.