A global mining company produces, processes, smelts, and refines commodities across the globe. Through marketing activities, they sell concentrates and other related by-product of zinc, lead, copper, nickel, gold, silver.
Much of their market insight has traditionally been performed manually with multiple disparate sources. Having an automated tool to easily ingest, model, and flex commodity and mine cost assumptions with on-demand outputs reduces the time to insight and improves decision making.
The mining company was evaluating the validity and usability of a third-party marketing and forecasting dataset containing thousands of mines across the globe. In addition, they wanted to combine the dataset with internal financials and production datasets to form a comprehensive internal and external view of the market, both present day and future. A unified data platform was sorely needed to bring these disparate source system datasets together and provide intelligible and actionable insights.
The objective was to create a solution to allow analysts to input assumptions of various commodity prices and mining site costs within different scenarios, recalculate outputs in near real-time, and display the results.
BlueGranite designed an Azure-based pipeline to transport data from their sources to an Azure SQL Database. Due to the infrequency of loads and updates required, the serverless database compute tier option was selected to help reduce database compute overhead costs by over 75%.
BlueGranite leveraged Azure Data Factory and their internal ETL framework to quickly start “hydrating” the data lake with the necessary data. The data was then staged inside the Azure SQL Database, where it was further modeled to facilitate not only the scenario forecasting tool, but additional ad-hoc requests as well. Logic for dozens of specific cash flow calculations were developed with the eye towards quick recalculations based upon a different set of inputs.
The diagram below shows the general data pipeline.
Power BI / Power App Reports
Once the data pipelines were designed and operational, a Power BI solution was developed to analyze the various scenario inputs/outputs. BlueGranite utilized PowerApps to facilitate users inputting different scenario parameters outside of the manual, internal, Cash Flow Sheets. Once inputted, the data is written back to the Azure SQL database, all necessary calculations processed, and the Power BI dataset was refreshed in near real-time so the results could be analyzed immediately. No waiting on the next “ETL Run” for updated results. A user can use their Power BI report not only to analyze previously constructed scenarios, but also to create new scenarios and gain insights instantly without ever leaving their Power BI report.
BlueGranite deployed the solution in production allowing for on-demand loads of internal cash flow sheets and weekly updates for the third-party market data.
The Power BI cost Curve visualization that leverages scenario outputs allows the analyst to obtain key market data such as total market supply of the chosen commodity and financial health of individual mines.
The PowerApp scenario editor allows the analysts to create and save a scenario back to the Azure SQL database to be almost immediately viewed in Power BI. BlueGranite designed both a macro scenario data flow and an individual mine scenario to facilitate flexible analyses.
As future analyses are required and continue to evolve at the company, key analysts now have the platform in place to automatically ingest future mine financials and quickly derive insights.
Building highly effective end-to-end Business Intelligence solutions are paramount to driving successful business decisions. BlueGranite can give your organization the tools needed to build these data pipelines, coupled with advanced analytics at scale. Contact us today to learn more.