A Fine Slice Of SQL Server
Patrick LeBlanc has a video for us:
Have you struggled to compare data from two different time periods in Power BI? Does the data just continue on and not overlap? Patrick shows you how you can easily overlay the data for comparison.
Click through for the video.
Comments closedArun Sirpal shows us how easy it is to tie Azure Key Vault into Azure Databricks:
The key vault should always be a core component of your Azure design because we can store keys, secrets, certicates thus abstract / hide the true connection string within files. When working with databricks to mount storage to ingest your data and query it ideally you should be leveraging this to create secrets and secret scopes.
Click through for a demo.
Comments closedMichael J. Swart walks us through spinlock contention:
When that occurred, the number of batch requests that the server could handle dropped significantly. So we saw more CPU use, but less work was getting done.
The high demand kept the CPU at 100% with no relief until the demand decreased. When that happened, the database seemed to recover. Throughput was restored and the database’s metrics became healthy again. During this trouble we looked at everything including the number of spins reported in the sys.dm_os_spinlock_stats dmv.
The spins and backoffs reported seemed extremely high, especially for the category “XVB_LIST”, but we didn’t really have a baseline to tell whether those numbers were problematic. Even after capturing the numbers and visualizing them we saw larger than linear increases as demand increased, but were those increases excessive?
Read on for the answer.
Comments closedShane O’Neill succumbs to peer pressure:
I can’t very well give out to people for not doing the right thing first time, even if it’s more difficult, if I don’t do the right thing myself!
As Kevin mentioned, once the data was in a proper format, a format designed for SQL, the calculations were trivial.
However, outputting the results in the same way in PowerShell required a way to pivot results in PowerShell. Thanks to some heavy lifting from Joel Sallow ( Blog | Twitter ), I now know how to pivot in PowerShell!
Here’s hoping that this post will help explain it for you also.
It’s interesting to see how much more difficult it is to turn a “tall” data set into a “long” data set in Powershell. It’s not that many lines of code once you know how to do it, but getting there is a challenge.
Comments closedMeagan Longoria shows us how Blank works:
Before you read the below tweet, see how many of these you can guess correctly:
Blank + 5 = ?
Blank * 5 = ?
5 / Blank = ?
0 / Blank = ?
Wat.
Comments closedGilbert Quevauvilliers shows us how to remove a Power BI On-Premises Gateway from a cluster:
In the past when I had to remove a Power BI Data Gateway from a cluster, I had to use PowerShell with a couple steps which was rather complex.
I recently found out that this can now be easily achieved using the Power BI Admin Centre in the steps below.
Click through for the process.
Comments closedJohn MacKintosh shows us the visNetwork package:
I’ve long been hoping for a reason to have to devote time to learning how to produce network plots. In my world, where bar and line charts reign supreme (with heatmaps and waffle charts thrown in occasionally) it is nice to be able to develop a new visualisation.
I’ve been wanting to produce a network plot for some time. But, the data structure, with its nodes and edges, and seeming lack of any identifiable characteristics, has meant it has not been hugely far up my agenda, or at least, never far up enough to make me learn more about it.
Click through for an example of where a network diagram can work out. H/T R-Bloggers
Comments closedMatt Allington walks us through a complex scenario using the Matrix visual in Power BI:
Not every report you may want to build has such simple layout requirements as the one above. When using an Excel pivot table, there is a feature called “Field, Items and Sets” that makes the process of creating asymmetric pivot tables relatively easy, but that doesn’t exist in Power BI. To help you understand, here is an example of an asymmetric visual (Power BI matrix in this case).
Note in my example above, the first 4 years come from the year column, just like before, but now I have 2 additional columns at the end of the matrix that are not simply the addition of other columns. To create the last 2 columns above I need to write measures, but you can’t have both measures and columns from your model showing like this in a Power BI matrix. Instead you need to build a solution that leverages a separate table to generate the columns you need, and then to generate the results you need in each column.
Below I will show you how to build such an asymmetric matrix like this.
Read on for the solution. This is quite clever but also makes me wonder if the product shouldn’t make this scenario a bit easier for us
Comments closedIn SQL Server 2016 Microsoft introduced Automatic Seeding for Availability Groups (AG). The Automatic Seeding process streams the database files directly to the secondary server(s) using the database mirroring endpoints, removing the need to restore the databases, before joining them to an AG.
Read on for Jamie’s thoughts and notes.
Comments closedKenneth Fisher has evil afoot:
Unfortunately, if there is a problem with the server / server name the sp_addlinkedserver doesn’t seem to throw an error and sp_addlinkedsrvlogin throws an error that breaks me out of the try block. So after my Google-fu failed me I did what I frequently do and asked on #sqlhelp. And I was given an answer that led me to the solution.
Click through for the solution. But not for the answer as to why he’s growing batches of monsters linked servers.