DMC’s client, a successful full-service marketing firm, was unable to get up-to-date windows into their current financial situation. The existing financial reporting process looked at potential revenue from their CRM system, hourly reports from their time entry software, and billed time that was over a month old. However, because they were in disparate systems it was difficult and time consuming to combine the data for a holistic view of their current financial performance against their yearly goals.
DMC utilized Microsoft Power BI to import data from the three systems and combine them such that the client was able to see projected revenue for the year against their goal. Rather than relying on data that was a month old, Power BI gives the customer an up-to-date view of financials with data refreshed on an hourly basis.
DMC began by collaborating with the client to learn about the current financial reporting process to fully understand both the reporting needs and the steps needed to extract, transform, and load data into the spreadsheets being used to do reporting. Once this was completed, DMC was able to begin using Power BI’s query tools to mimic the process and automate the manual steps.
Once the data was loaded in the correct format to Power BI, DMC began the process of connecting data using the data modeling functionality within Power BI. This allows any future dashboards to utilize a combined dataset when visualizing data. To do so, data from the CRM system, Function Point, and QuickBooks was modified to allow there to be a unified measure between the three systems, as well as grouped on date to plot the data against a specified timeframe.
DMC, with input from the client, built out the dashboards requested. This included dashboards which outlined cashflow and revenue using QuickBooks data, open opportunities from Zoho CRM, and billable hours reports from FunctionPoint data. Each of these dashboards provided insight which was previously difficult to obtain and keep updated. However, the most complex and interesting dashboard combined the billed revenue from QuickBooks, opportunity value from Zoho CRM, and the unbilled time from Function Point to get a full year revenue projection. This dashboard allows management to see how they are tracking towards their yearly revenue goals and make decisions on how to deploy strategic resources.
Power BI’s true potential is unlocked by putting the data model created into the hands of the analysts and employees who work with the data each day. DMC conducted a training session to outline Power BI’s capabilities and to review the existing data. This allows the client's employees to build new dashboards and have them updated and shared.
The result was a system which not only displays current data in a fashion that was previously not possible, but one which can be built upon in the future to meet changing needs.