Using Project Cache and Power BI for Enterprise Reporting
Did you know that the Project Cache is a feature unique to BrightWork, not found in standard SharePoint solutions?
The Project Cache pulls information in from two specially designed lists, the Project Statement and the Project Metrics, from any sites containing these lists across the entire site collection and Projects Tracker list.
It is primarily used to speed up reporting load times as the BrightWork Reporter feature only has to query one list instead of an entire site collection.
As all the key information about projects is now consolidated into one list, this makes it an ideal data source to pull into Power BI dashboards for enterprise reporting.
Set up Enterprise Reports
For ease of use, I prefer to use the Power BI desktop application when manipulating the data. You can download the free application from Microsoft from here.
- Launch Power BI Desktop
- Click on the Home Ribbon Get Data More…
- Search from “SharePoint list”. Then click “Connect”. BrightWork online customers will also use this option.
- Enter the name of the site collection e.g. <servername>/sites/mysitecollection
- If prompted, enter in your sign in credentials for your SharePoint site.
- Once connected, click on the “Project Cache” list from the Navigation. Then click on Edit
- Check that the columns you wish to report on are the right data type. Note that the names reported on are the internal names and not the common names.I have created a simple table download that shows each of the columns, their internal and common names, and the data type. % Complete (bwPClPercentComplete) is an example that may need to be changed from a number to percentage data type.
- Click “Close & Apply” from the Home ribbon to commit changes. This won’t change the column data types in BrightWork, just how Power BI recognizes the data.
- On the right-hand side of the Power BI, you will see all the columns that can be pulled in and reported on. Configure, query, and create real-time dashboards with other enterprise solutions. The following is an example of enterprise reports using SharePoint and BrightWork.
This is a very loose example, as there is so much power (no pun intended!) to what you can add across your enterprise software solutions. Combine project information from SharePoint through BrightWork, accounts from SAP, account from QuickBooks, and so much more. And with our upcoming Work Cache in the pipeline for release, you will soon be able to integrate project work efforts into Power BI.