Creating Dashboards To Reveal The Real Info

Digging To The Bottom Of The Mystery

Before getting to any dashboards, the nitty gritty work of data engineering comes first. This company’s Dynamics ERP system was running on an on-premises server. Their data was spread across numerous different systems and data warehouses. SPK (based in California) set up a data gateway to work on the project remotely.

What we found was eye-opening.

There were two sources of problems. The first was related to their on-prem ERP system. After a lot of digging, we discovered that the company’s reporting system had actually been created by a third-party consulting firm; it wasn’t part of the original, internal Dynamics software. Thus it was applying undocumented filters to the company’s own data, and users didn’t realize that it was being manipulated! For example, if a new labor class of employee was created after the reports were set up, those employees would be excluded from any reports. That’s just one frightening example of many we discovered.

The other source of the incorrect reporting, we discovered, was their own Power BI setup. They used Power BI to import data from several different sources (such as Dynamics ERP and Salesforce) into the above-mentioned data warehouses, which had their own undocumented filters on them. Thus the data was being manipulated, incorrectly and under the radar, upstream of Power BI—rather than simply being imported directly into it.

For example, their purchase order (PO) budget forecasting report failed to indicate when additional POs were issued; it only provided totals for all POs combined. Thus they couldn’t see if they were going over-budget at any point in a project and might need to request an additional PO.

To make matters even worse for the beleaguered CFO, the Power BI reports themselves weren’t even documented or configured to her needs.

The Solution

As noted above, SPK set up a data gateway from their preexisting ERP system and imported the data into Microsoft Power BI. We then analyzed their data and generated dashboards that allowed them to view their business metrics straight from their raw data—which is what they’d wanted all along. The undocumented filters (and, incidentally, their internal IT person who had installed them on Power BI) were history.

Finally, the company had the KPIs it craved, including:

• Employee billable utilization percentage
• Contractor billable utilization percentage
• Utilization percentage compared to target
• Non billable time allocation
• PO budget forecasting totals—indicating when PO budgets will be exhausted compared to resource forecasting

And of course, since this is a real-world story, it’s not quite as simple as that. Because shortly after we did all this work for the client, they migrated to an entirely new ERP system—Oracle NetSuite—and we helped to seamlessly migrate all of the Power BI dashboards to it. To accomplish all of the above, SPK provided a team including a Senior Engineer, the Director of Finance, a Senior Application Integration Engineer, an Applications Services Engineer, and a Mechanical CAD Engineer. The Senior Engineer served as project manager; the Director of Finance was the business consultant and Power BI designer; the other engineers provided the technical assistance with the migration and gateway.

On the client side, our sponsor at the company was the CFO. We also worked with one of their Senior Business Analysts, the Director of Systems and Technology, and a Senior Revenue Accountant.

The initial analysis of the data and the project design took two months. The implementation took about twice that long.

Bountiful Benefits

Once the data engineering was complete, the data was clean and immediately available in easy-to-navigate dashboards, the CFO was able to, finally, grasp some painful—yet empowering—information. For example:

• They had been manually calculating billable hours divided by a flat 173 hours per month. But the new logic developed by SPK factored in real-world details such as the actual number of work-days each month, along with holidays and changes in employment, such as mid-month hire/fire dates or approved leaves. We also provided them with the same information for contractors, which had previously been unavailable.

Previously, the company had been unable to recognize which employees were being under-utilized and whether they needed to be assigned to additional projects. With the new dashboards in place, the company saw that:

• About 20 percent of employees were working—and getting paid—for previously-unrecognized “beach time.”
• Around eight percent should be considered for termination due to underperformance.

Those were lagging indicators. On the leading-indicators side, they could (finally) see that, with their current resource forecasting, they would exceed their PO budget—with about 22 percent of projects on track to be over-delivered.

Needless to say, armed with this information, the CFO was able to take immediate action.