Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

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.


No comments.

Leave a Comment

Please register or log in to leave a comment.