Power BI and Azure services in sales, cost, and production reporting
One of our clients, a company specializing in the production of animal products needed a set of reports to analyze their organizational data – from manufacturing through sales to accounting.
To process this large amount of initial data a data warehouse needed to be created. Thanks to the tools like Azure SQL, Databricks, Data Lake Storage, and Azure Data Factory, it was possible to configure an optimal connection to the data sources (which was mainly the internal management system) and create pipelines for individual reports. We scheduled daily data refreshes and automated the process of sending notifications when an error occurred.
We created 6 interactive reports which were compiled into a single application through Power BI App. Linking the related dashboards and reports made them easier to find and install by the employees, both in the Power BI service and on mobile devices. Additionally, on every data refresh, changes were automatically applied, and the most current data was visible to users.
Each of the created reports served as a reliable source of information in the process of making key operational and tactical decisions in many different departments – inventory and production control, quality assurance, sales, marketing, etc. The implemented solution included reports such as
- Sales Report – providing a holistic view on sales by showing profit trends over time, year over year metrics comparisons, and net sales of each product
- Price - Quantity Report - showing which sector of the production line is best suited for specific product groups based on the generated production costs
- Cost Report – comparing the cost of the personnel, depreciation, transport, media, etc. in each department over time
Data integration and reporting with Power BI and Azure services
The implementation of the Power BI reporting network together with Azure services allows for data unification and integration providing a clear view of the business. A great example of that is a project that we did for a well-recognized furniture manufacturer which required data integration from different stores and different management, financial, and accounting systems.
The vast amount of data that was generated and stored in the systems required building a data warehouse using Azure services. Data normalization and standardization achieved by consolidating databases using Azure SQL ensured the data consistency and validity. Data Lake Storage and Azure Data Factory helped to create and configure pipelines that automated the entire process of daily or weekly data refreshes and the process of notifying when an error occurred. It is important to note that this process was fully automatic and allowed for the elimination of human error in the data acquisition and cleaning process.
Each of the reports contained interactive tables and visualizations, showing detailed business results on a monthly and annual basis from the perspective of the entire company, as well as individual branches, warehouses, or single products. By using advanced DAX calculations, it was possible to create customized KPI metrics reflecting the company’s internal policies.
The analysis of the non-production costs (e.g., depreciation, H&S costs, logistics costs) allowed for a better allocation of resources and cost optimization. Tracking transportation expenses per commodity type, by destination, and by the carrier, made it possible to determine which logistic operator should be hired to transport a particular shipment.
The report comparing the company's revenue and costs broken down into individual components allowed the shareholders to set the right course for the optimization actions and in the long run improve business results.
The reports used exchange rate tables provided by the National Bank of Poland, which let all costs and revenues be expressed in one local currency, even though invoicing took place in different countries.
The entire report is now available in the Power BI service which makes it accessible both from the computer and any mobile device. The automation achieved thanks to the Azure services ensures reliability and smoothness of use, which directly translates into the repeatability of the analysis.
You can contact us
by phone +48 518 518 944,
by email email@example.com
or use our contact form: