Press "Enter" to skip to content

Day: December 3, 2019

Changes to EC2 Metadata Service

Praveen Sripati takes a look at changes to the AWS EC2 Instance Metadata Service following attacks against Capital One and dozens of other organizations:

Captial One Bank (1) and 30 different organizations were hacked around end of July, I have written a blog (1) around the same time on how to recreate the hack in your own AWS account and also a few mitigations around the same. Now, AWS has made a few changes to the AWS EC2 Instance Metadata Service (IMDS) around the same (12). AWS re:Invent 2019 session (1) around the same has also been planned on December 5th, 2019. Will update with the link once the recording of the session has been uploaded.

The old/existing approach is called IMDSv1 and the new one IMDSv2. Although IMDSv1 solves a few problems like not storing the access keys on the EC2, it bought its own headaches which lead to the hacks.

Click through to see what these problems were and how they led to IMDSv2.

Comments closed

Understanding the Eager Index Spool

Paul White gives us a lesson on eager spools:

Index spools do not tell the optimizer they support output ordered by the spool’s index keys. If sorted output from the spool is required, you may see an unnecessary Sort operator. Eager index spools should often be replaced by a permanent index anyway, so this is a minor concern much of the time.

There are five optimizer rules that can generate an Eager Index Spool option (known internally as an index on-the-fly). We will look at three of these in detail to understand where eager index spools come from.

Read on for a detailed discussion of eager spools.

Comments closed

Using PowerPoint to Create Power BI Layouts

Jon Fletcher has a good tip for snazzing up a Power BI dashboard:

First question, why bother with layouts?
Using layouts in Power BI allows a user to make their visuals stand out better, the page looks professional and more appealing to its audience.

Second question, why PowerPoint?
The default page size in Power BI desktop is 16:9, (this trick doesn’t work for other Power BI page sizes), which is identical to a PowerPoint slide.
Therefore whatever is designed in PowerPoint will fit onto a Power BI page perfectly. Also PowerPoint is very easy to use; most people are familiar with it.

Click through for an example. It’s easy to go overboard with this, but Jon does a good job of using a muted color so that the edges don’t overwhelm your eyes. I might knock it down a shade or two further from that, but regardless, this is a nice tip.

Comments closed

Forking GitHub Repos and Contributing to Open Source Projects

Rob Sewell takes us through the process of contributing to an open source project:

– Fork the repository into your own GitHub

– Clone the repository to your local machine

– Create a new branch for your changes

– Make some changes and commit them with useful messages

– Push the changes to your repository

– Create a Pull Request from your repository back to the original one

You will need to have git.exe available which you can download and install from if required

For bonus points, we learn that Shane O’Neill doesn’t use the Oxford comma.

Comments closed

Shrinking Your Power BI Dataset Sizes

Gilbert Quevauvilliers wanted to reduce Power BI memory usage:

I had already applied all the best practices in terms of reducing the cardinality, removing unwanted columns and making sure that only the data required is being brought into the dataset. Even at this point the dataset size was consuming 90GB of memory in Azure Analysis Services. With the steps below I got my dataset size down to a whopping 37GB of memory!

I used the awesome tools from SQLBI.COM and DAX Studio to see which columns were consuming the most space, and because my dataset had currency converted values, this meant that the cardinality was very high. (The reason that I decided to store the currency conversion values, is when trying to do it on the fly in a large dataset it is very slow)

Two simple tricks led to a pretty nice reduction in size.

Comments closed

A Git Cheat Sheet

Kendra Little has a cheat sheet for working with Git:

I created a cheat sheet for the Git Command Line Interface to go along with my Git tutorial for SQL Change Automation video. I find the Git CLI to be very friendly and easier to learn than a GUI interface.

Given the number of “How do I extricate myself from this Git mess?” messages in my company chat, I’m not sure I’d call the Git CLI friendly. Nonetheless, Kendra does a great job of putting together most of the common commands in an easy guide.

Comments closed

Creating an Azure Data Factory

Cathrine Wilhelmsen continues a series on Azure Data Factory:

In the introduction to Azure Data Factory, we learned a little bit about the history of Azure Data Factory and what you can use it for. In this post, we will be creating an Azure Data Factory and getting familiar with the user interface.

Spoiler alert! Creating an Azure Data Factory is a fairly quick click-click-click process, and you’re done. But! Before you can do that, you need an Azure Subscription, and the right permissions on that subscription. Let’s get that sorted out first.

This post is all about setup and getting an overview of the ADF canvas.

Comments closed