Roderick Koster is a consultant at Solid Professionals and he automated and optimized several processes with data visualization in Power BI. One of these processes is further explained in this article.
Background of the project
This project took place within the COORF Global Services domain of ING. The aim was to give local CFO's more insight into their financial figures (Balance Sheet and P&L) and to give them the opportunity to carry out further analyses themselves, in addition to a number of standardised overviews and explanations such as insight into the Manual Adjustments and textual explanations of month-on-month movements. Power BI was chosen as a tool to realize the necessary data transformations and data visualizations. This project involved a pilot (test environment, no production).
1. Understanding the current situation
The first step was to gain insight into the current situation by having conversations with the Process Owner. In the current situation, changes in the month-to-month figures are explained at account level (textual explanation) if they are larger than a certain threshold. This overview is drawn up per location/region in MS Excel and then shared with the stakeholders.
2. Mapping requirements
The second step was to map out the requirements, which were drawn up in consultation with the Process Owner;
A report for all locations, where each location only has insight into its own data (by applying Row-Level-Security).
- Insight into the Balance Sheet and P&L.
- Insight into the month-on-month and quarter-on-quarter changes, including textual explanations of these changes.
- Insight in Manual Adjustments (manual bookings).
- Further analysis possibilities for the user (trend analysis, drillthroughs, slice & dice).
3. Building reports
After obtaining the requirements, it was possible to start building the reporting in Power BI. The first step was to build the data model, which included linking Power BI with different data sources, performing data transformations, defining relationships between data sources and defining Data Analysis Expressions (DAX). Next, the visuals could be added and the rest of the report could be designed.
4. Presentation and feedback
When the first version of the report was completed, it was presented to the Process Owner for testing and feedback. Based on this session the reporting was further improved and finally finalized.
The result was a Power BI report that met all the requirements. This made the Power BI pilot successful. In a follow-up phase this pilot can be brought to production.
Want to know more about Power BI?
Keep an eye on our website or contact Hans van Doesburg.