Press "Enter" to skip to content

Curated SQL Posts

Building a P&L Statement with Power BI

Matt Allington has the need for some financials:

A few weeks ago, Danielle Stein Fairhurst from Plum Solutions contacted me and asked if I would speak at her financial modelling meetup group (a topic of my choice related to Power BI). I decided to speak about creating a P&L using Power BI and DAX. I first learnt how to do this way back in 2015 when I met Derek Rickard. I was still fairly early on my DAX journey at the time and certainly didn’t have any experience with financial accounting using DAX. Derek shared with me how he built his P&L using Adventure Works. I have used these principles many times to help customers, and I thought it would be a great time to pass on that knowledge, and the skills I have learnt along the way on my blog.

Click through for a video and the measures used.

Comments closed

The Siren Song of High Accuracy

Holger von Jouanne-Diedrich notes that accuracy is not in itself necessarily a good thing for a machine learning model:

In one of my most popular posts So, what is AI really? I showed that Artificial Intelligence (AI) basically boils down to autonomously learned rules, i.e. conditional statements or simply, conditionals.

In this post, I create the simplest possible classifier, called ZeroR, to show that even this classifier can achieve surprisingly high values for accuracy (i.e. the ratio of correctly predicted instances)… and why this is not necessarily a good thing, so read on!

The nuanced answer here is that with classifiers, accuracy is not in itself a great measure in the case of class imbalance. The more balanced your classes are, the more likely it is that a model with high accuracy is a good model. That’s where other measures such as specificity and sensitivity, positive & negative predictive value, etc. come into play.

Comments closed

R 4.0 Released

David Smith walks us through what’s new in R 4.0:

R 4.0.0 was released in source form on Friday, and binaries for Windows, Mac and Linux are available for download now.

As the version number bump suggests, this is a major update to R that makes some significant changes. Some of these changes — particularly the first one listed below — are likely to affect the results of R’s calculations, so I would not recommend running scripts written for prior versions of R without validating them first. In any case, you’ll need to reinstall any packages you were using for R 4.0.0. (You might find this R script useful for checking what packages you have installed for R 3.x.)

And I just got 3.6 into production yesterday. Them’s the breaks…

Comments closed

Accessing Managed Instances from SSMS

James Serra shows us what we need to do in order to reach an Azure SQL Managed Instance from SQL Server Management Studio:

It used to be that the only way to use SQL Server Management Studio (SSMS) against Azure SQL Database Managed Instance (SQLMI) was to create a VM on the same VNET as SQLMI and use SSMS on that VM. That VM was usually called a jumpbox (see instructions here).

But about a year ago Microsoft added a way to use SSMS without using a VNET (announcement) by allowing you to enable a public endpoint for your SQLMI. This made it easy for me to access a SQLMI database on my laptop.

That change enables what James shows us.

Comments closed

Performance and T-SQL’s CHOOSE

Grant Frichey answers a question:

Questions absolutely drive my blog content and I really liked this one: how does the T-SQL CHOOSE command affect performance.

On the face of it, I honestly don’t think it will affect performance at all, depending on where and how you use it. However, the answer is always best supplied by testing.

Grant’s post ended up being much more interesting than I had anticipated—my anticipated answer was “It’s a CASE statement so it behaves like a CASE statement.” But there is some nuance that I’ve left out.

Comments closed

Azure SQL Database Tiers

Tim Radney enumerates the tiers available to us with Azure SQL Database:

When Azure SQL Database first launched, there was a single pricing option known as “DTUs” or Database Transaction Units. (Andy Mallon, @AMtwo, explains DTUs in “What the heck is a DTU?“) The DTU model provides three tiers of service, basic, standard, and premium. The basic tier provides up to 5 DTUs with standard storage. The standard tier supports from 10 up to 3000 DTUs with standard storage and the premium tier supports 125 up to 4000 DTUs with premium storage, which is orders of magnitude faster than standard storage.

But there have been several additions since then and Tim lays it out for us.

Comments closed

Scheduling SSIS Packages in Azure

Magi Naumova takes us through the process of running SSIS in Azure Data Factory, including the scheduling of jobs to run our SSIS packages:

The main purpose of these tools is to force the Lift and Shift approach of migrating and running existing SSIS Packages in Azure. I wouldn’t say that this is the most effective approach of transferring the ETL to Azure, but it could be a good start on a road of a Modern Azure Datawarehouse Architecture. If you have already deployed SSIS packages in Azure SSIS Catalog, then SSMS 18 helps you to put them on schedule very quickly.

Running SSIS Packages in Azure requires provisioning of SSIS Runtime Engine, an Azure Data Factory instance and a SQL Database which hosts the SSIS catalog. Scheduling SSIS Packages in Azure requires creating a data flow pipeline in ADF which has a trigger defined for scheduled execution. While describing all those concepts is far above the scope of this chapter, a short description would be useful.

Read on for a good amount of detail and a demo which walks through the process.

Comments closed