Travel Data Sets

Travel Data Sets in Power BI are the basis for the Pending report set. They are made available to analysts to create their own reports to meet on-the-fly and seasonal needs. Information available in the data sets is delineated below. The data sets are refreshed every morning with actions taken the day before.

Travel - Pending Status Travel Requests Data Set

This data set includes information about Travel Requests, no matter their status in Archivum. Once a Travel Request is submitted, the information for that request is available in this data set the next day. Additional information about the Expense Reports connected to a Travel Request can be obtained from this data set.

Table name	Table type	Table description	Columns / Fields
Chartfield College-Global Department		This table contains college and global department information, including code, abbreviation, name, and ID for the chartfield information provided.	College Code College Code Abbreviation College Name Department Name HSC Directory Global Department Abbreviated Name Global Department ID Global Department Name Global Department Type Indicator
Data Refresh		This table contains the details regarding the date and time that data was last refreshed from the source system.	Last Refresh Date Last Run Timestamp
Expense Report Created Date		This table contains date and time information captured for the expense when the expense report was initiated.	Calendar Month Calendar Quarter Calendar Year Day of the Week ER Created Day Fiscal Month Fiscal Quarter Fiscal Year FYYY-Quarter Month Abbreviation Month Name YYYYMM
GEMS Department Profile View	View	This table contains a list of GEMS departments and its associated global department (links via Global Department ID). This can be used to determine which GEMS departments are grouped under a given Global Department.	Abbreviated Name - Travel College Code Creation Date Department Code Department Desc Department Name Department Profile ID GEMS Department Begin Date Global Department ID Short Name Type Indicator
MDM Current Employee View	View	This table contains appointment data for employees.	Department ID Employee ID Begin Date Key Person Record MDM ID Name Display
Person - Last Action		This table stores the name and MDM ID of the person who last took action on the travel request. MDM ID can be used to determine additional appointment information about the person in MDM Current Employee View.	Last Action Person First Name Last Action Person Last Name Last Action Person MDM ID Last Action Person Middle Name Last Action Person Name
Person - Last Send Back		This table stores the name and MDM ID of the person who last sent back the travel request to obtain more information. MDM ID can be used to determine additional appointment information about the person in MDM Current Employee View.	Last Send Back Person First Name Last Send Back Person Last Name Last Send Back Person MDM ID Last Send Back Person Middle Name Last Send Back Person Name
Person - Traveler		This table contains the details of the traveler, including department, employee ID, and name. MDM ID can be used to determine additional appointment information about the person in MDM Current Employee View.	Traveler Department Traveler Department ID Traveler First Name Traveler GEMS Employee ID Traveler Middle Name Traveler Last Name Traveler MDM ID Traveler Middle Name Traveler Name
Travel Begin Date		This table stores date information regarding the beginning date of the person's travel.	Begin Day Calendar Month Calendar Quarter Calendar Year Day of Week Days in Calendar Year Fiscal Month Fiscal Quarter Fiscal Year FYYY Quarter KEY Day Leap Year Monday Thru Friday Flag Month - YYYYMM Month Abbreviation Month Name Saturday Date Of Week Sunday Date Of Week Week Label Sunday Saturday Week of Cal Year Week of Month Week Sunday to Saturday Since FY00
Travel End Date		This table stores date information regarding the end date of the person's travel.	Bucket Range Days After Travel Calendar Month Calendar Quarter Calendar Year Day of Week Days After Travel Days in Calendar Year Fiscal Month Fiscal Quarter Fiscal Year FYYY Quarter KEY Day Leap Year Monday thru Friday Flag Month Abbreviation Month Name Month YYYYMM Saturday Of Week Sunday Of Week Todays date Travel End Day Week Label Sunday to Saturday Week Of Calendar Year Week Of Month Week Sunday to Saturday Since FY00
Travel Request Details		This table maps information about the travel request to information about the expense report. It is updated as action is taken on the request and the expense.	Archivum Number Details ID ER Description ER SB Action Description ER SB Action Type ER SB Note ER Type Expense Report ID KEY Travel Details Last Action Description Last Action Note Last Action Type TAR Number TER Number TR Description TR PTA Preventative Action TR PTA Reason TR SB Action Description TR SB Action Type TR SB Note Travel Title Type
Travel Request Expenses		This table contains the code and description of the travel expense.	Code Description ID Location Category
Travel Request Line Item Details		This table holds information on a specific travel request, including department, expense ID, payment type, and account code.	Chartfield KEY Department Profile Chartfield KEY Global Department Chartfield KEY USF Department Initiative Code KEY Travel Expense ID KEY Travel Location ID KEY USF Fund ID KEY USF Project ID KEY USF Project Profile OU USF Account Code Percentage Travel Request Amount Travel Request ID USF Account Code
Travel Request Location		This table contains details on the travel location, such as city, state, and country.	City City State Country Code Country Country Code Group ID KEY Travel Location State
Travel Request Status		This table contains the current status of the travel request.	ID Status
Travel Request Summary		This table contains the details of the travel request, including travel dates, purpose, request submitter, traveler employee ID, and travel location. It is updated as the request is processed to include status of the request, last action, and send back details.	Bucket Range Days After Travel Days Before Travel Days Between Days in Last Status ER Exists Flag KEY Last Action Day KEY Person Record KEY TR Begin Day KEY TR Create Day KEY TR End Day KEY TR Last Modification Day KEY Travel Details KEY Travel Doc Status KEY Travel Location KEY Travel Purpose Key Person Record Last Send Back by MDM ID Send Back Datetime Send Back Flag Send Back KEY Day Submitter Name TAR Number TR Created by MDM ID TR Total Amount TR Updated by MDM ID TR_TOTAL_AMOUNT_ZERO Travel Request ID Traveler Appt Global Department KEY Traveler Employee Record Traveler GEMS Employee ID Traveler GEMS KEY Department Profile
Traveler College-Global Department		This table contains the college and department information for the traveler as indicated in Archivum.	College Code Abbreviation College Name Global Department ID Traveler College Code Traveler Department Name HSC Directory Traveler Global Department Abbreviated Name - Travel Traveler Global Department Name Traveler Global Department Type Indicator
USF FAST Department		This table stores the FAST code and name.	FAST Department Code FAST Department Name
USF Fund Codes		This table provides information on fund codes.	Fund Code Fund Description Fund Group Type
USF Products		Contains list of FAST product codes and description	KEY USF Product USF Product USF Product Description
USF Projects		Contains list of FAST projects and associated details such as start and end dates, associated employees, total amounts, award status, and purpose.	Adjusted Award Amount Adjustment Amount Amount Total Award Status Award Type Awards Date Contract Begin Date Contract Business Unit Contract Description Contract Employee ID Contract Employee Name Contract End Date Contract Number Contract Title Contract Type Cust ID Department ID GM Begin Date GM CFDA Num GM Source Income GM Source Income GR Source Funding Initial Award Amount KEY USF Project Primary Sponsor ID Project Begin Date Project CFDA Project End Date Project ID Project PI Employee ID Project PI MDM ID Project PI Name Proposal ID Purpose Ref Award Number Ref Awd Num2 Sponsor Award ID Sponsor Name USF Business Unit USF Operating Unit Version ID

Travel - Pending Status Expense Report Data Set

This data set includes information about Expense Reports, no matter their status in Archivum. Once an Expense Report is submitted, the information for that ER is available in this data set the next day.

Table name	Table type	Table description	Columns / Fields
Chartfield College-Global Department		This table contains college and global department information, including code, abbreviation, name, and ID for the chartfield information provided.	College Code College Code Abbreviation College Name Department Name HSC Directory Global Department Abbreviated Name Global Department ID Global Department Name Global Department Type Indicator
Data Refresh		This table contains the details regarding the date and time that data was last refreshed from the source system.	Last Refresh Date Last Run Timestamp
Day - Last Action Date		This table contains date and time information captured for the expense when the expense report was initiated.	Calendar Month Calendar Quarter Calendar Year Last Action Day Fiscal Month Fiscal Quarter Fiscal Year FYYY-Quarter Month Name
Day - ER Created Date		This table contains date and time information captured for the expense when the expense report was initiated.	Calendar Month Calendar Quarter Calendar Year Day of the Week ER Created Day Fiscal Month Fiscal Quarter Fiscal Year FYYY-Quarter Month Abbreviation Month Name YYYYMM
GEMS Department Profile View	View	This table contains a list of GEMS departments and its associated global department (links via Global Department ID). This can be used to determine which GEMS departments are grouped under a given Global Department.	Abbreviated Name College Code GEMS Department Code GEMS Department Description Global Department Name KEY Global Department Type Indicator
MDM Current Employee View	View	This table contains appointment data for employees.	Department ID Employee ID Begin Date Key Person Record MDM ID Name Display
Person - Last Action Person		This table stores the name and ID of the person who last took action on the travel expense report. MDM ID can be used to determine additional appointment information about the person in MDM Current Employee View.	Person MDM ID Last Action Person Name
Person - Last Send Back		This table stores the name and ID of the person who last sent back the travel expense report to obtain more information. MDM ID can be used to determine additional appointment information about the person in MDM Current Employee View.	Last Send Back Person MDM ID Last Send Back Person Name
Person - Traveler Name		This table contains the details of the traveler, including department, employee ID, and name. MDM ID can be used to determine additional appointment information about the person in MDM Current Employee View.	Traveler EMPLID Traveler First Name Traveler GEMS ID Traveler Middle Name Traveler Last Name Traveler MDM ID Traveler Middle Name Traveler Name
Person - Submitter		This table contains the details of the submitter, including department, employee ID, and name.	Submitter EMPLID Submitter GEMS ID Submitter GEMS End Date Submitter GEMS Start Date Submitter Hire Date Submitter Termination Date Submitter MDM ID Submitter Name
Day - Travel Begin Date		This table stores date information regarding the beginning date of the person's travel.	Begin Day Calendar Month Calendar Quarter Calendar Year Fiscal Month Fiscal Quarter Fiscal Year FYYY Quarter Month Name Calendar Day YYYYMM
Day - Travel End Date		This table stores date information regarding the end date of the person's travel.	Calendar Month Calendar Quarter Calendar Year Day of Week Fiscal Month Fiscal Quarter Fiscal Year FYYY Quarter Month Abbreviation Month Name YYYYMM Travel End Day
Expense Report Details		This table contains details on the expense report details and is updated as action is taken on the request and the expense.	ER Description ER SB Action Description ER SB Action Type ER SB Note ER Type Expense Report ID Last Action Description Last Action Note Last Action Type TAR Number TER Number TR PTA Preventative Action TR PTA Reason TR SB Action Description TR SB Action Type TR SB Note Travel Description Travel Title TR Type Travel Expense ID
Expense Report Travel Expenses		This table contains the code and description of the travel expense.	Expense Code Expense Description Expense ID Expense Type
Expense Report Line Item Details		This table holds information on a specific travel expense, include department, expense ID, payment type, and account code.	Chartfield Amount Chartfield Percentage Expense ID Expense Report ID Initiative Code OU Payment Type Percentage Traveler EMPLID Traveler EMPLRCD USF Account Code
Expense Report Travel Location		This table contains details on the travel location, such as city, state, and country.	City City State Country Location Code Country Country Code Location Group Location ID State
Expense Report Status		This table contains the current status of the travel request.	Status ID Status
Expense Report Summary		This table contains the details of the travel expense report, include last status, submission, last action, traveler employee ID, and traveler employee record.	Bucket Range Cash Advance Days Between Modified Days Between Days in Last Status Days in Last Status by ER Days Since Last Action ER Completed Flag ER Created by MDM ID ER Modified by MDM ID ER Total Expenses Expense Report ID Fiscal Review FR Sendback Hold Count Last Action MDM ID Last Send Back by MDM ID On Hold Duration On Hold Flag ER Prepay Auditor Approved Datetime Send Back Flag Submission Submission to Approved Supervisor Review Supervisor Sendback Total Sendbacks TR Exists Flag Travel End to Approved Travel End to Submission Traveler Empl Rcd Traveler EMPLID
Traveler College-Global Department		This table contains the college and department information for the traveler as indicated in Archivum.	College Code College Name Global Department Name HSC Directory Dept Name Type Indicator
USF FAST Department		This table stores the FAST code and name.	FAST Department Code FAST Department Name
USF Fund Codes		This table provides information on fund codes.	Fund Code Fund Description Fund Group Type
USF Products		Contains list of FAST product codes and description	KEY USF Product USF Product USF Product Description
USF Projects		Contains list of FAST projects and associated details such as start and end dates, associated employees, total amounts, award status, and purpose.	Adjusted Award Amount Adjustment Amount Amount Total Award Status Award Type Awards Date Contract Begin Date Contract Business Unit Contract Description Contract Employee ID Contract Employee Name Contract End Date Contract Number Contract Title Contract Type Cust ID Department ID GM Begin Date GM CFDA Num GM Source Income GM Source Income GR Source Funding Initial Award Amount KEY USF Project Primary Sponsor ID Project Begin Date Project CFDA Project End Date Project ID Project PI Employee ID Project PI MDM ID Project PI Name Proposal ID Purpose Ref Award Number Ref Awd Num2 Sponsor Award ID Sponsor Name USF Business Unit USF Operating Unit Version ID