Even the best teachers in school need support sometimes. Our client provides them with advice, courses, and educational materials.
Advisory businesses are driven by a few KPIs that revolve around projects and time spent by advisors.
To ensure the business keeps running efficiently, our client asked us to create a set of reports that help them understand the profitability of projects and time efficiency of advisors.
As always we started the project by determining the best data architecture to get the job done.
Our client runs almost completely on Microsoft Dynamics 365 for Sales, with just a few manual input tables in Excel stored on a SharePoint folder.
Getting data out of these cloud systems is easy, thanks to the connectivity options of Power BI.
Usually we would advise clients to put a data warehouse layer in between the source systems and the reporting layer. However, together with the client we decided to go directly to Power BI in this project.
The reason is that the number of reports and data size are limited. There was no need to integrate data from multiple sources. Also, the client expects to be upgrading or replacing Dynamics 365 a year from now, so building a data platform on top of it would be a waste of money.
After defining the KPIs necessary for reporting and designing the architecture of the solution, we determined the data we needed and found it in the source systems.
In Power BI, we created an OData feed connection to Dynamics 365 with just the necessary tables and columns. Usually, OData is not that fast (that’s why usually, we build a data warehouse in between), but for a small dataset like this it works perfectly fine.
From the connections to the OData feed we created the data structure designed for reporting (with transactional tables and lookup tables) and the calculations or KPIs on top of the data.
From there, all we had to do was visualise the data in such a way our users could quickly spot exceptions in their billable hours, revenue by project, employee, specialisation, and much more.