Press "Enter" to skip to content

Day: April 2, 2020

Horizontal Dumbbell Dot Plots in Excel

Published 2020-04-02 by Kevin Feasel

Stephanie Evergreen walks us through an interesting technique for creating dumbbell-style dot plots in Excel:

Ok, babes, prepare to be amazed. It used to be that making this viz was pretty tedious but I’ve recently refined a totally new hack (thanks to a lollipop chart example provided by Sevinc Rende, one of my mentees) that makes this soooooooo easier. It used to be ninja level 9. Now it is ninja level 5, if that.

We will create a dumbbell dot plot out of a stacked bar, where the first stack is composed of our first set of dot values and the second stack is composed of *the difference* between our first and second values (so that it would end at our second values on the x-axis scale). So let’s calculate the difference between the 2020 and 2010 scores.

Read on to see how.

Comments closed

Visualizing a Single Variable in R

Published 2020-04-02 by Kevin Feasel

Michaelino Mervisiano takes us through the types of visuals we can create to understand a single variable in R:

How to create a histogram in R? And what information that we can get from histogram?
Histogram shows a frequency distribution. It is a great graph for showing the mode, the spread, and the symmetry (skewness) of your data. Here is a histogram of 1,000 random points drawn from a normal distribution with a mean of 2.5

Of course I don’t like option number 4 and would replace it with something else (column/bar charts, Cleveland dot plots, or stacked column/bar depending on what you’re trying to observe). But this is a good way of thinking about how you can visualize a variable.

Comments closed

Accessing Blob Storage from Azure Databricks

Published 2020-04-02 by Kevin Feasel

Gauri Mahajan shows how we can read data in Azure Blob Storage from Azure Databricks:

Since our base set-up comprising of Azure Blob Storage (with a .csv file) and Azure Databricks Service (with a Scala notebook) is in place, let’s talk about the structure of this article. We will demonstrate the following in this article:

1. We will first mount the Blob Storage in Azure Databricks using the Apache Spark Scala API. In simple words, we will read a CSV file from Blob Storage in the Databricks
2. We will do some quick transformation to the data and will move this processed data to a temporary SQL view in Azure Databricks. We will also see how we can use multiple languages in the same databricks notebook
3. Finally, we will write the transformed data back to the Azure blob storage container using the Scala API

It’s just a few lines of code. One of the best things Microsoft and the Databricks team did for Azure Databricks was to ensure that it felt like a first-party offering—everything feels a little more integrated than Databricks for AWS.

Comments closed

Obtaining Accurate Totals in DAX

Published 2020-04-02 by Kevin Feasel

Alberto Ferrari explains a nuance of summation in DAX:

In simple DAX measures, the total of a report is the sum of its individual rows. For more sophisticated measures, the total might seem wrong because the formula does not include an aggregation over the rows that are visible in the report. For example, if the total of a measure must be the sum of the values displayed in the rows of a report, we consider the expected result a “visual total”, which is a total that corresponds to the visual aggregation of its values sliced by different rows in the report.

Click through for a straightforward demonstration.

Comments closed

Understanding Point-In-Time Recovery with SQL Server

Published 2020-04-02 by Kevin Feasel

Eduardo Pivaral walks us through what it takes to get point-in-time recovery of data in SQL Server:

Nowadays, data is a precious asset for companies today. If you are a database administrator (by decision or by mistake) or simply you are the “IT guy,” you have the mission of guarantee all the data is backed up and accessible for recovery.

Trust me, even when you could think you have the more reliable hardware on the planet, or you have multiple database replicas around the globe, anything can happen (a user deleting an entire schema by mistake, an application updating the wrong records, some process crashing, a lot of things can happen).
So trust me and don’t question me, just backup all your databases regularly.

During my time as a DBA, I think the most frequent reason for needing point-in-time backups was “We goofed up at 2:20 PM and need to get the database back to that state,” where goof-ups typically involved mass updates or deletes of data.

Comments closed

Auto-Detecting Column Delimiters with Data Factory

Published 2020-04-02 by Kevin Feasel

Mark Kromer shows us a way of dynamically learning what the likely delimiter of a delimited file is:

Processing delimited text files in the data lake is one of the most popular uses of Azure Data Factory (ADF). To define the field delimiter, you set the column delimiter property in an ADF dataset.

The reality of data processing is that delimiter can change often. ADF provides a facility to account for this data drift via parameterization. However, this assumes that you know that the delimiter is changing and what it will change to.

I’m going to briefly describe a sample of how to auto-detect a file delimiter using ADF Data Flows.

Click through for the demo.

Comments closed

Azure AD Passthrough and Password Hash Authentication in SQL DB, DW, MI

Published 2020-04-02 by Kevin Feasel

Mirek Sztajno announces two new security pieces for Azure SQL Database, Azure Synapse Analytics, and Azure SQL Managed Instances:

We are announcing support for Azure AD pass-through and password hash authentication for Azure SQL DB (single database and database pools), Managed Instance, and Azure Synapse (formerly SQL DW).

– Azure AD password hash authentication is the simplest way to enable authentication for on-premises Active Directory users in Azure AD. Users are synchronized with Azure AD and password validation occurs in the cloud using the same username and password that is used in on-premises environments. No additional infrastructure is required.

– Azure AD pass-through authentication provides a password validation mechanism that validate users directly with on-premises Active Directory, outside the cloud. Pass-through authentication does not require ADFS or other third-party federation services.

– Each of these authentication methods can be configured by Azure AD Connect, allowing you to provision users in the cloud.

Read on to see what this means for you.

Comments closed

Which Groups can Set Permissions in Power BI

Published 2020-04-02 by Kevin Feasel

Gilbert Quevauvilliers walks us through the groups which can set permissions in Power BI:

As you can see from above it is good to know which groups can be used to assign permissions in the Power BI Service.

If there is anything I have missed, is wrong or needs updating please let me know via the comments section below.

Thanks for reading!

Gilbert has a nice matrix as well as lots of screenshots establishing the matrix’s veracity.

Comments closed