Deltek PIM Integrated Data
Field mapping reference for Deltek PIM (On-Premise)
What this page covers: A complete reference mapping every Deltek PIM field to its Mosaic equivalent. Use this guide when configuring your integration, verifying synced data, or troubleshooting discrepancies.
| Integration type | On-Premise (Direct database access via SQL Server replication) |
| Stored procedures required? | No — read-only access via replicated tables and agent-managed views |
| Setup complexity | Moderate |
| Configurable fields | 1 setting controls orphan time entries; all other fields sync with fixed sources |
| Prerequisite | Deltek PIM Integration Setup |
How Data Flows
Deltek PIM (SQL Server Replica) → Mosaic Sync Engine (field mapping from replicated tables and agent-managed views) → Mosaic (11 synced data types)
The PIM integration uses SQL Server Replication to mirror a subset of your PIM database tables into a read-only replica that Mosaic connects to. On first connection, the agent creates a set of helper views (prefixed U2VW_) on the replica that drive most of the field mappings below. See the SQL Server Integration Requirements for replication setup instructions.
Integrated Data Types
The integration syncs 11 data categories from Deltek PIM into Mosaic:
| Data Type | Description |
|---|---|
| Members | Employee name, status, email |
| Clients | Client (organisation) records |
| Projects | Project records (number, dates, fee, client, default rate group) |
| Phases | Phase breakdowns under projects — sourced from PIM Workstages and Variations |
| Work Categories | PIM timesheet item categories synced to Mosaic for time categorization |
| Time Entries | Posted and unposted timesheet entries (hours, charge rate, work category, approval status) |
| Standard Roles | Organization-wide role definitions sourced from PIM Grades |
| Member Roles | Default role per Member based on the Grade assigned to the Contact |
| Rate Groups | Finance Rate Tables grouped for role-based billing |
| Rates | Individual rate entries within a Rate Group, keyed by Grade and effective date |
| Standard Bill Rates | Charge rate amounts derived from PIM Finance Rate rows |
This list may expand as new features are added.
Fee, Default Rate Group, and Phase Status are synced as fields within Projects and Phases — they are not standalone entities.
The agent creates a number of helper views on your replica (prefixedU2VW_) the first time it connects. These views are read-only and are required for fee, rate, and phase mappings. See Adding Helper Views to PIM for details.
Not Synced
| Data Type | Description |
|---|---|
| Departments | Departmental groupings within a member |
| Offices | Member or project office/branch assignment |
| Regions | Geographic region assigned to a project or member |
| Member Cost Rates | Cost rates exist in PIM (U2VW_Finance_Rates.costRate) but are not surfaced — only charge rates are synced |
| Member Project Bill Rates | Project-specific employee rates are excluded at fetch (Project_ID IS NULL filter) |
| Overtime Rates | Excluded at fetch (Finance_Analysis_Code_ID IS NULL filter) |
| Work Plans | PIM does not surface a resource-planning entity that Mosaic can sync as Work Plans |
| Parent / Sub-Project Hierarchy | PIM Project.Parent_Project_ID is not mapped — projects sync as a flat list |
| PTO / Holidays / Invoices | Not in the supported data type list |
Reading the Field Mapping Tables
Symbol legend for technical tables
The Technical Reference tabs below use a shorthand notation for database table sources. Read this legend before reviewing the mappings.
| Symbol | Meaning | Example |
|---|---|---|
. | Database table or view field reference | Contact.Forename = the Forename column from the Contact table |
• | Values combined into one composite value | Entity_Workstages.Entity_Identifier • Entity_Workstages.Entity_Workstages_ID |
> | Fallback priority — use the first available value | Entity_Finance.Finance_Rate_Table_ID > -1 = use the linked rate group; if null, use -1 |
Members
| What Syncs to Mosaic | Where It Comes From in PIM |
|---|---|
| Employee ID | The unique contact identifier |
| First Name | The contact's first name |
| Middle Name | The contact's middle name(s) |
| Last Name | The contact's surname |
| The contact's primary email address | |
| Created Date | When the contact record was created |
| Modified Date | When the contact record was last modified |
Important: Who counts as an Employee
Only contacts that are not linked to a customer organisation are synced as Members. Specifically, the agent fetches
Contactrows whereOrganisation_ID = -1. External contacts (clients, suppliers, etc.) are excluded.
Clients
| What Syncs to Mosaic | Where It Comes From in PIM |
|---|---|
| Client ID | The unique organisation identifier |
| Client Name | The organisation name |
Projects
| What Syncs to Mosaic | Where It Comes From in PIM |
|---|---|
| Project ID | The unique project identifier |
| Project Name | The project's name |
| Project Number | The project's code |
| Description | The project's description |
| Client | The client organisation linked to the project (via the project's client-role link) |
| Start Date | The project's start date |
| End Date | The project's completion date |
| Fee | The total fee from PIM's finance summary view — equivalent to PIM's profits-and-costs total |
| Rate Group | The Finance Rate Table linked on the project's finance record (falls back to the default rate group when not specified) |
| Billing Type | Always Role-based — PIM projects bill by Grade-based rates |
| Created Date | When the project record was created |
| Modified Date | When the project record was last modified |
Phases
PIM phases are derived from two PIM entities: Workstages (the primary phase breakdown under a project) and Variations (sub-phases or change orders within a project). Both flow into Mosaic as Phases under a single Project. Workstages are ordered before Variations in the Mosaic phase list.
| What Syncs to Mosaic | Where It Comes From in PIM |
|---|---|
| Phase ID | A composite of the project ID and the Workstage or Variation ID |
| Phase Name | The workstage or variation name |
| Phase Number | The workstage abbreviation (for Workstages) or variation code (for Variations) |
| Parent / Project | The parent project's ID |
| Start Date | The workstage or variation start date |
| End Date | The workstage completion date (for Workstages) or variation end date (for Variations) |
| Status | Active if the workstage is still open for time; Complete if closed or marked not-applicable |
| Fee | The total fee for the workstage (from PIM's workstage finance summary) or variation (from PIM's variation finance summary) |
| Rate Group | The Finance Rate Table linked on the phase's finance record (falls back to the default rate group when not specified) |
| Billing Type | Always Role-based |
| Order | Workstages come first (ordered by sequence), then Variations (ordered by variation code) |
Work Categories
| What Syncs to Mosaic | Where It Comes From in PIM |
|---|---|
| Activity ID | The timesheet item category identifier |
| Activity Name | The category name used for time categorization |
Time Entries
| What Syncs to Mosaic | Where It Comes From in PIM |
|---|---|
| Time Entry ID | The unique timesheet item time identifier |
| Date | The date of the time entry |
| Employee | The contact (Member) who logged the time |
| Project | The project the time was logged against |
| Phase | The workstage or variation the time was logged under |
| Work Category | The timesheet item category used for the entry |
| Hours | The hours recorded on the timesheet line |
| Hourly Rate | The charge-out rate on the parent timesheet item |
| Description | The note attached to the time entry |
| Status | Approved, Submitted, or Not Submitted — derived from committed and approved flags |
Time Entry Status
- Approved — the entry has been committed and approved
- Submitted — the entry has been committed but is awaiting approval
- Not Submitted — the entry has not yet been committed
Mosaic syncs both unposted (not yet committed) and posted (committed) time entries.
Orphan time entries
By default, time entries that have no project/phase context (no Workstage or Variation) are dropped. You can opt in to syncing those entries against an auto-created placeholder Project/Phase via the Sync time entries without an entity name setting — see the Visual Settings Guide.
Standard Roles
| What Syncs to Mosaic | Where It Comes From in PIM |
|---|---|
| Role ID | The unique PIM Grade identifier |
| Role Name | The PIM Grade name |
Member Roles
| What Syncs to Mosaic | Where It Comes From in PIM |
|---|---|
| Member Role ID | A composite of the employee and their assigned Grade |
| Employee | The contact this role assignment belongs to |
| Role | The Grade assigned to the employee |
| Override Roles | Always false — Member Roles do not override existing Mosaic Role assignments |
Rate Groups
| What Syncs to Mosaic | Where It Comes From in PIM |
|---|---|
| Rate Group ID | The Finance Rate Table identifier |
| Rate Group Name | The Finance Rate Table name |
| Active Entity Type | Always Role — all PIM rates are by Grade |
| Is Default | Always false |
Rates
Rates and Standard Bill Rates are both sourced from the U2VW_Finance_Rates helper view. Rates link a Grade to a Rate Group and represent the role-rate row; Standard Bill Rates carry the actual charge amount and effective dates.
| What Syncs to Mosaic | Where It Comes From in PIM |
|---|---|
| Entity Rate ID | A composite identifier built from the Rate Group, Grade, and effective-from date |
| Rate Group | The Rate Group this rate belongs to |
| Role | The Grade this rate applies to |
| Start Date | The effective-from date |
| End Date | The effective-to date |
| Is Cost Rate | Always false — only charge rates are synced |
Standard Bill Rates
| What Syncs to Mosaic | Where It Comes From in PIM |
|---|---|
| Rate ID | A composite identifier (same scheme as Entity Rate ID — Rate Group + Grade + effective-from date) |
| Rate Amount | The charge-rate amount |
| Rate Description | A human-readable label built from the Rate Table name, Grade name, and effective-from date |
| Is Cost Rate | Always false |
Configurable Fields at a Glance
PIM has a single configurable setting that affects what data syncs. All other field mappings are fixed.
| Entity | Behaviour | Controlled By |
|---|---|---|
| Time Entry | Whether orphan time entries (no project/phase) are synced | Sync time entries without an entity name setting (default: off) |
See the Visual Settings Guide for details.
Replicated Data Sources
The agent reads from the following PIM tables on the replica. Helper views (prefixed U2VW_) are created by the agent on first connection and are required for fee, rate, and phase mappings.
Tables and views read by the integration
Replicated source tables
| Table | Used For |
|---|---|
Contact | Members, Member Roles |
Contact_Method | Email lookup (via Contact_Email view) |
Organisation | Clients, Project Client lookup |
Project | Projects |
Entity_Finance | Projects (Rate Group), Phases (Rate Group) |
Finance_Rate_Table | Rate Groups, Projects, Phases |
Finance_Rate | Standard Bill Rates (via U2VW_Finance_Rates) |
Finance_Rate_Type | Rates (via U2VW_Finance_Rates) |
Finance_Analysis_Code | Rate filter (overtime exclusion) |
Finance_Config | Variation phases (resolves variation workstage) |
Grade | Standard Roles, Member Roles, Rates |
Entity | Projects, Phases |
Entity_Class | Phase fetch (class filter) |
Entity_Group | Rates (via U2VW_Finance_Rates) |
Entity_Identifier | Phase ID composition (Workstage path) |
Entity_Workstages | Phases (Workstage path), Time Entries |
Workstage | Phases (Workstage path) |
Variation | Phases (Variation path), Time Entries |
Timesheet | Time Entries |
Timesheet_Item | Time Entries, Hourly Rate |
Timesheet_Item_Time | Time Entries |
Timesheet_Item_Category | Work Categories |
Agent-managed helper views and tables (created on first connection from DDL shipped with the agent)
| Object | Purpose |
|---|---|
Contact_Email (view) | Surfaces primary email per contact |
Lookup_Organisation (view) | Helper for client lookups |
U2VW_Finance_Project_Client_Lookup (view) | Project ↔ Client link |
U2VW_Finance_Search_Profits_and_Costs (view) | Project fee |
U2VW_Finance_Entity_Workstages_Extended (view) | Workstage phase fee |
U2VW_Finance_Variations (view) | Variation phase fee |
U2VW_Finance_Rates (view) | Charge rates per Grade × Rate Table |
mosaicEntity_Group_Entities (table) | Replaces the non-replicable Entity_Group_Entities table |
Intermediate U2VW_* and U2FN_* views | Used by the views above (budget, summary, writeoffs, etc.) |
PIM replication can include additional tables beyond those above. Mosaic only reads the subset listed here; any tables in your replication setup that are not in this list are not used by the integration.
Troubleshooting & FAQ
Why are some employees missing from Mosaic?
Only contacts where Organisation_ID = -1 (i.e. not associated with an external organisation) are synced as Members. If an employee's contact record was created with an organisation link, they will not appear in Mosaic. Reset the contact's organisation link in PIM, then re-sync.
Why aren't certain time entries showing up?
By default, time entries with no project or phase context are dropped. If your PIM workflow allows logging time without an entity, enable the Sync time entries without an entity name setting — those entries will be routed to an auto-created placeholder Project and Phase in Mosaic. See the Visual Settings Guide.
Where does the Project Fee come from?
Project fee comes from the U2VW_Finance_Search_Profits_and_Costs.Total_Fee view column, which aggregates PIM's finance summary. Phase fee comes from U2VW_Finance_Entity_Workstages_Extended.total_fee (Workstages) or U2VW_Finance_Variations.total_fee (Variations).
Are Cost Rates supported?
No. Only charge rates from U2VW_Finance_Rates.chargeRate are synced. Cost-rate columns exist in the view but are not surfaced to Mosaic.
Are project-specific member rates supported?
No. The agent filters out rate rows that have a Project_ID set — only Rate-Table-level (Grade × Rate Group) rates sync. Project-level overrides are not surfaced.
Why are some PIM Rates missing?
The agent applies several filters at fetch time:
- Rates with a null
chargeRate - Rates with
Effective_From_Date < 1900-01-01(PIM placeholder dates) - Rates with no Grade (
Grade_ID IS NULL) - Rates tied to a specific project (
Project_ID IS NOT NULL) - Overtime rates (
Finance_Analysis_Code_ID IS NOT NULL)
I need a data source or field that isn't listed here.
Any integration not listed in this documentation is considered a customization and is charged at an hourly rate. For more information, see Integration Requests.
Deltek Documentation Links
| Topic | Link |
|---|---|
| Deltek PIM Product Site | Deltek PIM |
| Deltek PIM 22.0 Online Help | PIM Help |
| Deltek Help & Learning Portal | Deltek Help |