none
UPDATE CUBE statement using WEIGHTED_ALLOCATON gives "type mismatch" error RRS feed

  • Question

  • UPDATE: Environment is Microsoft SQL Server 2008 R2 (SP2) - 10.50.4290.0 (X64)

    ----

    Hi all,

    In a writeback enabled cube I am having trouble with a "type mismatch" error that occurs as soon as I specify anything else than "True" in the MDX statement for the SSAS Role's Cell Data "Enable Read/Write permissions".

    The update cube statement works fine, for the very same user, when the cell data MDX is simply set to "True". But if I try to specify anything slightly more limited than "True", for example only "Measures.CurrentMember IS Measures.Budget", then the very same statement fails with this message "The following system error occurred:  Type mismatch."

    As you can see below Measures.Budget is indeed the measure specified in the SET-portion of the update cube statement so the above rule should to my best understanding work fine. And as said previously, it works fine when the read/write MDX is specified as "True" and the statement can also, not surprisingly, be issued successfully by an SSAS Administrator.

    Does anyone have the slightest idea what can be causing this? Here is the related MDX for this problem.

    UPDATE CUBE [MyCube]
    SET ([Measures].[Budget]
     ,[Organisation].[Organisation].[Account].&[002]&[599]&[5801]
     , [Calendar].[Period].[ALL]) = 2000000
     
    USE_WEIGHTED_ALLOCATION BY ( 
      IIF(
    		([Organisation].[Organisation].[Account].&[002]&[599]&[5801]
    		, [Calendar].[Period].[ALL]
    		, [Measures].[Actual Prev YE]) = 0
    		, 0
    		,
    		IIF(
    			([Organisation].[Organisation].[Account].&[002]&[599]&[5801]
    			, [Calendar].[Period].CurrentMember
    			, [Measures].[Actual Prev YE]) = 0
    			,0
    			,
    				([Organisation].[Organisation].[Account].&[002]&[599]&[5801]
    				, [Calendar].[Period].CurrentMember
    				, [Measures].[Actual Prev YE] ) 
    				/  
    				([Organisation].[Organisation].[Account].&[002]&[599]&[5801]
    				, [Calendar].[Period].[ALL]
    				, [Measures].[Actual Prev YE])
    			)
    	 )
    )

    One hint I've managed to get is that the problem seem to have to do with the denominator in the calculation. This is because if I replace the MDX in the denominator to a constant, for example "2", the update cube statement rune fine. But why? Am I missing something obvious here?

    Thanks in advance,

    Martin



    • Edited by Martin_Ar Wednesday, September 18, 2013 4:17 PM Update
    Wednesday, September 18, 2013 3:30 PM

All replies

  • Hi Martin_Ar,

    This issue have occured before and solved in Cumulative Update package 6 for SQL Server 2008 R2, please see the following article:
    FIX: "Bad variable type" or "Type mismatch" error when you run an UPDATE CUBE statement that has a custom weighted allocation formula in SSAS 2008 R2 or in SSAS 2008: http://support.microsoft.com/kb/2504524/en-us

    For this issue, I would suggest opening a case with Microsoft Customer Support Services (CSS) (http://support.microsoft.com), so that a dedicated Support Professional can assist you in a more efficient manner.

    If you have any feedback on our support, please click here.

    Best Regards,

     


    Elvis Long
    TechNet Community Support

    Thursday, September 19, 2013 5:57 AM
    Moderator
  • Thanks for your reply Elvis,

    I am aware of the issue above. Though, we are experiencing this problem in 2008 R2 SP2, so that bug should already be fixed in our environment. I have now issued a ticket with Microsoft CSS in hope to be able to reolve our issue, thanks for the tip.

    Martin

    Thursday, September 19, 2013 9:30 AM
  • Solved. Casting both member values used in the allocation calculation expression, to decimal using VBA function CDec, did the trick. The update runs successfully and data is written to the writeback table as expected.

    As such:

    CDec(([Organisation].[Organisation].[Account].&[002]&[599]&[5801]
    , [Calendar].[Period].CurrentMember
    , [Measures].[Actual Prev YE]))
    /  
    CDec(([Organisation].[Organisation].[Account].&[002]&[599]&[5801]
    , [Calendar].[Period].[ALL]
    , [Measures].[Actual Prev YE]))

    But can anyone explain to me why the cast is needed for this to work? Both measures included in the calculation are of the same data type in the cube (double) and also in the fact table (decimal(38.6)).

    And why does it work without the cast when setting cell data read/write MDX expression to simply "True" instead of for example "Measures.CurrentMember is Measures.Budget" (which I need to do) ? 

    Martin


    • Edited by Martin_Ar Thursday, September 19, 2013 4:19 PM
    Thursday, September 19, 2013 4:16 PM
  • Hi,

    I have exactly the same problem with SQL Server 2014 (SP1 and CU6).

    I want to limit the writeback-possibility. Only Budget-Values should be entered, Actual-values must not be modified by the end user.

    So I modified the cell data permissions like this:

    1. Enable read permissions (SET Read-Permissions for Actual Values)
    [Datenart].[Datenart].CURRENTMEMBER IS [Datenart].[Datenart].&[1]

    2. Enable read/write permissions (SET WRITE-Permissions for the other Values (Budget and Forecast)
    NOT [Datenart].[Datenart].CURRENTMEMBER IS [Datenart].[Datenart].&[1]

    SQL Server Profiler gives the error message: Systemfehler:  Typenkonflikt. (System Error: Type mismatch) Because I use Equal Allocation I cannot use the workaround Martin described. Although I tested it with the workaround and it didn't work for me.

    Relational Measure data type from source table: numeric(10,2)
    Relational Measure data type from writeback table: money (changed it also to numeric10,2 but no difference)
    Data Type in DSV: System.Decimal
    Data Type in SSAS: Currency

    Thanks for any help,
    Chris

    Wednesday, June 15, 2016 8:55 AM
  • Solved it myself...

    In the "Enable read permissions" field only a "1" is needed, no MDX expression

    Wednesday, June 15, 2016 9:16 AM