On a few occasions, I have opened up my PerformancePoint scorecard on SharePoint to see an error staring at me:

So two of my KPI’s, which have been working fine for months, suddenly don’t work.  Follow along and I will quickly show you how I found the problem.  First, I look at the Cash Flow KPI:

Format(([Account].[Account Hierarchy].[Cash Flow], [Scenario].[Scenario Key].&[2], [Measures].[GL Monthly Amt]), “#,##0.00″)

Seeing how it uses the Cash Flow calculation, I look at that:

sum(

openingperiod([GLDate].[Calendar with Qtr].[Calendar Month Name],[GLDate].[Calendar with Qtr].currentmember)

: closingperiod(

[GLDate].[Calendar with Qtr].[Calendar Month Name],

[GLDate].[Calendar with Qtr].currentmember),

[Cash Flow Monthly])

That is using the Cash Flow Monthly calculation, and that looks like:

[Cash from Operations]+[Cash from Investing]

OK, so that is using two other calculations.  Let’s look at the first one, Cash from Operations:

[Net Income]

+[DD&A]

+[Provision for Deferred Taxes]

+

+[Adj to MRI ARO charged to earnings]

+[Stock Option Expense]

+[Equity in Earnings of JV]

+[Other Non-Cash Charges & Credits]

+[Minority Interest Expense]

+[Gain/Loss on Sale of PP&E]

+[Non-Cash Impact of Derivative Transaction]

+[Accounts Receivable]

+[Contractual Recovery]

+[Insurance Receivable]

+[Inventories]

+[Prepaids & Other Current Assets]

+[Cash Settlements of ARO]

+[AP & Accrueds]

+[Disc Ops: working capital / non-cash changes]

This has got a lot of calculations, so I browse the cube and look at each one.  They all have values until I get to [Other Non-Cash Charges & Credits], which is empty.  That looks like:

( [Account].[Account Hierarchy].[Account LOD5].&[60000]&[68000]&[68100]) //Insurance Settlement

+( [Account].[Account Hierarchy].[Account LOD6].&[89000]&[89300]&[89401]&[89450])

+( [Account].[Account Hierarchy].[Account ID].&[89410 .130 - Loan fees])

I look at the value of the first reference, and it is fine.  But when I look for the second reference:

[Account].[Account Hierarchy].[Account LOD6].&[89000]&[89300]&[89401]&[89450]

I find that this does not exist.  And If a calculation has a hierarchical reference that no longer exists, the calculation will be empty, and all KPI’s that use that calculation will return an error.  The two KPI’s above that show errors, plus a few other KPI’s on other scorecards, all where using the Cash from Operations calculation.

Turns out, an update to this particular account (89450) on the source system was entered incorrectly, causing it to be moved to a different spot in the account hierarchy, and when it fed into the data warehouse, it caused all those KPIs to fail.

So keep this in mind if you see your KPIs returning an error: It could be that a calculation that the KPI is using is referencing a non-existing member of a hierarchy.