MDX with SSAS 2012 Cookbook


MDX with SSAS 2012 Cookbook
eBook: $35.99
Formats: PDF, PacktLib, ePub and Mobi formats
$30.59
save 15%!
Print + free eBook + free PacktLib access to the book: $95.98    Print cover: $59.99
$59.99
save 37%!
Free Shipping!
UK, US, Europe and selected countries in Asia.
Also available on:
Overview
Table of Contents
Author
Support
Sample Chapters
  • A wide range of time-related, context-aware, and business-related calculations
  • Combine MDX with utility dimensions
  • Illustration of techniques to enrich business intelligence solutions, aided by practical, hands-on Cookbook recipes

Book Details

Language : English
Paperback : 420 pages [ 235mm x 191mm ]
Release Date : August 2013
ISBN : 1849689601
ISBN 13 : 9781849689601
Author(s) : Sherry Li, Tomislav Piasevoli
Topics and Technologies : All Books, Big Data and Business Intelligence, Data, Cookbooks, Enterprise

Table of Contents

Preface
Chapter 1: Elementary MDX Techniques
Chapter 2: Working with Sets
Chapter 3: Working with Time
Chapter 4: Concise Reporting
Chapter 5: Navigation
Chapter 6: Business Analytics
Chapter 7: When MDX is Not Enough
Chapter 8: Advanced MDX Topics
Chapter 9: On the Edge
Index
  • Chapter 1: Elementary MDX Techniques
    • Introduction
    • Putting data on x and y axes
    • Skipping axes
    • Using a WHERE clause to filter the data returned
    • Optimizing MDX queries using the NonEmpty() function
    • Using the PROPERTIES() function to retrieve data from attribute relationships
    • Basic sorting and ranking
    • Handling division by zero errors
    • Setting a default member of a hierarchy in the MDX script
    • Chapter 2: Working with Sets
      • Introduction
      • Implementing the NOT IN set logic
      • Implementing the logical OR on members from different hierarchies
      • Implementing the logical AND on members from the same hierarchy
      • Iterating on a set in order to reduce it
      • Iterating on a set in order to create a new one
      • Iterating on a set using recursion
      • Dissecting and debugging MDX queries
      • Chapter 3: Working with Time
        • Introduction
        • Calculating the YTD (Year-To-Date) value
        • Calculating the YoY (Year-over-Year) growth (parallel periods)
        • Calculating moving averages
        • Finding the last date with data
        • Getting values on the last date with data
        • Calculating today's date using the string functions
        • Calculating today's date using the MemberValue function
        • Calculating today's date using an attribute hierarchy
        • Calculating the difference between two dates
        • Calculating the difference between two times
        • Calculating parallel periods for multiple dates in a set
        • Calculating parallel periods for multiple dates in a slicer
        • Chapter 4: Concise Reporting
          • Introduction
          • Isolating the best N members in a set
          • Isolating the worst N members in a set
          • Identifying the best/worst members for each member of another hierarchy
          • Displaying few important members, others as a single row, and the total at the end
          • Combining two hierarchies into one
          • Finding the name of a child with the best/worst value
          • Highlighting siblings with the best/worst values
          • Implementing bubble-up exceptions
          • Chapter 5: Navigation
            • Introduction
            • Detecting a particular member in a hierarchy
            • Detecting the root member
            • Detecting members on the same branch
            • Finding related members in the same dimension
            • Finding related members in another dimension
            • Calculating various percentages
            • Calculating various averages
            • Calculating various ranks
            • Chapter 6: Business Analytics
              • Introduction
              • Forecasting using the linear regression
              • Forecasting using the periodic cycles
              • Allocating the nonallocated company expenses to departments
              • Analyzing fluctuation of customers
              • Implementing the ABC analysis
              • Chapter 7: When MDX is Not Enough
                • Introduction
                • Using a new attribute to separate members on a level
                • Using a distinct count measure to implement histograms over existing hierarchies
                • Using a dummy dimension to implement histograms over nonexisting hierarchies
                • Creating a physical measure as a placeholder for MDX assignments
                • Using a new dimension to calculate the most frequent price
                • Using a utility dimension to implement flexible display units
                • Using a utility dimension to implement time-based calculations
                • Chapter 8: Advanced MDX Topics
                  • Introduction
                  • Displaying members without children (leaves)
                  • Displaying members with data in parent-child hierarchies
                  • Displaying random values
                  • Displaying a random sample of hierarchy members
                  • Displaying a sample from a random hierarchy
                  • Performing complex sorts
                  • Using recursion to calculate cumulative values
                  • Chapter 9: On the Edge
                    • Introduction
                    • Clearing the Analysis Services cache
                    • Using Analysis Services stored procedures
                    • Executing MDX queries in T-SQL environments
                    • Using SSAS Dynamic Management Views (DMV) to fast-document a cube
                    • Using SSAS Dynamic Management View (DMVs) to monitor activity and usage
                    • Capturing MDX queries generated by SSAS frontends
                    • Performing a custom drillthrough

                    Sherry Li

                    Sherry Li has worked with Microsoft SQL Server Business Intelligence stacks for many years. She lives in Phoenix, Arizona where she works for a major fi nancial organization with responsibilities in implementing data warehousing and BI solutions. She specializes in ETL, dimensional modeling, cube design, and reporting in MDX and T-SQL. Sherry Li maintains her blog at bisherryli.com.

                    Tomislav Piasevoli

                    Tomislav Piasevoli (tomislav@piasevoli.com) is a Business Intelligence Specialist with years of experience in Microsoft SQL Server Analysis Services (SSAS). He lives in Croatia and works for SoftPro Tetral d.o.o., a company specializing in development of SSAS frontends and implementation of BI solutions. His main interests are dimensional modeling, cube design, and MDX about which he blogs at http://tomislav.piasevoli.com. Tomislav also writes articles and speaks at regional conferences and user groups. For his contribution to the community, Microsoft awarded him with the Microsoft SQL Server MVP title.
                    Sorry, we don't have any reviews for this title yet.

                    Code Downloads

                    Download the code and support files for this book.


                    Submit Errata

                    Please let us know if you have found any errors not listed on this list by completing our errata submission form. Our editors will check them and add them to this list. Thank you.

                    Sample chapters

                    You can view our sample chapters and prefaces of this title on PacktLib or download sample chapters in PDF format.

                    Frequently bought together

                    MDX with SSAS 2012 Cookbook +    Oracle BPM Suite 11g Developer's cookbook =
                    50% Off
                    the second eBook
                    Price for both: £32.75

                    Buy both these recommended eBooks together and get 50% off the cheapest eBook.

                    What you will learn from this book

                    • Create time-aware calculations that are relative to the current date
                    • Construct context-aware calculations that are relative to members on axes
                    • Implement business-related calculations such as forecasting, allocation of values, and ABC analysis
                    • Combine MDX with utility dimensions
                    • Implement error handling
                    • Apply AND, OR, NOT logic
                    • Conditionally format your MDX calculations
                    • Optimize, dissect, and debug MDX calculations and queries
                    • Capture MDX generated by SSAS front-ends
                    • Register SSAS-related assemblies and use stored procedures in them

                    In Detail

                    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.

                     

                    Approach

                    The book includes 90 detailed and step-by-step recipes

                    Who this book is for

                    This book is aimed towards developers. However people experienced with other BI and/or SSAS functions will be able to use this book. If you are a Microsoft SQL Server Analysis Services developer and want to improve your solutions using MDX, then this book is for you.

                    This book is also an essential resource for report developers who need to access the multidimensional cubes through the MDX language.

                    The book assumes you have some basic working knowledge of MDX and a basic understanding of dimensional modelling and cube design.

                    Code Download and Errata
                    Packt Anytime, Anywhere
                    Register Books
                    Print Upgrades
                    eBook Downloads
                    Video Support
                    Contact Us
                    Awards Voting Nominations Previous Winners
                    Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
                    Resources
                    Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software