De-Normalized Fields from P6 EPPM

Previous TopicNext TopicContents

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