Press "Enter" to skip to content

Category: Cloud

Is Power BI SSAS In The Cloud?

Published 2016-04-13 by Kevin Feasel

Koos van Strien hits us with an interesting thought about SSAS versus Power BI:

As I’m currently planning to migrate the entire BI architecture of one of my customers to the cloud, this made me think: can we ditch SSAS as we know it already in favor of Power BI? What are the alternatives?

To study that, I’ve put some diagrams together to show the possibilities of moving BI to the cloud. First, I’ll discuss the possible architectures, then the impossible architecture (but maybe the situation I was looking for).

One man’s opinion:  there will be SSAS for Azure.  I have no proof of this, and the nice part about having no proof is that I can throw out wild speculation without fear of violating NDA….  But to me, Power BI solves a different problem and acts in conjunction with SSAS rather than as its replacement.  I also don’t see any technical reasons why SSAS couldn’t live in the cloud, and so that leads me to believe that it will be there eventually.  But hey, it turns out wild speculation is occasionally wrong…

Comments closed

Extended Events In Azure SQL Database

Published 2016-04-12 by Kevin Feasel

Julie Koesmarno walks through Extended Events in Azure SQL Database (currently in preview):

Extended Event (XEvent) feature is available as public preview in Azure SQL Database as announcedhere. XEvent supports 3 types of targets – File Target (writes to Azure Blob Storage), Ring Buffer and Event Counter. Once we’ve created an event session, how do we inspect the event session target properties? This blog post describes how to do this in 2 ways: using the User Interface in SSMS and using T-SQL.

It’s nice to see Extended Events making their way into Azure SQL Database.

Comments closed

Linear Regression In Azure ML

Published 2016-04-08 by Kevin Feasel

Ginger Grant gives a brief discussion of linear regression:

There are two types of indicators for linear correlation, positive and negative as shown on the following charts. The Y axis represents Grades, and the x axis is changed to show positive and negative correlation of the amount of X on grades. When X is the amount of study hours, there is a positive correlation and the line goes up. When X is changed to watching cat videos, there is a negative correlation. If you can’t draw a line around the points there is no correlation. If I were to create a graph where X indicated the quantity of the bags of Cheese Doodles consumed on grades, it would not be possible to draw a straight line, where the data points cluster around it. Since this is Line-ar regression, if that line doesn’t exist there is no correlation. Knowing there is no correlation is also useful.

Simple linear regression is a powerful tool and gets you to “good enough” more frequently than you’d think.

Comments closed

SQL Server Marketplace Images

Published 2016-04-05 by Kevin Feasel

Melissa Coates debates whether to use a SQL Server marketplace image for an Azure VM:

As more and more customers are interested in moving some portion of their BI/analytics workloads to cloud services, one question that comes up occasionally is whether or not you should start with a marketplace image that has SQL Server already installed. So far I’ve noted a few key considerations for this decision:

  1. Do you want to pay for the SQL Server license as part of the VM pricing?

  2. Do you want to configure SQL Server in a specific way (i.e., following best practices)?

  3. Do you want Azure to handle things like automated patching by default?

My rule of thumb is if it’s Express Edition or just for me to mess around with, I’m typically happy with an image.  But for a production setup, I’d want the fine-grained control at installation time that you just won’t get.

Comments closed

Jupyter Notebooks With R

Published 2016-04-04 by Kevin Feasel

Andrie de Vries notes that Azure Machine Learning now supports Jupyter Notebooks with R:

I wrote about Jupyter Notebooks in September 2015 (Using R with Jupyter Notebooks), where I noted some of the great benefits of using notebooks:

  • Jupyter is an easy to use and convenient way of mixing code and text in the same document.

  • Unlike other reporting systems like RMarkdown and LaTex, Jupyter notebooks are interactive – you can run the code snippets directly in the document

  • This makes it easy to share and publish code samples as well as reports.

Jupyter Notebooks is a fine application, but up until now, you could only integrate it with Azure Machine Learning if you were writing Python code.  This move is a big step forward for Azure ML.

Comments closed

Against The Cloud

Published 2016-04-01 by Kevin Feasel

SQLWayne is against “the cloud:”

First off, the word itself.  The Cloud.  What is The Cloud?  It’s a server that you don’t own.  You can’t touch it, it’s in someone else’s data center.  It may or may not be virtual.  Amazon’s Cloud or Microsoft’s or Google’s are several data centers with racks and racks of servers.  They are physical, just not at your location.  And they’re accessed across the Internet.  This is something that we’ve been doing for 30 years, it’s called a Wide-Area Network, just scaled up bigger.  We had bi-coastal WANs before the World Wide Web came along.

Four or five years ago, I was absolutely in agreement.  Today, I’m 50/50, being near 100% for many types of servers (web servers, etc.) and closer to 25-30% for databases.  My expectation is that those numbers will continue to shift upward as time goes on, but there will always be reasons not to migrate certain servers to someone else’s data center.

Comments closed

Clearing The Azure Procedure Cache

Published 2016-03-30 by Kevin Feasel

Tim Radney shows us a new way of clearing the procedure cache in Azure SQL Databases (and in 2016 RC0 or later):

It turns out that DBCC FREEPROCCACHE is not supported in Azure SQL Database. This was troubling to me, what if I’m in production and have some bad plans and want to clear the procedure cache like I can with the box version. A little Google/Bing research lead me to find the Microsoft article, “Understanding the Procedure Cache on SQL Azure,” which states:

SQL Azure currently doesn’t support DBCC FREEPROCCACHE (Transact-SQL), so you cannot manually remove an execution plan from the cache.  However, if you make changes to the table or view referenced by the query (ALTER TABLE and ALTER VIEW) the plan will be removed from the cache.

In discussing this with Kimberly Tripp after not seeing that described behavior, it does not flush the plan from cache, but it does invalidate the plan (and then the plan will be eventually aged out of the cache). While this is helpful in certain situations, this was not what I needed. For my demo I wanted to reset the counters in sys.dm_exec_cached_plans. Generating a new plan would not give me the desired results. I reached out to my team and Glenn Berry told me to try the following script:

Read on for the new command, and just like DBCC FREEPROCCACHE, be careful where you point that thing.

Comments closed

Pausing Azure SQL Data Warehouse

Published 2016-03-29 by Kevin Feasel

Brian Davis shows us how to pause Azure SQL Data Warehouse:

This is where automation comes to the rescue again! Most of our SQLDWs can be paused after 6:00 PM on weekdays, as well as the entire weekend. Now, I could manually go and pause each individual SQLDW at the end of the day, but what happens if I have plans for dinner or something else during that time? I decided that I needed an automated process to check each SQLDW and pause it if it is running. Using Azure Automation andAzure Runbooks, I was able to create a scheduled task that looks for any running SQLDW and pauses it.

Here are the basic steps to implement the automated solution I came up with:

  1. Create a credential in your automation account with access to all SQL Data Warehouses.

  2. Create a PowerShell Workflow Runbook with the code below.

  3. Create and link the schedule(s) needed for it to run.

Azure gripe #4 for me is that they’re so inconsistent about what I can do not to pay money.  Apparently you can pause Azure SQL Data Warehouse, which is good.  But DocumentDB or HDInsight?  Nope, deletion is the only way to stop running up charges.  Check out Brian’s script if you use Azure SQL Data Warehouse and save your company a bit of cash.

Comments closed

Powershell With Azure SQL Database

Published 2016-03-17 by Kevin Feasel

Mike Fal introduces us to Azure SQL Database operations using Powershell:

What is this all about? It took me a bit of digging, but what it boils down to is that Microsoft made a fundamental change to how things are managed within Azure. You will now find documentation on these two different deployment models: Classic Deployments and Resource Manager Deployments. These two different set of Powershell cmdlets reflect these different models, as anything for Classic Deployments are handled by cmdlets in the Azure and Azure.Storage modules. All the Resource Manager Deployment stuff is handled by the AzureRM* modules.

This is the first in a series and serves as an introduction to the topic.

Comments closed