none
Creating a KPI in Dashboard Designer RRS feed

  • Question

  • [Using SharePoint 2013 Enterprise SP1]

    I am trying to create a KPI in Dashboard Designer, and finding surprisingly little documentation for all its features. (Ok, maybe I'm pretty jaded and not that surprised.)

    I have a Data Connection created which connects to a SQL Server Table, and that connection seems to be working properly. The table has 1 widget per record, with (among other things) a creation time field (shortdatetime), and a Pass/Fail field (bit). I want my KPI to be be "green" if, say, >95% pass in a given day, "yellow" if between 85%-95% pass, and "red" if <85% pass.

    For the Number Format in the KPI, I have both Actual & Target using the Percentage format, 1 decimal place.

    For Data Mappings, the Measure I am using for Actual is the Pass/Fail field from the Sql Server Table. I have 1 dimension: CreateDate.Day (I selected Year, Month and Day "Time Period Levels" back in the Data Connection, with my CreateDate field as the Master Time Dimension.) I set the dimension's default to be, say, yesterday. For Target, my Data Mapping is 1 (Fixed values).

    For the Calculation column, I set Actual to "Sum of children," and for Target it is Default.

    When I try to add this to a Scorecard, I just get Error for Actual and Target. Not sure where to go from here. Thanks for any pointers!


    • Edited by cgtyoder Thursday, March 20, 2014 8:20 PM
    Thursday, March 20, 2014 8:20 PM

Answers

  • After some more searching and experimenting, I figured it out. I did not find any documentation for DD '13 but did find some for DD '10, which was "close enough" to translate to the newer version. Here are some relevant MS doc pages:
    Walkthrough: Calculate normalized scores for KPIs
    Configure data column properties
    Configure time and aggregation settings

    For my specific problem, here are the steps I took:

    SQL Table
    • Change the PassFail column from a bit to an integer which contains either 0 or 100 (instead of 0 or 1).

    Data Source
    • On the View tab, set all fields (other than the CreateDate and PassFail fields) to a Column Type of Ignore (this is in the Details pane).
    • For the CreateDate field, set the Column Type to be TimeDimension. For the PassFail field, set the Column Type to Fact and the Aggregation to Average.

    KPI
    • Reset the Number Format for both Actual and Target to (Default).
    • Change the Target Data Mapping from 1 to 100 (still Fixed value).
    • In the Calculation column either Default or Data value will work.

    That should do it! Now, on to filters...

    • Marked as answer by cgtyoder Thursday, March 27, 2014 3:25 PM
    Thursday, March 27, 2014 3:25 PM

All replies

  • Sum of Children is for MDX queries against an OLAP cube. Not for TSQL query results.

    Most of PerformancePoint is designed for OLAP cubes, not SQL databases. Consider creating a VIEW which performs the aggregation (SUM by DISTINCT columns or whatever), and point PPS to the VIEW instead of the TABLE.


    Scott Brickey
    MCTS, MCPD, MCITP
    www.sbrickey.com
    Strategic Data Systems - for all your SharePoint needs

    Thursday, March 20, 2014 8:26 PM
  • Ok, I changed the Calculation on Actual to Default (which is really what I want - the Average) but I still get Error. I don't see what advantage a View would have over the current table - shouldn't I be able to do exactly what I'm trying to do here with DD's existing capabilities?

    Thursday, March 20, 2014 8:54 PM
  • Most of PerformancePoint is designed for OLAP cubes, not SQL databases.
    Then why are there 4 options in DD for creating Tabular data sources? It seems to me there is (in theory) lots of support for non-cube data, just little actual documentation on how to implement it.
    Monday, March 24, 2014 12:49 PM
  • TSQL features are there if you have views which expose the KPI's correctly (like having VIEWS which perform the TOTAL/AVG/etc)... but practically speaking the real benefits (decomp tree, etc) are only available to cubes.

    Scott Brickey
    MCTS, MCPD, MCITP
    www.sbrickey.com
    Strategic Data Systems - for all your SharePoint needs

    Monday, March 24, 2014 2:01 PM
  • TSQL features are there if you have views which expose the KPI's correctly (like having VIEWS which perform the TOTAL/AVG/etc).
    This is great info. Is there a place where this is documented that I can read more?
    Monday, March 24, 2014 3:30 PM
  • After some more searching and experimenting, I figured it out. I did not find any documentation for DD '13 but did find some for DD '10, which was "close enough" to translate to the newer version. Here are some relevant MS doc pages:
    Walkthrough: Calculate normalized scores for KPIs
    Configure data column properties
    Configure time and aggregation settings

    For my specific problem, here are the steps I took:

    SQL Table
    • Change the PassFail column from a bit to an integer which contains either 0 or 100 (instead of 0 or 1).

    Data Source
    • On the View tab, set all fields (other than the CreateDate and PassFail fields) to a Column Type of Ignore (this is in the Details pane).
    • For the CreateDate field, set the Column Type to be TimeDimension. For the PassFail field, set the Column Type to Fact and the Aggregation to Average.

    KPI
    • Reset the Number Format for both Actual and Target to (Default).
    • Change the Target Data Mapping from 1 to 100 (still Fixed value).
    • In the Calculation column either Default or Data value will work.

    That should do it! Now, on to filters...

    • Marked as answer by cgtyoder Thursday, March 27, 2014 3:25 PM
    Thursday, March 27, 2014 3:25 PM