SQL Server 2005 Analysis Services with the Project Server 2007 : Part 4

Managing Cube Building Service

The Microsoft Office Project Server 2007 Cube Building Service uses the technology provided by SQL Analysis Services to create a database containing several online analytical processing (OLAP) cubes that are used for data analysis reporting. After configuration, you need to manage tasks such as cube build & configuration setting, manage view .

• Analysis Services settings In Project Server
• Build Settings
1. Log on to Project Web Access as an administrator.
2. Click Server Settings. In the Cube section, click Build Settings.
3. Analysis Services Settings - specify the information for the server on which SQL Server Analysis Services is running,name of the database that is used by Analysis Services(If the database does not exist, it will be automatically created.),Extranet URL for accessing the OLAP cube and Portfolio Analysis views from outside the Intranet and Description
4. Database Date Range - Specify the date range of data you want included on the cube ·
5. Cube Update Frequency - Specify how often you want the cube to be updated
6. click Save / Save and Build.
• Cube Configuration
  customize the Project Server OLAP cubes by adding custom fields as dimensions or measures to the cubes associated with the selected entity, and by adding calculated measures. The OLAP cube will contain this information the next time you update the cube, and any selected custom field will only appear after its data have been published.
1. Log on to Project Web Access as an administrator.
2. Click Server Settings. In the Cube section, click Configuration.
3. Cube Dimensions section - Specify the custom fields you want to add to the cube as dimensions. The selected custom fields will be added to both Timephased and Non-timephased cubes, when applicable.Note: Only custom fields that use lookup tables will appear in list.
4. Cube Measures section - Specify the custom fields you want to add to the cube as measures. The selected custom fields will be added to the related Non-Timephased cube.
5. Calculated Measures section - Specify an MDX expression to define the calculated measure
6. Save data
   
• View OLAP Cube data
  
  • Create a Data Analysis view
  1. Log on to Project Web Access as an administrator.
  2. Click Server Settings.In the Look and Feel section, click Manage Views.
  3. On the Manage Views page, click New View.
  4. Name and Type section - select Data Analysis type and enter a name and description for the Data Analysis view.
  5. Analysis Services Settings section -
     a. select the Default server option, you will use the instance of SQL Server Analysis Services you specified when you configured your cube build and configuration settings. (If you select the Custom server option, you have the option to select a different instance of SQL Server Analysis Services.)
     b. select the database that hosts the Project Server 2007 cube and select the cube you want to use for this view. There are fourteen cubes to choose from that are automatically generated by the Cube Building Service (CBS). You can also create cubes that store additional data not created by the CBS.
  6. View Options section - choose how you want the data to be displayed
  7. View Definition section - pick default measures and dimensions from the PivotField List and add them to the PivotTable or PivotChart. Choose the data you want to display in the view and drag it to the row, column, or filter field in which you want it to appear.
  8. Security categories section - select the categories that you want to make available to this view.
  9. Click Save to create the view.
  
  
  • Example : Timesheet Compliance view
  To create this view, follow these steps:
  
  1. Open PWA -> Server Settings -> Manage Views -> Select New View
  2. In the View Type section, select Data Analysis. Type Timesheet Audit Report in the Name box and a description in the Description box.
  3. In Analysis Services Settings section, select your Analysis Services server
  4. In the Analysis Services Database list, select the appropriate database.
  5. In the Cube list, select the MSP_Project_Timesheet cube.
  6. In the View Options section, select PivotTable with PivotChart and Show Toolbar.
  7. Select Timesheet List, Timesheet Period and any enterprise custom field, that you might have defined and configure in cube configuration, from the PivotTable Field List and drag them to the Drop Filter Fields Here area of the PivotTable workspace.
  8. Add the Fiscal Time dimension to the Drop Column Fields Here PivotTable area to organize your view data by years, quarters, months, and days.
  9. Select Project List and Resource List from the PivotTable Field List and drag them to the Drop Row Fields Here PivotTable area
  10. Expand the Totals set of fields in the PivotTable Field List, select Work, Actual Work,and Remaining Work, and then add them to the Drop Totals or Detail Fields Here PivotTable area.
  11. Now all your projects and resources are displayed as part of the view, making the view, especially the chart part, too cluttered. Use any filter field to restrict the data displayed.
  12. You can also control the graph type you want to use with your Data Analysis views.Review the default char.Change to a different chart type by selecting the Chart Type button
  13. Add the appropriate security categories to access the view.
  14. select Save at the bottom of the page.
  
  
  
  
  
  

-------------------------
<<Part 3
-----