AzureBootcamp2022: More efficient Hydropower Plants with Azure@Axpo by Mathias Pawlovsky and Meinrad Weiss

This session is one of the sessions of Azure Bootcamp Switzerland 2022.
www.azurebootcamp.ch

Fewer routine operations, less administration, fewer errors. These are some of the benefits of the digital hydropower plant that Axpo is currently piloting at the Sarganserland power plants. Plant operations and maintenance are becoming more efficient thanks to digital technologies. Sensor data and status reports from several Swiss hydropower plants can be used to better determine the condition of the plants using machine learning models. Axpo also uses this data to further develop intelligent maintenance, asset management and power plant deployment.In this session, we will present the technical solution, its benefits and its architecture. Involved technologies are IoT Egde, Azure Stream Analytics, Azure SQL Server Serverless, Python, Flask and Azure Web Apps.
🙂 MEINRAD WEISS ⚡️ Senior Cloud Solution Architect @ Microsoft
🙂 MATHIAS PAWLOWSKY ⚡️ Head Data Science @ Axpo Group

Check out Meinrad at: https://www.linkedin.com/in/meinrad-weiss-b6861a5/
Check out Mathias at: https://www.linkedin.com/in/mathias-pawlowsky/

Transcript

1. MATHIAS PAWLOWSKY ⚡️ Head Data Science @ Axpo Power MEINRAD WEISS ⚡️ Senior Cloud Solution Architect @ Microsoft 13:40
3. Agenda • The big goal and solution overview • Data ingestion “Bridge the gap” • Process and store data efficient • The value of ML and third-party involvement • Outlook
4. 4 808 CHF million Revenue 2019/2020 826 CHF million EBIT 2019/2020 Active in 31 Countries 5 350 Collaborators 16,6 GW of renewables sold 67 557 millions kWh delivered + 100 years of expertise Leading marketer of renewable energies in Europe Switzerland's largest producer of renewable energy & international leader in energy trading
5. Hydro 4.0 Take the operation of hydropower plants to the next level • Axpo Hydro possesses great know-how in operating hydropower plants • Hydro industry lags behind in digitalization maturity • Combining Axpo’s know-how with recent technological advances would allow to improve the operation of hydropower plants significantly • Three main areas • Workforce: A tablet in every tool case ;) • Robotics: Automate (difficult or repetitive) data collection • Analytics: Get data-driven insights to make decisions straightforward
6. Why Data Science & Hydropower? • Why analyze data? Generate insights to answer questions • When do you have questions? When times are changing • Why is there change in the hydropower industry? • Energy transition • Aging fleet
7. Source of the Architecture (Axpo Hydro) IoT Edge IoT Hub Stream Analytics Azure SQL Database Azure Machine Learning /Azure Functions Web Application Third Party
8. Live Demo
9. Agenda • The big goal and solution overview • Data ingestion “Bridge the gap” • Process and store data efficient • The value of ML and third-party involvement • Outlook 13:55
10. Source of the Architecture (Axpo Hydro) Stream Analytics Azure SQL Database Azure Machine Learning /Azure Functions Web Application Reference Data Third Party IoT Edge IoT Hub
11. Data architecture – from power plants to users
12. Data architecture – from power plants to users
13. Cost optimization tip • With IoT Hub you pay for messages from edge to cloud • However, there is an Azure Blob Storage on IoT Edge module • Syncs blobs to the cloud for free (incoming traffic) OPC Publisher Module
14. Agenda • The big goal and solution overview • Data ingestion “Bridge the gap” • Process and store data efficient • The value of ML and third-party involvement • Outlook 14:00
15. Source of the Architecture (Axpo Hydro) IoT Edge IoT Hub Azure Machine Learning /Azure Functions Web Application Third Party Stream Analytics Azure SQL Database Reference Data
16. Facts & Figures 6 Power Plants 10 Locations ~ 70’000 Measurement Points ~ 40’000’000 Events per Day
17. Facts & Figures 6 Power Plants 10 Locations ~ 70’000 Measurement Points ~ 40’000’000 Events per Day 6 10 - 2 4 6 8 10 12 Power Plants Locations 6 10 70'000 - 10'000 20'000 30'000 40'000 50'000 60'000 70'000 80'000 Power Plants Locations Measurement Points 6 10 70'000 40'000'000 - 5'000'000 10'000'000 15'000'000 20'000'000 25'000'000 30'000'000 35'000'000 40'000'000 45'000'000 Power Plants Locations Measurement Points Events per day
18. Azure Stream Analytics • Azure Stream Analytics is a real-time analytics and complex event-processing engine that is designed to analyze and process high volumes of fast streaming data from multiple sources simultaneously. Azure Stream Analytics | Microsoft Azure
19. Main Azure SQL Database Structures Ingest Core Mart Stream Analytics
20. Signal Registration Process { "EventTimestamp": "2021-12-08T14:01:14.5094082Z", "SignalLabel": "Temperature", "Value": "12.34", "Text": "", "EventProcessedUtcTime": "2021-12-08T14:01:14.5771421Z", "PartitionId": 1, "EventEnqueuedUtcTime": "2021-12-08T14:01:15.0040000Z", "IoTHub": { "MessageId": null, "CorrelationId": null, "ConnectionDeviceId": "mewTsSmartStartHubDevice001", "ConnectionDeviceGenerationId": "637740536776612383", "EnqueuedTime": "2021-12-08T14:01:15.0050000Z" } } [Ingest].[MeasurementWithSignalName] [Ingest].[Measurement]
21. Storage Details – Basic Usage IoT data arrives in batches • “ordered by” Ts The typical query for Time Series data is: • select [Ts], [SignalId], [MeasurementValue] from [Core].[Measurement] where [SignalId] = 15 AND [Ts] between '2021-12-08 16:30:05' and '2021-12-08 16:30:10.876' order by SignalId, TS ALTER TABLE [Core].[Measurement] ADD CONSTRAINT [PK_Core_Measurement] PRIMARY KEY CLUSTERED ([SignalId] ASC, [Ts] DESC, [Ts_Day] DESC ) The index will be extremely fragmented after a few inserts Today -2 Today -1 Today New data [Core].[Measurement] Fragmented Not fragmented Today -2 Today -1 Today New data [Core].[Measurement] EXEC [Core].[RebuildFragmentedIndexes]
22. Facts & Figures - 2 6 Power Plants 10 Locations ~ 70’000 Measurement Points ~ 40’000’000 Events per Day 6 10 70'000 40'000'000 - 5'000'000 10'000'000 15'000'000 20'000'000 25'000'000 30'000'000 35'000'000 40'000'000 45'000'000 Power Plants Locations Measurement Points Events per day 40 (max)
23. Main features • Optimal ingest and query performance • Minimal impact if database grows • Use PaaS services and functionality • Auto registration of new Devices/Signals Stream Analytics Azure SQL Database Reference Data IoT Hub Avoid duplicate delivery of events (Keep them for quality control) Row level security Show data only allowed rows, company internal but also for third party users Replace signal name with internal SignalId (Tuned for efficiency) Deal with mal formed events (Keep them for quality control)
24. Agenda • The big goal and solution overview • Data ingestion “Bridge the gap” • Process and store data efficient • The value of ML and third-party involvement • Outlook 14:12
25. Source of the Architecture (Axpo Hydro) IoT Edge IoT Hub Stream Analytics Azure SQL Database Azure Machine Learning /Azure Functions Web Application Reference Data Third Party
26. Competitive advantage Axpo in developing data analysis modules Experience in maintenance and operation of hydropower plants Sovereignty over our data Know-how in machine learning, anomaly detection, etc. in Hydro 4.0 - Team Integration of third-party applications
27. Collaboration across companies Existing products don’t fulfill Axpo’s requirements
28. Collaboration across companies Unified view of the power plant • Advantages: • Grant access to data selectively • Easy to onboard new «insight suppliers» • One UI for Axpo’s employees
29. Collaboration across companies Easily integrate IoT Sensors • Advantage: • Common framework instead of isolated solutions • One UI for Axpo’s employees
30. Agenda • The big goal and solution overview • Data ingestion “Bridge the gap” • Process and store data efficient • The value of ML and third-party involvement • Outlook 14:20
31. Onwards and upwards • Multi-tenancy: Extend offer to external customers • Seamless integration into customer processes: Expand web app
32. Azure SQL Database Family Azure SQL Server Hyperscale Azure Synapse Analytics SQL Pool Azure SQL DB DB [Cache] Azure SQL Serverless 4 (16) TB*) 100 TB *) > 100 TB (no defined limit) *) “limitless” with link to data lake files
33. Azure IoT SQL Smart Start Workshop • Access to the solution template • ½ day workshop (Microsoft Office or remote) • Afternoon 7th June 2022 (registration open till 29th May) • https://forms.office.com/r/evvDB1vynE
35. Agenda • The big goal and solution overview • Data ingestion “Bridge the gap” • Process and store data efficient • The value of ML and third-party involvement • Outlook 14:25