Introducing Common Database Elements | Microsoft Office Project 2003 Inside Out

A database is a collection of information broken into categories or groups of data that have predefined relationships to each other. Each group of data, in turn , is a collection of specific details that is distinct from other groups of data; for example, task information is distinct from resource information. If you can imagine a project as a set of tasks , a set of resources, and a set of assignments, you're already on your way to understanding how the Microsoft Project database fits together.

All the databases used by Microsoft Project share some common elements, including general types of data that are stored in each database. All Microsoft Project databases contain information about the following:

Projects. Project tables link all related information (tasks, resources, assignments, and so on) to a specific project. Project tables also define unique information related to a particular project. In addition, project tables store some global settings such as those found in the Options dialog box (Tools, Options) and the Properties dialog box (File, Properties).

Tasks. Task tables define all the fields and relationships in Microsoft Project that make up a task, such as the task name , start date, finish date, duration, and assigned resources.

Resources. Resource tables define all the fields and relationships that make up a resource, such as the resource name, e-mail address, hourly rate, and availability dates.

Assignments. Assignment tables link tasks and resources and other related information, in addition to defining unique information related to a particular assignment, such as assignment delay, assignment work, and total cost for the assignment.

Calendars. Calendar tables contain all working times calendars used by projects, resources, tasks, or assignment availability in a project.

Custom Field Information. Each database has a different way of storing customized information. In general, you can create custom fields ”including code, date, duration, flag, number, and text fields ”for both local and enterprise projects.

Application Settings. Global settings, such as those stored in the project global template and the enterprise global template, are typically stored in project-related tables.

The three databases have a number of unique tables. Some of these unique tables track data that can be accessed only from within that database, or have a specific purpose for using project data outside of Microsoft Office Project Standard 2003 or Project Professional; for example, the security and view tables in the Project Server database. Some just track data in a different way than another database does. For example, the custom fields in the Project Server database are separated out into their own individual tables; whereas in the OLE DB, these same fields are included within the Task, Resource, Assignment, and Project tables.

In addition to the general types of information stored by all three databases, the Microsoft Project database and Project Server database also track the following information:

Internal Tables. These tables store strings of data; for example, the words that appear in the user interface in Microsoft Project or Project Web Access. Internal tables are also used by Microsoft Project to link "behind-the-scenes" information.

Security Tables. These tables are used to determine which users have access to Project Web Access and other specific features available when Project Server, Project Web Access, and Project Professional are used together. These security tables also determine the specific areas within this enterprise project management workspace that users can view, and whether a user can log on to Project Server to check out a project.

Display Settings. These settings are used in Project Web Access to control the appearance of Gantt charts and other views. These settings are similar to those you can control with the Microsoft Project global template. The difference is that information stored in this database is modified using Project Web Access. Note that the enterprise project global template is an actual project stored in the database. If you checked out the enterprise global template and made changes, you would be changing these display settings in the database.

Notifications and Reminders. Project Web Access has automated notifications and reminders that are tracked with unique tables in the Project Server database. This service requires a Simple Mail Transfer Protocol (SMTP) mail server.

Enterprise Views. Project Web Access can be used to create, store, and display project views. This is the administrative table that stores the settings used to create the views and a large number of dedicated view tables. In general, the type of information stored in these view tables is very similar to the type of information contained in the Microsoft Office Project 11 OLE DB Provider.

Storing Data in a Database

Information in a database is stored in a set of predefined tables, each made up of a set of predefined columns . A table can have an unlimited number of rows. Think of rows as a collection of data with a set of individual components , also known as records . Think of columns as all the individual components that make up a collection of data, also known as fields .

To demonstrate what this means in a project database, each task displayed on a separate row in a table is a different task record. Each of the columns intersecting that task record's row represent the task's fields; for example, the task name, duration, start and finish date, and assigned resources. In the same way, each resource is a different resource record.

To view your project information as an Access database, follow these steps:

In Microsoft Project, open the project whose information you want to use in Access.
Click File, Save As.
In the Save As Type box, click Microsoft Access Database (*.mdb).
In the File Name box, enter a name for the project database in Access.

By default, the name of the Access file is adopted from the project filename. The filename extension changes from .mpp to .mdb.
Click Save.

The Export Wizard appears.
In the first Export Wizard page, click Next.
In the Export Wizard “ Data page, be sure that the A Full Project option is selected (see Figure 32-1) and then click Next.

Figure 32-1: The Export Wizard walks you through the process of exporting your project to the Access database format.
In the Export Wizard “ Project Definition page (see Figure 32-2), click Finish.

Figure 32-2: Click Finish in the third Export Wizard page, and you're ready to open your project data in Access.
In Access, click File, Open and browse to the location of the project database you just exported. Select the project file with the .mdb filename extension and then click Open.

The project database opens, showing all the tables that were exported from the Microsoft Project database into Access (see Figure 32-3).

Figure 32-3: The Access Database window lists each table in the exported project database.
Double-click the MSP_TASKS table (see Figure 32-4).

Figure 32-4: Every unique task in your project has its own row in the MSP_TASKS table.
Scroll through the table to see how the project information appears in the database table. Open other tables in the database to get a better idea of how the project database appears in Access.

Notice that each column has a different name, such as TASK_NAME or TASK_DUR_IS_EST. Each task occupies a different row in the table. Locate the TASK_NAME column to see the names of your project's tasks. Note that this field is not at the far left of the table, as you expect to see it in Microsoft Project (see Figure 32-5).

Figure 32-5: The TASK_NAME field is probably closer to the right edge of the table.

Cross-References
For more information about exporting Microsoft Project files to other applications, see "Importing and Exporting Information".

Notice also the TASK_UID column, which assigns each task a unique ID number. No other task in the entire database can share this value. Obviously, a project with hundreds of assignments and resources and thousands of tasks will be a very large table in Access. Giving a unique ID number to each row in each table in the database is the simplest way to keep everything organized. The unique ID number is also used to identify relationships in the database; for example, which tasks and resources are part of an assignment. If your database has dozens of projects, the unique ID number is used to identify which tasks and assignments are part of a specific project.

You can open any of the tables in the Microsoft Project database. You can also change information in your project plan and see it instantly reflected in the Microsoft Project database. To open a project database table and change project information, follow these steps:

With your Microsoft Project database opened in Access, open the MSP_PROJECTS table (see Figure 32-6).

Figure 32-6: The MSP_PROJECTS table is used to store all project-related data.
Find the PROJ_PROP_AUTHOR column.

Your name might be there, depending on how Microsoft Project is set up.
Go to Microsoft Project and make sure the same file is open.
In Microsoft Project, click File, Properties. Enter or modify your name in the Author field and then click OK.
Return to Access and review the PROJ_PROP_AUTHOR field.

You'll see that the field has changed to reflect the edit you made in Microsoft Project.

If you don't see the change, close and then reopen the MSP_PROJECTS table to refresh it.

It might seem that you could change your name in the field in Access, save it, and then have it appear in Microsoft Project. However, data updates don't work this way in this instance because the project saved as an Access database acts as a read-only data source, not a read/write data source; Access provides read-only access to a Microsoft Project file saved as a database.

Microsoft Access yes/no values

Using -1 and 0 in a database is a simple way to define an either/or situation. If a column contains data that can be either true or false, yes or no, or any other choice between two options, often the database will display a1or a 0, or a 0 or a -1, depending on how the database is set up. In this case, Access displays 0 and -1 for either/or values. The higher number represents the positive or true value; the lower number represents the negative or false value.

Understanding Data in the Database

A database is made up of lots of little pieces of data. In the case of Microsoft Project, there are task names, task durations, calendars, resource costs, and so on. Each of these pieces of data that has a column in a database table also has a data type associated with it. Data types are used to define the way the data is stored in the database. For example, a resource cost field needs to hold a number that might not be a whole number, so it needs a data type that allows a number such as 20.50. A task name field needs to hold whatever name the project manager gives it, so this field needs to be able to accept alphanumeric text data.

There are generalized types of data. Basically, four major areas are required: numbers, dates, text, and yes/no data. The OLE DB uses just these basic data types because it's a relatively simple database when compared to the Microsoft Project and Project Server databases, which use more than four data types. For example, the Project 11 OLE DB Provider uses Number for all fields that store non-date numbers; in the Microsoft Project and Project Server databases, numbers are represented by more specific data types such as Int (integer, requiring4bytes of storage), Decimal, Float (approximate numbers ), Smallint (integer, requiring2bytes of storage), or Tinyint (integer, requiring1byte of storage), and so on.

A data type restricts the type of data allowed in that field in the database and helps keep the right data in the right place. The following sections describe general data types; specific data types are mentioned also.

Yes/No Data

The Yes/No data type is used when you must choose between two options. Examples include the following:

True or False
Yes or No
Schedule From Start or Schedule From Finish

Fields in the Project OLE DB Provider use the Boolean data type to define this data. In the Microsoft Project and Project Server databases, the fields that contain the same data are either Bit, Smallint, or Tinyint data types. These three data types are used to represent small amounts of data:

Bit stores either a 0 or a 1.
Smallint can store integers ranging from -32,766 to 32,766.
Tinyint can store integers ranging from 0 to 255.

Dates and Times

Date and time data types are stored in either Date (Project OLE DB Provider) or Datetime (Microsoft Project and Project Server databases) fields. They both display their data like this: mm/dd/yy hh:mm:ss AM/PM. For example: 06/01/04 12:00:00 AM for midnight, June 1, 2004.

Note	To see an example of this data type in your project database in Access, look at the TASK_EARLY_FINISH field in the MSP_TASKS table.

Text Data

The text data type data is, quite simply, alphanumeric text; that is, any letters and numbers. It can be the name of a project, a resource, or a task. It can be the text that describes an option in the user interface. In the Project OLE DB Provider, Text is the data type that stores this data. In the Microsoft Project database, the data types Char, Varchar, and Text are used to store this data. In the Project Server database, the data types Char, Nvarchar, and Text are used to store this data.

Typically, the Text data type allows the maximum number of characters (up to 2,147,483,647); whereas Char, Varchar, and Nvarchar have their maximum allowable characters defined in the database using (n) to define the maximum length. For example, the Project Name field cannot store more than 255 characters. In the Microsoft Project database, the data type for this field is Varchar(255).

Specific Values

A specific value data type is information that contains a certain value predetermined by Microsoft Project. For example, a specific value can be an integer that represents a selection you have made in the user interface. It can be a single number or a large, seemingly random number that represents a duration or an amount of work.

Inside Out: How duration field values are stored

If the field stores a duration value, it will store the duration as the result of a calculation: minutes x 10. For example, 10 hours would be stored as 10 hours x 60 minutes x 10, or 6000. A work value is stored as minutes x 1000. So2hours worked would store as2hours x 60 minutes x 1000, or 120,000.

In the Project OLE DB Provider, the Number data type is used to represent these types of data. In the Project Server database, the data types Decimal, Integer, Smallint, and Tinyint are used to represent these types of data.

In the Project Server database, the data types Decimal, Float, Integer, Smallint, and Tinyint are used.

How the Project database stores values

If you want to see an example of the way the Microsoft Project database stores specific values based on settings you choose in the user interface, do the following:

In Microsoft Project, open the project you exported as the database.
Click Tools, Options.
On the View tab of the Options dialog box, change Decimal Digits to1and Placement to something other than the default.
If necessary, open the project in Access.
Open the MSP_PROJECTS table.
Find the PROJ_OPT_CURRENCY_DIGITS and PROJ_OPT_CURRENCY_POSITION fields.

Notice the values in the database and notice the values in the View tab of the Options dialog box. In the case of PROJ_OPT_CURRENCY_DIGITS, the value corresponds to the value shown in the Options dialog box (0, 1, or 2). In the case of PROJ_OPT_CURRENCY_POSITION, the value is 0, 1, 2, or 3; which represents $1 (before), $1 (after), $1(before, with space), or $1(after, with space), with 0 being the first item available in the list.

Miscellaneous Data

There are some data types that store unique types of data. These data types are used sparingly. For example, image data types are used to store binary data in the Microsoft Project and Project Server databases. There is also a Uniqueidentifier data type in the Project Server database that tracks globally unique IDs.