Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

This error 1.#INF is not only related to division by zero

Posted by thomasivarssonmalmo on July 9, 2007

How many times have you not found this error message in a cell when you have created a calculated member. This is not a hard problem to handle but remember that it can appear in both division by zero errors and when you refer to a member that does not exist, like the parent of the All member in a dimension hierarchy.
 
If you search on MDX and 1.#INF you will mostly see references to the division by zero problem.
 
Run this MDX-select in management studio and see that the All Products member at the top shows this error. I simply calculate the child percentage Internet Sales Amount of the parent.
 

With Member Measures.ParentRatio As [Measures].[Internet Sales Amount]/([Measures].[Internet Sales Amount],[Product].[Product Categories].CurrentMember.Parent),Format_String = ‘###.#%’ 

Select {[Measures].[Internet Sales Amount],Measures.ParentRatio} On Columns,NON EMPTY Descendants([Product].[Product Categories],[Product].[Product Categories].[Subcategory],Self_And_Before) On Rows From [Adventure Works]Where [Date].[Calendar].[Calendar Year].&[2003];

 

I work with more BI products than SSAS so  my head is not always filled with MDX. Since this problem will appear from time to time, frequently, I thought I could share the solution for all you that have the same knowledge of MDX as me.
 
The error(1.#INF) appears because I am calling the parent of the All Product that does not exist. In the solution, taken from the MDX book, MDX Solutions, I check if the parent is null or not.
 
With Member Measures.ParentRatio As IIF([Product].[Product Categories].CurrentMember.Parent IS NULL, Null, [Measures].[Internet Sales Amount]/([Measures].[Internet Sales Amount],[Product].[Product Categories].CurrentMember.Parent)), Format_String = ‘###.#%’

Select

{[Measures].[Internet Sales Amount],Measures.ParentRatio} On Columns, NON EMPTY Descendants([Product].[Product Categories],[Product].[Product Categories].[Subcategory],Self_And_Before) On Rows From [Adventure Works]Where [Date].[Calendar].[Calendar Year].&[2003];

The 1.#INF will be replaced by null.  Can katmai show nulls automatically for this error and the division by zero error, so that we can avoid putting a lot of code to solve these issues? Keeping run time checks to a minimum should be good for performance.

 
Advertisements

4 Responses to “This error 1.#INF is not only related to division by zero”

  1. Stefan said

    If the calc. member is located in the MDXScript you can use SCOPE instead. Then you do not need to use IIF. I think performance would be better as well.( not in the query you have as example but for bigger ones).

  2. Thomas said

    Hello Stefan. If the performance guide tell us to avoid run time checks and this feature is already in Essbase I think it will help SSAS developers and SSAS performance if we do not have to enter these checks.  

  3. Ariel said

    Hello Thomas,

    I have a problem related with 1.#INF.
    I have 2 environment, with the same OLAP DB restored..

    SQL2008 R2 (SP 3) and SQL2012 (SP1)

    when I try to get data from MDX query, In 2008 got 1.#INF but the same query in SQL2012 I got the correct data!! for example the value is (

    MDX Query:

    select {[Measures].[Weighted Average Interest Rate]} on columns,
    {[Credit Contract].[Original Principal Local Range].&[1]} on rows
    from [Credit Products for Local Analysis]
    where ([Date].[Dates].[Year].&[2014].&[4], [Contract Status].[Credit Status].&[1])

    the same OLAP BD restored in SQL2008 and SQL2012.
    the same MDX query in both environment.
    results:
    SQL2008: 1.#INF
    SQL2012: 35.06

    Are you know what is the problem? I need update something on SQL2008?

    thank you in advances!!

    Best Regards,
    Ariel.

  4. Since SQL Server 2012 you have the DIVIDE() function that should solve this issue.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: