none
Using Dashboard Designer to make KPIs on a PPS Planning cube. RRS feed

  • Question

  •  

    I've noticed that there are no M&A demos or dashboards which use a PPS planning model as a data source.

    I think this is because PPS cubes use a scenario dimension at the lowest level of granularity for the value. The result is that when you try to import/create KPI's in Designer, it will only find the scenario objects, e.g. Value ot Actual, Forecast, Budget etc.. which make pretty useless KPI names. The real KPI names are in the Accounts member set, or whatever dimesnion you decide to use.

    So what is the best way to import/load many KPIs into Designer from a PPS cube data source? - consider also that it may be necessary to add new KPI's in future, and that I may want to defined specific banding rules for certain KPI's - so using a generic KPI then splitting it with the Accounts dimension is not an option.

    Monday, June 30, 2008 10:25 AM

Answers

  • Ha..my keyboard works again..

    And I have a quick method of creating lots of KPI's without having to type in or copy/paste all the names individually.

    I used some tips from various blogs. It took me 50 secs to generate 33 word perfect KPI names, in Dashboard by using an Excel tabular list, a copy from Planning business modeller dimension member view, and the Paste Special with Transpose. It took 1.30mins to create 170 KPIs for another PPS model.

     

    1. In Dashboard Designer create a new data source called KPISource - using Tabular List > Import from Excel 2007
    2. In the Edit panel for the new source, click on [Edit] button to open a blank Excel worksheet - wait for it...it can take a few secs. Be aware the Excel sheet may launch BEHIND dashboard window, so look for it.
    3. Now switch to Planning Business modeller view the memebers of the dimension which contains all the names you want to use for KPIs. You can use a view list, filter or member set to restrict the displayed members.
    4. Using the mouse, select all the rows you want to copy, and right-click -> Copy
    5. Click on a lower row e.g. A5 and paste the list into sheet1 of the Excel worksheet. It will paste all columns from the dimension - never mind for now. Now select only the column containing the names you want to use for KPI's, Copy the selection, select cell A1 on the worksheet and Paste Special ->Transpose.
    6. This will paste all the name as one row across. You can then delete the original list of member stuff at A5
    7. In row A2, enter number 1, and copy across all of row 2, so each KPI has a number below it.
    8. Click on Accept Data button on the floating Edit in Excel dialogue - usually hovering bottom right.
    9. The data in rows 1 and 2 is read into Dashboard designer and should be automatically classified as Fact columns.
    10. Save the KPISource and publish.
    11. Next create a new scorecard, call it KPIList, make sure the Use scorecard wizard to create scorecards option is enabled (in the dashboard properties).
    12. Select KPISource as the source for this scorecard, then repeatedly select Add KPI, and it will automatically read each column from the source as a KPI. On completion it will create all the KPI's also.

    Now you can bulk edit the created KPIs and set them to use the REAL data source for your dashboard data.

    You can delete the KPIList scorecard afterward to tidy up.

    As a variation, I added two extra KPI columns in the KPISource called Actual and Target. I could then select these as the Actual and Target columns in the scorecard wizard, otherwise it defaults to using the same KPI name.

     

     

     

    Wednesday, July 23, 2008 5:17 PM

All replies

  •  

    Thanks - yes, I have seen this, it describes using a generic KPI which is no good when you need specific banding rules e.g. decreasing is better for some, increasing for others. Then it describes making KPIs individually with this obvious bit..

    10. Repeat steps 4 through 9, assigning the Target value.

    11. Repeat steps 1 through 10 for each KPI.

     

    it's the repeating for 40 or so KPIs that I was hoping to avoid.

     

    I will try importing kpis from a tabular list, then changing their source settings.

    Wednesday, July 2, 2008 10:11 PM
  • Mark,

    I have recently come across this issue, and have found that defining a custom set is the best way to present your data.  First you will need to create a single KPI to define the different columns for the scorecard.  Then add the KPI to the scorecard as normal, hiding it if you really don't want it to show up.

    The trick is to then add a custom set formula within the scorecard to define the rows using MDX.  For example [Account].[Revenue Accounts].Allmembers, or whatever expression defines the data you would like to show.  There are some other tricks to get a dynamic set of rows to display in your scorecard, the problem is the grouping of hierarchical data.

    If you need to add grouping to your scorecard you will need to write a custom grid view transform.  I do not have the code, but can assure you that is very simple.  You can refer to this blog to help get you started.

    http://blogs.msdn.com/performancepoint/archive/2008/04/09/hiding-empty-rows-in-a-scorecard-code-sample.aspx

    Best of luck,

    Shane Risk
    Thursday, July 3, 2008 1:41 AM
  • Thanks, but that blog entry looks like serious custom coding, which I am desperately trying to avoid.

    I realise now that Designer scorecards need to be man-handled each time there is a change to the PPS model - e.g. add an account - means add a KPI, filter it, drag to correct place on scorecard, update, publish. Unless you fancy a spot of API programming.

     

    The thing is that the kpi names are already in the model as Account member names so it is a pain that they cannot be loaded into Designer.

     

    Also, if you use the method you describe, the generated KPIs in the scorecard are not REAL KPIs, they are dimension levels, which means I cannot use the to link to stuff like Visio Strategy Maps!! Only real KPIs can be selected as data links in maps.

     

    Monday, July 7, 2008 5:07 PM
  • Right-   (bad keyhboard soz - rain tgot tgo itg!!)

     

    I have the relevant SQL table containing the list of names I want to use for KPI's

    But the KPI wizard in DD expects the names to be the Column names of FACT type cols.

    So I need to make an SQL SELECT statement which transposes the values in my table column 'NAME' into a row of names.

    I could make an Excel data  query, list the names, then copy/paste special/transpose, then save as CSV or ODBC the sheet, but that sucks..

     

    Can anyone suggest a transposing SQL SELECT statement?

     

    adv(thanks)ance

    Thursday, July 10, 2008 1:43 AM
  • Well I looked up transposing col data to row in the SQL T-SQL forum and found this useful link about the PIVOT function in SQL2005/08:

     

    http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=PIVOTData

     

    However it requires a typed and therefore pre-defined column name for each column, dynamic col names are a no-go.

    Looks like an Excel pivot table and a macro for me...

     

    Thursday, July 10, 2008 9:35 AM
  • Ha..my keyboard works again..

    And I have a quick method of creating lots of KPI's without having to type in or copy/paste all the names individually.

    I used some tips from various blogs. It took me 50 secs to generate 33 word perfect KPI names, in Dashboard by using an Excel tabular list, a copy from Planning business modeller dimension member view, and the Paste Special with Transpose. It took 1.30mins to create 170 KPIs for another PPS model.

     

    1. In Dashboard Designer create a new data source called KPISource - using Tabular List > Import from Excel 2007
    2. In the Edit panel for the new source, click on [Edit] button to open a blank Excel worksheet - wait for it...it can take a few secs. Be aware the Excel sheet may launch BEHIND dashboard window, so look for it.
    3. Now switch to Planning Business modeller view the memebers of the dimension which contains all the names you want to use for KPIs. You can use a view list, filter or member set to restrict the displayed members.
    4. Using the mouse, select all the rows you want to copy, and right-click -> Copy
    5. Click on a lower row e.g. A5 and paste the list into sheet1 of the Excel worksheet. It will paste all columns from the dimension - never mind for now. Now select only the column containing the names you want to use for KPI's, Copy the selection, select cell A1 on the worksheet and Paste Special ->Transpose.
    6. This will paste all the name as one row across. You can then delete the original list of member stuff at A5
    7. In row A2, enter number 1, and copy across all of row 2, so each KPI has a number below it.
    8. Click on Accept Data button on the floating Edit in Excel dialogue - usually hovering bottom right.
    9. The data in rows 1 and 2 is read into Dashboard designer and should be automatically classified as Fact columns.
    10. Save the KPISource and publish.
    11. Next create a new scorecard, call it KPIList, make sure the Use scorecard wizard to create scorecards option is enabled (in the dashboard properties).
    12. Select KPISource as the source for this scorecard, then repeatedly select Add KPI, and it will automatically read each column from the source as a KPI. On completion it will create all the KPI's also.

    Now you can bulk edit the created KPIs and set them to use the REAL data source for your dashboard data.

    You can delete the KPIList scorecard afterward to tidy up.

    As a variation, I added two extra KPI columns in the KPISource called Actual and Target. I could then select these as the Actual and Target columns in the scorecard wizard, otherwise it defaults to using the same KPI name.

     

     

     

    Wednesday, July 23, 2008 5:17 PM
  • This is bugging me, the above method of using an Excel Tabular source cuts out the work of inputing the KPI names, but it still takes ages to assign the correct Actual and Target source settings to point to your REAL data source as opposed to the dummy KPILoader source. One reason it takes ages is that the Bulk Editor for KPIs does not allow you to 'Change Source'. So although it took about 1min 30 to make 170 KPI's, it took another 1.3 hours to wire the Actuals/Targets up to the correct source/filter.

     

    So I had an illegal poke around the .bswx file and using an XML editor (Notepad++ from Sourceforge is brilliant) and found that it is pretty easy to change the Datasource GUID on each KPI Actual and/or Target using a global replace! There may be other stuff that needs changing, but generally thats the idea and it seems to have worked for me.

     

    I created all the KPIs using the KPILoader idea, then added the real data source to my workspace, then saved my workspace (I did not PUBLISH the KPIs) and closed it. Then opened the relevant .bswx file, found the correct datasource GUIDs and did a global replace. When I reopened the workspace, I checked all the KPIs and used Bulk Edit to set the basic filters. Then published all and my scorecard works...

     

    Still a bit of a hack, but I get mouse-hand cramp from clicking all those damn KPI settings and filter buttons in the Edit bit.

     

    Hopefully there will be a magic button to do all this INR (in next release)

     

    regs..

    Friday, July 25, 2008 10:35 AM