ProjectManager PowerBI Data Model

To begin using PowerBI, please start with our article on how to integrate with PowerBI.

Once you've set up a connection between PowerBI and ProjectManager, the next step is to understand how our data model works. This article explains what types of data are available, how they are structured into tables and the relationships between them.

ProjectManager Tables

Within your ProjectManager workspace are several different data sources. When you connect your workspace to PowerBI, these are represented as tables.

Here's a list of the key tables available for reporting:

• Projects - This table represents the list of projects within your workspace. A project is a collection of tasks and tasks can be assigned to resources.  
  • All projects have a priority level, found on the ProjectPriority table.
  • All projects have a status, found on the ProjectStatus table.
  • A project can have any number of custom fields, these are stored in the ProjectFieldValues table. The name of each custom field is stored in the ProjectField table.
  • Most project fields can be found in this table including: name, description, dates, codes, rates, budget, progress, status updates and more.

• Tasks - Individual work items that make up the effort for a project. Each task can be assigned to one or more resources.  
  • A task can have one or more tags.  The list of tags for a task is found in the TaskTag table.  The name for each tag is found in the Tag table.
  • A task can have any number of custom columns, stored in the TaskFieldValues table. The name of each custom column is stored in the TaskField table.
  • A task can belong to one custom board swimlane.  This swimlane is stored in the TaskListItem table, and the name of the swimlane is found in the TaskListGroup table.  You can identify what project a swimlane belongs to using the TaskList table.
• Resources - A resource is something that can be assigned to work on tasks for a project. The most common type of resource is a person; other types of resources can be equipment such as tools or locations.
• Timesheet - When a resource is a person, it may be useful to ask the person to keep track of the time they spent on a task so that the actual work can be compared to the estimate prepared in advance. A timesheet keeps track of the time spent by a resource on a task on a specific day.

If you see other tables listed in your PowerBI, these tables may be available for use but are not fully documented.  We recommend only using tables defined in this documentation.

These tables have the following key relationships:

• Task.ProjectId == Project.Id
• TimeSheet.ResourceId == Resource.Id
• TimeSheet.TaskId == Task.Id

With these relationships, it is possible to load data into PowerBI and run reports on projects, tasks, resources and timesheets. A common use case is “How much time has my company spent on a project?” 

You can determine this by connecting TimeSheet.TaskId to Task.Id, then connecting Task.ProjectId to Project.Id. When you've connected these tables, you can sum up the total number of hours on the timesheet and group by Project.Id to calculate the total hours spent per project.

Troubleshooting

When you connect PowerBI to ProjectManager, PowerBI will attempt to determine the correct relationships between tables.  However, PowerBI will sometimes attempt to create extraneous connections that can cause data to load incorrectly.

These are the types of errors that can occur if PowerBI creates incorrect relationships between tables:

• “Column X in table Y contains a duplicate value”
• “Load was cancelled by an error in loading a previous table”
• “There are ambiguous paths between X and Y”

To fix this, check the instructions in “All Data Relationships” below and ensure that only valid connections exist within your data.

All Data Relationships

To check the list of data relationships in PowerBI, follow these steps:

• Launch PowerBI and connect to your workspace in ProjectManager.
• Once you have successfully loaded data, select the “Modeling” menu in the ribbon bar.
• Click the ribbon bar button labeled “Manage Relationships”.

In the Manage Relationships screen, check each of the relationships.  If PowerBI has created a relationship that does not appear in the official list of supported relationships, you may receive data loading errors.

Additionally, some tables have multiple ways to join data.  PowerBI can cause errors if it detects multiple pathways through your data, so please be careful not to create extraneous relationships.

This is the list of official relationships between tables:

• Project.ProjectPriorityId  (* <-- 1)  ProjectPriority.Id
• Project.ProjectStatusId (* <-- 1)  ProjectStatus.Id
• Task.ProjectId (* <-- 1)  Project.Id
• TaskFieldValue.TaskFieldId (* <-- 1) TaskField.Id
• TaskFieldValue.TaskId (* <-- 1) Task.Id
• TaskListGroup.TaskListId (* <-- 1) TaskList.TaskListId
• TaskListItem.TaskId (* <-- 1) Task.Id
• TaskListItem.TaskListGroupId (* <-- 1) TaskListGroup.TaskListGroupId
• TaskTag.TagId (* <-- 1) Tag.Id
• TaskTag.TaskId (* <-- 1) Task.Id
• TimeSheet.ResourceId (* <-- 1)  Resource.Id
• TimeSheet.TaskId (* <-- 1)  Task.Id

If you encounter problems with your PowerBI reports, please check your relationships - you may want to replace the ones detected by PowerBI with the ones described above.  Keep in mind that ProjectManager engineers are always working to improve our PowerBI integration and the list of relationships may change over time.

Please email support@projectmanager.com if you need further assistance.