Cortex Project History version 1.8 introduces a new feature to help you visualize your historical project data using Microsoft Power BI, Tableau, or any third-party analysis tool. We wanted to provide a simple way to visualize Cortex data without needing to involve IT specialists and integrations.
In our example, Bob regularly asks you to provide historical project data to analyze in Power BI for different types of projects he’s working on. You can easily generate the data for Bob. After building his reports the first time, you can use the OneDrive auto-sync feature to provide updates without rebuilding the reports.
This week Bob is working on a new parking structure and asks you to find similar past project data. Once you find the projects and click the Export to Excel button, you see a new option to Export flat project data. When you select this option, you also choose the element cost breakdown Bob wants to see.
After you click OK, the system downloads an Excel workbook with a sheet for the project-level data, plus a sheet for each level of the element breakdown you selected, in this case Uniformat. Cortex flattened the data and it is ready for use with Power BI.
The next step assumes you have a Microsoft OneDrive account set up. While you could import the Excel workbook into Power BI at this point, instead you save it in your OneDrive folder so you can take advantage of Power BI’s auto-sync feature. The auto-sync feature relies on the file name staying the same, so you give the file a name you will re-use every time Bob asks for new data.
When you save a file in your local OneDrive folder, it is automatically uploaded to your OneDrive folder in the cloud. The next steps walk through connecting your Power BI report to the file in your OneDrive cloud folder.
In Power BI, when you select the option to Get Data from a file, you get the option to select from either a business or personal OneDrive folder.
After selecting the appropriate OneDrive folder, you select your Excel file, click Connect, and then select the Import Excel data into Power BI option.
After importing the file the first time, you can develop your reports. In this example, Bob likes to analyze costs across projects grouped by Uniformat.
Now, let’s say a week has gone by and now Bob needs similar project data for a new office building he is working on. Once you gather the office project data for Bob, save the flat project data file in your OneDrive folder using the same name. Power BI automatically checks for updates about every hour, but if Bob is in a hurry, he can manually refresh the report with the new data in Power BI.
After the refresh, Bob’s parking structure report is now an office building report!
Once you get comfortable with Power BI, you can set up dashboards for parking garages, offices, hospitals, or any project type using multiple filter criteria to “slice and dice” the data to your heart’s content.