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.
Â· 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)
· 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:
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:
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.