DEV Community: Kay Sauter

I'm moderating at Dataweekender conference

Kay Sauter — Thu, 15 Oct 2020 08:40:04 +0000

The Dataweekender conference is coming up again this Saturday Oct 17! After the very successful first Dataweekender conference on May 2nd 2020 with more than 1000 sign-ups and with an excellent speaker lineup, the 6 founders of Dataweekender decided to setup another one this year.

Just like the first time, I will be moderating again this October 17. The conference will be held from 9:00 - 19:00 Central European Summer Time.

Dataweekender is a free to watch and free of sponsorships virtual conference and anyone can join. If you're interested in the schedule and if you're hooked up for watching one or more sessions, sign up here. I'll be on track number 4.

The core organizers of Dataweekender, Kevin Chant, Gethyn Ellis, Ásgeir Gunnarsson, Mark Hayes, Damir Matešić and Magnus Ahlkvist are just like I am, strong supporters of inclusion. No matter gender, race or disability, everyone should be equally able to participate this very nice conference with 6 tracks and 60 talks.

Join us at the conference and have a good time!

You've missed MS Ignite 2020? Don't worry!

Kay Sauter — Thu, 24 Sep 2020 09:51:23 +0000

Yes, while some webcasts of MS Ignite are not recorded or published after the event, some are!

Even if you are a beginner developer, I really can recommend MS Ignite every year because it showcases a lot of ideas that might inspire you in your daily work. I personally try to follow MS Ignite every year because it is delivering so much content you can learn from. I also try to follow Twitterverse during that time because during those days the community is buzzing more than it normally does!

If you don't have the time, just crawl some through some of the videos on YouTube or check the slides on the GitHub.

Make sure that you visit those sites again in the next few days because according to my experience, Microsoft is going to upload sessions one after one in the next few days.

Have fun and enjoy the sessions!

I'd like to thank a lot to everybody who somehow contributed to Microsoft Ignite 2020!

The Art Of Data Visualization

Kay Sauter — Sun, 26 Apr 2020 17:42:17 +0000

Introduction
Pie charts and why not to use them
- A bad example first to get the picture
- How to improve this graph

Introduction

I began telling stories with data in 2008 when I was working for a Japanese electronic manufacturing company. I was in marketing back then and as an technical analyst, I had to cleanse, calculate, and maintain a lot of data in excel. I think most of you reading this have been there, done that: compiling a report for the management who wants to get insights quickly and while not drowning in numbers. They need to be plain and simple graphics; sometimes also called dashboards. This is a skill that any analyst and data scientist should have. This article is going to be a series and split in several postings.

Pie charts and why not to use them

A bad example first to get the picture

Nice pie-chart – or is it? Well, lets go through this pie chart:

Title

Yes, it does have a title! It’s clear that this chart is meant to tell something about sales. But fails to tell what figures are actually shown. Is it Revenue? Profit?

Legend

Yes, it does have a legend, but what about the year? If we don’t know which year we are looking at. The year’s quarter don’t really help either if we know if one or more of them haven’t passed yet because it could be either last year’s quarterly figure or it could be a projection.

Numbers

There are no numbers so there is no way to tell how exactly those quarters are split.

Pie chart

The pie chart is always the wrong choice. You may have realized by now that it is hard to tell which sales were better, in Q2 or Q3? Humans are not good when it comes to comparing areas and especially not when it comes to pie shaped areas. I will go into this in the next chapter.

How to improve this graph

Did I mention that pie charts are bad? It’s not just because human brains are not good at comparing areas but they are wasting of space, too. This chart could have been worse if I had picked a 3-dimensional chart which are even worse, for any chart. That is because if you choose to pick a 3-dimensional chart, you tend to bring in perspectives which makes reading even harder. Below you see the exact
same chart in 3-dimensional chart as before and you’ll notice that Q4 actually
seems quite the same like Q2 and Q3:

Probably you’ve already noticed that displaying Q1..Q4 is a bit dull because you can't compare them to their counterparts from earlier years. In other words, pie charts are incredibly bad in comparing other values. Let’s compare the chart above with a pillar chart:

As you can see, although this pillar chart has the exact same size as the other two charts before, the pillar chart contains much more information. You can see the previous year, the budget and you can also see the quarters that are still in future by representing no values.

Donut or ring chart which are essentially exactly the same as pie charts
plus having a hole in them and perform as bad as the normal pie charts do. Another similar concepts are cockpit charts. They have an arrow in it to point a certain value, sometimes they also have a background colorization from green to yellow to red; but they perform even worse than pie charts because they
display just one single value with no comparison. Oftentimes it is not even clear what the colors are referring or comparing to.

As we can see, pie charts are wasting space for information and on top
of that, they are not easy to read. Readability is what I will cover in
the next chapter which I intend to publish next week.

Edit 24.09.2020: After I've written this and estimated how much more I'd want to write about this topic, I've decided to write a book about about it! So please stay tuned. I don't have all details yet, but as soon as I can, I will write this on dev.to.

Microsoft Data Platform Stack Overview

Kay Sauter — Sun, 12 Apr 2020 16:44:45 +0000

Table Of Contents
Motivation
On-premises software
Cloud-based: Azure tools
Tabular overview from ETL to frontend
Development tools
Programming languages used in Data Platform Stack

Motivation

I vividly remember my very first time years ago when I had to explain a relatively simple business intelligence architecture to some people who never have heard of such architecture before. I've left them confused. Well, I can't blame them for I was talking with too many acronyms like SSAS, SSIS, SSMS, SSRS! During my career, I've learned a lot on how to explain this, but that is still no easy task if you're talking to people who are not familiar with the data world. And because Microsofts data platform family is still growing, this problem doesn't get any easier.

Unfortunately, even though I am very happy with Microsoft's documentation about their data stack, I am yet to find a good overview of all Data Platform Tools which you do not have to click through tons of links. Such one single site would help to get just an overview of the tools and tell concepts to people who are new to this topic.

This is what I aim to do with this post, a simple overview of all tools by Microsoft that somehow are related to the Data Platform Stack. I will focus on software by Microsoft only. Still, because this already contains so many tools, I will probably miss some. If you spot one or you believe that something is wrong, please drop a line in the comments! I will add or correct it and credit you! I also will try to maintain the links and add new tools by Microsoft. This page is also available on Github, so you may create a PR there as well.

On-premises software

Tool Name	Explanation	Costs
SQL Server	A the relational database solution software by Microsoft which is usually installed on-premises. It also can be installed on Virtual Machines in the cloud (Azure).	Licensed per CPU, depending on edition and setup
SQL Server Analysis Services	SSAS is an analytical data engine that supports both multidimensional and tabular models. In short, it sort of aggregates and calculates data for analytical purposes. Usually, it consumes data from a data warehouse.	Licensed per CPU, depending on edition and setup
SQL Server Integration Services	SSIS is the SQL Server feature that ingests and transforms data that are to be loaded into a database, oftentimes to a staging database or a data warehouse.	Licensed per CPU, depending on edition and setup
SQL Server Reporting Services	SSRS is an on-premises service which creates reports and dashboard which may be scheduled and subscribed by users. The focus of this solution is paginated reports.	Licensed per CPU, depending on edition and setup
Excel	Excel is a very powerful spreadsheet software that can present analyze and present data and is worldwide the most widely used software for this purpose. It can be connected to a SQL Server or SSAS or numerous other data sources.	Part of the MS Office package, different editions available
Power BI Premium	A business analytics solution for dashboarding and other reports. The focus of Power BI is non-paginated reports. Its idea is to collaborate and share reports to analysts and other stakeholders with ease. It also can be used on mobile devices, hence having the focus on non-paginated reports and dashboards. It also contains SSRS.	Subscription based
Machine Learning Services	A SQL Server Feature that provides abilities to use the programming languages R or Python for Machine Learning that can be executed directly on SQL Server.	Licensed per CPU, depending on edition and setup
Master Data Services	Manages the Master Data sets which also has something like version control of the master data. It makes it easier to ensure that the master data is as clean as possible.	Licensed per CPU, depending on edition and setup

Cloud-based: Azure tools

Tool Name	Explanation	Costs
Azure SQL	Microsofts SQL Servers pendant in the cloud. Its strengths rely on Azures' versatility and the possibility for elasticity and scaling-up or scaling out and are fully managed by Microsoft.	Depends on various tier plans which depend on CPU, memory, and location.
Azure Data Factory	The Azure data integration solution (ETL & ELT) while being managed by Microsoft. It can also use SSIS packages.	Depends on several factors like service type, tiers, etc.
Azure Cosmo DB	Fully managed by Microsoft, is globally distributed for high availability and high speed and can use SQL and NoSQL alike. It also has elastic and automatic scaling.	Depends on various tier plans which depend on CPU, memory, and location.
Azure Analysis Services	A fully managed analysis service for tabular models on Azure. It is fully managed (Platform-as-a-service) by Microsoft. It does not support multidimensional models, neither PowerPivot for SharePoint.	Depends on various tier plans which depend on CPU, memory, and location.
Azure Machine Learning	Microsofts ML Service on Azure, which features a neat GUI which enables hardcore data scientists and mere mortal business analysts alike. The latter group may use pre-defined packages that were written by experts so that its usage is fairly easy.	Depends on various tier plans which depend on CPU, memory, and location.
Azure Synapse Analytics	Specialised analytics solution for data warehouses and big data for extreme speed. It can work with non-relational data and "traditional SQL" alike; it also provides possibilities for monitoring and data visualization with Power BI.	Depends on various tier plans which depend on CPU, memory, and location.

Tabular overview from ETL to frontend

Data & Integration	Data Storage	Data Computing	Analysis & Presentation
SSIS	SQL Server	SQL Server Analysis Services	Excel
Azure Data Factory	Azure SQL	SQL Server Machine Learning Services	Power BI
Master Data Services	Azure Cosmo DB	Azure Machine Learning	SQL Server Reporting Services
		Azure Analysis Services	Azure Synapse Analytics
			Azure Machine Learning
			SQL Server Machine Learning Services

Development tools

Tool Name	Explanation	Costs
SQL Server Management Studio	SSMS is a Software to manage and query your SQL Server instances and databases.	Free
Azure Data Studio	ADS enables to manage and query SQL Server instances and databases with a focus on Azure. It is not as powerful as SSMS but has other strengths than SSMS, i.e. it can use Jupyter Notebooks.	Open Source & free
Visual Studio	An Integrated Development Environment which is used for developing SSAS and SSIS solutions, but also can develop databases although the latter is not so commonly used that way. Usually, SQL Server developers would prefer SSMS.	Community Edition free, other editions not
SQL Server Data Tools	SSDT which is a toolset living in Visual Studio. It enables you to create workflows for SSIS and create cubes (multidimensional and tabular for SSAS) and does not require a higher edition than community edition. SSDT exists as standalone software (shell) or within Visual Studio as an add-on.	Free
Visual Studio Code	A lightweight editor that allows installing an addon-for SQL Server for. It enables you to write a T-SQL Code similar to Azure Data Studio.	Open Source & free
Extended Events	Enables to monitor (tracing) of events on SQL Servers, etc. for troubleshooting or solving performance bottlenecks.	Free (a feature of SQL Server, no extra costs for extended events)
SQL Server Report Builder	This is a report builder for SSRS. It looks pretty much like Visual Studio minus the complexity Visual Studio gives because it is a complete and powerful IDE (Integrated Development Environment).	Free
Power BI Desktop	Software for creating dashboards and reports that may be connected to various sources, including SQL Server, SSAS and a lot more. Allows writing code in R and Python.	Free

Programming languages used in Data Platform Stack

Language Name	Explanation
T-SQL	Transact-SQL, the Structured Query Language variant for SQL Server which offers a rich and powerful automatization language for querying and administration of SQL Server
PowerShell	A shell language for administring Windows, but there are also many important packages for the Data Platform Stack.
Python	A widely-used general-purpose scripting language which is very popular for the use of Machine Learning and Data Science. SQL Server can execute Python directly. It can also be used in PowerBI Desktop or Azure ML.
R	A very popular functional language which focuses on statistical calculations and is helpful in Machine Learning and Data Science. SQL Server can execute R directly. It can also be used in PowerBI Desktop or Azure ML.
DAX	Data Analysis Expressions is a functional language used for calculations on SSAS. It is used in SSMS, Visual Studio, PowerBI Desktop and Excel.
MDX	Multidimensional Expressions allows us to query multidimensional cubes on SSAS.

Cover photo by Kolar.io on Unsplash

Creating SQL Server on Linux with Docker is surprisingly easy!

Kay Sauter — Sun, 05 Apr 2020 16:20:38 +0000

A few weeks ago, I wanted to test out a problem for SQL Server within a virtual machine, but then I thought: "Why not on Linux? That's what I've been wanting to try for ages and I could do it on Docker which I also wanted to try for ages". I've put it aside thinking that this could take quite some time until I've done it.

I was wrong. It is surprisingly easy to get a docker image by Microsoft. Since Microsoft already has a tutorial for this, I am just going to point to this tutorial and give more links for this really neat stuff! This small collection of links will make sure that you will see the most important sites you need for this.

The first link is a tutorial by Microsoft: https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-ver15&pivots=cs1-bash
If you prefer to watch a youtube video done by an expert, watch the video by Microsoft Data Platform MVP Grant Fritchey here: https://www.youtube.com/watch?v=nHI3fxoS5kw
Keep in mind that this docker image runs on Linux, so SQL Server also runs on Linux. While this is possible, it comes with some limitations though: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-editions-and-components-2019?view=sql-server-ver15#Unsupported
There is also a FAQ by Microsoft as to questions like licensing etc: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-faq?view=sql-server-ver15

Have fun!

Introduction into Window Functions on SQL Server

Kay Sauter — Sat, 07 Mar 2020 14:53:01 +0000

Why window functions may come in handy

Let us dive directly into a real world example. You've got a table with salaries of different job functions and you are asked to find out what the n-tiles (quantiles, mean etc.) are of each function within a company. So this means you've got many people that hold a position which you want to compare with others. You want to compare salaries of marketing professionals to their peers, but you don't want to compare said salaries to marketing directors because they have their own salary bands. So that means you need to frame (also called partition) job titles with their own with which you calculate the n-tiles. Without window functions, this could be a quite fussy task.

In order to walk through the problem, create a new database, I'm going to name mine FoehnWind. The following script creates a new table and inserts some sample data of 20 rows into it.

Tutorial

USE [FoehnWind];
GO
CREATE TABLE dbo.Employee
(ID           TINYINT IDENTITY(1, 1) NOT NULL, 
 FirstName    VARCHAR(150) NOT NULL, 
 LastName     VARCHAR(150) NULL, 
 JobTitle     VARCHAR(100) NOT NULL, 
 AnnualSalary INT NOT NULL, 
 CONSTRAINT [PK_dbo.Employee] PRIMARY KEY CLUSTERED(ID ASC)
)
ON [PRIMARY];
GO
INSERT INTO [dbo].[Employee] 
VALUES
 ( 'Ken','Sanchez','Chief Executive Officer', 530000)
,( 'Jo','Berry','Marketing Assistant', 71875 )
,( 'Lori','Penor','Marketing Assistant', 73398 )
,( 'Pat','Coleman','Marketing Assistant', 51874 )
,( 'Stuart','Macrae','Marketing Assistant', 59108 )
,( 'Mary','Dempsey','Marketing Assistant', 57641 )
,( 'Wanida','Benshoof','Marketing Assistant', 60779 )
,( 'David','Bradley','Marketing Manager', 130000 )
,( 'Jill','Williams','Marketing Specialist', 108117 )
,( 'John','Wood','Marketing Specialist', 99407 )
,( 'Sariya','Pit','Marketing Specialist', 95048 )
,( 'Terry','Eminhizer','Marketing Specialist', 106316 )
,( 'Michael','Sullivan','Marketing Specialist', 87047 )
,( 'Ovidiu','Cracium','Marketing Specialist', 107883 )
,( 'Rob','Walters','Marketing Specialist', 109369 )
,( 'Garrett','Young','Senior Marketing Specialist', 118051 )
,( 'George','Li','Senior Marketing Specialist', 117253 )
,( 'Russell','King','Senior Marketing Specialist', 123362 )
,( 'Sandra','Alayo','Senior Marketing Specialist', 118185 )
,( 'Denise','Smith','Vice President Marketing', 250000 );
GO

Firstly, lets use a window function which calculates the averages and percentiles of salaries for each job title:

SELECT distinct e.JobTitle
        ,AVG(e.AnnualSalary) OVER (PARTITION BY e.JobTitle) AS AverageSalary
        ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY e.AnnualSalary) 
            OVER (PARTITION BY e.JobTitle) as MedianContinous
        ,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY e.AnnualSalary) 
            OVER (PARTITION BY e.JobTitle) as MedianDisContinous
FROM dbo.Employee e
order by e.JobTitle

You perhaps now wonder how the functions PERCENTILE_CONT() and PERCENTILE_DISC differ. The discontinous variant returns an actual value that exists in the calculated set and pointing out that this values is the closest one being to the calculative median. The continous percentile in contrast returns a value that is actually the calculated median. For example, Marketing Assistant Stuart Macrae has an Annual Salary of 59'108. This amount is also the median in this job role, but the calculated median is 59'943.5 so Stuart is slightly below the median but his salary is the closest one to the median.

JobTitle	EmployeeName	AnnualSalary	AverageSalary	MedianContinous	MedianDisContinous
Chief Executive Officer	Ken Sanchez	530000	530000	530000	530000
Marketing Assistant	Pat Coleman	51874	62445	59943.5	59108
Marketing Assistant	Mary Dempsey	57641	62445	59943.5	59108
Marketing Assistant	Stuart Macrae	59108	62445	59943.5	59108
Marketing Assistant	Wanida Benshoof	60779	62445	59943.5	59108
Marketing Assistant	Jo Berry	71875	62445	59943.5	59108
Marketing Assistant	Lori Penor	73398	62445	59943.5	59108
Marketing Manager	David Bradley	130000	130000	130000	130000
Marketing Specialist	Michael Sullivan	87047	101883	106316	106316
Marketing Specialist	Sariya Pit	95048	101883	106316	106316
Marketing Specialist	John Wood	99407	101883	106316	106316
Marketing Specialist	Terry Eminhizer	106316	101883	106316	106316
Marketing Specialist	Ovidiu Cracium	107883	101883	106316	106316
Marketing Specialist	Jill Williams	108117	101883	106316	106316
Marketing Specialist	Rob Walters	109369	101883	106316	106316
Senior Marketing Specialist	George Li	117253	119212	118118	118051
Senior Marketing Specialist	Garrett Young	118051	119212	118118	118051
Senior Marketing Specialist	Sandra Alayo	118185	119212	118118	118051
Senior Marketing Specialist	Russell King	123362	119212	118118	118051
Vice President Marketing	Denise Smith	250000	250000	250000	250000