Press "Enter" to skip to content

Curated SQL Posts

Embedding Power BI into PowerPoint

Published 2022-08-16 by Kevin Feasel

Matt Allington integrates a Power BI report into PowerPoint:

I first blogged about this back in October 2021 when Microsoft announced live Power BI embedding was coming to PowerPoint. Believe it or not, the ability to embed Power Pivot reports into PowerPoint was one of the first features delivered by Microsoft way back in 2014 or 2015. It used to be possible with the first release of Power Pivot for Excel and SharePoint Enterprise Edition. Sometime after releasing this feature, Microsoft refocussed its efforts away from Power Pivot/SharePoint Enterprise and started to re-build again from scratch as a new standalone BI app known to us today as Power BI. It then took another 7 years for this PowerPoint feature to be returned. It’s here now, so let me cover what it is and how you can use it.

I’d joke about how much of an atrocity this is but it really isn’t. Thinking about how many meetings get derailed by the person trying to leave PowerPoint, struggling to open another application, having things fall apart, and then going back to the slide deck (inevitably from the beginning rather than the current slide), this is a good idea.

Comments closed

A Wake for Distributed Replay

Published 2022-08-16 by Kevin Feasel

Grant Fritchey thinks about what could have been:

Honestly, sincerely, no kidding, I love Distributed Replay. Yes, I get it. Proof positive I’m an idiot. As we needed proof. To be a little fair to me, I love what Distributed Replay could have been, with a little more love. However, fact is, it’s on the deprecation list for 2022. Which means, what minimal amount of love, if any, that Microsoft was giving to it, it’s all gone, forever. Unlike the Little Engine That Could, turns out that Distributed Replay was the Little Engine That Almost Could, But Didn’t. Really Didn’t. Let’s discuss it a bit.

I liked the concept a lot but the tooling was so finicky and there were just so many built-in assumptions that tended to fall apart in real life. Grant actually got it to work outside of toy environments, which was one step further than I could ever get.

Comments closed

Attaching All SQL Server Data Files in a Single Directory

Published 2022-08-16 by Kevin Feasel

David Fowler migrated a bunch of databases:

Have you ever had the need to attach a large number of database in one go? There’s no way to attach multiple databases in SSMS or via script, so you’re probably going to be left with the slow, arduous task of doing them one by one.

I recently had to deal with a DR situation (I won’t go into details of what happened just yet as things are still quite sensitive, but I might look at it at some point in the future) where I faced exactly that issue. For one reason or another I needed to attach several hundred databases quickly. I didn’t fancy doing that via SSMS or script each one individually so I knocked together this script to do the job for me.

Click through for that script and instructions. Alternatively, a bit of Powershell and the right dbatools command could get you to the same result but this is good in the event that you can’t leave SSMS.

Comments closed

Comparing Pre- and Post-Event in Power BI

Published 2022-08-16 by Kevin Feasel

Marco Russo and Alberto Ferrari do a bit of comparative analysis:

Many article ideas come from questions we receive from our customers and readers, and this article is no exception. The requirement is to analyze the sales volume before and after a selected time period, to a specific customer segment. In this case we define the segment as the brand that the customers have purchased in the past. For example, considering the customers who purchased Contoso-branded products in February 2018, what is the Sales Amount of Contoso and other products sold to those same customers 180 days before and after February 2018?

Read on to find out.

Comments closed

Solving the Traveling Salesman Problem in R

Published 2022-08-15 by Kevin Feasel

Tomaz Kastrun gives us a solution to the Traveling Salesman Problem:

Travelling Salesman Problem is an NP-complete problem and an old mathematical problem. For this useless function, we will look for the nearest city from the previous city (or starting point) and repeat until we visit all cities. The greedy solution is fairly simplified but one disadvantage; it might not give you the best path (optimal solution) and proving that the solution is correct is an additional issue 

As Tomaz notes, this is not guaranteed to be the best solution, just a solution. Considering that TSP is NP-hard, if Tomaz did have a globally optimal solution for us, he certainly wouldn’t be calling it ‘useless-useful’ but instead would be calling it “My prize-winning algorithm.”

Comments closed

Building a Data Serving API in Azure

Published 2022-08-15 by Kevin Feasel

Justice Zishanhi has some recommendations for serving data in Azure:

Data is an important asset to all organizations big and small. As these organizations mature, building an end-to-end data platform to enable BI and AI at scale has become part of that journey. Some organizations, have the requirement to expose modelled data in a data warehouse or data lake (Azure Data Lake Storage Gen2) to downstream consumer applications (mobile or web apps) where access patterns can be unpredictable in respect to frequency of access and/or type of data that is requested.

Data warehouse engines and data lakes are not designed for singleton transactional (request / response) interactions.  To serve these requests at scale and to meet the different SLAs and access pattern unpredictability, data needs to be offloaded to a suitable database engine (i.e., a caching layer) that is built to serve such queries.  

The “Design Patterns” section of this article highlights a generalized pattern for implementing a data serving API which meets this requirement – consisting of a Data Platform component and an API component. For implementing the API, two patterns are commonly adopted – a synchronous pattern or an asynchronous pattern. Both are explored in the “API Implementation Patterns” section of this article.

The example focuses on Cosmos DB and provides quite a bit of helpful guidance.

Comments closed

Converting a Referenced Query Column to a Table

Published 2022-08-15 by Kevin Feasel

Erik Svensen does a bit of magic:

Let’s imagine I have a query with a column called Reportid – and I want to create another query with the unique values from the column.

Normally I would create a new blank query and than reference the column with – queryName[ColumnName]

And then there are a bunch of other steps which eventually lead you to the result. Or you can go straight to easy mode. That sounds like a pretty good idea to do instead.

Comments closed

SQL Server 2022 and S3 Object Integration

Published 2022-08-15 by Kevin Feasel

Anthony Nocentino does some PolyBase work:

In this blog post, I’ve implemented two example environments for using SQL Server 2022’s s3 object integration. One for backup and restore to s3 compatible object storage and the other for data virtualization using Polybase connectivity to s3 compatible object storage. This work aims to get you up and running as quickly as possible to work with these new features. I implemented this in Docker Compose since that handles all the implementation and configuration steps for you. The complete code for this is available on my GitHub repo…I’m walking you through the implementation here in this post.

In my post Setting up MinIO for SQL Server 2022 s3 Object Storage Integration we did this step by step at the command line. Using Docker Compose it will do all the hard work for you and you can get up and running fast.

Let’s walk through what you’ll get in each environment.

Read the whole thing, especially the note at the bottom about minimum RAM requirements on the Docker container.

Comments closed