MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition

Tomislav Piasevoli, Sherry Li

• Publisher: Packt Publishing
• Year: 2016
• ISBN:
  9781786467881
• Edition: 3
• Topic: Computer Science
• Subtopic: Data Warehousing
• Index: Computer Science

Over 70 practical recipes to analyze multi-dimensional data in SQL Server 2016 Analysis Services cubesAbout This Book• Updated for SQL Server 2016, this book helps you take advantage of the new MDX commands and the new features introduced in SSAS• Perform time-related, context-aware, and business related-calculations with ease to enrich your Business Intelligence solutions• Collection of techniques to write flexible and high performing MDX queries in SSAS with carefully structured examplesWho This Book Is ForThis book is for anyone who has been involved in working with multidimensional data. If you are a multidimensional cube developer, a multidimensional database administrator, or a report developer who writes MDX queries to access multidimensional cube, this book will help you. If you are a power cube user or an experienced business analyst, you will also find this book invaluable in your data analysis. This book is for you are interested in doing more data analysis so that the management can make timely and accurate business decisions.What You Will Learn• Grasp the fundamental MDX concepts, features, and techniques• Work with sets• Work with Time dimension and create time-aware calculations• Make analytical reports compact, concise, and efficient• Navigate cubes• Master MDX for reporting with Reporting Services (new)• Perform business analytics• Design efficient cubes and efficient MDX queries• Create metadata-driven calculations (new)• Capture MDX queries and many other techniquesIn DetailIf you're often faced with MDX challenges, this is a book for you. It will teach you how to solve various real-world business requirements using MDX queries and calculations.Examples in the book introduce an idea or a problem and then guide you through the process of implementing the solution in a step-by-step manner, inform you about the best practices and offer a deep knowledge in terms of how the solution works. Recipes are organized by chapters, each covering a single topic. They start slowly and logically progress to more advanced techniques.In case of complexity, things are broken down. Instead of one, there are series of recipes built one on top of another. This way you are able to see intermediate results and debug potential errors faster.Finally, the cookbook format is here to help you quickly identify the topic of interest and in it a wide range of practical solutions, that is – MDX recipes for your success.Style and approachThis book is written in a cookbook format, where you can browse through and look for solutions to a particular problem in one place. Each recipe is short, to the point and grouped by relevancy. All the recipes are sequenced in a logical progression; you will be able to build up your understanding of the topic incrementally.

MDX with Microsoft SQL Server 2016 Analysis Services Cookbook Third Edition

---

MDX with Microsoft SQL Server 2016 Analysis Services Cookbook Third Edition

Copyright © 2016 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

First published: August 2011

Second edition: August 2013

Third edition: November 2016

Production reference: 1241116

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham

B3 2PB, UK.

ISBN 978-1-78646-099-8

www.packtpub.com

Credits

Authors Tomislav Piasevoli Sherry Li	Copy Editor Safis Editing
Reviewers Dave Wentzel	Project Coordinator Shweta H Birwatkar
Commissioning Editor Wilson D'souza	Proofreader Safis Editing
Acquisition Editor Tushar Gupta	Indexer Mariammal Chettiyar
Content Development Editor Sumeet Sawant	Graphics Disha Haria
Technical Editor Sneha Hanchate	Production Coordinator Arvindkumar Gupta

About the Authors

Tomislav Piasevoli is a Business Intelligence (BI) specialist with years of experience working with Microsoft SQL Server Analysis Services (SSAS). He successfully implemented many still-in-use BI solutions, helped numerous people on MSDN forum, achieved the highest certification for SQL Server Analysis Services (SSAS Maestro), and shared his expertise in form of MDX cookbooks.

Tomislav currently works as a consultant at Piasevoli Analytics company (www.piasevoli.com) together with his brother Hrvoje. They specialize in Microsoft SQL Server Business Intelligence platform, SSAS primarily, and offer their BI skills worldwide.

In addition to his regular work, Tomislav manages to find the time to present at local conferences or to write an article or two for local magazines. His contribution to the community has been recognized by Microsoft honoring him with the Most Valuable Professional (MVP) award for six consecutive years (2009-2015).

A large portion of this cookbook is present in all editions, therefore I feel obliged to express my gratitude once again to all the people that influenced its contents or helped making it better. They are: Chris Webb, Greg Galloway, Marco Russo, Darren Gosbell, Deepak Puri, Hrvoje Piasevoli, Willfried Färber, Mosha Pasumansky, Teo Lachev, Jeffrey Wang, Jeremy Kashel, Vidas Matelis, Thomas Kejser, Jeff Moden, Michael Coles, Itzik Ben-Gan, Irina Gorbach, Vincent Rainardi, and my ex-colleagues at SoftPro Tetral company. Next, I appreciate Packt Publishing for giving me a chance to write the first edition of this book. In this third edition, I am thankful to Sumeet Sawant and Tushar Gupta for their help and patience. Dave Wentzel deserves a big thank you for making sure the recipes make sense and that they are laid out in an understandable and clear way. A huge thank you goes to Sherry Li, my dear partner in this project. Her friendly attitude and willingness to help meant a lot to me while I was struggling with some recipes. Speaking of recipes, there were few bright people that took significant part in forcing me to rethink the recipes repeatedly and, in that way, either inspired me or helped me make them better. They are: Snježana Škledar, Aleš Plavčak, Hrvoje Gabelica, and Philipp Trannacher. Thank you, guys! Finally, a thank you goes to my family, close friends, business partners, and clients for understanding why I partially neglected you while working on the book. I dedicate this book to my children, Petra, Matko, and Nina.

Sherry Li is an Analytic Consultant who works for a major financial organization with responsibilities in implementing data warehousing, Business Intelligence, and business reporting solutions. She specializes in automation and optimization of data gathering, storing, analyzing and providing data access for business to gain data-driven insights. She especially enjoys sharing her experience and knowledge in data ETL process, database design, dimensional modeling, and reporting in T-SQL and MDX. She has co-authored two books, the MDX with SSAS 2012 Cookbook and MDX with Microsoft SQL Server 2016 Analysis Services Cookbook, which have helped many data professionals advanced their MDX skill in a very short time. Sherry Li maintains her blog at bisherryli.com.

This book is dedicated to readers who are enthusiastic about Multidimensional modeling and MDX (Multi-Dimensional eXpressions). What I love to do the most is share knowledge, so it is wonderful knowing that the MDX Cookbook is a popular book! Readers who want to become proficient in MDX have given tremendous responses to the first two editions of the book. There is nothing that satisfies me more than knowing that this 2016 edition have even more to share with the readers. I owe tremendous thanks to Packt Publishing for giving me another opportunity to write this edition of the MDX Cookbook. Their first-class professionalism in book designing, editing, publishing and collaboration has impressed me during the entire book project. Special thanks to Sumeet Sawant who is a wonderful content editor, and Tushar Gupta who initiated the project.

Three years ago I was daring enough to take the challenge of working on the second edition of the MDX Cookbook. This third edition has brought me once again working side-by-side with Tomislav Piasevoli, who had this bold idea of adding two new chapters with contents that were never fully presented before in previous MDX books. His dedication to the readers and attention to details left me with a great impression. This 2016 edition would not be possible without his leadership. Thank you Tomislav for your commitment to collaboration, encouragement, and deep knowledge of MDX and cube design. I look forward to future collaboration. To Da...