Press "Enter" to skip to content

Curated SQL Posts

An Overview of Partitioning and Sharding in Postgres

Published 2023-08-07 by Kevin Feasel

Michael Christofides defines terms:

It has been possible to do partitioning in PostgreSQL for quite a while — splitting what is logically one large table into smaller physical tables. Since version 10, a huge leap was made with the introduction of declarative partitioning, and more improvements have come every year since.

Sharding is a different story — splitting what is logically one large database into smaller physical databases. The primary tool for this in the PostgreSQL ecosystem is the Citus extension. But you can also handle the sharding logic at the application level, as recent posts from the likes of Notion and Figma have described. Somewhat confusingly, some forms of sharding are sometimes referred to as vertical partitioning, including by the team at Figma.

Read on for a few thoughts on when to perform each action and what the costs and benefits are.

Comments closed

Visualizing Snowflake Geospatial Data with Power BI

Published 2023-08-07 by Kevin Feasel

Rebecca O’Connor builds a map:

Power BI can leverage Geospatial data from snowflake with my favourite map visual – Iconmap – https://www.icon-map.com/ . Icon map can render points, polygons and linestrings using ‘Well Known Text’ format (WKT).

Snowflake supports converting geospatial datatypes to WKT. Not only this, Snowflake has the capabilities to perform the Engineering and analytical needs for Geospatial analysis without using any other tool. And the results can be visualised in a variety of medias such as Tableau, Hex, Carto or even a Custom built Streamlit application. I have written a Streamlit blog on this very recently.

Click through for information on how to get the data shaped in a way that Power BI likes.

Comments closed

Diving into the New Purview Portal

Published 2023-08-04 by Kevin Feasel

Wolfgang Strasser takes screenshots:

In order to access the new version of the Purview portal, you need to migrate/upgrade you existing Purview account to the new, one account per tenant model. You can find more information about that process in the documentation (https://learn.microsoft.com/en-us/purview/account-upgrades)

Wolfgang shows off what you get if you do make this migration or create a new account.

Comments closed

Blocking Trees in sp_HumanEventsBlockViewer

Published 2023-08-04 by Kevin Feasel

Erik Darling talks up a famous Canadian:

I know there’s been a lot of blog content about the creation of and updates to my stored procedures lately. I try to consolidate as much as possible, but this one comes from Valued Contributor©.

The one, the only, the Canadian: Michael J Swart (b|t) recently contributed a really cool piece of code that I’ve been dying to have since I created sp_HumanEventsBlockViewer: the entire blocking chain is visualized in the results.

Click through to see what it looks like, and next time you see Michael, give him a thumbs up.

Comments closed

Connecting to SQL Server 2022 via Azure AD

Published 2023-08-04 by Kevin Feasel

Deepthi Goguri makes a connection:

Applicable to-

SQL Server 2022 on-prem on Windows and Linux and SQL Server 2022 hosted on Windows Virtual Machines.

Once you install the SQL Server, there are three different authentication methods that you can use to connect SQL Server along with the Windows and SQL Server authentication. They are –

  1. Azure Active Directory Universal with Multi-Factor Authentication
  2. Azure Active Directory Password
  3. Azure Active Directory Integrated

Read on for the pre-requisites as well as a detailed guide on how to set everything up.

Comments closed

Logging Notebook Runs in Microsoft Fabric

Published 2023-08-04 by Kevin Feasel

Reitse Eskens checks the logs:

I reported an issue yesterday with Microsoft Support and during the following call today (they’re really quick to set up an initial meeting), the support engineer showed me where I can find a lot of logging information.
Suppose you’ve got a notebook that has been run a few times. The front-end will only retain the information from the last run. If you see an error, for example this one

Click through to learn where you can find these execution logs.

Comments closed

Range-Based Groupings in Power BI

Published 2023-08-04 by Kevin Feasel

Gilbert Quevauvilliers pulls back the curtain:

I was once again working with the grouping feature in Power BI Desktop.

This time I had a lot of values that I wanted to put into multiple groups. I also did not want to select the individual values (I have previously shown this in my blog post Creating a group with all values (Even if not in the data) in Power BI)

It is amazing that sometimes it takes me a while to fully understand how to use some features in Power BI and today was one of those days!

Click through to see what Gilbert learned and how you can use that information to group by ranges instead of individual values.

Comments closed

Business Problems and Business Solutions

Published 2023-08-04 by Kevin Feasel

Kurt Buhler tells a story:

Bink the Data Goblin is a Data Analyst who supports her department by making, maintaining and helping others with Power BI solutions. Bink is quite proficient at Power BI, and is recognized in her user community as a go-to person for answers to Power BI questions. Recently, Bink was promoted to a position in their Center of Excellence to help her department make the most of Power BI.

One day, Bink receives a message on Teams from Bonk the Business Goblin. Bonk is an Analyst who works in Finance and is typically responsible for the reporting on behalf of the Finance team. Bonk asks…

I think some of this is a little idealized (or I’m a bit cynical) but it’s a good reminder that the technical domain is important but not the only thing to think about.

Comments closed

Simplifying Nested Lists and Vectors in R

Published 2023-08-03 by Kevin Feasel

Steven Sanderson simplifies things:

Today, we’re diving deep into the incredible world of R programming to explore the often-overlooked but extremely handy unlist() function. If you’ve ever found yourself dealing with complex nested lists or vectors, this little gem can be a lifesaver. The unlist() function is like a magician that simplifies your data structures, making them more manageable and easier to work with. Let’s unlock its magic together!

Click through to see how it works, including explanation and examples.

Comments closed

Microsoft Fabric Licensing and Capacity

Published 2023-08-03 by Kevin Feasel

Aimee Johnson explains how Microsoft Fabric licensing works:

Microsoft Fabric is a Software-as-a-Service platform (SaaS) which enables you to build an end-to-end analytics solution without the need to spin up complex infrastructure. If you want to know more about Microsoft Fabric then check out our introduction blog post which you can find here.

Since Microsoft Fabric has been announced there have been many questions and queries about licensing and in this blog post I will go through everything you need to know!

One thing I keep forgetting is that Power BI Premium P1 is equivalent to Fabric F64; I keep wanting it to be F32 or F16, but that’s because I’m frugal that way.

Comments closed