Installing PerformancePoint Planning SP1

Well I successfully updated our development server with SP1. Our dev environment is a distributed environment with Analysis Services on one server and PerformancePoint and the relational databases on another. Before installing Service Pack 1 you should review the following documentation on TechNet:

http://technet.microsoft.com/en-us/library/cc514369(TechNet.10).aspx

Here are the steps as outlined on the TechNet site that I went through to install SP1:

Shutdown all the planning services

The first thing I did was shutdown all the PerformancePoint Services. This includes the following two services:

• Planning Process Service
• World Wide Web Publishing Service (IIS)

Backup databases

Once the services have been stopped the following databases need to be backed up:

• PPSPlanningService
• PPSPlanningSystem
• Application Databases

Run SP1 update for the planning server components

Now you can install SP1 for the planning server:

• PPLSrv.msi

This step is very straightforward. Just double click on the file and follow the wizard. The next step is the most important and is probably worth reviewing.

Run the PerformancePoint Configuration Manager

When you run the configuration manager a couple things to note is that when you get to the add/remove service step (#4) you want to select Upgrade Databases. Secondly when you get to the Upgrade Databases screen (#5) you need to check the option that says Upgrade Application Databases. You can also specify the location of the Application Database Upgrade log file which is useful if the upgrade fails. Below are the screen shots for most of the key steps:

1. Open the Configuration Manager	2. Click Next
3. Verify Components	4. Upgrade databases
5. Check upgrade databases	6. Validate upgrade
7. Final confirmation	8. Install status

After you install SP1 you will need to restart the server. Once the server has been restarted make sure all the necessary services are running:

• Planning Process Service
• World Wide Web Publishing Service (IIS)

You may also need to ensure that the SQL Server Relational Service and the Analysis Server Service are running depending on your server configuration (Startup setting for the service could be set to manual). Once you have verified that all the services are running make you will need to access the Planning Administration Console (http://servername:46788/). Navigate to the PerformancePoint Applications. The applications should all be set to Offline. You can now set the application to Online:

Run SP1 for all client computers

Although installing SP1 if very simple for the client computers it is important that you upgrade these components (Business Modeler and the Excel Add-in) before connecting to the PerformancePoint Server. After the components have been successfully installed you will need to reboot the client machine. Here is a list of the install files for the client:

• PBMCli.msp (Business Modeler )
• PPLXCli.msp (Excel Add-in)

Taking it for a test drive

Well once you have installed SP1 its a good idea to take it for a little test drive. I thought a good test would be to deploy the Alpine Ski House application. Sure enough I deployed the site and everything worked okay. I also created a quick form based on the Alpine Ski House application and there does seem to be a significant increase in performance. One cool thing to note is that you can now apply conditional formatting for numbers and the formatting will remain after you refresh the worksheet or workbook. This should decrease the need for macros to format your reports.

Although I was able to install SP1 in an evening on our test servers it will definitely require a lot more time and planning for production environments.

Troy

Technorati Tags: performancepoint, performance point, microsoft, analysis services, sql server 2005, planning, budgeting

PerformancePoint 2007 Service Pack 1 - Released!

PeformancePoint Service Pack 1 is now available from Microsoft. Check out this post:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3444589&SiteID=17

The most significant change for Service Pack 1 will be the increase in performance when opening/closing and refreshing forms and reports in the PerformancePoint Excel Add-in. I'll be installing the service pack on our test server over the next couple of weeks. It is important to note that the service pack cannot be applied to pre-leases of PerformancePoint (e.g. CTP 4). You must upgrade from the pre-release version to RTM fist before applying the update:

http://technet.microsoft.com/en-us/library/cc514369(TechNet.10).aspx

Troy

Technorati tags: performancepoint, performance point, microsoft, planning, budgeting, sql server 2005, analysis services, business intelligence

New Data Migration Tool for Planning!

On May 30, 2008 Microsoft released a data migration tool for PerformancePoint Planning (free download). Although it was possible to migrate a Planning application using PPSCMD, it was only possible to migrate the application objects (dimensions, models, security etc.). The fact and reference data had to be migrated manually. For example once you have set up your test or dev environment you may want to migrate the fact and reference data to the production server. Below is a link to the download and the documentation.

• PerformancePoint Server 2007 Planning Data Migration Tool
• Data Migration Tool Overview
• Migrating Planning Data from a Test Environment

I am not sure what level of preparation is required with the new tool. Hopefully it is as simple as taking a backup of the StagingDb from the Source Application and restoring the database in the Destination environment. Once the database is restored in the new environment you can register it with the new application. At this point the tool can take care of the migration. Make sure you review the Migrating Planning Data from a Test Environment on the Tech Net site before trying out the tool.

I should get an opportunity in the next couple of months on my current project to try the tool out.

Troy

Technorati Tags: performancepoint, performance point, microsoft, sql server 2005, planning, budgeting, business intelligence

Enabling NativeSQL/NativeMDX Rules

In order to enable Native SQL and Native MDX rules you must perform the following two steps:

• Enable Native SQL/MDX Rules in the Administration Console
• Have a DBA (Database Administrator) activate the rule in the back end

The above steps cannot be performed by a user who only has Business Modeler rights. In order to perform these steps the user will require Global Administrator (GA) rights.

Enable Native SQL/MDX Rules in the Administration Console

The first step can be performed when creating a new PerformancePoint Application or after the Application has been created. It is quite possible that Native SQL/MDX rules have not been enabled for the Application your are using because the default setting is set to false. To enable these rule types the (GA) needs to access the Planning Administration Console and select the appropriate planning application. In our case I will select Alpine Ski House:

Now select Edit to edit the application settings:

The Alpine Ski House Application is currently not configured to use Native SQL/MDX rules. Simple select the check box and then click OK.

At this point the GA can now inform the Business Modeler that they can use native SQL/MDX rules.

Activating the Rule in the Database

To demonstrate how to do this I will create a very simple Native SQL rule (it doesn't get much simpler than this). The rule below will provide no functionality to the Alpine Ski House Application (that will be a topic for a future post) but it will allow me to show you the necessary steps required to enable a native rule. Here is the SQL statement we will use for the rule:

select 1

Now we need to create the rule in the Business Modeler. I going to create a new rule set in the Corporate Costs model in the Alpine Ski House Application. Make sure the model is checked out and select the Business Rules tab. Now select Create a Rule Set. Enter Dev for the Name and Label. Make sure the rule set is Procedural and that is a Root rule set:

Select OK. Now we can create a native SQL rule. Right-Click the Dev Rule Set and select Create a New Rule.... . Enter Test SQL for the name and label of the rule. Set the rule type to Assignment and the implementation to Native SQL.

The first thing you will probably notice is that the rule has a status of Inactive. Do not change the status of the rule. After you have created the business rule you must save the rule with the Inactive Status or you will get an error. Now we can enter our simple business rule:

select 1

Once the rule has been entered you can now save the model site. At this point you will need a GA or a DBA to activate the rule in the Application Database. The GA or DBA will use SQL Server Management Studio to logon to the Application database and look for the rule in the RuleSetsOrRules table. Use the following query to find the rule we created in the Alpine_Ski_House_AppDB:

select * from dbo.RuleSetsOrRules
where RuleSetOrRuleLabel = 'Test SQL'

When you run this query you will notice that the IsActivated field is set to 0. We need to update this value to 1 so that the rule can be activated and executed from the Modeler. Now run the following query to update the IsActivated field to 1.

update dbo.RuleSetsOrRules
Set IsActivated = 1
where RuleSetOrRuleLabel= 'Test SQL'

Total row affected for the update query should equal one. If you want to check that the value was updated you can run this query again:

select * from dbo.RuleSetsOrRules
where RuleSetOrRuleLabel = 'Test SQL'

The IsActivated field will now contain the value of 1:

Now the GA or DBA can inform the Business Modeler that the rule is Active. Once the rule has been activated the Business Modeler can execute the rule. One thing to note though is that if you are creating Native SQL/MDX rules you will want to thoroughly test them outside of the Business Modeler because every time you modify and save the rule PerformancePoint will set the Status flag of the rule to InActive. This means that you have to save the rule and then get the GA or DBA to change the status to Active. Even if you are a GA or DBA rights this task would become tedious. Therefore it is a good idea to use a tool like SQL Server Management Studio to create your native rules. If you only have Business Modeler rights then you will want to work closely with your DBA to develop these rules.

Lets try running the rule. When you refresh the Business Modeler the rule Status will change from InActive to Active. You can right-click the rule and select Execute Rule Set or Rule... . You will probably be prompted to Deploy Rules and Variables. Answer yes to this. Once the rules and variables have been deployed the rule will be executed. If everything was setup correctly you should see the following message box:

Troy

Technorati Tags: performance point, performancepoint, budgeting, planning, business intelligence, microsoft