MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook


MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook
eBook: $29.99
Formats: PDF, PacktLib, ePub and Mobi formats
$25.49
save 15%!
Print + free eBook + free PacktLib access to the book: $79.98    Print cover: $49.99
$49.99
save 37%!
Free Shipping!
UK, US, Europe and selected countries in Asia.
Also available on:
Overview
Table of Contents
Author
Support
Sample Chapters
  • Enrich your BI solutions by implementing best practice MDX calculations
  • Master a wide range of time-related, context-aware, and business-related calculations
  • Enhance your solutions by combining MDX with utility dimensions
  • Become skilled in making reports concise
  • Learn how to optimize, dissect, and debug your MDX calculations
  • Maximize your learning with detailed explanations following each solution
  • Packed with practical, hands-on cookbook recipes, illustrating the techniques to enrich your Business Intelligence solutions

Book Details

Language : English
Paperback : 480 pages [ 235mm x 191mm ]
Release Date : August 2011
ISBN : 1849681309
ISBN 13 : 9781849681308
Author(s) : Tomislav Piasevoli
Topics and Technologies : All Books, Enterprise Products and Platforms, Microsoft Servers, Cookbooks, Enterprise, Microsoft, Microsoft SQL Server

Table of Contents

Preface
Chapter 1: Elementary MDX Techniques
Chapter 2: Working with Time
Chapter 3: Concise Reporting
Chapter 4: Navigation
Chapter 5: Business Analytics
Chapter 6: When MDX is Not Enough
Chapter 7: Context-aware Calculations
Chapter 8: Advanced MDX Topics
Chapter 9: On the Edge
Conclusion
Appendix: Glossary of Terms
Index
  • Chapter 1: Elementary MDX Techniques
    • Introduction
    • Skipping axis
    • Handling division by zero errors
    • Setting special format for negative, zero and null values
    • Applying conditional formatting on calculations
    • Setting default member of a hierarchy in MDX script
    • Implementing NOT IN set logic
    • Implementing logical OR on members from different hierarchies
    • 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
    • Using NON_EMPTY_BEHAVIOR
    • Optimizing MDX queries using the NonEmpty() function
    • Implementing logical AND on members from the same hierarchy
    • Chapter 2: 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
      • Hiding calculation values on future dates
      • 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 3: 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 4: 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 5: Business Analytics
            • Introduction
            • Forecasting using the linear regression
            • Forecasting using the periodic cycles
            • Allocating the non-allocated company expenses to departments
            • Calculating the number of days from the last sales to identify the slow-moving goods
            • Analyzing fluctuation of customers
            • Implementing the ABC analysis
            • Chapter 6: 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 non-existing 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 7: Context-aware Calculations
                • Introduction
                • Identifying the number of columns and rows a query will return
                • Identifying the axis with measures
                • Identifying the axis without measures
                • Adjusting the number of columns and rows for OWC and Excel
                • Identifying the content of axes
                • Calculating row numbers
                • Calculating the bit-string for hierarchies on an axis
                • Preserving empty rows
                • Implementing utility dimension with context-aware calculations
                • Chapter 8: Advanced MDX Topics
                  • Introduction
                  • Displaying members without children (leaves)
                  • Displaying members with data in parent-child hierarchies
                  • Implementing the Tally table utility dimension
                  • 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 Views (DMVs) to monitor activity and usage
                    • Capturing MDX queries generated by SSAS front-ends
                    • Performing custom drillthrough
                    • Appendix: Glossary of Terms
                      • Parts of an MDX query
                      • MDX query in action
                      • Cube and dimension design
                      • MDX script
                      • Query optimization
                      • Types of query

                      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 Microsoft SQL Server 2008 R2 Analysis Services Cookbook +    SOA Patterns with BizTalk Server 2009 =
                      50% Off
                      the second eBook
                      Price for both: $30.59

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

                      What you will learn from this book

                      • Create time-aware calculations (relative to the current date)
                      • Create context-aware calculations (relative to members on axes)
                      • Implement business-related calculations like forecasting, allocation of values and ABC analysis
                      • Calculate various percentages, averages, and ranks
                      • Work with related members (on the same and other dimensions)
                      • Combine MDX with utility dimensions
                      • Implement error handling
                      • Implement 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

                      Microsoft SQL Server is an enterprise database platform that contains a multitude of technologies, Analysis Services being one of them. SQL Server Analysis Services (SSAS) provides OLAP and data mining capabilities and allows users to analyze multidimensional data stored in cubes using the MDX query language.

                      This cookbook contains over 80 practical, task-based recipes that show how Microsoft SQL Server 2008 R2 Analysis Services solutions can be taken further by enriching them with high-performance MDX calculations and flexible MDX queries.

                      Packed with immediately usable, real-world recipes, the book starts with elementary techniques that lay the foundation for designing further MDX calculations and queries. Here you will find topics such as iterations on a set, Boolean logic, and dissecting and optimizing MDX calculations.

                      In the first half of the book you will learn how to efficiently work with time, strings, metadata, calculated members and sets in general, and how to implement MDX solutions that are appropriate in a particular context: a time-aware calculation, a concise report, a calculation relative to another. You will also learn how to implement various types of conditional formatting, how to perform typical MDX calculations like ranks, percentages and averages, and year-to-date calculations.

                      The book then deep dives into topics such as enhancing cube design with utility dimensions, context-aware calculations, and other advanced topics. In this part you will learn how a utility dimension can be of great help, for example when you want to calculate histograms or implement time-based calculations. The advanced topics also cover parent-child hierchies, recursion, random values, and complex sorts.

                      Enrich your Business Intelligence solutions with over 80 recipes for high-performance MDX calculations and flexible MDX queries

                      Approach

                      This book offers practical, task-based, and immediately usable recipes covering a wide range of MDX calculations and queries. In addition to its cookbook style, which ensures the solutions are presented in a clear step-by-step manner, the explanations are done in great detail, which makes it good learning material for everyone who has experience in MDX and wants to improve.

                      The book is designed in such a way that you can read it chapter by chapter or refer to recipes in no particular order. However, some of the recipes depend on each another. When this is the case, you will be notified.

                      The book is focused on Microsoft SQL Server 2008 R2 Analysis Services, but most of the concepts and explanation are also applicable to previous versions of Microsoft SQL Server Analysis Services.

                      Who this book is for

                      If you are a Microsoft SQL Server Analysis Services developer and want to improve your solutions using MDX, then this book is for you.

                      The book assumes you have a working knowledge of MDX and a basic understanding of dimensional modeling 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