PerformancePoint 2007: Installing Planning Server

s clients ask for more complex dashboards that integrate data from their accounting systems, you’ll find that Performance Point Server (PPS) can help you answer the call. It’s quite hard to find useful information on “what” PerformancePoint is, and what it allows you to do, so this series of articles attempts to remedy that by documenting the installation process, and detailing what you really need to get some meaningful results—an actual dashboard that can impress in a demo. By following along here, and making sure that you have the prerequisites taken care of, you should be able to get a dashboard up and running—in a lab environment—in a handful of hours.

As these articles will show, the configuration process is complex and somewhat daunting if you try to get a sense of the process by reading the Microsoft documentation. As a point of reference and an actual anecdote, a Microsoft employee, who will remain anonymous, declared to me that the PerformancePoint configuration process was the most complex he’d seen from a Microsoft product.

From a high level, the PerformancePoint suite includes the following main components:

  • Planning Server
  • Monitoring Server
  • Analytics Server (ProClarity Analytics Server)

Figure 1 shows the Microsoft Business Intelligence stacks and where PerformancePoint Server 2007 is located in the stack.

 
Figure 1. Microsoft BI Stack: SQL Server serves as the foundation, upon which SharePoint Server 2007 builds, and then PerformancePoint “adds the icing to the cake.” (Courtesy: Microsoft).

Each installation procedure is complex, so this article series covers the installations in separate articles, starting with the Planning Server.

Author’s Note: Here’s a timesaving tip: Before moving forward, make sure you have the “Cumulative Update Package 3 for SQL Server 2005 SP 2.” That will probably require you to submit a ticket to Microsoft, and they may take a couple of days to respond!

Planning Server Overview
Microsoft’s TechNet site provides a good overview of Planning Server. Cutting through some of the extra marketing content, here’s a brief overview.

From a high level, PPS competes with products such as Hyperion (recently purchased by Oracle) and Cognos (now acquired by IBM) but at a much lower price point. Planning Server is just one of the components of PerformancePoint; it provides an enterprise-class application for supporting management processes—specifically, processes that relate to business planning and management reporting. Essentially, an organization would use Planning Server to create “what if” scenarios running against OLAP cubes of data created from SQL Server 2005.

Planning Server is built on Microsoft SQL Server 2005-based databases and SQL Server 2005 Analysis Services. It uses Excel for line-of-business reporting and analysis. It also supports the management and financial Generally Accepted Accounting Principles (GAA) consolidation process with multiple currency conversions, inter-company eliminations and reconciliations, and multi-tier allocations. It lets business users publish live reports from Excel to SQL Server 2005 Reporting Services, Microsoft Office SharePoint Server 2007, or Windows SharePoint Services 3.0. Power users can build, customize, and share production-quality reports from Excel while connected to a centrally-managed server.

Planning Server Installation Steps
TechNet has several guides available for planning, architecture, and deployment. The first step was to download the PerformancePoint ISO file (en_office_performancepoint_server_2007_x86_cd_X14-00932.iso) from MSDN. The file is around 95.5 MB.

Next, I took snapshots of my MOSS 2007 server and the SQL 2005 Server—just in case. Having solid server images so I can roll back if things don’t go well has saved my bacon a number of times, as well as many hours of pointless work rebuilding servers.

Installing SQL Server and SQL Server Analysis Services
One of my basic questions was where to install PPS, so I figured I’d start with the “PerformancePoint Server 2007 Deployment Guide” and more specifically, the “Installing SQL Server and SQL Server Analysis Services” section, where you’ll find these key prerequisites:

  • Microsoft Office PerformancePoint Server 2007 requires Microsoft SQL Server relational engine and SQL Server Analysis Services to be used for storage purpose.
  • SQL Server 2005 with Service Pack (SP) 2, relational components only. Developer Edition or Enterprise Edition only.
  • A cumulative update has been released for SQL Server 2005 SP2. This update is called “Cumulative Update Package 3 for SQL Server 2005 SP2” and is also known as build 3186. All Analysis Services computers in your PerformancePoint Server environment must have this update applied prior to your Planning Server installation.
  • SQL Server setup and support files
  • SQL Server Native Client components
  • OLE DB Provider for OLAP Services 9.0
  • Microsoft Core XML Services (MSXML 6.0)

Going through that list, I verify that my ABCSQL01 server does have SQL Server Enterprise installed, and has SP2. I didn’t think I’d installed the “Cumulative Update Package 3 for SQL Server 2005 SP 2.” So I open up the Microsoft SQL Server Management Studio and I see that, according to the title of the top node in the left-hand pane, my build number is 9.00.3042—which is lower than Build 3186, the required build. So I visit the download link, but—as expected—that page simply tells me to submit a request to Microsoft Online Customer Services to obtain the cumulative update package. I do that, filling in a couple of fields to tell them I have SQL Server 2005, 32 Bit, English version, and give them my email address. Now I get to wait!

Author’s Note: I tried http://thehotfixshare.net but they don’t seem to have any of the SQL hot fixes. Drat.

Nothing happened for two days, so I submitted my request again (who knows, it might not have gone through the first time) and finally, I get a link to download the hotfix (a file named 322810_intl_i386_zip.exe) about a day and a half later. Now the fun can start!

The rest of this article details the steps I followed to complete the install. I’ve numbered the actual steps, and put other information you might need to know in between. As you read these articles keep in mind that the steps documented here aren’t prescriptive, they’re descriptive. In other words, these are the actions that I took to install the software, on my particular server configuration. I’ve documented the process so others will know what to expect. My hope is that readers will be able to leverage my experiences for planning their own installations, or to anticipate the undetaking.

  1. I start the hot fix install on my ABCSQL01 server by double clicking the executable. That simply extracts another .exe file, named SQLServer2005-KB936252-x86-ENU.exe. I double-click that and the SQL Server Setup loads.
  2. I accepted the defaults on the Feature Selection Window (see Figure 2). The installation then checks for locked files, and since SQL 2005 is running, tells me I’ll have to stop SQL services to load it, which I do (see Figure 3 for the view of Services in Computer Management console after the SQL Services have been stopped). When you get a “Locked files not found. Press Next to continue” message, click Next.
 
Figure 2. SQL Server 2005 Service Pack 2 Cumulative Hotfix: You should not change these installation options.
 
Figure 3. Computer Management: Stop the SQL-related services so the hotfix installation can proceed.
  1. At that point, you can click Install, and the install should start (see Figure 4). So far, this seems pretty much the same as any other SQL Server 2005 Service Pack.
 
Figure 4. Installation Process: Sit back, relax, and watch the blue bar grow.
  1. When the install completes (it took about 20 minutes on my VMware SQL server), follow the on-screen directions to complete the install.
  2. I rebooted the server at this point just to make sure everything would start up properly, and there were no error messages on boot up, nor were there any unpleasant errors in the logs.

Now I make sure I have the other items on the prerequisites checklist. I’m pretty sure I have the SQL Server setup and support files and SQL Server Native Client components, because I installed everything on this server, but I’m not sure about the OLE DB Provider for OLAP Services 9.0 or Microsoft Core XML Services (MSXML 6.0), so I follow the link they provide. Looks like the file I need is msxml6.msi.

  1. I download msxml6.msi and try to install, but the installation fails, telling me I have a newer version . (6.10.1200.00) already installed, so I should be good there.

I suspect I also need the OLE DB Provider for OLAP Services 9.0. There’s no apparent download on Microsoft’s site, but a little browsing indicates that this is included as part of Analysis Services, which I do have installed. This page mentions that the DLL associated with OLE DB Provider for OLAP Services 9.0 is msolap90.dll.

  1. A quick file search on the SQL server finds msolap90.dll in C:Program FilesCommon FilesSystemOle DB, which I take as proof that I have the OLE DB Provider for OLAP Services 9.0.

OK, so I’m pretty sure I have met all the prerequisite criteria and can move forward.

 
Figure 5. Server Properties from the SQL Server Management Studio: Verify that the Server Collation setting matches Microsoft’s recommendations for case-insensitive collation, or the setup will fail.

I glance through the steps for installing SQL Server 2005, which I’ve already done. The section on “Selecting Collation Settings” catches my attention, as I’ve been burnt by servers have the wrong collation settings.

  1. I verify my settings in SQL Server Management Studio, by right clicking on the top level node and looking under “General settings” (see Figure 5). Microsoft recommends SQL_Latin1_General_CP1_CI_AS, which is the default for a SQL install. I verify that this is in fact the Server Collation method being used. The “CI” in this collation refers to the fact that it is case insensitive, and as the note tells us, “Microsoft Office PerformancePoint Server 2007 requires case-insensitive collation be configured for its host SQL Server. If the host SQL server is configured to use case-sensitive collation, Setup will fail.”

That’s the end of the “Installing SQL Server and SQL Server Analysis Services” instructions, so I move back to the “PerformancePoint Server 2007 Deployment Guide.” The next link in that document is for Planning Server.

Installing Planning Server
Planning Server consists of the following components:

  • Planning Web Service: Planning Web service for Planning Business Modeler and PerformancePoint Add-in for Excel.
  • Planning Process Service: Planning Process service that is used to process jobs and workflow.
  • PerformancePoint Planning Remote Administration Console: Planning Administration Console service that is used to administer the Planning and Processing Web Services. Planning Administration Console can be used remotely or locally.
  • PerformancePoint Planning System and Service Databases: Databases used by all Planning servers.
  • PerformancePoint Planning Application and Staging Databases: Databases used by a planning application.

Next, I glance over the Planning Server Prerequisites listed on this page, and don’t see any showstoppers for my hardware configuration. I visit the software prerequisites page and see the following:

  • Windows Server 2003 with Service Pack 1 (SP1), Standard Edition
  • Microsoft Windows XP Professional with Service Pack 2 (SP2)
  • Microsoft Office 2003 with Service Pack 2 (SP2)
  • SQL Server 2005 with Service Pack 2 (SP2), Enterprise Edition
  • A cumulative update has been released for SQL Server 2005 SP2. This update is called “Cumulative update package 3 for SQL Server 2005 SP2” and is also known as build 3186. All Analysis Services computers in your PerformancePoint Server environment must have this update applied prior to your Planning Server installation.
  • Windows Installer 3.1
  • Microsoft .NET Framework 2.0
  • Microsoft Internet Information Services 6.0 (IIS)
  • Microsoft ASP.NET 2.0

OK, so now I’m getting itchy and just want to install the software, so I jump to “Installing and Configuring Required Planning Server Software.” I’d like to get the stand-alone configuration working, since it’s only a test environment. Another click takes me to “Installing Planning Server,” which says, “You must configure the following four components:”

  • Planning Server databases
  • Planning Process Service
  • Planning Web Service
  • Planning Administration Console
 
Figure 6. PerformancePoint Installation Screen: After meeting the prerequisites, select the Planning Server installation to get started.

Sounds good?so I then click on Install Planning Server on a stand-alone computer. Looks like I can finally install the software!

  1. I map my VMWare SQL Server CD drive to the ISO (en_office_performancepoint_server_2007_x86_cd_X14-00932.iso) and get the install screen (see Figure 6).
  1. I click on Install Planning Server. I accept the licensing terms, click Next.
  2. I select the default installation path, click Next, then click Install, and the install starts.
  3. The install finishes in 30 seconds or so and I am given a check box option to “Run the Planning Server Configuration Manager Wizard.” I leave it selected and click Finish.
  4. The Planning Server Configuration Manager Wizard (see Figure 7) opens, and I click Next.
 
Figure 7. Planning Server Configuration Manager: By the time you’ve reached this screen you should have already installed any prerequisites, but the wizard will validate your setup to ensure you have everything.
 
Figure 8. Planning Server Configuration Manager: Here are the results of the prerequisites check.

  1. The Wizard checks to make sure all the prerequisites are met (see Figure 8), which in this case they are (all that prep work paid off so far) and I click Next.
  1. On the Installation options page, I leave the “Standalone Configuration” circle checked (see Figure 9) and then click Next.
 
Figure 9. Planning Server Configuration Manager: Select the Standalone configuration for a “simple” lab environment.
 
Figure 10. Planning Server Configuration Manager: Specify your SQL server. For a lab environment, simply choose “Create databases for Planning Server.”

 
Figure 11. Planning Server Configuration Manager: Insert the appropriate domain and user name for the Global Administrator account.
  1. On the Databases page, I leave the default settings (see Figure 10). The page correctly identifies my SQL server as ABCSQL01 and tells me that the databases will be created. I click Next.
  1. On the Global Administrator account page (see Figure 11) I keep the Domain (ABC), and the User Name (Administrator), for the Global Administrator account and click Next. In a production environment I’d probably want to designate a specialized administrator to this role.

Installing Planning Server (continued)

  1. On the Analysis Services setting screen I leave the Analysis Server location (see Figure 12) as the default ABCSQL01. Click Next.
 
Figure 12. Planning Server Configuration Manager: Choose the SQL Server Analysis Services (SSAS) instance.

  1. The Service and Application Pool Identities page asks me to specify an account to use as the service identity for the Planning Process Service, which is also used as the Application Pool identity for the Planning Web Site and for the Planning Administrator Console. I decide it’s probably best to create an account for this purpose, because it’s good practice to avoid using a local administrator account for these more specialized roles. So I create a SQLPlan account in Active Directory and use that (see Figure 13). I Click Next.
 
Figure 13. Planning Server Configuration Manager: Specify the user account for the service identity. This account will also be used as an Application Pool identity.
 
Figure 14. Planning Server Configuration Manager: Define a system log location, and enable auditing and/or trace logging.

  1. On the System Logs page, I leave the default path on the C drive, and leave “Enable auditing” and “Enable trace logging” checked (see Figure 14). For a real world configuration you’d most likely change to path to the logs drive. Click Next.

  1. On the Port Configuration page, I uncheck “Require SSL connections to all Planning Server Web Sites” because this is just a test lab, and I don’t want to go through the bother of getting and installing an SSL certificate and this should simplify the configuration process (see Figure 15). I choose some more memorable port numbers for Planning Web Server (41000) and Planning Administration Console (42000) and then click Next.
  2.  
    Figure 15. Planning Server Configuration Manager: Enable SSL connections if needed, and set ports for Planning Web Server and Planning Administration Console.
     
    Figure 16. Planning Server Configuration Manager: This screen provides validation of the previous settings.

  3. A quick validation happens, as shown in Figure 16. Click Next.
  4. The next screen is the confirmation screen, so I review my settings briefly and click Configure. This takes a couple of minutes, and there’s an attractive spinning progress wheel to watch, and then I see the “Configuration completed successfully” note, and the click Close.

That phase of the installation appears to be done. A click of the Start button and glance at the All Programs menu shows a new Microsoft Office PerformancePoint Server 2007 entry, which contains links to documentation and the Planning Server Configuration Manager (which is what I just went through).

Planning Server Administration
I open IIS Manager and note that there are two new Web sites, “PPSPlanningWebServices” and “PPSPlanningAdminConsole.”

  1. I open Internet Explorer and try to access port 41000 (enter http:abcsql01:41000) and 42000 (http://abcsql01:42000). The second URL opens up the “Planning Administration Console” (see Figure 17), and I click on the Connect button after the location field, which conveniently displays the path to the other Web site (http://abcsql01:41000) in it. The status changes from red to green and the message indicates that I am now connected to the computer in question (also shown in Figure 17).
 
Figure 17. Planning Administration Console: This screen lets you specify the path to the server running Planning Server.

So far, this is encouraging, but there are still more pieces to install. The next step in the installation process is called “Creating file shares,” and the corresponding “Creating File Shares” documentation page indicates the following:

“Microsoft Office PerformancePoint Server 2007 uses a network file share for templates and data files that are created by and for Planning Server users.

This shared folder name should match the application name for each application, and each application should have its own share. If a shared folder is created, it must be in a Universal Naming Convention (UNC) location available to users. In addition, the Planning Server service identity (SI) account and all users of PerformancePoint Add-in for Excel must have access to the location. Permissions must include read, write, and modify.

These are the recommended subfolders for each file share location:

  • Form templates
  • Report storage
  • Assignment forms
  • Assignment master forms”
      1. That sounds pretty straightforward, so I create this structure on my Windows Server 2003 domain controller (ABCDC01). I give the abcadministrator and abcSQLPlan accounts full control, and allow Everyone to change and read in this share.
       
      Figure 18. Create an Application Window: Follow the instructions in the article for a sample configuration.

      The next step on TechNet is “Creating an Application” which requires the following steps:

      1. Click “Applications” in the navigation pane of the window, and then click “Create.”
      2. The “Create an Application” window pops up, and it is quite intimidating (see Figure 18) but I’m guessing I can get through it.
      3. So I’ll keep the name simple and call it “Application1,” and the label “Application1Label,” give it a quick description, and plug in the SQL Server name. I enter a new database name, with the assumption that the application gets its own database (That may be wrong, but TechNet doesn’t provide much information on this topic; my reasoning is that there would probably be a lookup or browse button if I needed to pick an existing database).
      4. Then I enter the locations of the files—the share and subfolders I created a little while ago. See Figure 19 for the details.
      5.  
        Figure 19. Create an Application Window: Here’s how the sample configuration looks for the first ten fields.

         
        Figure 20. Create an Application Window: The figure shows the sample configuration for the Advanced Options, Application Scripts and Default Model Site fields and options.
      6. After perusing the TechNet information, I decide to leave the Advanced Options and the Application Scripts option checked.
      7. Under Default Model Site I enter a site name, App1RootSite, give the label the same name, and tell it that ABCSQL01 is the Analysis Services computer (see Figure 20). I click OK to continue.

      Voila, that seems to have worked, as shown in Figure 21. It did create a new database, titled PlanningApp1. Out of curiosity I check the settings for this database, and SQLPlan is the owner, Full Recovery model is selected, and the size of the database is about 43 MB. Back in the Planning Administrator Console, I notice that under the Model Sites link my App1RootSite now shows up (see Figure 22).

       
      Figure 21. New Application: The figure shows the results of a successful application creation process.
       
      Figure 22. Planning Administrator Console: The new Application Name now appears in the Model Sites page.

      Almost done. I go back to the Install Screen for PerformancePoint 2007. The next item listed is Planning Business Modeler, so I’ll install this next, even though the TechNet instructions don’t seem to specifically refer to it.

      Installing Planning Business Modeler
      I start installing the Planning Business Modeler.

       
      Figure 23. Planning Business Modeler Setup: Accepting the default installation path leads to a quick installation.
      1. Click on Planning Business Modeler.
      2. Accept the license terms. Click Next.
      3. Accept the default installation path. Click Next.
      4. Click Install to start the installation. This completes in a few minutes (see Figure 23). Click Finish.

      Installing the Excel Add-In
      The only piece left is to install the PerformancePoint Add-in for Excel, but since I don’t have Excel on the SQL Server, I’ll install this on my MOSS Server (ABCMOSS02) which has the Office 2007 applications installed.

       
      Figure 24. Installing the PerformancePoint add-in for Excel: A prerequisites check takes place on the server or system where the add-in is installed.
      1. From ABCMOSS02 I map to the ISO, and re-launch the installation program. I then click on “Install PerformancePoint Add-in for Excel,” it reviews the prerequisites, and tells me I need ADOMD.NET 9.0. The display provides a link where I can get the ADOMD.NET install (see Figure 24). My MOSS Server apparently meets the other criteria for the Excel Add-in.
      1. I visit the ADOMD.NET install link and download the application, and then install it by following the prompts. I get a message that I have an older version of the application installed, so I agree to upgrade it.
      2. After the ADOMD.NET install completes, I get to a “Directory Selection” screen. I accept the default location and click Next.
      3. On the next screen I click on “Install” to start the installation.
      4. This completes in 30 seconds or so, and I click Finish to complete.
      5. Out of curiosity, I open Excel 2007, and find I now have a “PerformancePoint” tab as shown in Figure 25.
       
      Figure 25. Excel 2007 After PerformancePoint add-in for Excel installation: Note the new PerformancePoint tab in this figure.
       
      Figure 26. Testing the Connect Button for the Excel 2007 Add-in: Testing the connection verifies that the system with Excel 2007 and the add-in can connect properly.

      1. I then click the Connect button on the ribbon, as it is one of the few active buttons, and enter the url of the PerformancePoint System, as shown in Figure 26. I get a “Successfully connect” message, and click OK.
      2. I click OK again to close the window, and another connection window pops up (see Figure 27). Once that completes, I have additional tools available on the ribbon, and a new task pane, as shown in Figure 28.
      3.  
        Figure 27. Testing the Connect button for the Excel 2007 Add-in: When you press Connect, this dialog provides additional connection details.
         
        Figure 28. 2007 After Installing PerformancePoint Add-In for Excel: After connecting to the Planning Server, you’ll find additional tools available in Excel by clicking the new PerformancePoint tab.

      4. Because I’m still in installation mode, and need to continue with the Monitoring Server, I won’t spend more time with the tools at this point, but I will come back to it after the installation is complete.

      That completes the first portion of the install. As more and more organizations come to see Microsoft as a legitimate player in the BI arena, IT departments will be asked to provide prototype and testing environments with PerformancePoint installed. The next article in this series will cover installing the Monitoring Server, and after that I’ll tackle the Analytics Server (still currently branded as the ProClarity Server). I hope this article will help give you the confidence to move forward with the install process. At the very least, it should give you a decent checklist of the steps that take place during the PerformancePoint Planning Server installation process.

      Share the Post:
      Share on facebook
      Share on twitter
      Share on linkedin

      More From DevX