Press "Enter" to skip to content

Month: November 2023

Azure Database for MySQL Flex Server and Power BI

Published 2023-11-22 by Kevin Feasel

Denny Cherry dips a toe into unfamiliar waters:

Recently, I upgraded our core Azure SQL Database for MySQL Single Server to Azure SQL Database for MySQL Flex Server. The migration was pretty straight forward and I simply did an export with mysqldump and then an import with MySQL. The problem came up when I tried to use Power BI (PBI) and connect to to the Azure SQL Database for MySQL Flex Server instead. When I tried to connect PBI to the Flex server I kept getting “The given key was not present in the dictionary”.

Read on to see what Denny did to resolve the issue.

Comments closed

Starting a Free Trial of Microsoft Fabric

Published 2023-11-22 by Kevin Feasel

Andy Leonard kicks off a trial:

Are you interested in learning more about Microsoft Fabric?

One way to begin tinkering with the new platform is to start a free trial. At the time of this post, a free trial is available here:

Read on for instructions on how to try Fabric out. Now that Fabric is in GA, you’ll have to pay once the trial is over, but this does at least give you some time to check it out before then.

Comments closed

Flat File Importation via Azure Data Studio

Published 2023-11-22 by Kevin Feasel

Josephine Bush needs to import a file:

Initially, I thought I would have to use sqlcmd because I’m on a Mac and don’t have SSMS. It turns out Azure Data Studio has a nifty way to import data from flat files – yay!

I’ve used this extension a few times in the past on Linux and Windows and it’s pretty good, especially if you have a fairly straightforward flat file. If it’s a messy file, you’ll still get inscrutable errors. And, as far as data sources go, GIGO.

Comments closed

Computing Accurate Percentages in Power BI with Row-Level Security

Published 2023-11-22 by Kevin Feasel

Marco Russo and Alberto Ferrari don’t want to let any information slip out:

Let us start with a simple challenge: we want to show the percentage of sales in Europe, compared to the sales made to all customers worldwide. It is a relatively trivial question, the kind of DAX code you learn at the beginning of your Power BI career, and it can be solved with a simple measure:

But what happens when the model includes security roles? Well, that’s what you’ll have to read on to learn.

Comments closed

Visualizing JSON Files in Fabric Notebooks

Published 2023-11-22 by Kevin Feasel

Sandeep Pawar wants readability:

JSON is ubiquitous, particularly when working with APIs and logs. Its unstructured nature makes it highly flexible for handling anything from a simple array to a complex nested structure. However, this can also make it challenging for data analysis. When parsing JSON, it’s crucial to understand its structure so you can flatten it and convert it into a tabular format for analysis. Once the structure is identified, you can use pandas or PySpark to explode or normalize it into the desired shape. In this article, I will explain the method I use. While this approach is applicable to any notebook, there is a specific trick to make it work in a Fabric notebook.

Read on for that trick.

Comments closed

Quadratic Regression in R

Published 2023-11-21 by Kevin Feasel

Steven Sanderson needs more than a line:

In the realm of data analysis, quadratic regression emerges as a powerful tool for uncovering the hidden patterns within datasets that exhibit non-linear relationships. Unlike its linear counterpart, quadratic regression ventures beyond straight lines, gracefully capturing curved relationships between variables. This makes it an essential technique for understanding a wide range of phenomena, from predicting stock prices to modeling population growth.

Embark on a journey into the world of quadratic regression using the versatile R programming language. We’ll explore the steps involved in fitting a quadratic model, interpreting its parameters, and visualizing the results. Along the way, you’ll gain hands-on experience with this valuable technique, enabling you to tackle your own data analysis challenges with confidence.

Read on to see how you can model a quadratic relationship between one independent variable (or multiple independent variables) and the dependent variable in lm().

Comments closed

Fixing a Double Donut Chart

Published 2023-11-21 by Kevin Feasel

Amy Esselman drags a donut chart:

Here we have not one, but two donuts! It takes a while to figure out what this data represents. Based on the title, it appears that we’re meant to compare the categories of the donuts across the two charts. With the sections in different places, this is rather challenging. 

One may argue that the colors and donut form make the data more visually interesting; however, this often hinders our ability to understand the data. Let’s look at some strategies to make this information easier to comprehend.

Read on for advice around converting two donut charts into something humans can more easily interpret.

Comments closed

Getting Started with Microsoft Fabric

Published 2023-11-21 by Kevin Feasel

Eugene Meidinger is ready to be at the intersection of Dunning-Kreuger and Imposter Syndrome:

I’ve written before about struggling to learn Azure Synapse, and I’ve struggled as well with getting excited about Microsoft Fabric. I think the pitch and the potential of Microsoft Fabric is real. The issue is that it solves problems I don’t have. In my work, I don’t deal with data so big that Power BI can’t handle it. I don’t deal with data so unstructured that Power Query can’t handle it.

But I know I need to learn Fabric. Power BI is a part of Fabric, the integrations are only going to continue to improve. If nothing else, I need to be able to tell customers if they should look into using Fabric or not. So what do you do when there is a technology you aren’t excited about, but have to learn?

Read on for Eugene’s scenario, which is certainly more interesting than Adventure Works.

Comments closed

Strategies for Filtering on Bit Columns

Published 2023-11-21 by Kevin Feasel

Aaron Bertrand answers a question:

Recently someone posted a question where they couldn’t quite figure out how to construct a predicate based on a bit parameter. They tried to write a procedure like this, which wouldn’t parse, of course:

At first, I thought Aaron meant querying integer bitmasks in T-SQL, in which case, the best strategy is “don’t.” But this is a different and much more useful scenario.

Comments closed

The State of Microsoft Fabric

Published 2023-11-21 by Kevin Feasel

Reitse Eskens shares some advice:

Last week the big announcement came at Microsoft Ignite, Fabric is GA.

Very cool, a lot of noise again for this shiny toolbox, but do we need to abandon everything and focus solely on the new toys?

Before I’ll answer that question, let’s look at a few moving parts of Fabric.

I think it’s still 1-2 years out from being fully baked. My hope is that there are (or will be) enough pieces in place to make it useful for enough scenarios that people don’t notice the gaps too much. There’s a lot of potential here and I don’t want Fabric to end up with a reputation of “too much stuff is missing to use it” because that reputation is hard to shake.

Comments closed