Press "Enter" to skip to content

Month: January 2018

Clustering The Power BI Gateway

Published 2018-01-05 by Kevin Feasel

Craig Porteous show how to cluster the Power BI Data Gateway to allow for disaster recovery:

I love PowerShell and I even wrote a module with functions to query Power BI metadata but there should always be another way to get this vital information.

The documentation I mentioned earlier points you to a PowerShell module file included in the November update. You can load this file & use the commands they provide to get information about your Gateway cluster and its members or make changes to clusters.

If it’s important enough to use, it’s important enough to include in a disaster recovery plan.

Comments closed

Finding Maxima And Minima

Published 2018-01-04 by Kevin Feasel

Jobil Louis shares various techniques for finding a global maximum or minimum:

Let’s say we want to find the minimum point in y and value of x which gives that minimum y. There are many ways to find this. I will explain three of those.

1) Search based methods: Here the idea is to search for the minimum value of y by feeding in different values of x. There are two different ways to do this.

a) Grid search: In grid search, you give a list of values for x(as in a grid) and calculate y and see the minimum of those.

b) Random search: In this method, you randomly generate values of x and compute y and find the minimum among those.

The drawback of search based methods is that there is no guarantee that we will find a local or global minimum. Global minimum means the overall minimum of a curve. Local minimum means a point which is minimum relatively to its neighboring values.

My favorite class of algorithm here is evolutionary algorithms, particularly genetic algorithms and genetic programming.  They’re a last-ditch effort when nothing else works, but the funny thing about them is that when nothing else works, they tend to step up.

Comments closed

Plotting Data With Python In ML Services

Published 2018-01-04 by Kevin Feasel

Robert Sheldon continues his Python in Machine Learning Services series:

One of the most useful modules is the matplotlib library, which provides an extensive codebase for plotting data and creating rich, customized visualizations. You can use matplotlib components to generate a wide range of graphics, including bar charts, pie charts, scatter plots, histograms, and many others. For example, you can generate a series of line charts that aggregate inventory or sales data in your SQL Server database and then save those charts to .png or .pdf files.

This article includes several examples that demonstrate how to create matplotlib visualizations and save them to .pdf files, using data from the AdventureWorks2017 sample database. The article assumes that you know how to use the sp_execute_external_script stored procedure to run Python scripts in SQL Server. If you’re not familiar with the stored procedure, you should review the first two articles in this series before continuing with this one.

If you’re already familiar with matplotlib, using it within SQL Server is pretty easy, as Robert shows.  If you’re not familiar, this is a useful introduction to the library.

Comments closed

Plotting Graph Data In R

Published 2018-01-04 by Kevin Feasel

Sifiso Ndlovu shows how to take graph data from SQL Server and plot it in R using Machine Learning Services:

However, with recent focus on big data for many of my clients, we have experienced an increase in different business requests that requires for many-to-many data modelling. Consequently, as a Microsoft shop we’ve had to turn to other non-Microsoft products to ensure that we optimally respond to such business requests. Not surprisingly, ever since word got around that graph database will be part of SQL Server 2017, we’ve been looking forward to this latest release of SQL Server. Having played around with the graph database feature in SQL Server 2017, we have noticed that unlike other graph database vendors, plotting and visualising the data out of the graph database is not readily available in SQL Server 2017. Luckily, thanks to SQL Server R, you can easily plot and visualise SQL Server 2017 graph database data without turning to 3rd party plugins. In this article, I demonstrate how SQL Server Machine Learning Services (previously known as SQL Server 2016 R Services) can be used to plot a diagram according to the data defined in a SQL Server 2017 graph database.

The igraph library is a good one; there’s a lot of power in it that this post just introduces.

Comments closed

ML Services Can Fill The Plan Cache

Published 2018-01-04 by Kevin Feasel

I have a post talking about a bug in SQL Server:

For now, the workaround I have is to restart the SQL Server service occasionally. You can see that I have done it twice in the above screenshot. Our application is resilient to short database downtimes, so this isn’t a bad workaround for us; it’s just a little bit of an annoyance.

One thing to keep in mind if you are in this scenario is that if you are running ML Services hundreds of thousands of times a day, your ExtensibilityData folders might have a lot of cruft which may prevent the Launchpad service from starting as expected. I’ve had to delete all folders in \MSSQL14.MSSQLSERVER\MSSQL\ExtensibilityData\MSSQLSERVER01 after stopping the SQL Server service and before restarting it. The Launchpad service automatically does this, but if you have a huge number of folders in there, the service can time out trying to delete all of them.  In my experience at least, the other folders didn’t have enough sub-folders inside to make it worth deleting, but that may just be an artifact of how we use ML Services.

It’s very unlikely to affect most shops, as we only notice it after running sp_execute_external_script millions of times, and that’s pretty abnormal behavior.

Comments closed

Getting Dates Between Two Dates In Power BI

Published 2018-01-04 by Kevin Feasel

Imke Feldmann has created an M function to get the set of dates from a start date to an end date at some fixed interval in Power BI:

This function takes 3 parameters:

  1. From- or Start-date
  2. To- or End-date
  3. A selection of ONE of these intervals: Year, Quarter, Month, Week or Day

All dates will be created at the end of the chosen interval: So if you want to analyse events with a duration for example, where you want to transform your data to show one day per (monthly) event, this function generates month-end-dates for every month within the timespan. Please not that if the To-/End-date is within a month, the last element of the list will NOT be that day, but the day of the end of that month.

The default-value for the 3rd parameter is “Day”, so if you omit the specification, the function will return a list of all days in between.

Click through for the script and some explanation of the code.

Comments closed

SSMS Tip Per Day

Published 2018-01-04 by Kevin Feasel

Wayne Sheffield has pledged to do a month worth of SSMS tips and he’s off to a good start.  Here’s day 1, where he talks about creating a solution in SSMS:

Are you the type of person that has all of your custom queries in one folder, and finding the particular one that you are looking for can sometimes be a pain? Well, solutions can certainly help you. A solution consists of one or more projects, which then contain files. In SSMS, the project can contain Connections, Queries and Miscellaneous files. These various projects can be used to group your queries so that they are easily accessible.

Personally, I maintain two different solutions. One is for all the presentations that I do, each in a separate project within that solution. The other is all of my day-to-day scripts.

The first step in using a solution is to open up the Solution Explorer window. This is available from the View Menu | Solution Explorer, or by pressing the keyboard shortcut Ctrl+Alt+L.

Day two shows you how to split the screen in SSMS so you can view two sections of the same script at the same time.

Day three shows you how to create tab groups, so you can see two scripts at the same time.

Comments closed

Generating Passwords In T-SQL And Powershell

Published 2018-01-04 by Kevin Feasel

Dave Mason shares a couple methods for generating good passwords:

There’s really nothing special there. On line 7 I specify how many characters long I want the password to be. I can run the code as many times as needed, or put it in a UDF or a loop if I want to get fancy.

Recently, though, I had the need to generate passwords outside of a T-SQL environment. I immediately went looking in the .NET Framework, and none to my surprise, I found something: the function System.Web.Security.Membership.GeneratePassword(). I did some initial testing in C#, then decided to proceed with a PowerShell scrip

Click through for the scripts.

Comments closed

Restricting Accidental Operations

Published 2018-01-04 by Kevin Feasel

Shane O’Neill shares a few methods for preventing accidental script runs:

Recently I came across a question in Stack Overflow (SO) that said the following:

The other day I was trying to hit another button on the menu but hit Execute – which executed the whole code and ended up deleting some tables. I have always found this scary that hitting one button can execute the whole code.

I want SQL Server to execute code only when something is selected. Is it possible? Or can SQL Server prompt before executing a query?

I thought this is a great question because it can be answered in 4 different ways…

Click through for those four methods.

Comments closed