Installing SQLCMD On Linux

James Livingston shows us how to install SQLCMD on Red Hat Enterprise Linux:

Microsoft has decent instructions on installing SQLCMD on linux. My current company blocks access to external yum repositories, so I followed the “Offline” installation. There’s two packages to install:
1) msodbcsql – aka Microsoft’s ODBC driver for SQL Server
2) mssql-tools – SQLCMD

Read on for the step-by-step instructions.

Using Profiler To Get Power Query Timings

Chris Webb shows us how we can combine DAX Studio with Profiler in order to time our Power Query operations:

And there you have it, exact timings for each of the Power Query M queries associated with each of the tables in your dataset. Remember that the time taken by each Power Query M query will include the time taken by any other queries that it references, and it does not seem to be possible to find out the amount of time taken by any individual referenced query in Profiler.

There is a lot more interesting information that can be found in this way: for example, dataset refresh performance is not just related to the performance of the Power Query M queries that are used to load data; time is also needed to build all of the structures inside the dataset by the Vertipaq engine once the data has been returned, and Profiler gives you a lot of information on these operations too. 

Check it out if you do any work with Power BI.

Implicit Parent Reference On Foreign Keys

Deborah Melkin shows us an interesting way of creating foreign keys:

No matter how long you work with something, you can always find something that you never knew before. I found one about foreign keys this week.

I was reviewing SQL scripts for coworkers and I noticed that the foreign keys were written without referencing the parent table’s column. But the script didn’t fail and created the foreign keys correctly. So how did this work?

I don’t think I’ve ever seen this syntax either. I’m not a big fan of it for the same reason that Deborah isn’t a big fan of it: adding a couple more words does clarify your intent, and so add the words.

Tips For Creating Sample Data Frames

Neil Saunders shares some tips for creating sample data frames, such as when asking questions on Stack Overflow:

For better or worse I spend some time each day at Stack Overflow [r], reading and answering questions. If you do the same, you probably notice certain features in questions that recur frequently. It’s as though everyone is copying from one source – perhaps the one at the top of the search results. And it seems highest-ranked is not always best.

Nowhere is this more apparent to me than in the way many users create data frames. So here is my introductory guide “how not to create data frames”, aimed at beginners writing their first questions.

Read on for a few tips. These are aimed at people asking questions but they’re sound advice in general.

codecentric.ai Bootcamp

Shirin Glander announces a free German-language bootcamp:

This bootcamp is a free online course for everyone who wants to learn hands-on machine learning and AI techniques, from basic algorithms to deep learning, computer vision and NLP. However, the course language is German only, but for every chapter I did, you will find an English R-version here on my blog (see below for links).

Right now, the course is in beta phase, so we are happy about everyone who tests our content and leaves feedback. Also, not the entire curriculum is finished yet, we will update and extend the course during the next months. If there are specific topics you’d like to have us cover, just let us know!

If you understand German and want to learn about data science, check this out and leave feedback.

Building A Calendar Table

I have a post up on building a calendar table:

Another thing to keep in mind here is that you’re only going to load your calendar table once, so if it takes two minutes to do, who really cares? The version I have should run reasonably fast–I calculated 726 years on slow hardware in 19 seconds and fast hardware in 11 seconds. I’m sure you can play code golf and get it done faster, but that’s probably not a good use of your time.

What you want to sweat instead is query time: how long is it taking to access this data?

Click through for a script.

Dynamic Data Masking and Execution Plans

Arthur Daniels looks at what you can see in an execution plan with dynamic data masking:

I think Dynamic Data Masking is pretty cool. The idea is basically to provide a mask for certain users when they might see protected data. The documentation on this feature is actually pretty deep, it’s worth a look.

I just want to show you how you can see the masking in an execution plan. Let’s mask some data in StackOverflow2010! (Also, there’s an interesting side note at the end)

Click through for those notes.

Concerns With DISTINCT

Anvesh Patel does not like DISTINCT:

I am telling you personally that I hate the use of DISTINCT.
DISTINCT used by those people, who are not sure about their data set or SELECT statement or JOINS.
Whenever I get any query with DISTINCT, immediately I suggest to remove it.

I agree with this sentiment about 85% of the time. There are cases where I know l am working with data at a finer grain than I need and the counts aren’t important. But just tossing a DISTINCT on a query to stop it from repeating rows is the wrong approach: figure out why that repetition happens and fix it.

One More Data Gateway Is All You Need

Meagan Longoria explains when you might need data gateways when implementing an Azure BI architecture:

Let’s start with what services may require you to use a data gateway.

You will need a data gateway when you are using Power BI, Azure Analysis Services, PowerApps, Microsoft Flow, Azure Logic Apps, Azure Data Factory, or Azure ML with a data source/destination that is in a private network that isn’t connected to your Azure subscription with a VPN gateway. Note that a private network includes on-premises data sources and Azure Virtual Machines as well as Azure SQL Databases and Azure SQL Data Warehouses that require use of VNet service endpoints rather than public endpoints.  

There are a few of them so check out Meagan’s post and take notes.

Azure VM Boot Diagnostics

John Morehouse shows us how to enable and use boot diagnostics on Azure VMs to troubleshoot why that server isn’t coming up the way you’d expect:

The next blade will show you an active console of the virtual machine.  From here you are able to determine what the current status of the virtual machine might be.  You will also noticed that you can gain access to the serial log (shown below), which will give you more detailed information about the boot process.
Once we click on Boot Diagnostics, we will then see the initial startup screens of the server:

This is useful if you have some huge misconfiguration and the server’s failing for some reason.

Categories

February 2019
MTWTFSS
« Jan  
 123
45678910
11121314151617
18192021222324
25262728