A global energy trading company wishes to build a Data Warehouse in the Microsoft Azure Cloud for its Front Office reporting. The business requirements can be gathered from evaluating the existing reports as well as from discussions with the business users, in particular, which improvements the new system should offer. The project involves deciding on the technical architecture in Azure, designing, implementing and testing the first use case before implementing others in the new system in Azure. The existing Tableau Server and its reports are configured to query the new Data Warehouse.
Supplement
Microsoft Azure SQL Data Warehouse is chosen as the datastore because the structure of the source data is well-known and static, also the expected volume of data is large. The organisation uses Talend Realtime Big Data Platform as a strategic tool to extract the source data and push it into Azure BLOB storage. The subsequent data transformation takes place inside the SQL Data Warehouse. For source systems able to push data into the Cloud, a set of custom Azure Function Apps behind an Azure REST API is set up. These provide additional features such as data reconciliation. The transformation fills the reporting tables with the data unnormalized and pre-aggregated (e.g. hourly, daily, by market, by loadshape), to improve the report performance in Tableau. In the next stage of the project the Azure SQL DWH will be replaced by a Snowflake instance. This tool offers more flexibility in analyzing data outside of a rigid schema structure and potentially better performance for the data ingestion.
Subject description
Currently the use cases for the Front Office reporting are implemented in many separate systems, making it difficult to combine different data pools, for example loading the most current market prices for open positions in the power generation data. The new system offers a single point of truth. Setting up the new Data Warehouse also gives the business the opportunity to re-asses and prioritize their reporting requirements. Because the Data Warehouse is in the Azure cloud the setup is very quick. It is also quick and easy to scale up the computing power, meaning much less effort is required from the internal IT. Technical and business architects from Microsoft are consulted during the project to ensure that the project follows best practices, in particular for data security and governance as well as for the technical design and implementation.