Alternatives to ODBC for Project Data
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.
Microsoft Office Project Professional 2007 and Office Project Standard 2007 do not use ODBC (Open Database Connectivity) to save project files to databases. If you need to migrate solutions from earlier versions of Project that rely on ODBC, your alternatives include upgrading the solution to use Project Server 2007, using Visual Reports, or saving and importing project data as XML.
Earlier versions of Project Professional communicate with Project Server in part through ODBC. ODBC does not work well for many scenarios where Project Professional and Project Server are on a slow WAN, have poor performance with many simultaneous connections, are separated by a firewall, or can be disconnected before data transfer is complete. Using ODBC can also create security issues, such as the need to open ports in a firewall and provide data encryption. Project 2007 eliminates the problems of ODBC connections by using SOAP over HTTP or HTTPS for connecting Project Professional with Project Server. Because Project Standard is built on the same platform as Project Professional, saving files through ODBC is also removed in Project Standard.
.gif "Note") Note |
|---|
For backward compatibility with project files that are accessible only through ODBC, Project Standard 2007 and Project Professional 2007 can open files using an ODBC connection. To save any changes after you open the file, however, you must save the file in another format on the local computer or to Project Server. |
Removing ODBC for saving files solves major problems, but it also means solutions that use ODBC with earlier versions of Project must use one or more different approaches to migrate to Project 2007. With Project Standard 2007 and Project Professional 2007, you cannot directly store project data using ODBC connections with Microsoft SQL Server, Microsoft Office Access, or other databases.
Selecting an Alternative to ODBC
For solutions that use ODBC with earlier versions of Project Standard or Project Professional, you have three alternative ways to migrate to Project 2007:
Upgrade Project Standard solutions to use Project Professional 2007 and Project Server 2007. This is the best alternative for ODBC solutions that include modifying and saving projects as well as exporting project data for reporting.
Use Visual Reports for ODBC solutions that only need to save project data for reports on individual projects.
Save as XML for ODBC solutions that need to import and export project data, but do not need more than a snapshot of timephased data. If you cannot upgrade Project Standard to Project Professional with Project Server, the XML format is the other alternative for Project Standard solutions that are required to read and write project data.
Upgrading an ODBC Solution to Use Project Server 2007
For ODBC solutions that require saving project files to a central database, we recommend upgrading to the enterprise project management (EPM) platform with Project Server 2007. There are many advantages to using the EPM platform with Project Server over an ODBC solution, such as the following:
Projects are saved to SQL Server databases, to provide a centralized location with secure access.
Project managers, team members, and executives can access project data and reports using the appropriate tools and role-based permission sets. Project managers can use Project Professional, team members can use Project Web Access, and executives can see reporting roll-ups across all projects or suites of projects using Project Server Web Part pages in Windows SharePoint Services 3.0.
Custom solutions for project data import and export can use the Project Server Interface (PSI) in desktop or Web-based applications.
Reporting solutions can use the Reporting database and the eleven OLAP cubes for Project Server, to see information in one project or across all projects.
Team members can easily collaborate on project documents, issues, risks, and custom lists of data in project workspaces on Windows SharePoint Services.
Project and resource managers can use the built-in tools in Project Web Access to handle resource conflicts and management across the organization.
You can extend the Project Server Reporting database with external data, for example, to create reports that incorporate information from human resources and finance applications that your organization uses.
Considering the Cost of Upgrading
In small companies, for a small number of projects, or for single departments that are using an earlier version of Project Standard currently, upgrading to Project Server and Project Professional can be relatively costly. You need to weigh the benefits of having a centralized project database and other advantages of Project Server against the time and expense to plan, implement, and learn to use Project Server. In some cases, a hosted solution can be less costly overall. Consultation with a Project solution provider can help you clarify the costs and alternatives for your organization.
It is not necessary to use all of the features of a full EPM solution. If you do upgrade to a Project Server solution, we recommend that you start with a Project Server pilot program that replaces the functionality of the ODBC solution, and then grow into using other areas of Project Server as needed.
Using Visual Reports
The new Visual Reports feature in Project Standard and Project Professional saves project data to an OLAP cube in a database file (.mdb) on the local computer. You can use the built-in Microsoft Office Excel (version 2003 or 2007) or Microsoft Office Visio 2007 report templates, edit the templates to add data or dimensions such as an outline code (Figure 1), or create new report templates. For more information about Visual Reports and the Microsoft Visual Basic for Applications (VBA) object model, see Visual Reports.
Figure 1. Editing the Resource Cost Summary Report in Visual Reports
.gif "Editing the Resource Cost Summary Report")
You can save any of the six OLAP cubes that contain reporting data for the project (task, resource, and assignment usage, plus task, resource, and assignment summary data). To save project reporting data, click Save Data in the Visual Reports - Create Report dialog box. You can also save a Microsoft Office Access 2000 database that contains all reporting data for the project, including 37 tables (Figure 2) and the following eight queries:
MSP_EpmAssignment_OlapView
MSP_EpmAssignmentByDay_OlapView
MSP_EpmResource_OlapView
MSP_EpmResourceByDay_OlapView
MSP_EpmTask_OlapView
MSP_EpmTaskByDay_OlapView
MSP_EpmTimeByDay_OlapView
MSP_ParentChild_OlapView
Figure 2. Tables of Project reporting data in an Access database
.gif "Tables of Project reporting data in Access")
A Visual Reports solution is designed for reporting purposes only. A Visual Reports database does not contain all project data. For example, there is no information about task predecessors and successors. Although you have flexibility to add and remove existing data fields, you cannot extend the Visual Reports database with external data as you can with the Project Server Reporting database.
After you save a Visual Reports database, you cannot use Project Standard or Project Professional to update it. If you make changes in a project and you want the changes to appear in the Visual Reports database, you must re-save the entire database through the Visual Reports dialog boxes. You cannot open a Visual Reports database as a project; the database is designed for reporting, not for project management.
If your ODBC solution reports data that the Visual Reports database can save from individual projects, then Visual Reports can be a useful alternative to using ODBC. If you need to modify project data, access all project data, or make reports across multiple projects, then you should use an XML or Project Server solution in place of ODBC.
Saving and Importing Project XML Data
When you save a project as XML, the file conforms to the Microsoft Office Project XML Schema. The XML file includes the project properties, custom field data, and calendar data; task data including predecessors and summary tasks; resource data including calendar exceptions, work weeks, and resource custom fields; and assignment data including timephased data. For more information, see the Project 2007 XML Data Interchange Schema Reference.
| Note |
|---|
Lookup tables are peers to custom fields in the Project 2007 XML Schema, so that multiple custom fields can use the same lookup table. The Schema also treats outline codes in the same way as custom fields. Project 2003 value lists are converted to lookup tables and stored in the <OutlineCodes> section of the Schema. Custom fields are in the <CustomAttributes> section, for compatibility with earlier versions of Project. For more information, see Microsoft Office Project 2003 XML Schema Reference. |
When you open a project XML file, it must conform to the Project XML Schema. You can import the file in the following ways:
Open the file as a new project
Append the file's data to the active project
Merge the file's data into the active project
When you append the XML data, you can add the data multiple times to create copies of the data within one project. However, there can be problems when you append XML data to a project. For example, Project increments the unique IDs of imported summary tasks. Project then checks for duplicate IDs in the rest of the appended XML data, and assigns new unique IDs. As a result, you may disassociate assignments, tasks, resources, and cross-project links from the original data. To avoid these problems, you should separate the XML data into individual components, each of which conforms to the Project XML Schema. That is, append one task, resource, or assignment at a time, without the summary task. The VBA object model is useful for appending numerous small XML files to a project.
When you merge the XML data, any changes in the XML data updates the active project data. Merge XML data into an active project when you need to use an external application to change specific information, for example, to update resource calendar exceptions from a human resources application.
Opening and saving Project data as XML files can replace some ODBC solutions where you need to append data or merge updated data for isolated projects. However, an XML file is not the same as a Project .mpp file. The XML file has a much slower performance; it is designed to import and export project data. You need to specifically request the XML format each time you save or open a Project XML file, and also specify how you want to open the data. Individual files can be misplaced, and it is easy to get confused with multiple copies of XML files of the same project from different times. To save the XML files in a database, you need to implement a custom process.
The XML data is specific to one project; you cannot combine multiple projects into one XML file. To report across multiple projects, you would need to develop a custom application to read the files and extract the relevant data. Project Server 2007 provides a much better alternative for many ODBC solutions that involve reporting as well as reading and saving project data.
See Also
Concepts
Project Server Architecture and Programmability
Other Resources
Project 2007 XML Data Interchange Schema Reference
Enabling the Enterprise Project Management Solution