By Nate Auchter, Director of Products and Solutions at Sensei Project Solutions
Hey there to all our Insiders! For a while we’ve seen many users posting and asking for detailed documentation on the Reporting Service provided by Microsoft for Project Online. We’ve decided to put that information together in easy to understand, Entity Data Models with supplementary blog posts so readers can learn more about specific fields and unique characteristics of each of these entities.
For those not familiar with what we’re talking about here, you can view all the entities and properties in your Project Online Reporting Service by signing into Office 365 and then navigating to https://[tenantname].sharepoint.com/sites/[PWAName]/_api/ProjectData/$metadata.
There you will see something like the following:
These are the “Tables” of data that you would use in a Project Online report if you’re using the default ProjectData, OData service as your data source. Now, the URL above is NOT what you’d use to retrieve the data in your reporting service; for that, you’d trim off the /$metadata part of the URL, leaving you with: https://[tenantname].sharepoint.com/sites/[PWAName]/_api/ProjectData. This is the URL you’d use in Excel 2016 or Power BI – ‘Get Data’. This allows you to then use the Query Editor (formerly Power Query) to explore the entities (tables) that you might want to retrieve. See figure below:
Many articles have already been written about getting data from the Reporting Service and how to build reports for Project Online but if you can’t find an article that details the steps/procedure you’d like, feel free to comment below and we’ll see if we can put one together for you. Here we will focus on the entities themselves, the properties of each, and at the end of our series, some of the relationships between them. We’d like to bring up a few points before we start talking about the details here. We’ve taken the traditional Visio Entity Data Model (EDM) template, and we’re using some of the components of the diagram a little differently, so we don’t want you Insiders to get confused.
- We kept the name of each table set to the name you would use in an OData query, NOT the real entity name. If you request the Visio files from us, you will find the entity name in the “Notes” for that entity, OR you can see entity names when accessing your ProjectData Service here: https://tenant.sharepoint.com/sites/pwasite/_api/ProjectData/$metadata. For the most part, the entity name is just the singular version of the table name used in the query.
- Navigation Properties are at the bottom of the table, below the properties in bold. The name of the navigation property is listed, and the relationship can be found in the Visio diagram notes for that column (property). Navigation properties, in simple terms, allow us to drill a little deeper into data for a specific record, for example, getting all Assignments for a specific project, or getting all the Timesheet Lines on a specific Timesheet.
- The key properties, as stated by Microsoft, are listed above the line at the top of the table. NOTE – in most cases there are additional properties in the entity that could be used as keys (foreign keys) to create relationships to other entities (tables).
- I’m going to frequently use the terms entity and table interchangeably and I may even use the terms property, column, and field interchangeably. See visual below:
We hope you enjoy this Insiders Series, and if you ever have any questions, feel free to reach out to your Business Solutions Executive or firstname.lastname@example.org. Happy reporting!
About Nate Auchter, MCSE MCT, Director of Products and Solutions, Sensei Project Solutions
Nate has been working with Microsoft’s cloud productivity and communications solutions since their initial release, and prior to that gained much of his expertise configuring, administering and developing solutions on the on-premises SharePoint, Exchange and Project Server platforms. With a strong background in enterprise reporting, business process automation through workflow development, and project management using Microsoft Project, Nate helps Sensei’s clients realize significant benefits from the Microsoft cloud productivity toolset. Recently earning the Microsoft Certified Solutions Expert (MCSE) in both Productivity and SharePoint, Nate is certified in the installation, configuration and architecture of the SharePoint and Microsoft PPM platforms both online and on premises, leveraging his proficiency to assist clients in their move to the Microsoft cloud. With dozens of successful Microsoft PPM implementations under his belt in a wide range of industries, Nate is an expert at distilling how Microsoft’s suite of products and features provide value to Sensei’s customers. Also a Microsoft Certified Trainer (MCT) he frequently presents at Microsoft and Microsoft Project User Group (MPUG) events.
About Sensei Project Solutions
Sensei Project Solutions, a Finalist for the 2017 Microsoft Project and Portfolio Management (PPM) Partner of the Year, focuses on bringing Instant Productivity to your team. Our mission is to help individuals and organizations be more productive so that they can achieve their greatest potential. As a Gold certified Microsoft Partner and Registered Education Provider (R.E.P.) with the Project Management Institute (PMI®), Sensei offers a complete set of services and products for a successful Microsoft PPM deployment. Our guiding principles for Proactive PPM follow best practices and industry standards aligned with the Project Management Institute (PMI) and Gartner, enabling organizations to manage resource demand, obtain business intelligence that facilitates better decision making, increase business effectiveness by easily connecting people, and become self-sufficient with PPM processes and solutions. In short, Sensei helps organizations achieve Instant Productivity.