SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Tracking down KPI error in PerformancePoint

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:

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]
+[Provision for Deferred Taxes]
+[I/C profit / loss on settlement of MRI ARO]
+[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]
+[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.

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


No comments.

Leave a Comment

Please register or log in to leave a comment.