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