Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Account Intelligence does not seem to be working after upgrade to SQL Server 2012 BI Edition

Answered Account Intelligence does not seem to be working after upgrade to SQL Server 2012 BI Edition

  • Monday, February 18, 2013 5:31 PM
     
     
    Sign In to Vote
    0

    Hello all,

    We have upgraded from 2008 R2 to 2012.  An issue we are experiencing is the Account Intelligence isn't working after the SSAS project has been deployed & processed.  Specifically - although the measure has "ByAccount" for its aggregation function, it still sums up rather than respecting the Account Type.  The expected behavior is for Income & Expense accounts to be summed, but Assets and Liabilities should be returning the "LastNonEmpty" balance - they too are being summed which is incorrect.  This is a solution which produces financial statements.

    Below are details & things I've verified/tested.

    • Version:  SQL Server 2012 SP1 (11.0.3000.0) (X64)
    • Edition:  BI Edition (64-bit on Windows NT 6.1) (upgraded from 2008R2 Enterprise Edition)
    • The AggregationFunction is set for Income, Expense, Asset, Liability, Balance, Statistical, and Flow within the "Account Type Mapping" section of the database properties.  Income, Expense, and Flow are set to Sum.  All other Account Types are set to LastNonEmpty. 
    • By Measure Aggregation Type is set to "ByAccount" within the cube measure group.
    • The Account dimension Type property is set to "Accounts."  This is a parent/child dimension.
    • The Account Type attribute in the Account dimension has its Type property set to "AccountType."  The Account, AccountNumber and AccountName are set as well.
    • The Parent attribute within the Account dimension has its Operator property set.
    • The Account dimension values in the underlying relational table look correct (i.e., I see values of Income, Expense, Asset, Liability, etc).  This can be verified when putting Account Type on the axis of the report as well. 
    • The SSAS database has been deleted in SSMS and re-deployed clean from SSDT.  No change.
    • As a test, I redeployed the SSAS project with the measure set directly to LastNonEmpty.  The semi-aggregate then works fine; however, it doesn't work anymore when returned to ByAccount.  (Can't leave this in permanently - it returns correct data for balance sheet accounts, but incorrect for income statement accounts).

    Any suggestions on what else to check? 

    Thanks for the help!

    Melissa

    Melissa Coates www.sqlchick.com

     

All Replies

  • Tuesday, February 19, 2013 3:23 PM
     
     Answered
    Sign In to Vote
    0

    Just a follow-up with the resolution.  In a nutshell, we ran through the Account Intelligence wizard to force SSAS to regenerate the Account Intelligence. 

    What's interesting is the wizard initially wouldn't accept the parent/child attribute that had been specified as the "Account" Type (aka "Chart of Accounts" within the wizard window).  It returned an error which said a non-parent attribute has been specified as the 'Account' type.  Screen shot of this error is below.  Puzzling - the attribute was definitely set as "Parent" & I could see in the attribute properties window.  I also could see that value in the xmla.  Anyway, I cleared all of the Type values from the Account dimension & Account attributes (i.e., put them back to Regular).  At that point I re-ran the wizard & it was able to complete without the error & it set the values for the Types.  And voila, it worked.

    I compared the xml before and after & haven't found a meaningful difference that actually explains what happened.  So it's still a bit of a mystery.  Nonetheless, it's resolved.

    Melissa Coates www.sqlchick.com

    • Marked As Answer by MelissaCoates Tuesday, February 19, 2013 3:23 PM
    •