#Azure #SQLDW, the cost benefits of an on-demand data warehousing

Prices illustrated below are based on East US 2 as December 18th, 2017. For price changes updates, visit Azure Analysis Services, SQL Database, and SQL Data Warehouse pricing pages.

Azure SQL Data Warehouse is Microsoft’s SQL analytics platform, the backbone of your Enterprise Data Warehouse. The service is designed to allow customers to elastically and independently scale, compute and store. It acts as a hub to your data marts and cubes for an optimized and tailored performance of your EDW. Azure SQL DW offers guaranteed 99.9 percent high availability, PB scale, compliance, advanced security, and tight integration to upstream and downstream services so you can build a data warehouse that fits your needs. Azure SQL DW is the only data warehouse service enabling enterprises to gain insights from data everywhere with a global availability in more than 30 regions.

This is the last blog post in our series detailing the benefits of Hub and Spoke data warehouse architecture on Azure. On-premises, a Hub and Spoke architecture was hard and expensive to maintain. In the cloud, the cost of such architecture can be much lower as you can dynamically adjust compute capacity to what you need, when you need it. Azure is the only platform that enables you to create a high performing data warehouse that is cost optimized for your needs. You will see in this blog post how you can save up to 50 percent on cost by leveraging a Hub and Spoke design while increasing the overall performance and time to insights of your analytics solutions.

With the Microsoft Azure data platform you can build the data warehouse solution you want with workload isolation, advanced security and virtually unlimited concurrency. All of this can be done at an incredibly low cost if you leverage Azure Functions to build on-demand data warehousing. Imagine a company who wants to create a central data repository from a variety of source systems and push the combined data to multiple customers (e.g. ISV), suppliers (e.g. retail) or business units/departments. In this case study, this customer expects a strong activity for its Data Warehouse from 8 AM to 8PM during workdays. The performance ratio between high and low activity times is around 5x. They expect its curated data lake, SQL Data warehouse to be 10 TB large after compression and have peak time needs at 1,500 DWUs. For dash boarding and reports the solution will use Analysis Services, caching around 1 percent of the data. Thanks to SQL DB elastic Pools or Azure Analysis Services, the company can add concurrency, advanced security and workload isolation between their end users. SQL DB Elastic Pool offers a wide range of performance and cost with the cost per database starting at $0.60 with the Basic Tier.

The figure below illustrates the various benefits from moving to a Hub and Spoke Model. Microsoft Azure is the only platform offering the ability to build the data warehouse that fits your unique data warehousing needs.

Figure 1 – Benefits from a Hub and Spoke Architecture

In step one, this is the traditional data warehouse and is the starting point of building your Data Warehouse. Every data warehouse will have inherent limits that will be encountered with more and more people connecting to the service. In this example, with no auto-scale and a rigid level of provisioning you could spend $15k/month.

In step two, we introduce Azure Functions to use the full elasticity of SQL DW. In that simple example, we leverage the time trigger function and ask SQL DW to be at 1,500 DWUs at peak time (workdays 8AM-8PM) and 300 the rest of the time. This is a simple example, but you can go deeper on performance levels, add auto-scaling and auto-pausing/resuming to make your data warehouse auto-scale. In this example the cost goes down to $8k/month.

Step three is a great example on the breadth of customization you can make around SQL DW using SQL DB or Azure Analysis Services. No other data warehouse enables such a high level of customization because you cannot expand them. With that model, there is virtually no limit in concurrency and performance of your data warehouse. Here are a few examples of what you can do:

• For high performance, interactive dash boarding and reports with pre-aggregate queries, Azure Analysis Services will be the right choice.
• Do you want to provide a predictable performance to a large department at a fast speed? SQL DB Premium Single Database will be the right choice.
• If you are an ISV, do you have a large number of customers that you need to accommodate at a free subscription level?  A Basic SQL DB Elastic Pool can accommodate a cost per database for less than $1/month.

Deploy in a SQL Data Warehouse Hub Spoke Template with SQL Databases.

In the example below, the cost of the data warehouse varies from $10k/month to $15.5k/month depending on what tier and service you pick. Remember that by offloading the performance from SQL DW to data marts or caching layers, you can dramatically reduce your DWU provisioning (while increasing concurrency). Also remember that you can leverage Azure Functions to start automating the level of performance you need at a specific point in time. Learn more about using Azure Functions to automate SQL DW Compute Levels.

In step four, you can further optimize the performance of your data marts by connecting them to Azure Analysis Services for caching. In this example, the cost is between $16k and $21.5k/month with the opportunity to be even lower if you offload the performance needs on your data marts.

Figure 2 – Summary of the benefits to build a Hub and Spoke Data Warehouse

In summary, we moved from a static and monolithic data warehouse costing $28k per month to an elastic Hub & Spoke data warehouse optimized for performance and accessed by thousands of users with a potential cost saving of 50 percent. We can guarantee you that each of the services will continue further integrating with each other to provide the best data warehouse experience.

If you need our help for a POC, contact us directly by submitting a SQL Data Warehouse Information Request. Stay up-to-date on the latest Azure SQL DW news and features by following us on Twitter @AzureSQLDW. Next week, we will feature the deeper integration between Azure Analysis Services and SQL DW.

Source: Azure Blog Feed