By Microsoft Customer Stories on February 7, 2017
Filed under Power & Utilities
Integral Analytics switches to Azure from AWS for high performance and cost-effective data warehousing
Integral Analytics’ power play
Where would you place a new charging station for electric vehicles? How is power usage changing right now? What incentives would be most effective in helping customers change their consumption patterns? How will usage patterns change in the future? Using available data in traditional, siloed ways, most electric utilities would find these questions difficult to answer because most of them are focused on optimizing for engineering efficiencies, and they have not started analyzing other aspects of their data.
However, with additional data sources and innovative ways to query those sources, electric utilities can now gain insights that were not possible before. For example, by fusing econometric data with traditional load data, utilities can gain valuable insights on the future growth of electrical load for better planning. Integral Analytics helps traditionally engineering-focused electric utilities tap into other sources of data—like econometrics and customer-owned power assets—to learn how customers currently use power and how that usage will likely change over time.
Integral Analytics recognizes that the industry is evolving. Customers are now producers, with utilities becoming customers themselves. The industry’s legacy planning, operations, and valuation platforms are outmoded. Furthermore, networks are being reconfigured at the edge of the grid, and dynamic, actionable analytics are required to maintain the highest reliability at the lowest cost for all electric customers.
Breaking down data siloes with cloud-based solutions
To provide greater insights, it is essential that Integral Analytics applications organize and query traditionally siloed buckets of data from engineering, operations, and valuation. Integral Analytics software captures that data and breaks down the siloes. Its workloads can run up to 25 TB of data, including text data and aggregates from meters, transformers, and substations. Integral Analytics software analyzes the data and helps utilities more quickly and accurately valuate hidden costs and risks; it also helps utilities uncover insights to better understand and serve their customers.
To perform its mission successfully, Integral Analytics needed a cloud provider that supports batch processing, real-time processing, easy development, and integrated advanced-analytics services. Microsoft Azure SQL Data Warehouse and other Azure services help Integral Analytics do all of this with a highly scalable and flexible service-oriented architecture. As Kevin Kushman, COO of Integral Analytics, put it, “Azure SQL Data Warehouse is a fundamental part of our IT ecosystem. Cloud-based data solutions like Azure are going to be crucial for data-intensive companies like ours.” But not just any cloud-based data solution could meet Integral Analytics’ needs: Amazon Web Services (AWS) was not able to provide an end-to-end solution, and it required substantial effort for Integral Analytics to build the full solution. Additionally, Amazon Redshift also turned out to be more expensive for Integral Analytics.
“Azure SQL Data Warehouse is a fundamental part of our IT ecosystem. Cloud-based data solutions like Azure are going to be crucial for data-intensive companies like ours.”
— Kevin Kushman, COO, Integral Analytics
Azure SQL Data Warehouse closes the circuit for Integral Analytics
Integral Analytics built its original solutions on an on-premises Microsoft SQL Server implementation. The data that Integral Analytics originally worked with was highly structured, and the size of the data was manageable on the company’s single-instance SQL Server deployment. But as the company started offering customer solutions involving greater volume, it faced tough choices. Nightly feeds would take about three hours to import, and after three to four years of data, Integral Analytics couldn’t query the entire history in a meaningful way quickly enough on its single database instance. Integral Analytics started exploring cloud-based, managed solutions.
Integral Analytics initially went with Amazon Redshift, but it ran into limitations early on. One disappointment was that AWS offerings were all à la carte—AWS did not have the kind of unified solutions that could help with Integral Analytics’ move to the cloud. In addition, due to Integral Analytics’ deep SQL Server experience, its developers were used to working with .NET, which was not applicable on Amazon Redshift.
Cost was also an issue for Integral Analytics as it tried to use AWS. Reducing costs by pausing Amazon Redshift when not in use proved to be a hassle, as Integral Analytics had to take a snapshot of the data for backup purposes and then get rid of the entire Amazon Redshift instance. This also meant that restarting Amazon Redshift instances was effectively a restoration chore. As a result, Integral Analytics tried scaling its Amazon Redshift instances up and down according to its compute needs, but this wasn’t enough to control its month-to-month costs on Amazon Redshift.
“The savings [from moving to Azure SQL Data Warehouse] have been substantial. The snapshot/restore functionality of Amazon Redshift took hours to complete, which wasn’t conducive to an ad-hoc environment. As a result, we tended to leave our Amazon Redshift instance running more often and scale it up and down to try and limit expenses as much as possible.”
— Nathan Pond, Senior Software Engineer, Integral Analytics
Insights at the flip of a switch
Integral Analytics investigated Azure SQL Data Warehouse and found a solution that met its needs for affordability, scalability, and the support for the .NET tools that its developers were already familiar with. It is easy for Integral Analytics to pause Azure SQL Data Warehouse so that the company only pays for what it needs. Integral Analytics focuses on running occasional, large queries, so pausing Azure SQL Data Warehouse when not in use is a huge benefit. On average, Integral Analytics runs compute jobs on Azure SQL Data Warehouse about eight hours a week. “The savings have been substantial,” reports Nathan Pond, Senior Software Engineer at Integral Analytics. “The snapshot/restore functionality of Amazon Redshift took hours to complete, which wasn’t conducive to an ad-hoc environment. As a result, we tended to leave our Amazon Redshift instance running more often and scale it up and down to try and limit expenses as much as possible.”
“When we learned about the pause and resume capabilities of SQL Data Warehouse and integrated services like Azure Machine Learning and Data Factory, we switched from Amazon Redshift, migrating over 7 TB of uncompressed data over a week for the simple reasons of saving money and enabling a more straightforward implementation for advanced analytics. To meet our business-intelligence requirements, we load data once or twice a month and then build reports for our customers. Not having the data-warehouse service running all the time is key for our business and our bottom line.”
— Bill Sabo, Managing Director of Information Technology, Integral Analytics
The ability to pause compute is not the only benefit that Azure SQL Data Warehouse brings to Integral Analytics. Azure SQL Data Warehouse can also scale for the company when it needs greater computational power. Integral Analytics currently scales its instances to 1,000 data-warehouse units (DWUs), and it plans to scale up to 6,000 DWUs now that Azure SQL Data Warehouse has moved into general availability.
Integral Analytics’ developers like working with the familiar .NET Framework and native T-SQL queries. While Amazon Redshift uses a SQL-like language, working with native T-SQL queries in .NET enables developers at Integral Analytics to create data-warehouse queries that work smoothly with SQL Server. Because Integral Analytics still uses a lot of highly structured data residing on SQL Server and Azure SQL Database, the ability to create queries that span Azure SQL Data Warehouse and SQL Server—hybrid queries, as Integral Analytics refers to them—is critical for the company’s evolving business.
“Switching from Amazon Redshift was not just about a direct comparison to Azure SQL Data Warehouse. The overall Azure offering provided a lot of motivation.”
— Bill Sabo, Managing Director of Information Technology, Integral Analytics
A large part of the synergy with Azure for Integral Analytics stems from being able to use both Azure SQL Data Warehouse and Azure SQL Database together seamlessly. Because of the way that electrical power is generated and transmitted on traditional electrical grids, power data it intrinsically hierarchical. For Integral Analytics, this hierarchy of data moves from substations to transformers to individual meters. Such data is easy to store and analyze using relational databases like SQL Server and Azure SQL Database. But things like time-series data from patterns of electrical use are better suited to solutions like Azure SQL Data Warehouse. Azure enables Integral Analytics to create queries that take hierarchical data from Azure SQL Database and then pull time-series data from Azure SQL Data Warehouse. This capability is essential for Integral Analytics because such hybrid queries are the basis for the company’s business moving forward. “Switching from Amazon Redshift was not just about a direct comparison to Azure SQL Data Warehouse,” says Bill Sabo, Managing Director of Information Technology at Integral Analytics. “The overall Azure offering provided a lot of motivation.”
Other Azure services that Integral Analytics uses include Azure Blob storage and Azure Batch. Azure Blob storage provides an easy, affordable way to stage cold data until Integral Analytics needs it in Azure SQL Data Warehouse. This helps Integral Analytics pay only for what it needs—in this case, storage in Azure SQL Data Warehouse for data that the company is currently working with. Integral Analytics also uses Azure Blob storage to feed the batch analysis and data-modeling jobs that it spins up with Azure Batch. Azure Batch in turn helps speed Integral Analytics’ development in many ways as discussed below.
Plugging in development
Data modeling was often a challenge for Integral Analytics when it relied solely on its on-premises infrastructure. Big jobs required a lot of orchestration on premises to ensure that developers had enough server capacity to run them. Beyond orchestration, developers also had to troubleshoot the servers when running batched data models.
Azure Batch streamlines development by handling all the orchestration for data modeling that Integral Analytics runs, whether on premises or in Azure. Azure Batch also does a lot of error handling for Integral Analytics’ developers so that they can focus on their work.
Being able to use Azure to span its on-premises and cloud-based infrastructure is crucial for Integral Analytics. The company still has a lot of peripheral data sets that use “home-brewed” applications for data transformation that reside on premises.
Familiar tools are also a big win among Integral Analytics’ developers. They like the level of Microsoft Visual Studio integration with Azure. It is easy for developers to synch schemas and data that reside on premises with those in the cloud. And because they can use the same T-SQL queries in Azure SQL Data Warehouse that they use in SQL Server, Integral Analytics’ developers can make use of their experience with SQL Server while working in Azure SQL Data Warehouse.
Integral Analytics has also started using Azure to prepare and transform data for analysis. The company uses Azure Data Factory to extract, transform, and load (ETL) processes from its data sources over to Azure SQL Data Warehouse. This simplifies development for Integral Analytics’ developers, who no longer have to manually handle all aspects of ETL. In addition, Integral Analytics uses Azure Data Factory to extract data from the warehouse for analysis. As COO Kevin Kushman sums up, “Integral Analytics is a small company that plays among multinationals. We punch well above our weight, and one way we can do that is through using Microsoft and Azure.”
Azure SQL Data Warehouse delivers analytics power
“Integral Analytics is a small company that plays among multinationals. We punch well above our weight, and one way we can do that is through using Microsoft and Azure.”
— Kevin Kushman, COO, Integral Analytics
Integral Analytics built its core business by combining many types of siloed data to extract insights, such as economic risk and opportunities, that help utilities improve their margins through better business decisions. Real-time and historical power-usage data, along with predictive analytics, might well help utilities know when new charging stations are needed and where to place them. Azure SQL Data Warehouse helps Integral Analytics build on its foundation with crucial capabilities that simplify and accelerate software development. Azure SQL Data Warehouse:
Supports critical queries that span Azure SQL Data Warehouse and SQL Server.
Enables the company to scale well beyond what it could do without scaling to an enterprise-grade cluster on premises.
Helps control costs by easily pausing and scaling up compute resources as needed.
Features familiar development tools and automated management.
As Bill Sabo of Integral Analytics concludes, “When we learned about the pause and resume capabilities of SQL Data Warehouse and integrated services like Azure Machine Learning and Data Factory, we switched from Amazon Redshift, migrating over 7 TB of uncompressed data over a week for the simple reasons of saving money and enabling a more straightforward implementation for advanced analytics. To meet our business-intelligence requirements, we load data once or twice a month and then build reports for our customers. Not having the data-warehouse service running all the time is key for our business and our bottom line.”
For more information about Azure SQL Data Warehouse, visit https://azure.microsoft.com/en-us/services/sql-data-warehouse/.