Book Image

MDX with SSAS 2012 Cookbook - Second Edition

Book Image

MDX with SSAS 2012 Cookbook - Second Edition

Overview of this book

MDX is the BI industry standard for multidimensional calculations and queries. Proficiency with this language is essential for the realization of your Analysis Services' full potential. MDX is an elegant and powerful language, and 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 is the preferred language for both the tabular model and multi-dimensional model. MDX with SSAS 2012 Cookbook is a must-have book for anyone who wants to be proficient in the MDX language and to enhance their business intelligence solutions.MDX with SSAS 2012 Cookbook is packed with immediately usable, practical solutions. It starts with elementary techniques that lay the foundation for designing advanced MDX calculations and queries. The discussions after each solution will provide you with a solid foundation and best practices. It covers a broad range of real-world topics and solutions and provides you with learning materials to become proficient in the language.This book will guide you through the hands-on and practical MDX solutions, best practices, and many intricacies that hide within the MDX calculations and queries. We will start by working with sets, creating time-aware, context-aware calculations, and business analytics solutions, through to the techniques of enhancing the cube design when MDX is not enough. We will then move on to capturing MDX generated by SSAS front-ends and using SSAS stored procedures, and we will explore the whole range of MDX solutions for real-world BI projects.  
Table of Contents (16 chapters)
MDX with SSAS 2012 Cookbook
Credits
About the Authors
About the Reviewers
www.PacktPub.com
Preface
Index

Finding the last date with data


In this recipe we're going to learn how to find the last date with data for a particular combination of members in the cube. We'll start with a general calculation, not dependent on the time context, and later show how to make it time-sensitive, if required so.

Getting ready

Open SQL Server Data Tools (SSDT) and then open Adventure Works DW 2012 solution. Double-click on the Date dimension found in the Solution Explorer. Select the Date attribute and locate the property ValueColumn at the bottom of the Properties pane.

There's a value in that property. Column FullDateAlternateKey, of the Date type, is specified as the ValueColumn of the key attribute property, the Date attribute. This check is important because without that property filled correctly, this recipe won't work.

Next, start SQL Server Management Studio and connect to your SSAS 2012 instance. Click on the New Query button and check that the target database is Adventure Works DW 2012.

In this example...