The client, Columbia University Medical School, receives grants from the federal government and multiple private sector organizations, most of which are spent on various medical research projects. The staff would initially generate reports from the CUMC’s PeopleSoft ERP system.
Without a semantic layer, there were performance issues when using voluminous data sets. Additionally, developing new reports and modifying existing ones was costly and time-consuming. The reports were rigid in that they rarely offered the flexibility to focus on different levels of granularity.
As a solution, the Orion team created an SSAS tabular data model that pulled data from the Grants Data Mart and fed it Power BI. We also implemented data security measures to restrict access based on the type of user and which data was relevant to them. Lastly, we developed interactive repots using Microsoft Power BI.
With our solution, the client can view grant allocations, track spend and monitor resource utilization. They can also analyze trends and spend patterns and use forecasting models to predict future spends for effective planning purposes. The solution includes alerts based on thresholds for preventative and corrective action, along with standardized data analysis on an 2016 SQL Server.