By de-normalizing the fields from P6 EPPM, the ODS database is particularly conducive to generating reports, as extensive joins will not be necessary. The following types of fields are de-normalized in the ODS:
Refer to the OdsFieldMapTable.html file located in the physical media or download location for detailed information relating to the fields in the ODS.
Hierarchies
There are several hierarchy tables in ODS. The purpose of these hierarchy tables is to facilitate many types of roll-up queries. Instead of writing complex recursive or "tree-walking" SQL, users can take advantage of the extra rows and columns in these hierarchy tables to write much simpler queries. The ODS contains the following hierarchy tables:
For each hierarchy table, there is a row for every parent-descendant relationship.
Note: This is more extensive than merely a row for every parent-child relationship. There is also a reflexive row for each object (where the object is both parent and child).
Each hierarchy table contains a set of columns for the parent object, and a set of columns for the child object. In addition, there are several metadata columns that contain the number of levels from the top for the parent and child, and whether the child has children.
ODS Calendar Table
The Calendar table in the ODS represents days for which work occurs. There are three types of calendars:
For each calendar defined in the P6 EPPM Database, the ODS Calendar table will contain a set of rows representing each distinct day within the Full Calendar Date Range (as defined in the ODS configuration screen). Each row contains the calendar name it represents, the calendar type, the actual date of the day it represents, and a bitmap of work hours.
ODS Field Name | Data Type | Example Value | Description |
---|---|---|---|
ObjectId | integer | 566 | The unique ID generated by the system. |
IsDefault | string | N | The flag that identifies the default global calendar (applies to global calendars only).'Y' or 'N'. |
Name | string | Crew4 | The name of the calendar. |
ProjectObjectId | integer | 275 | The unique ID of the associated project. |
BaseCalendarObjectId | integer | 633 | The unique ID of the global calendar to which this calendar is linked. Any changes to the global calendar are automatically propagated to this calendar. |
lastchangedate | date | 7/6/07 16:46 | The date that the calendar was last edited. |
Type | string | Resource | The calendar type - either Global, Resource, or Project. Global calendars can be assigned to projects and resources. Resource calendars can be assigned only to resources. Project calendars are specific to projects. |
daydate | date | 9/20/07 0:00 | The actual day that the calendar row represents. |
WeekdayNumber | integer | 5 | Integer day of week (1-7), Sunday=1 if Sunday is selected as the first day of the week in the Admin Preferences of the P6 EPPM module. |
WorkDayFlag | string | Y | Y' or 'N', indicates if this day has work time. |
TotalWorkHours | double | 8 | Number of work hours for the day. |
WorkHoursByHalfHour | string | 000000000000000011111111001111111100000000000000 | Bit mask (48 bits) for each half hour of the day, indicating whether the half hour is work time. 0=nonwork time, 1=work time. The first bit represents 00:00-00:30, the second bit represents 00:30-01:00, etc. |
WorkDayStartTime | date | 9/20/07 8:00 | Time of day when work first starts. |
WorkDayFinishTime | date | 9/20/2007 17:00:00 pm | Time of day when work stops. |
IsBaseline | string | N | Set to 'Y' if this is a project calendar and the project is a baseline project. |
isTemplate | string | Y | Set to 'Y' if project is a template project. |
Spreads
The following tables in the ODS contain spread bucket data:
Each spread table contains spread data columns. Each spread row contains the spread data for a given object (for example: EPS, project, or WBS) for a particular time period. The spread data is aggregated from the Activity and Resource Assignment Spread tables to the WBS, project, and EPS Spread tables.
Legal Notices
Copyright © 1999, 2014,
Oracle and/or its affiliates. All rights reserved.
Last Published Friday, January 31, 2014