The corporate standard for reporting was changed to Power BI and because of that, several reports which were build up in script/ Excel files, needed to be migrated to Power BI.
Within the various reporting tools also some data preparation was performed. Data preparation was done on data available from a database with as result temporary values and values which have to be saved in the database.
Solution
Power BI is a powerful reporting tool in which data preparation is possible. However, the options are quite limited and it is relatively difficult to keep track of combined raw and calculated columns and measures. Therefore most of the data preparation has been performed on a database level. Within Power BI the strength of measures was used for aggregate values (e.g. summations/ averages/ year-to-date values).
It was chosen to keep the script for writing required values back to the database to keep the data connection between the database and Power BI read-only.
Using standard visualizations and sliders several reports have been migrated to Power BI. The data within the reports is kept up-to-date using the data gateway. The sliders within the reports can be used to change the time interval and interactive filtering of the reports can help looking at data concerning one specific product or event.
Project results
Daily, weekly and monthly production reports have been successfully migrated to the corporate standard.
All data preparations have been implemented to database level (column calculations) and Power BI level (measures).
Reports are available for the stakeholders, and can be viewed from variable devices.
Additionally, the data in the reports is refreshed without any manual action needed.