Migrating PPS Applications and Dashboards – TIPS

This is from my BI Manager Kristi Taylor

We recently began the process of migrating a PPS Application from our development environment into test and into production. We utilized the Planning Server Data Migration Wizard to help us accomplish this task. Although this wizard saved us a lot of time we still ran into some problems and I would like to share with you the resolutions.

Problem #1 our inherited application had some “manual edits” renaming the PPS calendar dimension members. When we ran the PSD Import command line the calendar was created with the PPS default labels and when we went to sync the staging db to the app db on the destination, the tool failed because the calendar dimension didn’t match.

Resolution: For auto-generated PPS dims and members such as Time, it is critical that you create the destination application and then go in and rename the members to match those renamed members in the source server.

 

Problem #2 It turns out when migrating the tables, many dimensions get new Primary Id’s assigned to their members. Your scorecard file by default references these id’s (through your KPI creation). After we finally migrated our application we opened our pre-existing Dashboard Designer file and re-mapped the data source to our new AS cube. Our scorecard would not render any values however we were able to create a “new” report pointed to the Olap cube and could see all values associated with each KPI.

Resolution

·         If you DON’T have multiple applications running on one PPS site server, reference Database Mirroring on technet for guidance which will enable exact table replication, therefore not running into the problem of “re-generated” primary key. This is not recommended for environments running multiple applications.

·         Manual Primary Key Remapping: The tedious way which must be repeated for each KPI every time you migrate: manually delete all filters (dimensions and members) associated with each KPI (and each actual, target and trend indicator associated with each KPI)

OR

·         Eliminating the primary key dependency in your Dashboard Designer file. We have to give credit to Srikanth Yalamanchili for pointing us in this direction:

We will likely apply these steps to EVERY Dashboard Designer File we create so that future migrations are not an issue and we have no dependency primary keys that change on every migration. Here are the steps that can be applied prior to or after an application migration:

·         Open the Tables in your PPS App DB for all dimensions being in your Dashboard Designer KPI’s, in our application this was:

                                Scorecard Scenario

                                KPI’s

                                Measures (Actuals, Target and Trend)

·         Identify the primary key # associated with each of the members you are referencing

·         Open the Dashboard Designer file with Notepad

·         Find and replace all primary key strings with the actual “Label” name of the members you are referencing

                                Example for Scenario Reporting:

Find .&[112]

Replace ALL with .[Reporting]

·         Once you have done this save your file and open with dashboard designer.

·         Your dashboard designer file is now independent of “changing” primary keys

·         You can “see” what you have done if you Open a KPI, Open the Dimension Filters associated with the KPI, note that “no” member name is actually highlited here (if it were it would again populate the xml with the primary key # instead of the label)

·         REMEMBER if you add a kpi, or any attribute to a kpi such as trend etc, you will again have a primary key value assigned to that KPI, if you don’t follow the steps above, when you migrate your connection will again break.

 

Problem #2.B We had some KPI’s that use an & in the name i.e. “Windows & Sharepoint….”, these KPI’s could not be replaced with the label because they have an & in the label.

Resolution: For these KPI’s apply Manual Primary Key Remapping noted in the first resolution above.

 

Problem #3 We also ran into an issue when “publishing” these changes to the Sharepoint and M&A Server we were migrating to. We would “Publish” our book to the destination M&A server and all seemed well, however any dashboard previously published to this server with the same name was not truly “overwritten”. This was apparent when we attempted to either preview or publish the new dashboard to sharepoint and received an error that the dashboards couldn’t be found. When we refreshed (anticipating to pull down the dashboard we had just published) what we had published moments earlier was overwritten by the pre-existing dashboard with the same name… again resulting in no values showing for the scorecard.

Resolution We refreshed the Dashboard Designer file from the destination M&A server and  “deleted” all (to be safe) pre-existing published data on the server regardless of name (Dashboards, KPI’s, Scorecards, Reports and Data Sources). We then published our newly edited book and successfully were able to publish to Preview and Sharepoint seeing all of our changes. 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s