OLAP database management (Project Server 2010)
Applies to: Project Server 2010
Topic Last Modified: 2011-08-05
Through Microsoft Project Web App (PWA), multiple OLAP databases can be delivered that contain the specific resources, projects, and custom fields that each group within your organization requires for its particular group reporting needs.
In Microsoft Project Server 2010, you can create multiple OLAP databases that have the following characteristics:
They only contain data for projects and resources that they administer
They only contain facts and dimensions that they select from the new integrated OLAP database management user interface
They support departmental filtering to restrict which projects, custom fields, and resources are loaded into the OLAP database
They support selection of intrinsic measures for inclusion/exclusion. For example, you can remove fields that you may not use, such as baseline cost 7, to reduce data clutter.
They include data for Inactive Tasks and User Scheduled Tasks
They let you choose whether to add Timephased/NonTimephased data
They have support for Multiple Measure groups in a single OLAP database
They contain field names in multiple languages to enable multi-language report creation
Also, when a new OLAP database is created, the necessary Office Data Connections and Excel Reporting templates are created in the Business Intelligence Center in the Reports folder. This data-connected blank template will help you quickly create new reports that are based on the new OLAP database.
For more information about the cubes created within each OLAP database, see Cube Build Service (https://go.microsoft.com/fwlink/p/?LinkId=186369) in the MSDN Library Online.
Departments
The Department field is a new feature for Project Server 2010. Both projects and resources can have departments. The main purpose of departments is to act as a filter for what custom fields are displayed to users within given areas of Microsoft Project Professional 2010 and PWA. Departments allow for different business units to define and make visible their own set of custom fields. Departments are also used to filter OLAP databases so that only the data for that department is loaded.
When configuring a cube, you can specify both the project and resource departments so that the database data is filtered based on these criteria. These values are specified in the OLAP Database Build Settings page.
Also, within the OLAP database configuration, you can add the Project department field as a dimension to the Project and Tasks cubes. And you can add the Resource department field as a dimension to the Resource cube as long as the department field has not been converted to a multi-value field.
With Project Server 2010, departmental custom fields help relieve the problem of too much information and too many choices. Departments help you manage the custom field list, and help you define, at a resource, task, or project level, which fields are required or not required.
Whereas in Microsoft Office Project Server 2007 all custom fields are globally scoped, which means the fields are available to all users, in Project Server 2010, fields can be globally scoped or they can be scoped to a specific department.
Departmental fields enable two primary functions:
Filtering custom fields so that a user sees, by default, only those fields that are either global to the system or in the department that the user belongs to.
Controlling which fields require input.
Example of departments in use
| Field | Scope | Department | Required? |
|---|---|---|---|
ProjectCustomText1 |
Global |
- |
No |
ProjectCustomText2 |
Global |
- |
Yes |
ProjectCustomText3 |
Department |
Marketing |
No |
ProjectCustomText4 |
Department |
Marketing |
Yes |
ProjectCustomText5 |
Department |
Development |
Yes |
ProjectCustomText6 |
Department |
Development |
No |
If John Woods belongs to the Development department, then when he views areas of the product that have departmental custom fields enabled, he will see:
ProjectCustomText1
ProjectCustomText2
ProjectCustomText5
ProjectCustomText6
John will be required to enter data into ProjectCustomText2 and ProjectCustomText5.
Cindy White belongs to the Marketing department; when she views areas of the product that have departmental custom fields enabled, she will see:
ProjectCustomText1
ProjectCustomText2
ProjectCustomText3
ProjectCustomText4
Cindy will be required to enter data into ProjectCustomText2 and ProjectCustomText4.
By default, departments filter the list of custom fields that John Woods and Cindy White see. But the filter does not prevent them from viewing custom fields assigned to the other departments.
Departmental fields are not tied into security. You cannot use them with security categories and groups to enable or disable fields and their functions. Instead, their main purpose is to filter out fields which are not useful for the target user.
Department considerations for cubes
| Which cubes are filtered by which value | No project department specified | Project department specified |
|---|---|---|
No resource department specified |
All data is loaded for all cubes |
Project non-timephased cube Task non-timephased cube Issues cube Risks cube Deliverables cube MSP_Project_WSS virtual cube MSP_Project_Timesheet virtual cube MSP_Portfolio_Analyzer virtual cube Assignment non-timephased cube Assignment timephased cube EPM timesheet cube |
Resource department specified |
Assignment non-timephased cube Assignment timephased cube Resource non-timephased cube Resource timephased cube Timesheet cube MSP_Project_Timesheet virtual cube MSP_Portfolio_Analyzer virtual cube |
Filtered by Project Department:
Filtered by Resource & Project Department:
Filtered by Resource Department:
|
Cubes include assignments for resources in projects that belong to other departments or to no department. This ensures that all data is present when examining data such as a department's resources full calendar capacity.
The subset of projects and resources will be used to filter at the project and timesheet level as follows:
Project non-timephased:
The data in this cube will be filtered by the departmental project list.
Projects with assignments to the department’s resources will be included.
Task non-timephased:
Non-departmental tasks with assignments to the department's resources will be included. The full non-departmental project will not be included.
All tasks for departmental projects will be included.
Assignment non-timephased:
Non-departmental project assignments for the department’s resources will be included.
All assignments for departmental projects will be included.
Assignment timephased:
Non-departmental project assignments for the department's resources will be included.
All assignments for departmental projects will be included.
Deliverables:
All deliverables owned by the filtered list of projects will be included.
All deliverables to which the filtered list subscribes and the projects/tasks that subscribe to the filtered list's deliverables will be included.
All deliverables offered by non-departmental projects that are subscribed to by departmental projects will be included.
Issues:
- Issues connected to the filtered list of projects and tasks will be included.
Risks:
- Risks connected to the filtered list of projects and tasks will be included.
Resource non-timephased:
- Resources in the departmental list will be included.
Resource timephased:
- Resources in the departmental list will be included.
Timesheet:
- Timesheets for departmental list resources will be included.
EPM Timesheet:
Timesheets for departmental list resources will be included.
Task assignments from projects outside the department will be included.
Resources are described in three ways in the OLAP databases:
Fact focus (timesheets, capacity)
Associated with Facts (project task assignments)
Owning Facts (project owner, issue owner, assignment owner)
The departmental resource list is used to filter facts with focus (Timesheets). Consequently, a non-departmental resource will never have any timesheets or capacity in the OLAP database if the database has a resource filter. However the non-departmental resource will be in the Resource List dimension if it has association with a departmental project, and will only have the relevant assignment facts.
Resources who own things that have separate dimensions (that is, Assignment Owner) do not have to be in the resource list. The Resource List dimension for a specific OLAP database contains:
The departmental resources
All resources with assignments to departmental projects