DIM_PERSON

This table stores all user information from PPM center.

Foreign Keys

Primary Key Table

Primary Key Column

Foreign Key Column

DIM_PERSON

PK_PERSON_ID

MANAGER_ID

Column Descriptions

Column Name

Null?

Data Type

Description

SCD2?

PK_PERSON_ID

NOT NULL

INT

System-generated identifier

No

MD_BUSINESS_KEY

NOT NULL

VARCHAR(1000)

Primary key that is mapped to one or more source columns

 No

MD_ENTERPRISE_KEY

NOT NULL

INT

Unique identifier for entity

 No

EMAIL_ADDRESS

 NULLABLE

VARCHAR(80)

Email address of the user

 No

PHONE_NUMBER

 NULLABLE

VARCHAR(30)

Phone number

 No

FIRST_NAME

NOT NULL

VARCHAR(80)

First name

 No

LAST_NAME

NOT NULL

VARCHAR(80)

Last name

 No

FULL_NAME

 

VARCHAR(200)

Full name

 No

USER_NAME

NOT NULL

VARCHAR(200)

 User name No

DOMAIN

 NULLABLE

VARCHAR(80)

 Windows domain to use when logging in No

START_DATE

 NULLABLE

DATE

 The date of the user being valid in PPM No

END_DATE

 NULLABLE

DATE

 The date of the user being invalid in PPM No

ENABLED_FLAG

 NULLABLE

VARCHAR(1)

 Whether the user is enabled; Y or N. No

USER_DATA_SET_CONTEXT_ID

 NULLABLE

INT

 Parameter set context identifier for the user data

Yes

USER_DATA1-20

 NULLABLE

VARCHAR(200)

User data segment

 No

VISIBLE_USER_DATA1-20

 NULLABLE

VARCHAR(200)

User data segment

 No

COMPANY

 NULLABLE

VARCHAR(30)

 This is used to associate the user with a company

Yes

COMPANY_NAME

 NULLABLE

VARCHAR(150)

Company name of the user

Yes

REGION_ID

NOT NULL

INT

 Identifier of the region that this user

Yes

REGION_NAME

NOT NULL

VARCHAR(100)

Region name

Yes

REGION_OVERRIDE_FLAG

NOT NULL

VARCHAR(1)

 Whether the user has a specific region directly set to override inheritance; Y or N.

Yes

RESOURCE_FLAG

NOT NULL

VARCHAR(1)

 Whether the user is a resource; Y or N.

Yes

CURRENCY_CODE

 NULLABLE

VARCHAR(10)

 This is used to associate the user with a currency by means of a unique currency identifier

Yes

DEPARTMENT_CODE

 NULLABLE

VARCHAR(40)

 This is used to associate the user with a department by means of a unique

Yes

DEPARTMENT_NAME

 NULLABLE

VARCHAR(150)

Department name

Yes

LOCATION_CODE

 NULLABLE

VARCHAR(30)

 This is used to associate the user with a location by means of a unique location

Yes

LOCATION_NAME

 NULLABLE

VARCHAR(150)

Location name

Yes

RESOURCE_CATEGORY_CODE

 NULLABLE

VARCHAR(30)

 This is used to associate the user with a resource category by means of a unique resource category identifier

Yes

RESOURCE_CATEGORY_NAME

 NULLABLE

VARCHAR(150)

If this user is a resource, then this resource belongs to the category name

Yes

LABOR_CATEGORY

 NULLABLE

VARCHAR(255)

This is used to associate the user with a labor category by means of a unique labor category identifier

Yes

LABOR_CATEGORY_NAME

 NULLABLE

VARCHAR(150)

Labor category name

Yes

RESOURCE_TITLE_CODE

 NULLABLE

VARCHAR(30)

 This is used to associate the user with a resource title by means of a unique resource title identifier Yes

RESOURCE_TITLE_NAME

 NULLABLE

VARCHAR(150)

Resource title name

Yes

CALENDAR_ID

NOT NULL

INT

 Identifier of the calendar that belongs to the person

Yes

MANAGER_ID

 NULLABLE

INT

Unique identifier for manager

Yes

MANAGER_BUSINESS_KEY

 NULLABLE

VARCHAR(1000)

Foreign key that is mapped to DIM_PERSON

Yes

MANAGER_ENTERPRISE_KEY

 NULLABLE

INT

Unique identifier for foreign key

Yes

MANAGER_FIRST_NAME

 NULLABLE

VARCHAR(80)

Manager’s first name

Yes

MANAGER_LAST_NAME

 NULLABLE

VARCHAR(80)

Manager’s last name

Yes

MANGER_FULL_NAME

 NULLABLE

VARCHAR(200)

Manager’s full name

Yes

MANAGER_EMAIL_ADDRESS

 NULLABLE

VARCHAR(80)

Manager’s E-mail address

Yes

MANAGER_PHONE_NUMBER

 NULLABLE

VARCHAR(30)

Manager’s phone number

Yes

MD_BATCH_ID

 NULLABLE

INT

ID of data batch

No

MD_PROCESS_ID

 NULLABLE

INT

ID of ETL process running on a specific task

 No

MD_SOURCE_INSTANCE_ID

 NULLABLE

INT

Contnet pack ID of source instance

 No

MD_CREATEDDATE

 NULLABLE

DATETIME

Date of the record being inserted

 No

MD_DELETEDDATE

 NULLABLE

DATETIME

Date of the record being deleted

 No

MD_LASTMODDATE

 NULLABLE

DATETIME

Date of the record being updated

 No

MD_TRANSLASTIND

 NULLABLE

VARCHAR(1)

Flags whether it is the latest record after updated by SCD2

 No

MD_TRANSENDDATE

 NULLABLE

DATETIME

End date of the record being effective

 No

MD_ACTIVESTATUSIND

 NULLABLE

VARCHAR(1)

Flags whether or not the old record is deleted

 No

MD_HASH_CODE_SCD1

 NULLABLE

VARCHAR(40)

(Only for dimension table) Hash code that is generated by SCD1 columns

 No

MD_HASH_CODE_SCD2

 NULLABLE

VARCHAR(40)

(Only for dimension table) Hash code that is generated by SCD2 columns

 No

Projections

This table uses no projections.

Sequences

Sequence Name

Sequence Type

PERSON_ENTERPRISE_XREF_SEQ

MD_ENTERPRISE_KEY

PERSON_KEY_LOOKUP_SEQ

PK_PERSON_ID