Management of internal project related costs is quickly becoming a key strategic activity for organisations looking to move to the next level of cost management and transparency.
While this is a great idea, it can become extremely challenging to implement and track project related costs to allow appropriate disbursement of costs to the owning business units.
The Microsoft Project Online platform provides a market leading project and portfolio management solution. By using this platform to track the associated project costs for materials and resources (via timesheets), an accurate view of project related costs can be achieved. By tracking the organisational cost centres and providing a charge out / charge back approach to project costs, these costs can be efficiently tracked back to the business units who have funded the projects. The following graphic outlines the system relationships used for Project Financial Governance:
Build a Power BI report
Sensei’s Reporting/Integration Hub provides a SQL Azure database, with data synchronised from Project Online in near real time. This copy of the Project Reporting Database provides an ideal data source for dashboards and reporting, as well as, the perfect location to stage data before it is imported into Project Online, as all integrated data is available in this location.
Sensei’s Reporting/Integration Hub provides a SQL Server database, with all data synchronised from Project Services in near real time. This copy of the project database provides an ideal data source for dashboards and reporting, as well as, the perfect location to stage data before it is copied into the project services database, as all integrated data is available in this location.
Implementing Power BI analytics consists of 2 main stages:
- Source the data needed
- Create and publish dashboards/reports
Source the data
The Sensei Reporting Hub Database is an excellent source of data for Power BI reporting. Power BI allows data to be accessed directly (Direct Query) or via Import (on refresh schedule). Given the nature of the types of reports used for financial governance, using an Import approach with a daily (or more frequent if needed) refresh rate usually provides the best trade-off between data freshness and efficiency/cost.
Accessing project related data is often best achieved using the SQL Views provided with the database. They perform all the heavy lifting to get an entity’s standard and custom fields available. Most of the data needed is available from Views (entities and lookups) but occasionally you may need to get data from project database tables directly.
I have found that one of the easiest ways to build up your data query is to use SQL Server Management Studio and use the Design View approach to get all the data you need in a single view, which may then be filtered by the report. To ensure your view is created in the “Customer” schema (which is the only place you can create them), create the view first using a Create script and use the Customer schema (e.g. CREATE VIEW [Customer].[ChargebackData] AS … GO), then open the view in the View Designer.
Using the SQL View Designer screen allows the SQL Views needed to be dragged onto the screen (drag a view from the list on the left to the diagram pane) and the fields required may be visually selected, see the following diagram:
The fields to use in order to join the views are usually obvious. For example, when joining MSP_TimesheetLine_UserView with MSP_EpmResource_UserView you simply click on ResourceUID field on the associated view and drag it to ResourceUID field on Resource view and drop. This will form the correct join:
In this case we only want matches from both tables, so no further checkboxes should be selected. This creates the SQL join statement as:
INNER JOIN dbo.MSP_EpmResource_UserView ON dbo.MSP_TimesheetLine_UserView.ResourceUID = dbo.MSP_EpmResource_UserView.ResourceUID
Sometimes you may need to join onto the same view to get additional information from an alternate relationship (as in the above view). In this case, drag the view into the designer again and drag and drop the required join field(s). You can also create this second view join using the SQL statement (which is sometime easier):
INNER JOIN dbo.MSP_EpmResource_UserView AS [Resource2] ON dbo.MSP_EpmResource_UserView.ResourceTimesheetManagerUID = [Resource2].ResourceUID
This is very common when you need to get the Timesheet Manager name or Status Manager name.
In this case, we are getting timesheet related costs for resources, with cost centre information (that has already been added in custom fields), to allow the finance system to chargeback the related costs to the appropriate departments. The data for this view is retrieved using the following SQL Query:
dbo.MSP_EpmResource_UserView.ResourceCostCenter AS [From Cost Centre], Customer.GetDigits(ISNULL(dbo.MSP_EpmTask_UserView.[Task Cost Centre], dbo.MSP_EpmProject_UserView.[Cost Centre])) AS [To Cost Centre], dbo.MSP_TimesheetLine_UserView.TimesheetLineStatus AS [Approval Status], ISNULL(dbo.MSP_EpmTask_UserView.[Task Oracle ID], dbo.MSP_EpmProject_UserView.[Oracle ID]) AS [Oracle ID], dbo.MSP_EpmResource_UserView.[Suncorp Resource ID] AS [Resource ID], dbo.MSP_EpmResource_UserView.ResourceName AS [Resource Name], dbo.MSP_EpmResource_UserView.Role AS [Resource Role], dbo.MSP_EpmProject_UserView.ProjectIdentifier AS [Project ID], dbo.MSP_EpmProject_UserView.ProjectName AS [Project Name], dbo.MSP_EpmTask_UserView.TaskName AS [Task Name], dbo.MSP_EpmResource_UserView.[GL Code] AS [Resource GL Code], dbo.MSP_TimesheetLine_UserView.ActualWorkBillable AS [Hours Billable], dbo.MSP_TimesheetLine_UserView.ActualWorkNonBillable AS [Hours Unbillable], dbo.MSP_EpmResource_UserView.ResourceStandardRate AS [Resource Rate], (dbo.MSP_TimesheetLine_UserView.ActualWorkBillable * dbo.MSP_EpmResource_UserView.ResourceStandardRate) AS [Total Cost], dbo.MSP_TimesheetLine_UserView.ActualOvertimeWorkBillable AS [Overtime Hours Billable], dbo.MSP_TimesheetLine_UserView.ActualOvertimeWorkNonBillable AS [Overtime Hours Unbillable], dbo.MSP_EpmResource_UserView.ResourceOvertimeRate AS [Resource Overtime Rate], (dbo.MSP_TimesheetLine_UserView.ActualOvertimeWorkBillable * dbo.MSP_EpmResource_UserView.ResourceOvertimeRate) AS [Total Overtime Cost], dbo.MSP_EpmProject_UserView.[Project Departments] AS [Project Department], dbo.MSP_EpmProject_UserView.ProjectOwnerName AS [Project Manager], Resource2.ResourceName AS [Timesheet Manager], Resource3.ResourceName AS [Status Manager], dbo.MSP_EpmProject_UserView.[Enterprise Portfolio Categorisation] AS [Reporting Category], dbo.MSP_EpmResource_UserView.[Employment Type], dbo.MSP_EpmResource_UserView.Supplier AS [Resource Supplier], dbo.MSP_EpmProject_UserView.[Project Phase], dbo.MSP_EpmResource_UserView.[Resource Departments], dbo.MSP_EpmResource_UserView.[Resource Location], dbo.MSP_EpmResource_UserView.[Location Rate], dbo.MSP_EpmResource_UserView.[TDL Function], dbo.MSP_EpmResource_UserView.OBS AS [Resource OBS], dbo.MSP_EpmResource_UserView.[Resource Sub Domain], dbo.MSP_EpmEnterpriseProjectType.EnterpriseProjectTypeName AS [Project EPT Categorisation], dbo.MSP_TimesheetLine_UserView.PeriodStartDate, dbo.MSP_TimesheetLine_UserView.PeriodEndDate
dbo.MSP_TimesheetLine_UserView INNER JOIN dbo.MSP_EpmResource_UserView ON dbo.MSP_TimesheetLine_UserView.ResourceUID = dbo.MSP_EpmResource_UserView.ResourceUID INNER JOIN dbo.MSP_EpmTask_UserView ON dbo.MSP_TimesheetLine_UserView.TaskUID = dbo.MSP_EpmTask_UserView.TaskUID INNER JOIN dbo.MSP_EpmProject_UserView ON dbo.MSP_TimesheetLine_UserView.ProjectUID = dbo.MSP_EpmProject_UserView.ProjectUID INNER JOIN dbo.MSP_EpmResource_UserView AS [Resource2] ON dbo.MSP_EpmResource_UserView.ResourceTimesheetManagerUID = Resource2.ResourceUID INNER JOIN dbo.MSP_EpmResource_UserView AS Resource3 ON dbo.MSP_EpmTask_UserView.TaskStatusManagerUID = Resource3.ResourceUID INNER JOIN dbo.MSP_EpmEnterpriseProjectType ON dbo.MSP_EpmProject_UserView.EnterpriseProjectTypeUID = dbo.MSP_EpmEnterpriseProjectType.EnterpriseProjectTypeUID
Create the report
Once you have a view of all the data you will need, you are ready to start building the Power BI report. Implementing the Power BI report can be done from Power BI online in the browser (powerbi.com), or my preferred approach, using Power BI Desktop client (download here: https://powerbi.microsoft.com/en-us/desktop/ ).
Open Power BI desktop and immediately save the new report (File | Save As) and give it a relevant name that end users will recognise. Ideally save this file to a location that is version controlled.
The first thing we need to do for the report is get the data. In the Home tab, select Get Data, then depending on where your Project database is located, connect to the database. When working with Sensei’s Reporting Hub, this database is an Azure SQL Database:
Select Connect. You are then prompted to configure the database access and Data Connectivity mode. You may use Direct Query or Import, however, as this type of data does not need to be real time, the Import approach provides the most efficient and cost-effective approach.
The data Navigator screen opens. Select the SQL View you just created above by clicking on the checkbox beside it:
Then click Load to pull all the data in the View into Power BI, all available fields are shown on the right side in “Data View” (second icon down on the left):
Next, select the Visualisations icon (top one on the left), available visualisations are then shown on the right beside the field list. Mouse over the visualisation icons until you find the “Table” icon.
Click on that and it will add a table visual to the canvas. Remember that to see the visualisation configuration, it must be selected on the canvas. Resize the table visual to take up the whole canvas area.
Next you need to choose what fields from the data will be displayed on the table and what order they are displayed in. The quickest way to get this right the first time, is to select the data fields (by clicking on the checkbox beside it), in the order that you want them to appear on the report.
By default, Power BI will SUM numeric fields for the total at the bottom. In some cases, you may want an average for the column/field instead of a SUM, just click on the little arrow beside the field name and change to Average. Once you do that Power BI may rename the field adding “Average of” to the front of it. Use the field menu to Rename it back.
If you need to re-order the fields on the report, simply drag and drop them in the Values list to the correct position.
As you add fields to the tabular report, they will also automatically be added to the Filters section, which allows each column to be filtered by existing values, much like Excel column filtering.
For a report such as the one we are implementing, there may also be a desire to filter the report at the report level (there is also page level filtering to allow filtering each page in a report separately).
For this report there is a Period Start Date field that we would like to be able to filter the report on. Firstly, note that when adding a date field to the report, Power BI will do some extra work to break the date into multiple columns to create a Date Hierarchy (to allow specific filtering):
You can display this way, removing any sub-fields you wish, such as Quarter, or you may revert to just showing the date in the format you want. Select the small drop-down arrow beside the field in the Values list and select the field name to display as date:
Also, a good point to talk about display format. If you need to change the format of the data being displayed in the column, select the data view (second icon down on left), then select the column you wish to format. In the “Modelling” tab, ensure the data type is correct and then select the format for the date time you wish to display (dd/MM/yyyy provides the most aligned basic date format):
To make the Period Start Date a report level filter, simply drag the field from the Fields list into the space provided for Report Level Filters:
You probably would want to filter that date field on a period, so simply change the filter type to Advanced Filtering, which prompts the user for the period:
Now that the report is ready in a basic state, its time to publish the report to a Power BI workspace. Workspaces provide a mechanism to group and provide security on a set of related reports and dashboards. Workspaces can also be used to separate TEST versus PRODUCTION Power BI environments.
If you don’t have a workspace to publish this report to, go to Power BI in your browser, select Workspaces from the menu on the left and then select Create App Workspace:
Give it a name (e.g. PPM TEST), then set the security depending on how you need it, typically making it private and adding members who can view it, then click Save to create the workspace:
Once you have a workspace in place, Publish the report into the workspace by returning to Power BI desktop and clicking Publish on the ribbon:
It displays a list of workspaces you have access to, select the workspace you just created and click SELECT:
The report publishes to the selected workspace and can be seen by accessing this workspace in the browser, then selecting the report, which opens the report in View mode in the browser and makes all filters available (remember, the visual must be selected to see it’s filters):
There are numerous other facilities in Power BI, but one of the most common and hard to find is how to export the report to Excel for further ad-hoc analysis. This is accomplished by selecting the ellipsis menu at the top of the table visualisation, and select Export Data:
Choose whether you want summarised or underlying data then click Export:
If you planned to do an extract of this data for import to a financial system, the SQL View created earlier can quickly and easily be wrapped by a stored procedure with parameters to allow the data to be filtered for the extract period and to manipulate data further if needed before the extract. While the extract could be prepared manually by filtering this report, then exporting it to Excel, it is not recommended to have manual human intervention in an integration process, but as a mechanism to validate the extracts, a Power BI report such as this one is a highly valuable tool.
While there are many other capabilities we could apply using Power BI, this basic report allows users to view the data for a specific period, then filter the data as needed on the columns. This report can now be used to validate data that is being extracted for import into financial systems, providing a high-quality governance option, in addition to the information value of the report.
While this is just a basic tabular report, you can see how quickly and easily these types of data visualisations can be assembled in Power BI using Project Server data. Power BI provides all the charts, KPIs and reporting capabilities to quickly and accurately create Business Intelligence artefacts that can be used to validate data extracts or provide an on-going view of the actual costs for resources across the entire project base.
Sensei’s Reporting and Integration Hub provide an easy to access, secure and highly valuable instance of the project data that can be accessed for reporting and integration purposes, saving your organisation time and costs and facilitating project related reporting and integration processes.
By Derek Miller, Microsoft Architect at Sensei
With over 30 years in the software engineering world, Derek has seen a lot of changes and have never regretted his choice of industry.