Standardizing Reporting Process: Bridging Cloud and On-Premises Data
Goal
To standardize Program and Project Status Reporting solution across IT.
Challenge
In an effort to standardize Program and Project Status reporting across its IT department, a large healthcare provider in California implemented Microsoft Project Online (MSPO) as its standard project management tool. However, a significant challenge arose in combining the cloud-based MSPO data with the on-premises project and program financial data to derive accurate and consistent project statuses.
Solution
To address this challenge, various options were evaluated. Ultimately, the decision was made to bring down MSPO data to the on-premises database and synchronize it back to the Azure cloud. This approach ensured that both MSPO and Power BI, utilized for reporting, operated within the Azure environment.
The implementation involved extracting MSPO data multiple times daily using OData wrapped around an SSIS script. A multidimensional model was then created, integrating project, task, risk, and issue data with the project financials to capture budget, forecast, and actuals. Standard reports were developed using Power BI, enabling stakeholders to view program and project status consistently.
Result
Through the integration of cloud-based MSPO data with on-premises project and program financial data, the healthcare provider successfully standardized Program and Project Status reporting to drive effective decision-making in complex IT environments.