Automated project status sync between portfolio management and Power BI dashboards

PLM Aras Innovator
, , , , , , , ,
1

Sharing our implementation of automated project status reporting between Aras portfolio management and Power BI. We manage 40+ active projects across engineering, manufacturing, and quality. Previously project managers updated status manually in both Aras and our executive dashboards, leading to inconsistencies and reporting delays.

We built an API-driven integration that extracts project data from Aras every 6 hours and pushes it to Power BI datasets via REST API. The automation eliminated duplicate data entry and gave executives real-time visibility into portfolio health.

The solution handles project milestones, resource allocation, budget status, and risk indicators. Here’s our API extraction logic:

const projects = await fetch('/Server/odata/Project?$expand=milestones,resources&$filter=state eq "Active"');
const transformed = projects.map(p => ({
  id: p.id, name: p.name, status: p.status,
  completion: p.completion_percent
}));

Power BI dashboards now refresh automatically with current portfolio data. Project managers focus on updating Aras only, and reporting happens automatically.

2

What authentication method are you using for the Power BI REST API? We’re looking at similar integration but struggling with service principal setup.

3

For Power BI API authentication, you’ll need an Azure AD service principal with appropriate permissions. Register an app in Azure AD, grant it Power BI API permissions, then use client credentials flow to get access tokens. The service principal needs write access to your Power BI workspace and datasets. Make sure to rotate client secrets regularly and store them securely in your integration environment.

4

Our automated project status sync has been running for four months now, and I wanted to share the complete implementation details and lessons learned.

API-Driven Data Extraction:

We built a Node.js integration service that queries Aras portfolio management data via OData endpoints. The service runs on a scheduled task every 6 hours (02:00, 08:00, 14:00, 20:00 UTC) to align with executive reporting needs.

Key data extraction patterns:

// Pseudocode - Portfolio data extraction:
1. Authenticate to Aras using OAuth service account
2. Query active projects with relationships:
   GET /Server/odata/Project?$expand=milestones,resources,risks&$filter=state eq 'Active'
3. For each project, extract:
   - Core attributes (id, name, status, owner, dates)
   - Milestone completion (count, percent complete, overdue count)
   - Resource allocation (planned vs actual hours, utilization %)
   - Budget metrics (planned, actual, variance, forecast)
   - Risk indicators (count by severity, mitigation status)
4. Transform to Power BI schema and cache locally
5. Push to Power BI via REST API
// See: Aras OData Query Reference, Power BI REST API Docs

The extraction handles 40+ projects with an average of 12 milestones and 8 resources each. Total API response time is 90-120 seconds for full dataset with relationships.

Power BI REST Integration:

Power BI integration uses service principal authentication and the Push Datasets API. We chose push datasets over scheduled refresh because:

  • Complete control over refresh timing aligned with Aras updates
  • No need for gateway infrastructure
  • Ability to transform data before pushing
  • Real-time error handling and retry logic

Authentication setup:

  1. Created Azure AD app registration with Power BI API permissions
  2. Granted service principal workspace admin rights
  3. Configured client credentials flow for token acquisition
  4. Implemented token caching to minimize auth overhead

Data push workflow:

  1. Create/update dataset schema in Power BI workspace
  2. Clear existing rows from tables (full refresh approach)
  3. Push transformed data in batches of 1000 rows
  4. Validate push success via API response
  5. Update metadata table with last refresh timestamp

The push operation takes 15-20 seconds for our complete dataset. Power BI dashboards reflect new data immediately after push completes.

Automated Dashboard Refresh:

Dashboards consume data from push datasets with no additional refresh configuration needed. When the integration service pushes new data, all dashboard visuals update automatically. Executives see current project status without manual refresh actions.

Key dashboard KPIs derived from Aras data:

  • Portfolio health score (weighted average of project status)
  • On-time milestone completion rate
  • Resource utilization across all projects
  • Budget variance trending
  • High-risk project count and mitigation status

We added a “Last Updated” timestamp visual to show data freshness. Users can verify they’re viewing current information from the most recent Aras sync.

Implementation Challenges and Solutions:

Challenge 1: Relationship data explosion

Aras projects with full milestone and resource expansions create large payloads (2-3 MB for 40 projects). We optimized by:

  • Using $select to retrieve only needed fields
  • Aggregating relationship data server-side rather than client-side
  • Implementing pagination for projects with 50+ milestones

Challenge 2: Schema evolution

As portfolio management needs evolved, we added custom fields to Aras projects. The integration needed to handle schema changes gracefully:

  • Implemented dynamic field mapping configuration
  • Added schema validation before Power BI push
  • Deployed backward-compatible transformations

Challenge 3: Error handling and monitoring

API failures (Aras downtime, Power BI throttling, network issues) required robust error handling:

  • Exponential backoff retry logic for transient failures
  • Email notifications for persistent errors
  • Detailed logging of all API interactions
  • Health check endpoint for monitoring system status

Business Impact:

After four months of operation:

  • Eliminated 8-10 hours per week of manual status reporting
  • Reduced data inconsistencies between Aras and executive reports from ~15% to <1%
  • Improved executive decision-making with current portfolio visibility
  • Enabled project managers to focus on project execution rather than status reporting

The automated sync handles 100% of routine portfolio reporting. Project managers update Aras only, and executives get accurate dashboards automatically. The 6-hour refresh cadence proves adequate - we’ve had zero requests for more frequent updates.

Key success factors: robust error handling, comprehensive logging, clear data transformation logic, and alignment with actual reporting cadence needs. The API-driven approach scales easily - we’re planning to extend it to program-level portfolio reporting for 100+ projects next quarter.

5

We evaluated streaming datasets but found 6-hour refresh adequate for our executive reporting needs. Project status doesn’t change minute-by-minute, and more frequent updates would increase API load without meaningful business value. For critical projects, managers can trigger manual refresh through our integration portal. The 6-hour cadence balances data freshness with system performance - we’re pulling data for 40+ projects with full relationship expansions, which takes 3-4 minutes per sync cycle.

6

How frequently do you refresh the Power BI data? Every 6 hours seems conservative for real-time dashboards. Have you considered streaming datasets for more immediate updates?

7

How do you handle data transformation between Aras and Power BI? Are you using Power BI dataflows or doing transformation in your integration layer?