PSA – Data Model

This post is part of a series I’m creating connected with Project Service Automation (PSA) for Microsoft Dynamics 365. This time I will consider the project service data model.

As we consider enhancing Project Service or creating our own reports understanding the data model extensions that PSA provides may become important.

In some cases, new entities are included in the Dynamics 365 schema but often we also see additional attributes on existing system entities. You will be able to identify these additional attributes as their name will always have the prefix msdyn_

Note: It is worth being aware that Project Service and Field Service share many entities, therefore some of the msdyn_ attributes may relate to Field Service. As this post has a focus on Project Service I will try to focus on the key entities / attributes you may need to be aware of for PSA.

I won’t be documenting every field / entity in this post! But I hope to give enough of a flavor for the PSA data model to be able to navigate the detail yourself.

Opportunities

As is standard with opportunities each opportunity has multiple opportunity lines. Project Service adds some additional attributes to these entities.

The additional attributes on opportunity and opportunity lines include ….

Entity Schema Name Display Name Type Comments
Opportunity msdyn_ContractOrganization Contracting Unit Lookup (Organizational Unit) The organizational unit in charge of the opportunity
Opportunity msdyn_OrderType Order Type Option Set Options include “Work based”, “Item Based” and “Service-Maintenance Based”.

Opportunities that have an order type of “Work Based” will be Project based opportunities.

Entity Schema Name Display Name Type Comments
Opportunity Line msdyn_BillingMethod Billing Method Option Set (Global) Billing method for the project opportunity line.

Options include Time and Material and Fixed Price

Opportunity Line msdyn_BudgetAmount Budget Amount Currency The customers budget for the opportunity line.
Opportunity Line msdyn_linetype Line Type Option Set (Global) The field to distinguish the order lines to be of project service or field service.

Options include “Project Service Line” or “Field Service Line”

Quotes

As we associated quotes with opportunities not only do we get quote and quote line detail enhancements but also a number of new entities specific to Project Service come into play.

The quote line contains a link to the project entity and a number of additional entities are also associated that support the detailed estimation capabilities of project service.

Quote line detail – the “supporting” detail for the quote line. Including fields like transaction class, resourcing unit, role, start date, end date and so on. The total sales price will be rolled up into the quote line to give the quoted amount.

Quote line analytics breakdown – holds additional line detail for reporting purposes. Calculated information such as gross margin is held in the analytics table.)

Quote line invoice schedule – holds the intended invoice schedule for customer billing and will get carried forward to the project contract when the quote is won. This is generated off the billing frequency from the quote line.

Quote line milestone – milestones show the timing of fixed price billing items.

Quote line resource – holds the values for quote line to say if this resource / transaction category is included or excluded in the quote.

Project Contract

The concept behind the project contract in Project service is actually quite similar to quotation. The contract is actually the order entity and the contract line is the order line entity. But both of these will have additional fields specific to Project Service. The additional project contract entities are all named “project contract line …”.

Project

Project is a new entity specific to PSA and is the container for all of your project tasks and resourcing detail. Each project has a project team and also a number of tasks. (Both of these entities will be related to the concept of a bookable resource.) The estimate and estimates line entities contain the cost and sales estimates.

Bookable Resource

Each bookable resource then has multiple bookings. In Field Service the “Bookable Resource Booking” would relate to a work order and in PSA it will relate to a project task. (As we saw the in previous diagram.)

Bookable resources are shared between Field Service and Project Service. Each bookable resource can have a number of characteristics (skills) and each of the skills has a proficiency rating.

A bookable resource can be a user, contact, account (etc). Although in Project Service only resources that are users can currently record details on timesheets and submit expenses. So generally speaking, when I talk about a bookable resource we are ultimately considering a system user.

Categories are roles. So, each bookable resource can have many roles via the “Bookable Resource Category Assn” entity. (Although one is marked as their default role.) For example, my “main” role is Functional Consultant. But when required I can also play the role of Business Analyst, Project Manager or reluctantly Tester. It will therefore be common for one resource to have multiple roles but you can only one role per project. (I have many hats that I often swap but I never wear two at once!)

Actuals

The actuals entity will store details of actual business transactions on your project. Including information on sales values, costs and quantities. When you create financial reports or maybe source data for integration with other systems, the actual entity and its associated entities will be a common source for this data.

Transaction Origin, this holds the origin of the actual transaction entity and its associated entity. Including the transaction type.

Transaction Connections shows how the connections relate. For example, an unbilled say to a estimate line.

The actual entity also has relationships with the other entities in CRM such as the project, project contact, customer and bookable resource.

Actuals get created when there is a financial impact on your project. Meaning draft or submitted expenses would not show. Actuals would be created once the expense (or time) entries are approved.

Hopefully this post has given you a flavour for some of the main entities in PSA, I hope it helps get you started in producing your own custom reports etc.
J

3 thoughts on “PSA – Data Model

  1. Pingback: PSA – Reporting Options | Microsoft Dynamics CRM and Unified Service Desk

  2. Pingback: PSA – Reporting Options - Microsoft Dynamics CRM Community

  3. Pingback: PSA – Complete Guide | Microsoft Dynamics CRM and Unified Service Desk

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s