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 |