Do More Than Manage
Gord Schmidt's Ideas for Doing More with Microsoft Project Server
Summary Task

Connect to the Project Server OLAP Cube with Excel 2000

I have been working with a customer to produce Portfolio Analyzer Views. They liked the flexibility given to the user to modify the report based on the OLAP cube. They were interested in outputting the results to Excel to "play with the numbers" -- essentially "what if"-type scenarios. "Sure," I said, "You can export the results to Excel."

But wait! The client was running Excel 2000 and Excel XP/2003 was not just around the corner for them. Sigh.

Fortunately, OLAP cubes have been around awhile and Excel 2000 can deal with them. So instead of providing the report as a Portfolio Analyzer View in PWA, we could provide the report right in an Excel 2000 spreadsheet.

How does one connect to the Project Server OLAP Cube with Microsoft Excel 2000 (this works for other versions too)? I'm glad you asked.

  1. From the "Data" menu, pick "PivotTable and PivotChart Report". A PivotTable and PivotChart Wizard will begin.
  2. For the question "Where is the data that you want to analyze?" pick "External data source" and click the "Next" button.
  3. For the second step of the Wizard, you will need to click the "Get Data..." button.
    PivotTable and PivotChart Wizard - Step 2 of 3
  4. In the "Choose Data Source" dialog box, pick the "OLAP Cubes" tab and select "<New Data Source>" (or select the data source to the Project Server OLAP cube you set up previously) and click "OK".
  5. In the "Create New Data Source" dialog box, enter the following information:
    1. What name do you want to give to your data source? Project Server OLAP Cube
    2. Select an OLAP provider for the data you want to access: Microsoft OLE DB Provider for OLAP Services 8.0
    3. Click the "Connect..." button.
    Create New Data Source
  6. For the "Multidimensional Connection" dialog box, enter the following:
    1. Choose the location of the multidimensional data source that you want to use: Analysis Server
    2. Server: <The Name of your Analysis Server machine>
    3. I was using integrated Windows security, so I left the User ID and Password fields blank.
    4. Click the "Next" button.
  7. On the next page, select the database (as you named your Project Server OLAP Cube) and click "Finish".
  8. This will return you to the "Create New Data Source" dialog box where you can now select the Cube that contains the data you want; namely MSP_PORTFOLIO_ANALYZER
    Create New Data Source
    And then click the "OK" button.
  9. You will return to the "Choose Data Source" dialog box where you should click the "OK" button.
  10. You will return to the PivotTable and PivotChart Wizard where you should click the "Next" button.
  11. For the final step of the Wizard, choose the values that are appropriate to your situation and click the “Finish” button which should give you something like this:
    Excel 2000 with a Project Server OLAP Cube Pivot Table

Although the interface has been revamped, the basic functionality remains the same. The views that you can create in the Portfolio Analyzer can be generated in Excel 2000 as well.

Posted by Gord Schmidt on Tuesday, May 02, 2006 | Permalink | Extend