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

Handling division by zero errors


Handling errors is a common task, especially the handling of division by zero type errors. This recipe offers a common practice to handle them.

Getting ready

Start a new query in SQL Server Management Studio and check that you're working on Adventure Works database. Then write and execute this query:

WITH
MEMBER [Date].[Calendar Year].[CY 2006 vs 2005 Bad] AS
   [Date].[Calendar Year].[Calendar Year].&[2006] /
   [Date].[Calendar Year].[Calendar Year].&[2005],
   FORMAT_STRING = 'Percent'
SELECT
   { [Date].[Calendar Year].[Calendar Year].&[2005],
     [Date].[Calendar Year].[Calendar Year].&[2006],
     [Date].[Calendar Year].[CY 2006 vs 2005 Bad] } *
     [Measures].[Reseller Sales Amount] ON 0,
   { [Sales Territory].[Sales Territory].[Country].MEMBERS }
   ON 1
FROM
   [Adventure Works]

This query returns six countries on the rows axis, and two years and a ratio on the column axis.

The problem is that we get 1.#INF on some ratio cells. 1.#INF is the formatted value of infinity, and it appears whenever the denominator CY 2005 is null and the nominator CY 2006 is not null.

We will need help from the IIF() function, which takes three arguments: iif(<condition>, <then branch>, <else branch>). The IIF() function is a Visual Basic for Applications (VBA) function and has a native implementation in MDX. The IIF ( ) function will allow us to evaluate the condition of CY 2005, then decide what the ratio calculation formula should be.

How to do it…

Follow these steps to handle division by zero errors:

  1. Copy the calculated member and paste it as another calculated member. During that, replace the term Bad with Good in its name, just to differentiate between those two members.

  2. Copy the denominator.

  3. Wrap the expression in an outer IIF() statement.

  4. Paste the denominator in the condition part of the IIF() statement and compare it against 0.

  5. Provide null value for the true part.

  6. Your initial expression should be in the false part.

  7. Don't forget to include the new member on columns and execute the query:

    WITH
    MEMBER [Date].[Calendar Year].[CY 2006 vs 2005 Bad] AS
       [Date].[Calendar Year].[Calendar Year].&[2006] /
       [Date].[Calendar Year].[Calendar Year].&[2005],
       FORMAT_STRING = 'Percent'
    MEMBER [Date].[Calendar Year].[CY 2006 vs 2005 Good] AS
       IIF([Date].[Calendar Year].[Calendar Year].&[2005] = 0,
           null,
           [Date].[Calendar Year].[Calendar Year].&[2006] /
           [Date].[Calendar Year].[Calendar Year].&[2005]
          ),
       FORMAT_STRING = 'Percent'
    SELECT
       { [Date].[Calendar Year].[Calendar Year].&[2005],
         [Date].[Calendar Year].[Calendar Year].&[2006],
         [Date].[Calendar Year].[CY 2006 vs 2005 Bad],
         [Date].[Calendar Year].[CY 2006 vs 2005 Good] } *
         [Measures].[Reseller Sales Amount] ON 0,
       { [Sales Territory].[Sales Territory].[Country].MEMBERS }
       ON 1
    FROM
       [Adventure Works]

The result shows that the new calculated measure has corrected the problem. The last column [CY 2006 vs 2005 Good] is now showing (null) correctly when the denominator CY 2005 is null and the nominator CY 2006 is not null.

How it works…

A division by zero error occurs when the denominator is null or zero and the numerator is not null. In order to prevent this error, we must test the denominator before the division and handle the two scenarios in the two branches using the IIF() statement.

In the condition part of the IIF statement, we've used a simple scalar number zero to determine if [Measures].[Reseller Sales Amount] in the following slicer is zero or not. If it is zero, then it will be true and the calculated member will be NULL:

[Date].[Calendar Year].[Calendar Year].&[2005] = 0

What about the NULL condition? It turned out for a numerical value; we do not need to test the NULL condition specifically. It is enough to test just for zero because null = 0 returns true. However, we could test for NULL condition if we want to, by using the IsEmpty() function.

For the calculated member [CY 2006 vs 2005 Good] we could wrap the member with the IsEmpty() function. The result will be the same:

MEMBER [Date].[Calendar Year].[CY 2006 vs 2005 Good] AS
   IIF(IsEmpty([Date].[Calendar Year].[Calendar Year].&[2005]),
       null,
       [Date].[Calendar Year].[Calendar Year].&[2006] /
       [Date].[Calendar Year].[Calendar Year].&[2005]
      ),
   FORMAT_STRING = 'Percent'

There's more…

SQLCAT's SQL Server 2008 Analysis Services Performance Guide has a lot of interesting details regarding the IIF() function, found at http://tinyurl.com/PerfGuide2008R2.

Additionally, you may find the blog article MDX and DAX topics by Jeffrey Wang explaining the details of the IIF() function, found at http://tinyurl.com/IIFJeffrey.

Earlier versions of SSAS

If you're using a version of SSAS prior to 2008 (that is, 2005), the performance of the IIF() function will not be as good. See Mosha Pasumansky's article for more information: http://tinyurl.com/IIFMosha.