Press "Enter" to skip to content

Curated SQL Posts

Configuring Power BI Incremental Refresh

Gilbert Quevauvilliers has a follow-up from a post:

Following on from my successful blog post How you can incrementally refresh any Power BI data source (This example is a CSV File), I found a way where I can just use dates created in Power Query to get data refreshing incrementally.

Full credit goes to Rafael Mendonç who actually figured this out. All that I have done is to translate what Rafael Mendonça did in his PBIX and put it into steps that you can follow along with.

In this blog post I am going to demonstrate how to get this working with what I hope is very easy to follow.

Read on for the process.

Leave a Comment

Truncating All Tables in a Database with Powershell

Jess Pomfret nukes the database from orbit, as it’s the only way we can be sure:

The most popular post on my blog so far was called ‘Disable all Triggers on a Database’ and this one is a good follow up from that post.

The scenario here is you need to remove all the data from the tables in your database. This could be as part of a refresh process, or perhaps to clear out test data that has been entered through an application.  Either way, you want to truncate all the tables in your database.

Click through for the code.

Leave a Comment

Keep Parameter Sniffing On

Brent Ozar explains why you should keep parameter sniffing on:

What they THINK is going to happen is that SQL Server will do an OPTION(RECOMPILE) on every incoming query, building fresh plans each time. That ain’t how this works at all, and instead, I wish this “feature”‘s name was “Parameter Blindfolding.” Here’s what it really does.

Read on for the explanation. In reality, parameter sniffing is almost always a good thing. It’s when you have major skews in data that you even have to think about parameter sniffing being a problem.

Leave a Comment

Tips for Improving Power BI Dashboards

Tino Zishiri has a set of tips to design better-looking dashboards:

There are several reasons why you should design great looking dashboards. Here are a few;

– They make information more accessible – end users benefit from an intuitive design that makes insight easy to obtain so they can make informed decisions.
– They help convey your message – you’re in a better position to tell a coherent story. Applying design principles can also help accentuate your message. My colleague Kalina Ivanova has written an excellent series of blogs on Data Storytelling with Power BI.
– They encourage user adoption – if a report is useful to users and has a great look and feel then you’re winning.

In this blog, I’ll briefly cover the building blocks that make up a good Power BI dashboard. I then explore the stepping stones that will level up your dashboard and take it from good to great.

One area where I do have some disagreement is that the Z and F layouts are fine for text-heavy formats, but generally “text-heavy” and “dashboard” don’t go together very well. My preference is the notion of focal points (go about 3/4 of the way down, to the section entitled “Where We Look”), which works much better at describing eye behavior for image-heavy layouts. That aside, I like this post a lot.

Leave a Comment

Cost-Cutting in Confluent Platform

Nick Bryan shares some techniques for reducing the cost of running on Confluent Platform:

To start, there are several Confluent Platform features that can greatly reduce your Kafka cluster’s infrastructure footprint. For use cases involving high data ingestion rates, lengthy data retention periods, or stringent disaster recovery requirements, Confluent Platform can help to reduce infrastructure costs by up to 50%.

One of the most important features for this cost category is Tiered Storage.

Read on for a few tips.

Leave a Comment

Good Practices for Naming Things in Power BI

Chris Webb shares some thoughts on the power of names:

What’s wrong with this picture? Look at the names:

– The tables and columns have the same names that they had in the data source, in this case a SQL Server database. Note the table name prefixes of “Dim” for dimensions and “Fact” for fact tables.
– The column and measure names either don’t have spaces or use underscores instead of spaces.
– What on earth does the measure name _PxSysF even mean?

Chris mentions that some of the ideas in the post may be controversial, but to be honest, I don’t think any of them are. The important thing here is to keep your audience in mind.

Leave a Comment

Mounting a Disk Image in Powershell

Jack Vamvas shows us how we can mount a disk image from ISO in Powershell:

I want to set up a script to Mount a Disk in an automated way utilising Powershell ? The image exists as an ISO on a network path and requires to be made available as a drive letter & path. It doesn’t have to be a dedicated drive letter – just the next letter after the highest. So for example , if I already have E:, F:,G:  than I want it to be set as I: 

For no extra charge, Jack also shows us how to dismount a disk image.

Leave a Comment