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.