RatioToParent: High Accuracy

Aug 16, 2010 at 9:35 AM

Hi All,

I am trying to use the RatioToParent, but with a high accuracy. Currently, it only returns up to 4 decimals, no matter how I format it. I need it to be at least 9 decimal places. Can anybody assist? Am I missing something?

Here is the code:

CREATE MEMBER CURRENTCUBE.[Measures].[RatioToParent]
 AS IIF
    (
      IsEmpty([Measures].[Weighted Value])
     ,null
     ,ASSP.ASStoredProcs.SetOperations.RatioToParent
      (
        Axis(1).Item(0)
       ,[Measures].[Weighted Value]
      )
    ), 
FORMAT_STRING = "# ##0.000000000;-# ##0.000000000", 
NON_EMPTY_BEHAVIOR = [Measures].[Weighted Value], 
VISIBLE = 1  ;   

SELECT
  {
    [Measures].[Weighted Value],[Measures].[RatioToParent]
  } ON 0
 ,NON EMPTY
    ( 
		[MOD Month End],[Driver Account].[Account Number].MEMBERS
    ) ON 1
FROM [Driver Data];

Thanks in advance

NL

Jun 11, 2015 at 2:04 AM
+1

or some code to help it with precision.. my 'children' aren't adding up to 100% and it's causing problems in userland
Coordinator
Jun 11, 2015 at 11:34 AM
if you look at the code we are returning a decimal data type. Do you want to try changing to a double data type and recompiling and let us know if that resolves the problem?
Coordinator
Jun 11, 2015 at 12:26 PM
Also - what is the data type of the measure that you are passing in to the function? If it's currency and you are seeing truncation at 4 decimal places then we may be seeing the calculated measure being implicitly cast to the same data type as the input measure. If this is the case wrapping the measure in the CDBL() function might be worth a try.
Jun 11, 2015 at 3:23 PM
furmangg wrote:
if you look at the code we are returning a decimal data type. Do you want to try changing to a double data type and recompiling and let us know if that resolves the problem?
Thanks, tried it and it worked! The irony of using an approximate data type to work around incorrect arithmetic+aggregation coming out of an exact data type is not lost on me :) i'll do some more testing to make sure double is ok because according to the .net spec i should be using decimal (https://msdn.microsoft.com/en-us/library/system.double.aspx). There doesn't appear to be a 2014 version of the code in the source code downloads (the solution is there, but no project), so i created a new dll with just the ratio to parent.

I'll have to do some testing tomorrow because my understanding of decimals in .net is that they show as many decimal points as required (and within the size restrictions)

I tried float as well, but that was cut off at around 7 decimal places (im guessing because it's shown in scientific notation eg 0.1234567E-10) - the 7 is a guess, 1am here and i'm not about to load up SSMS to count how many exactly, but it was more than 4, but not enough to show the true value nor near the max limit of decimal.

dgosbell wrote:
Also - what is the data type of the measure that you are passing in to the function? If it's currency and you are seeing truncation at 4 decimal places then we may be seeing the calculated measure being implicitly cast to the same data type as the input measure. If this is the case wrapping the measure in the CDBL() function might be worth a try.
I thought that might be it so I tried with a different version of the measure that i'd explicitly cast to 32,18. Made no difference. Decimal points cut off at 4. Same with using an int based measure (order qty).

I'm wondering whether it's some weird behaviour in the MDXValue.ToDecimal() method in the AnalysisServices.AdomdServer library.
Jun 12, 2015 at 12:50 AM
Edited Jun 12, 2015 at 12:51 AM
remembered something late last night; I've encountered truncation/rounding problems before...
SSAS doesn't technically have a decimal data type from what i remember - https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bb7bcae4-e102-482d-a4b7-60d78ec451ee/decimal-precision-in-cube-measures?forum=sqlanalysisservices

it either goes into a currency (4 decimal places) or a 'double'.. The datatype on the DSV shows this


I can go one of two ways

perform the division using decimals but recast & return it as a double

or multiply the result by how ever many decimal points I want (eg 9 :- * 1000000000)

Then have to remember to divide by 1000000000 in the mdx:
 ,ASSP.ASStoredProcs.SetOperations.RatioToParent
  (
    Axis(1).Item(0)
   ,[Measures].[Weighted Value]
  )/1000000000