For the latest information, please see http://www.microsoft.com/project
Microsoft Project Server Database Architecture 6
Microsoft Project 2002 Tables 7
Microsoft Project Web Access Tables 8
Microsoft Project Server OLAP Cube Tables 9
Microsoft Project Server View Tables 10
Microsoft Project Server Data Security Architecture 11
Project Data Service 12
Database Security 13
Microsoft Project Server Data Security Overview 15
Extensibility of Microsoft Project Server 16
Microsoft Project Server Database Schema 17
Microsoft Project OLAP Cube Extensions 18
Project Data Service Extensions 19
Microsoft® Project 2002 includes a complete enterprise project management solution. Microsoft Project 2002 integrates the enterprise features of eLabor Enterprise Project with the ease-of-use of Microsoft Project. The enterprise solution includes the following products:
Microsoft Project Server 2002 provides timesheets, status reports, portfolio analysis and modeling, enterprise resources, an enterprise global template, and enterprise templates. Microsoft Project Server 2002 runs on Microsoft Windows® 2000 Server, Microsoft Internet Information Services (IIS) 5.0, and Microsoft SQL™ Server 2000.
Microsoft Project Web Access is a browser-based client that allows team members, resource managers, and executives to enter and view timesheet information and view portfolio reports. Microsoft Project Web Access runs on Internet Explorer 5 or later.
Microsoft Project 2002 Professional Edition provides a desktop client that allows project managers to create and edit project plans and enterprise resources. Project plans and resources can be saved to the Microsoft Project Server 2002 database. Microsoft Project Professional runs on Windows 98 or later, as well as Windows NT® 4.0 Workstation or later.
Microsoft Project 2002’s enterprise project management solution supports a wide range of users with features designed to support the needs of each member of a project team or organization.
Executives can use Microsoft Project Web Access to quickly access project status reports across their organizations. Reports are available at the portfolio, project, and resource level, so an executive can quickly identify projects in trouble. With the Portfolio Analyzer and easy access to online status reports and project documents stored on Sharepoint™ Team Services from Microsoft, executives can also analyze trends across projects and resources to understand the causes behind problems. The Portfolio Modeler allows executives to develop and evaluate solutions to problems by using interactive project staffing and scheduling tools. Microsoft Project Web Access allows executives to see, understand, and resolve problems in projects across the enterprise.
Team members can use Microsoft Project Web Access to review timesheets, status reports, and project-related documents. Team members can quickly view their timesheets for assignments that need to be completed and report progress on the assignments to the project manager. Team members can create and access status reports and project documents to simplify communication in the project team. Microsoft Project Web Access provides team members with a single place to access and work with project information.
Project managers can use Microsoft Project Professional to create and edit project plans. Microsoft Project Professional’s integration with Microsoft Project Server ensures that project managers can easily access enterprise resources and provide information required by a project management office (PMO). With the new Build Team from Enterprise and Resource Substitution features, project managers can staff their projects based on the availability of resources and the match between resource skills and skill demands in the project. Microsoft Project 2002 is also integrated with Microsoft Project Web Access. Project managers can now access reports, status reports, and project plan updates from within Microsoft Project. Microsoft Project Professional seamlessly integrates new enterprise project management features into the familiar and easy-to-use Microsoft Project user interface.
The standard tracking and report architecture for Microsoft Project Server has been redesigned with increased functionality and with scalability and performance as primary goals. The reporting capabilities of Microsoft Project Server have also been expanded to include online analytical processing (OLAP) reporting through the SQL Server Analysis Services engine. Office Web Controls combined with SQL Server Analysis Services provide a level of interactive, portfolio project management reporting that has been previously unavailable.
Microsoft Project Server provides a fully integrated security model that offers a superior way to filter and lock down your project management data when Microsoft Project Server is used with Microsoft Project Professional. The security model supports Windows NT Authentication, which offers all the security and convenience of single logon, as well as Microsoft Project Server application logons for organizations that prefer that method. The security model relies on server-side functionality, including Microsoft Project Server and Microsoft SQL Server, to provide the security.
Microsoft Project Server also allows you to integrate with other enterprise systems you may have installed (for example, enterprise resource planning (ERP) or human resources (HR) systems and databases). You can also expand and customize the capabilities of Microsoft Project Server to better suit your business processes and needs. The database schema and programmable components of Microsoft Project Server allow for expansion and integration.
This white paper describes:
The database architecture of Microsoft Project Server 2002.
The data security model of Microsoft Project Server 2002.
Extensibility for Microsoft Project Server 2002.
Microsoft Project Server Database Architecture
Microsoft Project Server uses Microsoft SQL Server 2000 as a data repository. SQL Server 2000 provides the scalability and performance required for Microsoft Project Server.
The Microsoft Project Server database architecture has changed from the database schemas used in Microsoft Project 2000 and Microsoft Project Central. Microsoft Project Server combines all of the table sets into a single database, providing scalability, performance, data access, and maintainability. The database schema used by Microsoft Project Server is a collection of four sets of tables, each with its own uses and data sets, as shown in the figure below.
Microsoft Project 2002 Tables
The Microsoft Project 2002 tables are the data repository used by Microsoft Project Professional. All of the data about every version of every project in your portfolio is stored in this set of tables, along with enterprise resources and the enterprise global template. These tables are similar to the database schema used by Microsoft Project 2000; there are additional tables and fields, but no existing fields or table names from the Microsoft Project 2000 database schema have been altered, so any reports or SQL queries that worked directly against the Microsoft Project 2000 database schema will work against the Microsoft Project 2002 table schema. All tables that are a part of this set follow the naming convention MSP_*.
Microsoft Project Web Access Tables
The Microsoft Project Web Access tables are the next generation of the database schema that was used by Microsoft Project Central. New tables have been added and others have been redesigned or eliminated to increase the scalability, performance, and functionality of Microsoft Project Web Access. These tables store the project data that is shared with your executives and resources, including each resource's task list as well as the high-level project data reported in the Project Center (formerly Portfolio View in Microsoft Project Central). Resource task updates are also stored in these tables for approval by the project manager. The links between tasks, issues, and documents are stored here as well. All tables that are a part of this set follow the naming convention MSP_WEB_*.
Microsoft Project Server OLAP Cube Tables
The Microsoft Project Server OLAP cube tables are a new set of tables added to Microsoft Project Server to accommodate the OLAP reporting features available in Microsoft Project Web Access. This set of tables is used as the staging and fact tables for creation of the OLAP cube through SQL Server Analysis Services. The data in these tables is manually updated by clicking a button in the Admin center of Microsoft Project Web Access; in addition, the data can be automatically updated periodically (weekly, for example) by a process that is part of Microsoft Project Server. All tables that are a part of this set follow the naming convention MSP_CUBE_*.
Microsoft Project Server View Tables
The Microsoft Project Server view tables are another new addition to Microsoft Project Server that increase the performance and scalability of the project analysis views from Project Center. These tables are a nonstandard, expanded view of the project data contained in the Microsoft Project 2002 tables as described above, and they look similar to the tables exposed through the Microsoft Project OLE DB provider. These tables are excellent sources for generating reports across multiple projects because they are updated every time a user checks in an edited project from Microsoft Project Professional or updates enterprise code values through Microsoft Project Web Access. The Microsoft Project Server view tables are used as a reporting mechanism only and should be treated read-only. All tables that are a part of this set follow the naming convention MSP_VIEW_*.
Microsoft Project Server Data Security Architecture
The data security model has been greatly enhanced for Microsoft Project Professional when it connects to Microsoft Project Server. A key element of the new data security model is the Project Data Service (PDS).
Project Data Service
The Project Data Service (PDS) is the middle layer between Microsoft Project Professional and the Microsoft Project Server database. The PDS gathers information about the user who is currently logged on to Microsoft Project Server to determine which information that user has been granted access to see from the Microsoft Project Server database. Each user’s permissions are determined by the permissions the user has been granted in Microsoft Project Web Access—providing a single place to set security on your project management data.
In some cases, the PDS returns data that has already been filtered back to Microsoft Project Professional in XML format. In other cases, the PDS acts as the security gatekeeper for the project management data stored in the Microsoft Project Server database.
In the end, the PDS plays an important role in the entire Microsoft Project 2002 system. However, a user will never know that the PDS exists, since it is used behind the scenes to provide the functionality that Microsoft Project Server and Microsoft Project Professional require. In addition, the PDS has been designed and implemented from the start to be scalable and to perform in a way that makes it unnecessary for a user to know about it.
Before performing any action on behalf of a user, the PDS validates that the user is currently logged on to Microsoft Project Server. This action gives the PDS the proper context to check the security and permissions associated with each user. The permissions for each user are determined from Microsoft Project Web Access; in other words, if a user has permissions to check out a project in Microsoft Project Web Access, then the PDS grants that same level of access to that project for the same user. Therefore, all user permissions are controlled through the administration pages of Microsoft Project Web Access.
Data Middle Layer
In some cases, the PDS gathers and filters data on the server side and returns the filtered data to Microsoft Project Professional (or another client calling the PDS) in XML format. For example, suppose a user opens a project by clicking Open on the standard toolbar or from the File menu in Microsoft Project Professional when it is online. Microsoft Project Professional calls a method on the PDS that gathers all the projects in the portfolio that the current user has been granted access to see in Microsoft Project Web Access. The PDS returns this list of projects to Microsoft Project Professional in XML format, including all relevant information about the projects, including name, version, checked-out state, and the level of permissions the user has for each project returned. This process ensures that in the Open Project dialog box, the user sees only those projects that he or she has been granted access to, as well as the level of permissions the user has for each of those projects. Similar methods are available for resources as well, both when a user opens resources to update the core resource information and when a user adds resources to the project team.
Simple Object Access Protocol Service
All of the PDS methods are exposed through the Simple Object Access Protocol (SOAP). SOAP is the standard object access protocol that is at the core of the Microsoft .Net technologies. So third parties can use the PDS methods through Microsoft Visual Studio® 6.0 using the SOAP Toolkit 2.0 Service Pack 2, as well as Visual Studio 7 when it is available. Microsoft Project Server and the PDS are built to be easily accessed and extended now and in the future.
Microsoft Project Server’s enterprise features include a significant revision in how Microsoft Project binds to the project database. When Microsoft Project 2000 opened a project from database, it binds directly to the Microsoft Project tables in the database. If the user’s data source name (DSN) allowed read/write access to the database, Microsoft Project could open and save changes to any project in the database, including projects managed by other project managers.
Microsoft Project Professional uses connections without a DSN to bind to SQL Server views of the Microsoft Project 2002 tables. The SQL Server views contain only the information required to open the project, resource, or resources selected by the user. This information only exists in the SQL Server views while Microsoft Project is opening or saving projects or resources. The Microsoft Project 2002 architecture provides application-level security through the PDS and database-level security through the SQL Server views, as illustrated below.
Microsoft Project Server Security Tables
Two additions to the Microsoft Project 2002 tables are the project security table and the resource security table. The PDS is the only component that directly accesses the security tables. To access data in the Microsoft Project Server database, a client (Microsoft Project Professional or a third party client) first needs to make a call to the PDS to request the database connection information, which includes a managed SQL Server user name and password stored on the Microsoft Project Server machine.
Next, the client creates a connection to the SQL Server using this managed SQL Server account. Each SQL Server connection has a unique identifier called a SQL Process ID (SPID), and the client gathers the SPID from the connection. This SPID is passed as a parameter to the PDS when the user requests access to a project or resource. The PDS uses the following procedure to process the request:
The PDS checks that the user is logged on to Microsoft Project Server.
The PDS checks that the user who is currently logged on has permission to access the requested project or resource.
If the user has the correct permissions, the PDS inserts a record into the appropriate security table and returns a successful result to the client.
Each security table row has fields for the project ID or resource's enterprise unique ID, as well as the SPID and flags for whether the user was granted read-only access or read/write access to the project or resource. After the client has completed reading or writing data to the database, another PDS method is called that revokes the access to the project or resource by deleting the appropriate rows in the appropriate security table.
SQL Server 2000 Views
To ensure that the data exposed to the client's connection to the database server is filtered, the client connection with the managed account only has access to a set of SQL Server views. Each base table that is part of the Microsoft Project 2002 database tables has four sets of SQL Server views associated with it: project read-only, project read/write, resource read, and resource read/write. Each view performs a select on the base table and filters the data by performing a join with the appropriate security table—relying on the SPID as the primary key to perform the filtering. In addition, each view specifies the SQL command WITH CHECK OPTION, which prevents any data from being read or updated through the view that does not meet the filtered definition of the view query. Therefore, if there is no entry in the security table granting access to the requested project or resource, the SQL Server view would be completely empty.
SQL Server 2000 Permissions
The managed SQL Server account that is used by clients to connect to the database server has the minimum SQL Server permissions required to perform the requested actions against the data in the database. The managed account has only select permissions against the read-only views and only has select, insert, update and delete permissions against the read/write views. The managed account does not have any rights to access any of the tables in the database at all. This ensures that any connection made using that managed account can only access data after access has been requested and granted by the PDS.
Microsoft Project Server Data Security Overview
The process that Microsoft Project Professional goes through to access project data from the Microsoft Project Server database is as follows:
Call the PDS to request the database connection information.
Make a connection to the database using that information and gather the SPID for that connection.
Call the PDS to request access to the project or resource that the client needs to access and pass along the SPID from the previous step.
Make queries against the appropriate set of SQL Server views to gather and/or update the required information.
Call the PDS when the access is no longer required in order to maintain security of the data.
Extensibility of Microsoft Project Server
Microsoft Project Server has been designed to allow for extensibility and customization. Numerous Microsoft Project partners and independent software vendors build products and customized solutions on top of the Microsoft Project platform and family of products.
Microsoft Project Server Database Schema
The database schema of the Microsoft Project Server database will be documented. The database schema documentation will allow you to:
Customize reports with the project management data stored in the Microsoft Project Server database.
Directly integrate the Microsoft Project Server database and another data repository, perhaps an HR or financial system.
Populate a custom OLAP cube, based on a combination of project data and detailed financial data.
Microsoft Project OLAP Cube Extensions
Microsoft Project Server provides functionality that builds an OLAP cube with enterprise project data. This functionality allows powerful enterprise reporting through Microsoft Project Web Access. To build the OLAP cube, a process on the server builds the fact and dimension tables and then uses SQL Server 2000 Analysis Services to generate the cube. The cube building process provides two breakout points that allow developers to do two things:
Extend the fact and/or dimension tables.
Extend the cube or build other cubes at the end of the cube-building process.
A developer can write a Component Object Model (COM) object for each breakout point that implements a defined public interface and properly registers the object on the Microsoft Project Server machine. Then, the object needs to be registered with Microsoft Project Server by making an additional entry in the registry on the server.
When the cube process completes building the fact and dimension tables, it will reach the first breakout point where it will call the first COM object. In this object, the developer can choose to do whatever he or she wants—typically this would a good place to extend our fact or dimension tables. When the object has completed its work, control is returned to the Microsoft Project cube building process. At this point, the actual Microsoft Project OLAP cube generation process begins. At the end of the cube generation, the cube generation component calls the second COM object. In this object, the developer can again choose to do whatever he or she wants—typically this would be a good place to add a new dimension and reprocess the cube, or build separate cubes using data from the staging tables. When the object has done its processing, the Microsoft Project cube generation process is complete.
The extension possibilities of Microsoft Project Server do not end at the database schema. The PDS already has a host of useful methods that are all available through its SOAP interface. All the PDS methods will be fully documented in the Microsoft Project 2002 Software Development Kit (SDK) hosted on http://msdn.microsoft.com/project. This documentation will allow you to create custom solutions to access many of the enterprise features accessed by Microsoft Project 2002 Professional and Microsoft Project Web Access. One key feature of the PDS is support for programmatically defining and updating enterprise outline codes. This method is most useful if you want to keep the Microsoft Project Server enterprise codes consistent with the schema of other business intelligence or line-of-business solutions (such as general ledger, customer relations management, or employee relations). Custom applications can be written to query for code schema definitions in external systems and then use PDS calls to update the corresponding enterprise codes in the Microsoft Project Server database.
In addition, developers can extend the set of methods exposed to the PDS by registering extension objects on Microsoft Project Server. A developer simply writes a COM object that implements a defined public interface and properly registers the object on the Microsoft Project Server computer. Then the object is registered with Microsoft Project Server by making an additional entry in the registry on the server. When a client application sends a request to the PDS through its SOAP interface that is not recognized as a native method, the PDS instantiates the registered PDS extension and sends the request on to the extension object. The extension object handles the request and then returns XML data representing the results of that request. Multiple PDS extensions can be registered, and each PDS extension can handle multiple method requests. The PDS calls each registered extension until one handles the method request. If none of the registered extensions handle the method request, then the PDS returns an unknown request status code.
The Microsoft Project 2002 family of products provides a highly scalable enterprise portfolio project management solution. SQL Server 2000 provides the database tier for Microsoft Project Server, and Microsoft Project Web Access provides the front end for collaboration with resources and management. Microsoft Project Professional provides the powerful desktop scheduling and planning tools that customers expect from Microsoft Project. The combination of all of these systems brings a powerful, enterprise-level solution to the portfolio project management needs of your organization. The extensibility and customizability of the system ensure that you can mold Microsoft Project 2002 to meet the needs and processes of your company.
This is a preliminary document and may be changed substantially prior to final commercial release of the software described herein.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This white paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in, or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
The example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious. No association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred.
Microsoft,SharePoint, SQL, Visual Studio, Windows, and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
The names of actual companies and products mentioned herein may be the trademarks of their respective owners.