Automating Azure SQL Data Warehouse

I had the great honor of speaking twice at Microsoft Ignite 2018 on the topic of Azure SQL DW. The second session was a practical session and I wanted to post the YouTube video, the GitHub code samples, and slides download for the session for all my blog readers.

Optimize cost, improve performance, and enhance the security of your DW with automation. This session demonstrates several common automation needs. Learn how to trigger a backup or pause your DW at the end your Azure Data Factory pipeline. Learn how to perform key rotation on SQL login passwords and blob storage keys for Polybase using Azure Automation.

How to shoot yourself in the foot with Azure SQL Data Warehouse

I had the great honor of speaking twice at Microsoft Ignite 2018 on the topic of Azure SQL DW. The first session was a fun one about some war stories from the trenches with Azure SQL DW. I wanted to post the YouTube video and slides download for the session for all my blog readers.

Having implemented Azure SQL Data Warehouse solutions for dozens of clients, here are the lessons learned we wish we had known several years ago. Learn a great way to make your loads run twice as long. Learn the best ways to make columnstores perform as slowly as molasses in January. Take a visit to the distribution key hall of shame. We review some common mistakes, understand what’s happening under the covers, then show the best practice to fix the problem.

Automating Azure Analysis Services

Last year I got the opportunity to speak at a local SQL Saturday. I used the opportunity to really stretch myself and research some creative ways of spinning up, spinning down, and automating Azure Analysis Services. When I wrote the abstract and submitted the session I wasn’t even sure these things were possible (and after you watch the video you may feel a few of my demo hiccups prove some of these ideas weren’t possible). The code samples are posted on GitHub and the slides are available here.

I also got a chance to talk to Shawn Weisfeld of UserGroup.tv who provides an amazing free service and publishes lots of user group session recordings. The video below is courtesy of UserGroup.tv.

The elasticity of the cloud lets you provide a better user experience instead of breaking the bank running your solution at the highest scale 24 hours a day. This demo-heavy session will show how to automate Azure Analysis Services. We will look at solutions to the following questions: How do you schedule a refresh of the data in your Azure Analysis Services model? How do you pause the server on the weekends? Better yet, how can you pause it when the last user disconnects and start it automatically when the first user connects in the morning? How do you scale to meet peak demand each day? How can Azure Analysis Services load balance across servers in multiple countries so that the user connects to the geographically closest server? How do you extend your on-premises SSAS solution into Azure Analysis Services?

Azure SQL Data Warehouse Announcement and Analytics Platform System Speaking Tour

I could not have picked a better time to do a speaking tour about the APS (Analytics Platform System which is Microsoft's data warehouse and big data appliance). On April 29 at the Build conference, Microsoft made an incredibly exciting announcement: Azure SQL Data Warehouse.

The video announcement from Build about Azure SQL Data Warehouse, a fully managed relational data warehouse-as-a-service

Artis has been doing a ton of work recently with the APS appliance, and I have really enjoyed boiling down some of the lessons learned into presentation form. Speaking on a technology like APS is incredibly rewarding when you see everyone's jaw drop at the query performance and the hardware stats.

SQL Saturday #396 - Dallas - BI Edition

I recently had the pleasure of presenting a session entitled One is the Loneliest Number: Scaling out your Data Warehouse to a local SQL Saturday crowd. The slides can be found here.

If your current data warehouse is overworked and underpaid, maybe it needs a friend to share the load. Do you want to be able to scan your 1TB fact table in 20 seconds? Do you want to maintain performance even if data volumes triple without rewriting any code? Do you want a data warehouse which extends to the cloud?

This demo heavy session will be a deep dive into scaling out your data warehouse onto the Analytics Platform System appliance from Microsoft and querying it with familiar T-SQL. You will learn performance tuning best practices for taking advantage of a distributed data warehouse. See demos of hybrid connectivity to Azure Blob Storage through a technology called Polybase and learn how a modern data warehouse can handle structured data, semi-structured data, and streaming data.

In case you're wondering about the title, it's a Three Dog Night song, so of course we had to start the session by watching the rather silly video.

Three Dog Night's "One"


Adopting a Modern Hybrid Data Warehouse

In the next few weeks I will be speaking in Houston and Dallas:

Houston - May 14, 2015
Dallas - May 20, 2015

Do you want to be able to get results much faster from really large data sets? Are you and your team tired of waiting for slow reports to complete? Do you want to be able to scan your 1TB fact table in 20 seconds?

Join Microsoft and Artis Consulting to learn about the Modern Data Warehouse and how it can handle structured data, semi-structured data, and streaming data with the Analytics Platform System (APS) appliance and the recently announced Azure SQL Data Warehouse from Microsoft.  In this interactive lunch session, you will:

  • See demos of hybrid connectivity to Azure Blob Storage through a technology called Polybase

  • Learn how to scale out your data warehouse onto the APS and query it with familiar T-SQL

  • Learn the underpinnings of a distributed data warehouse which can scale to satisfy even the most demanding workloads

  • Hear a case study of a company that is revolutionizing their business and their industry with a modern hybrid data warehouse

Together we will answer the question of whether migrating to the APS appliance is the next step for your overworked data warehouse.

Tabular Performance Guide Speaking Tour

Some of the most fun I’ve had this year was spent collaborating with some friends at Microsoft to publish an important guide on performance tuning Tabular models in Analysis Services. I am very proud to have helped co-author the paper which represents hundreds of hours of work by many people.

Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services


The reaction from the community has been great (here and here for example). Chris Webb even noted that there is some content in the whitepaper relevant to Multidimensional models, too. Specifically he mentioned we noted in the whitepaper that SQL 2012 SP1 CU4 (though you should just skip to CU6 which is the latest at the moment) included an enhancement so that multi-select filters no longer prevent caching in most scenarios. While that is a huge deal for Multidimensional models, it’s also very helpful for Tabular models if you’re querying them in a client tool like Excel which runs MDX queries. For those who are optimizing Multidimensional models, I’d also point you to a summary of SSAS enhancements made in SSAS 2012 on page 144, a warning about very wide models and heap fragmentation on page 67, and a discussion of updated SSAS drivers and performance on page 69.

PASS Summit 2013

John Sirmon and I had the privilege of summarizing 147 pages of content into a 75 minute presentation at PASS Summit 2013 in Charlotte, NC recently. The room was pretty packed and there were some great questions afterward.

SQL Saturday #255 – Dallas

For the locals who weren’t able to make it to Charlotte, I’m going to be doing the talk in my hometown for SQL Saturday Dallas on November 2, 2013. Here’s the abstract:

Gain the knowledge, tips, and techniques you need to tune your Tabular models in SQL Server Analysis Services 2012. This session will begin by exploring the Tabular model architecture and explaining the factors that affect performance. We’ll then walk through tuning techniques and strategies specific to querying, processing, partitioning, and server configuration and demonstrate optimizing performance in some common scenarios. This session is based on the recently released white paper “Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services.”

Tabular Performance Event

It’s hard to do justice to this content in an hour, so I am looking forward to speaking at an all-day event at our local Microsoft office in Irving, TX on November 12, 2013. If you are interested, please reach out to Bryan Smith.