Procore + P6 Analytics in Power BI via Azure and Cosmos DB

Most contractors already have the two systems that matter for project reporting. Procore holds the project management record: RFIs, submittals, change events, commitments. Primavera P6 holds the schedule. The problem is that they live in different places, refresh at different speeds, and almost never end up in the same report without someone exporting, pasting, and reconciling by hand.

This article walks through a proof of concept we built to close that gap: Procore RFIs synced into Azure Cosmos DB by Azure Function Apps, Primavera P6 schedules loaded from XER files with Power Query, and both brought together in a Power BI report that refreshes on a schedule. It covers the architecture, each step in the order we built it, the decisions behind it, and what we would tell anyone attempting the same thing.

The problem: project data and schedule data never meet

An executive asking "which open RFIs are threatening the schedule?" is asking a question that spans two systems. The RFI and its status, assignees and ball-in-court live in Procore. The activities, dates and critical path live in P6.

Without a deliberate data path, answering that question means:

• Exporting RFIs from Procore to a spreadsheet
• Exporting the schedule from P6
• Matching them up manually, every time the question is asked

That works once. It does not survive a weekly cadence, and the report is out of date the moment it is finished. The goal of this build was a pipeline where Procore data flows into Azure automatically, the schedule becomes report-ready without manual cleanup, and Power BI refreshes both on its own.

The architecture at a glance

The design has five components, each doing one job:

Procore data takes the cloud path: API to Function App to Cosmos DB to Power BI. The schedule takes the file path: XER on a server, read by Power BI through an on-premises data gateway. Both meet in a single Power BI dataset.

Step 1: Stand up Cosmos DB as the landing zone

We started by creating an Azure Cosmos DB account. Its URI is the endpoint everything else points at. Inside Data Explorer we created a container named procore and loaded RFIs into it.

Cosmos DB is a document database, which suits Procore's API well. An RFI is not a flat row. It carries nested arrays: assignees, ball-in-court, questions with their responses, locations. A document store keeps each RFI as one JSON document, exactly as the API returns it, with no up-front decision about how to split it into tables.

Step 2: Build Function Apps to sync Procore RFIs

The sync logic lives in an Azure Function App. We built several functions inside it, each with a narrow purpose.

Sync RFIs to Cosmos (initial load). This function handles the full cycle:

1. Authenticate against the Procore instance and obtain an access token.
2. Connect to Cosmos DB and read the RFIs already stored.
3. Fetch every RFI for the Procore project.
4. Compare the two sets. New RFIs are inserted, changed RFIs are updated, and any RFI that no longer exists in Procore is deleted from Cosmos.
5. Confirm everything was written to the container.

That last part of step 4 matters. Many sync jobs only ever add and update, which means a deleted RFI lingers in the report forever. Full create, update and delete logic keeps Cosmos an honest mirror of Procore.

Sync RFI (incremental). A second function syncs new or updated RFIs. The first function handles authentication and the initial load into the container; this one keeps it current.

Timer trigger. A timer function calls the sync endpoint on a schedule. This is what makes the pipeline run without anyone touching it.

Get RFIs. A simple read endpoint that returns what is stored. It is not needed for reporting. We added it so we could open a browser and confirm the RFIs were there, which is a cheap and useful check during development. You can also verify directly in Cosmos Data Explorer, where each RFI appears as a document in the container.

Once the functions were written, the Function App was published and deployed to Azure. From that point the timer runs in the cloud, the sync endpoint fires on its cadence, and the container stays current.

Why polling instead of webhooks

The obvious alternative to a timer is a webhook: Procore calls us when something changes, and we update immediately. Procore does support webhooks. When we set one up, the resource list we saw offered 66 events, covering areas such as change orders and change events, construction financials and commitments, and core modules like projects and companies.

What it did not include were the project management resources we needed. RFIs and submittals were not in that list. So the design falls back to polling: a scheduled GET request that pulls RFIs and reconciles them.

The lesson is general. Before you design around webhooks, check that the specific resources you need actually emit events in your account and at the level (company or project) where you register the hook, because the available list can differ and change over time. If they do not, a timer-driven sync with proper delete handling is a reliable, boring answer. Boring is what you want in a reporting pipeline.

For the details of authentication, pagination and rate limits on the Procore side, see the official Procore developer documentation and our Procore API integration guide.

A decision we reversed: flattening nested data

Early on we wrote logic to flatten RFIs before storing them. The assumption was that Power BI would not be able to load nested objects and arrays such as assignees or locations, so we would need to pre-split them.

That assumption turned out to be wrong. The Power BI Cosmos DB connector handled the nested structures, so the flattening step was left out and the documents are stored as returned. Fewer transformation steps means fewer places for the data to drift from the source, and the raw shape stays available if a future report needs a field nobody planned for.

Step 3: Connect Power BI to Cosmos DB

In Power BI Desktop we used Get Data, chose Azure Cosmos DB v2, and entered the Cosmos account URI as the endpoint. The first connection asks for an access key, which you copy from the Cosmos DB account in the Azure portal. After that, the credential is remembered.

The navigator then exposes the RFI documents along with their sub-arrays: RFI assignees, ball-in-court, and questions (including the responses tied to each question). You load whichever of these the report needs as separate tables. That gives a clean shape for reporting: RFIs as the main table, with related tables for who is assigned, who currently holds the ball, and the question-and-answer history.

Step 4: Load Primavera P6 XER files with Power Query

The schedule side works differently. A P6 XER export is a single delimited text file that contains many tables at once. Lines are marked by type: a table marker (%T), a field header row (%F), and data rows (%R). Opened directly, it is not usable. (Our glossary entry on XER has more on the format.)

We loaded it through Get Data > Text/CSV, selecting "All files" so Power BI would accept the .xer extension. Then we wrote a query in the Advanced Editor that does the following:

1. Convert the raw lines to a table.
2. Separate the tag on each line from its values.
3. Create a table-name column from the %T lines and fill it down so every row knows which P6 table it belongs to.
4. Filter to the table you want, starting with TASK.
5. Promote the %F row to headers.
6. Count the headers and rows so the split lines up.
7. Split each data row into columns and normalize the result.

The output is a readable task table with proper columns that Power BI can model and visualize. The same pattern repeats for other XER tables, such as projects, by changing the filter.

If you want to go further with P6 than reading a file into a report, our P6 MCP server article covers working with XER data offline through an AI-ready tool layer.

Step 5: Build the report

With both sources modeled, the report itself was straightforward:

• RFI schedule impact by status, a chart of RFI schedule impact grouped by RFI status.
• RFI detail, a table with the selected Procore fields for each RFI.
• Schedule tasks, key attributes pulled from the P6 task table.
• Gantt view of the schedule tasks.

Because both sources sit in one dataset, the RFI view and the schedule view stop being two separate exports and become one place to look. Note what this does and does not do: it puts RFIs and schedule tasks side by side. Tying a specific RFI to a specific P6 activity, so a slicer on one filters the other, needs a shared key such as an activity ID captured on the RFI. That is a modeling step to plan for, not something either system provides out of the box.

Step 6: Publish, connect gateways and schedule refresh

A report in Power BI Desktop is only useful to one person. To make it operational:

1. Publish from Power BI Desktop to a workspace in the Power BI service.
2. In settings, open Manage connections and gateways.
3. Configure the on-premises data gateway (the standard gateway, not personal mode) for the schedule file, pointing at the file's location on the server.
4. Configure the Cosmos DB connection with the account credentials and key, as in Desktop.
5. On the dataset, set a scheduled refresh.

Each refresh pulls the latest RFIs from Cosmos, which the Function App has already synced from Procore, and re-reads the schedule file.

One practical detail makes the schedule side work without intervention: keep the file name and location stable. If the scheduler always saves the latest export as, say, current schedule in the same folder, every refresh picks up the newest schedule automatically. Change the name or move the file and the refresh points at nothing.

Microsoft's documentation on the on-premises data gateway and scheduled refresh covers the configuration options in more depth.

What this means for your team

For a contractor, the outcome is not a new tool. It is the removal of a manual task that never stays done.

• Project managers and project controls see RFIs and schedule activity in one report instead of reconciling two exports.
• Executives get a view that refreshes on its own cadence, so the numbers in the meeting match the systems as of the last refresh.
• IT and data teams get a pattern with clear boundaries: one function per dataset, one container for Procore data, one gateway connection for the schedule file.

It also sets up what comes next. Once project and schedule data share a home, you can build risk tracking, executive dashboards and, eventually, AI-driven questions over the same data. If you are weighing where that data should ultimately live, our guide to Microsoft Fabric for contractors compares the lakehouse approach.

Practical lessons from the build

Verify webhook coverage before you design around it. Procore offers webhooks, but the events available did not include the RFIs and submittals we needed. Check the resource list first.

Handle deletes, not just inserts and updates. A sync that never removes records slowly turns your report into fiction. Comparing the source set to the stored set and deleting what is gone keeps the mirror honest.

Test your assumptions about the connector. We built flattening logic that turned out to be unnecessary because the Cosmos DB v2 connector handled nested arrays. Load a sample before writing transformation code.

Add a cheap way to see the data. The simple Get RFIs endpoint and Cosmos Data Explorer made it easy to confirm the sync worked without opening Power BI.

Treat the schedule file path as a contract. A stable file name and location is what lets a file-based source refresh unattended.

Scale by adding functions, not by complicating one. The design extends to any Procore dataset: create a new function for it, have it run on the timer, and add the new data to the model.

This was a proof of concept, and it is the foundation rather than the finished building. Submittals, change events and other Procore resources can follow the same path. The schedule side can move from a file to a more direct integration. And the reporting layer can grow into the kind of portfolio-level construction reporting that replaces the monthly spreadsheet.

Where to go next

• Watch the walkthrough and see the report in the Procore + P6 + Power BI demo.
• Read the Procore API integration guide for the source-system side of the pipeline.
• See how a larger version of this idea runs on Microsoft Fabric in building construction reporting in Microsoft Fabric.
• Have Procore and P6 data that still meet in a spreadsheet? Tell us what you want to build and we will map the path.