Employee and Contractor hierarchies

This hierarchy represents the employee, and is subdivided into Employee and Contractor. In the logical data model, Contractor is available within the Employee hierarchy.

Employee hierarchy

This hierarchy has the largest number of attributes in the model. Employee is the lowest level attribute and all other attributes are either a characteristic or a parent of Employee. The attributes and relationships, as well as main parent branches, can be seen in the following figure.

Attribute	Description	Example
Employee	Name of the individual working for the company, receiving salary and benefits in return.	John Smith.
Profile	Group of attributes that define the personal profile of an employee. See the following Profile table.	Age, nationality, and so on. See the following Profile table.
Address	Group of attributes that define the home address of an employee. See the following Address table.	Street, City, and so on. See the following Address table.
Department / Previous Department	Employee's company department. • This is a parent of Employee and Month (joint children) and belongs to the Function hierarchy.	Production consulting, pre-sales consulting, finance administration, HR administration, and so on.
Division / Previous Division	Employee's division. • This is a parent of Department and belongs to the Function hierarchy.	Administration, consulting, and so on.
Location / Previous Location	Employee's work site. • This can either be the zip+4 code (the nine digit numeric zip code), or the name of the city, depending on the type of company. • This is a parent of Employee and Month (joint children) and belongs to the employee's Geography hierarchy.	22180-1234, 22182-2345 Vienna, McLean, New York City, Washington DC, and so on.
Region / Previous Region	This is a parent of Location and belongs to the employee's Geography hierarchy.	Virginia, California, and so on.
Immigration Status	Employee's immigration status.	None, H1B Visa, Green Card, and so on.
Clearance Status	Employee's security clearance status.	Top-secret, secret, none, and so on.
Exempt Status	Exempt status of the employee. Parent of Employee.	Exempt, Non-exempt.
Work Experience	Employee's work experience (in years) within relevant fields prior to hire date.	1, 2, 3 and so on.
Minority Group	Employee's minority group.	Veteran, disabled, none, more than 1, and so on.
Position Status / FTE Coefficient	Status of employee's position. A coefficient can be used to further clarify Position Status: • 1 if full-time. • Between 0 and 1 if part-time.	Full-time, part-time 0, 0.5, 0.8, 1
Level	Employee's general job level (not a specific title).	Executive, manager, engineer, and so on.
Hire Date	Employee's start date with the company.	12/24/2002
Leave Date	Employee's last date with the company.	12/24/2003
Title	Employee's job title.	Product Manager, Program Manager, and so on.
Supervisor	Name of the employee's supervisor.	David Smith
Birth Date	Date of birth of the employee.	12/24/1980
ID	Employee identification number. • The attribute's relationship is one to one with Employee.	1234, 1225, and so on.
Employee Status	Indicates whether the employee is active/hired, terminated voluntarily, or terminated involuntarily.	• Active (hired) • Inactive (voluntarily departed, involuntarily departed).
Recruiting Source	Source used to recruit and hire the employee.	Head-hunter, referral, job posting on Internet, job posting in Newspaper, and so on.
Tenure	Length of employment.	1 month, 1 year, 5 years, and so on.
Location Transfer	Flag indicating whether the employee had a location transfer during a specific month.	0, 1
Department Transfer	Flag indicating whether the employee had a department transfer during a specific month.	0, 1

 

Profile Attribute Group	Description	Example
Age Range	Age range for the employee.	18-30, 31-40, 41-50, 50+
Gender	Gender of the employee.	F, M
Nationality	Nationality of the employee.	U.S. citizen, Italian, and so on.
Ethnicity	Ethnicity or race of the employee, if known.	Caucasian, Asian, African, and so on
Education	Education level of the employee.	Doctorate, Master, and so on
Degree Type	Education degree type of the employee, if any.	Computer Science, Finance, and so on
Marital Status	Marital status of the employee.	Single, married

 

Address Attribute Group	Description	Example
Address	Employee's street address.	123 Sunrise Avenue, McLean VA 22180
ZIP Code	Employee's ZIP Code	22180
Phone Number	Employee's home phone number.	703-573 2270
City	Employee's city.	Vienna, McLean, and so on.
State	Employee's state or region.	VA, MD
Country	Employee's country.	USA, Italy, and so on.

The detailed definition of each attribute in the MicroStrategy metadata listed previously is shown in the following tables.

Employee

Form	Form Expression	Lookup Table	Other Tables
ID	EMPLOYEE_ID	L_EMPLOYEE	F_BENEFIT_QTR, F_BONUS_PTNTL, F_COMP_MONTH, F_EMP_QLFN, F_EMP_HIST, F_EMP_PREV_HIST, F_OVERTIME, F_PERF, F_TIME_OFF, R_EMP_ADDRESS, R_EMP_DEMO
DESC	EMPLOYEE_DESC	L_EMPLOYEE	None

Children	Parents	Relationship Type	Table
None	Age Range	Many-to-one	R_EMP_DEMO
None	Birth Date	Many-to-one	L_EMPLOYEE
None	City	Many-to-one	R_EMP_ADDRESS
None	Clearance Status	Many-to-one	L_EMPLOYEE
None	Country	Many-to-one	R_EMP_ADDRESS
None	Current Status	Many-to-one	L_EMPLOYEE
None	Degree Type	Many-to-one	R_EMP_DEMO
None	Department Transfer	Many-to-one	L_EMP_HIST
None	Education	Many-to-one	R_EMP_DEMO
None	Ethnicity	Many-to-one	R_EMP_DEMO
None	Exempt Status	Many-to-one	L_EMPLOYEE
None	Gender	Many-to-one	R_EMP_DEMO
None	Hire Date	Many-to-one	L_EMPLOYEE
None	ID	One-to-one	L_EMPLOYEE
None	Immigration Status	Many-to-one	L_EMPLOYEE
None	Leave Date	Many-to-one	L_EMPLOYEE
None	Level	Many-to-one	L_EMPLOYEE
None	Location Transfer	Many-to-one	L_EMP_HIST
None	Marital Status	Many-to-one	R_EMP_DEMO
None	Minority Group	Many-to-one	L_EMPLOYEE
None	Nationality	Many-to-one	R_EMP_DEMO
None	Phone Number	Many-to-one	R_EMP_ADDRESS
None	Position Status	Many-to-one	L_EMPLOYEE
None	Recruiting Source	Many-to-one	L_EMPLOYEE
None	State	Many-to-one	R_EMP_ADDRESS
None	Street	Many-to-one	R_EMP_ADDRESS
None	Supervisor	Many-to-one	L_EMPLOYEE
None	Title	Many-to-one	L_EMPLOYEE
None	Work Experience	Many-to-one	L_EMPLOYEE
None	Zip Code	Many-to-one	R_EMP_ADDRESS

Tenure

Form	Form Expression	Lookup Table	Other Tables
ID	SENIORITY	F_EMP_HIST	None

Level

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_LEVEL_ID	L_EMP_LEVEL	F_EMP_LEVEL, L_EMPLOYEE
DESC	EMP_LEVEL_DESC	L_EMP_LEVEL	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	L_EMPLOYEE

City

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_CITY	R_EMP_ADDRESS	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	R_EMP_ADDRESS

Country

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_CONTRY	R_EMP_ADDRESS	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	R_EMP_ADDRESS

Phone Number

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_PHONE_NUM	R_EMP_ADDRESS	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	R_EMP_ADDRESS

State

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_STATE	R_EMP_ADDRESS	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	R_EMP_ADDRESS

Street

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_STREET	R_EMP_ADDRESS	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	R_EMP_ADDRESS

Zip Code

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_ZIP	R_EMP_ADDRESS	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	R_EMP_ADDRESS

Birth Date

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_BIRTH_DATE	L_EMPLOYEE	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	L_EMPLOYEE

Hire Date

Form	Form Expression	Lookup Table	Other Tables
ID	HIRE_DATE	L_EMPLOYEE	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	L_EMPLOYEE

Leave Date

Form	Form Expression	Lookup Table	Other Tables
ID	LEAVE_DATE	L_EMPLOYEE	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	L_EMPLOYEE

Department

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_DPTM_ID, LAST_DPTM_ID, CNTR_DPTM_ID	L_EMP_DPTM, F_EMP_PREV_HIST, L_CONTRACTOR	F_EMP_HIST, F_SURVEY, F_PLAN, L_EMPLOYEE
DESC	EMP_DPTM_DESC	L_EMP_DPTM	None

 

Children	Parents	Relationship Type	Table
Contractor	None	One-to-many	L_CONTRACTOR
Employee, Month	None	One-to-many	F_EMP_HIST
None	Division	Many-to-one	L_EMP_DPTM

Previous Department

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_DPTM_ID, LAST_DPTM_ID	L_LAST_DPTM, F_EMP_HIST	None
DESC	EMP_DPTM_DESC	L_LAST_DPTM	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	F_EMP_HIST
None	Previous Division	Many-to-one	L_LAST_DPTM

Division

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_DIVISION_ID	L_EMP_DIVISION	L_EMP_DPTM
DESC	EMP_DIVISION_DESC	L_EMP_DIVISION	None

 

Children	Parents	Relationship Type	Table
Department	None	One-to-many	L_EMP_DPTM

Previous Division

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_DIVISION_ID	L_LAST_DIVISION	L_LAST_DPTM
DESC	EMP_DIVISION_DESC	L_LAST_DIVISION	None

 

Children	Parents	Relationship Type	Table
Previous Department	None	One-to-many	L_LAST_DPTM

Location

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_LOCATION_ID, LAST_LOCATION_ID, CNTR_ LOCATION_ID	L_EMP_LOCATION, F_EMP_PREV_HIST, L_CONTRACTOR	F_EMP_HIST, F_PLAN, L_EMPLOYEE
DESC	EMP_DPTM_DESC	L_EMP_LOCATION	None

 

Children	Parents	Relationship Type	Table
Contractor	None	One-to-many	L_CONTRACTOR
Employee, Month	None	One-to-many	F_EMP_HIST
None	Region	Many-to-one	L_EMP_LOCATION

Previous Location

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_LOCATION_ID, LAST_LOCATION_ID	L_LAST_LOCATION, F_EMP_HIST	None
DESC	LAST_LOCATION_ DESC	L_LAST_LOCATION	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	F_EMP_HIST
None	Previous Region	Many-to-one	L_LAST_LOCATION

Region

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_REGION_ID	L_EMP_REGION	L_EMP_ LOCATION
DESC	EMP_REGION _DESC	L_EMP_REGION	None

 

Children	Parents	Relationship Type	Table
Location	None	One-to-many	L_EMP_LOCATION

Previous Region

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_REGION_ID	L_LAST_REGION	L_LAST_ LOCATION
DESC	EMP_REGION _DESC	L_LAST_REGION	None

 

Children	Parents	Relationship Type	Table
Location	None	One-to-many	L_LAST_LOCATION

Age Range

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_AGE_RNG_ID	L_EMP_AGE_RANGE	R_EMP_DEMO
DESC	EMP_AGE_RNG_ DESC	L_EMP_AGE_RANGE	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	R_EMP_DEMO

Degree Type

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_DGR_TYPE_ID	L_EMP_DGR_TYPE	R_EMP_DEMO
DESC	EMP_DGR_TYPE_ DESC	L_EMP_DGR_TYPE	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	R_EMP_DEMO

Education

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_EDU_ID	L_EMP_EDU	R_EMP_DEMO
DESC	EMP_EDU_ID	L_EMP_EDU	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	R_EMP_DEMO

Ethnicity

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_ETHN_ID	L_EMP_ETHNICITY	R_EMP_DEMO
DESC	EMP_ETHN_DESC	L_EMP_ETHNICITY	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	R_EMP_DEMO

Gender

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_GENDER_ID	L_EMP_GENDER	R_EMP_DEMO
DESC	EMP_GENDER_DESC	L_EMP_GENDER	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	R_EMP_DEMO

Marital Status

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_MRTL_STS_ID	L_EMP_MRTL_STS	R_EMP_DEMO
DESC	EMP_MRTL_STS_ DESC	L_EMP_MRTL_STS	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	R_EMP_DEMO

Nationality

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_NTNLTY_ID	L_EMP_NTNLTY	R_EMP_DEMO
DESC	EMP_NTNLTY_DESC	L_EMP_NTNLTY	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	R_EMP_DEMO

Clearance

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_CLEARANCE_ID	L_CLEARANCE	L_EMPLOYEE
DESC	EMP_CLEARANCE_DESC	L_CLEARANCE	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	L_EMPLOYEE

Exempt Status

Form	Form Expression	Lookup Table	Other Tables
ID	EXEMPT_FLG	L_EMPLOYEE	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	L_EMPLOYEE

Current Status

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_CURR_STS_ID, EMP_STS_ID	L_EMPLOYEE; L_EMP_STS_HIST	None
DESC	EMP_STS_DESC	L_EMP_STS_HIST	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	L_EMPLOYEE

Historical Status

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_STS_ID	L_EMP_STS	F_EMP_HIST
DESC	EMP_STS_DESC	L_EMP_STS	None

ID

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_ID_N	L_EMPLOYEE	L_EMPLOYEE

 

Children	Parents	Relationship Type	Table
Employee	None	One to one	L_EMPLOYEE

Immigration Status

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_IMM_STS_ID	L_EMP_IMM_STS	L_EMPLOYEE
DESC	EMP_IMM_STS_DESC	L_EMP_IMM_STS	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	L_EMPLOYEE

Minority Group

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_MNRTY_GRP_ ID	L_EMP_MNRTY_GRP	L_EMPLOYEE
DESC	EMP_MNRTY_GRP_ DESC	L_EMP_MNRTY_GRP	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	L_EMPLOYEE

Position Status

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_PSTN_STS_ID	L_EMP_PSTN_STS	L_EMPLOYEE
DESC	EMP_PSTN_STS_ DESC	L_EMP_PSTN_STS	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	L_EMPLOYEE

Recruiting Source

Form	Form Expression	Lookup Table	Other Tables
ID	RCRTNG_SOURCE_ ID	L_RCRTNG_SOURCE	L_EMPLOYEE
DESC	RCRTNG_SOURCE_ DESC	L_RCRTNG_SOURCE	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	L_EMPLOYEE

Supervisor

Form	Form Expression	Lookup Table	Other Tables
ID	SUPERVISOR_ID EMPLOYEE_ID	L_EMPLOYEE, L_EMP_SUPERVISOR	None
DESC	EMPLOYEE_DESC	L_EMP_SUPERVISOR	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	L_EMPLOYEE

Title

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_TITLE_ID, CNTR_TILE_ID	L_EMP_TITLE L_CONTRACTOR	L_EMPLOYEE
DESC	EMP_TITLE_DESC	L_EMP_TITLE	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	L_EMPLOYEE

Work Experience (Years)

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_WORK_EXP	L_EMPLOYEE	None

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	L_EMPLOYEE

Department Transfer

Form	Form Expression	Lookup Table	Other Tables
ID	DPTM_TRANSFER_FLG	F_EMP_HIST	F_EMP_PREV_ HIST

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	L_EMPLOYEE

Location Transfer

Form	Form Expression	Lookup Table	Other Tables
ID	LOC_TRANSFER_FLG	F_EMP_HIST	F_EMP_PREV_ HIST

 

Children	Parents	Relationship Type	Table
Employee	None	One-to-many	L_EMPLOYEE

Contractor hierarchy

The Contractor hierarchy is available within the Employee hierarchy. In HRAM, the Contractor hierarchy shares the following parents with Employee:

•

Department

•

Division

•

Location

•

Region

•

Title

In other scenarios, the Contractor hierarchy can also share other parents with Employee, such as Supervisor. The attributes and relationships in the following figure represent the Contractor hierarchy.

 

Attribute	Description	Example
Contractor	Individual working for the company under a time-term contract, receiving salary in return.	John Smith
Department	Contractor's department.	Production Consulting, Pre-sales Consulting
Division	Contractor's division.	Administration, Consulting
Location	Contractor's work site.	Washington DC
Region	Contractor's region.	Virginia
Contract Activity Status	Status of the contract. • Active • Inactive (terminated or expired)	Active, Inactive
Title	Contractor's job title.	Consultant, Product Manager, Program Manager, and so on
Contract Start Date	Contract start date.	12/24/2002
Contract Expiration Date	Contract end date.	12/24/2003

The detailed definition of each attribute in the MicroStrategy metadata listed previously is shown in the following tables.

Contractor

Form	Form Expression	Lookup Table	Other Tables
ID	CONTRACTOR_ID	L_CONTRACTOR	None
DESC	CONTRACTOR_DESC	L_CONTRACTOR	None

 

Children	Parents	Relationship Type	Table
None	Contractor Activity	Many-to-one	L_CONTRACTOR
None	Contract Expiration Date	Many-to-one	L_CONTRACTOR
None	Contract Start Date	Many-to-one	L_CONTRACTOR
None	Contract End Date	Many-to-one	L_CONTRACTOR
None	Department	Many-to-one	L_CONTRACTOR
None	Location	Many-to-one	L_CONTRACTOR

Contract Activity

Form	Form Expression	Lookup Table	Other Tables
ID	EMP_STS_ID CNTR_ACTIVITY	L_CNTR_STS L_CONTRACTOR	None
DESC	EMP_STS_DESC	L_CNTR_STS	None

 

Children	Parents	Relationship Type	Table
Contractor	None	One-to-many	L_CONTRACTOR

Contract Expiration Date

Form	Form Expression	Lookup Table	Other Tables
ID	CONTRACT_END_DATE	L_CONTRACTOR	None

 

Children	Parents	Relationship Type	Table
Contractor	None	One-to-many	L_CONTRACTOR

Contract Start Date

Form	Form Expression	Lookup Table	Other Tables
ID	CONTRACT_START_DATE	L_CONTRACTOR	None

 

Children	Parents	Relationship Type	Table
Contractor	None	One-to-many	L_CONTRACTOR

Compensation hierarchy

This hierarchy represents the types of monetary compensation paid to employees. This hierarchy consists of a single attribute.

Attribute	Description	Example
Compensation Item	Type of monetary compensation given to the employee per month. See examples for typical values for this attribute.	Base salary, regular bonus, allowance, and so on.

The detailed definition of the attribute in the MicroStrategy metadata listed previously is shown in the following table.

Compensation Item

Form	Form Expression	Lookup Table	Other Tables
ID	COMP_TYPE_ID	L_COMP_TYPE	F_COMP_MONTH
DESC	COMP_TYPE_DESC	L_COMP_TYPE	None