Press "Enter" to skip to content

Month: May 2023

Automatic Refresh in Power BI

Published 2023-05-15 by Kevin Feasel

Chris Webb does some ‘splainin’:

One of the most confusing things about troubleshooting Power BI refresh problems is the way Power BI will sometimes try running a refresh again after it has failed. It means that refreshes seem to take a lot longer than you would expect and also that more queries are run against your data source than you would expect. I don’t have all the answers but I thought it would be useful to highlight a few scenarios where it does happen.

Read on for two scenarios in which you might find this.

Comments closed

Postgres Vacuum and Auto-Vacuum

Published 2023-05-12 by Kevin Feasel

Henrietta Dombrovskaya covers a couple of background processes:

Welcome to the second blog of the “magic of parameters” series. In the first entry, I covered memory parameters, and in this article. In this article will talk about PostgreSQL configuration parameters which manage the (auto)vacuum and (auto)analyze background processes.

Read on for a deeper dive into vacuuming and a bit on auto-analyze as well.

Comments closed

The Two File Formats for Polyglot Notebooks

Published 2023-05-12 by Kevin Feasel

Matt Eland chooses a file extension:

I’ve been talking more and more about Polyglot Notebooks and as people try it out, they tend to ask me one common question: should I create a .dib file or an .ipynb file? What’s the difference anyways?

In this short article I’m going to explore the .dib and .ipynb file formats and explain the difference between the two while answering the question of which one you should choose when creating your own notebooks in Polyglot Notebooks.

Read on for Matt’s thoughts. My tendency is to create them in .ipynb format for additional tooling support and potential cross-product flexibility (assuming you have the right kernels installed on your Jupyter server), though Matt explains his preference for .dib.

Comments closed

Feature Branching and Hotfixes for Azure DevOps

Published 2023-05-12 by Kevin Feasel

Vytas Suopys covers a bit of source control strategy:

Have you ever deployed a release to production only to find out a bug has escaped your testing process and now users are being severely impacted? In this post, I’ll discuss how to deploy a fix from your development Synapse Workspace into a production Synapse Workspace without adversely affecting ongoing development projects.

This example uses Azure DevOps for CICD along with a Synapse extension for Azure DevOps: Synapse Workspace Deployment. In this example, I assume Synapse is already configured for source control with Azure DevOps Git and Build and Release pipelines are already defined in Azure DevOps. Instructions on how to apply this this can be found in the Azure Synapse documentation for continuous integration and delivery.

The specific example covers Synapse, though the general principle applies no matter what you’re deploying.

Comments closed

Implementing Temporal Tables with Existing Data

Published 2023-05-12 by Kevin Feasel

Matthew McGiffen gets to one of my problems with temporal tables:

I also referred to Temporal Tables which are available to us from SQL Server 2016 onward.

Temporal tables aren’t just about monitoring change, they also provide really nice methods for being able to query historical data – to see what the values were at a particular point in time e.g.

SELECT * FROM dbo.SomeData FOR SYSTEM_TIME AS OF '1900-01-01';

My big problem with temporal tables is that they only implement system-defined times. That’s fine in a quasi-historical OLTP scenario, where you want to track history but only occasionally make use of it. But if they supported application time, then you have the ability to create something akin to a type-2 slowly changing dimension with just a few extra words. I understand that the tricky part is that application-defined temporal tables lose the nicety of knowing that the latest insert always goes into the main table and drives the prior record into the historical table, but there are some clever ways around this problem as well. It’s just too early in the morning for me to articulate them is all…

Comments closed

False Alarms in Highly Available Postgres Clusters

Published 2023-05-12 by Kevin Feasel

Umair Shahid pulls the alarm:

False alarms can be a significant problem in highly available clusters of PostgreSQL. They can cause unnecessary downtime and disruptions that can impact the performance of the nodes. In this blog post, we will explore the causes, prevention, and resolution of false alarms in PostgreSQL clusters.

It’s a good idea to sit back and think about how complex the problem of high availability is, even if the service (SQL Server, Postgres, or whatever) offers capabilities to simplify a lot of it. The trick is that you want your service to fail over if and only if it needs to, but what tells you if it “needs to” is noisy signal.

Comments closed

Integrating VBA and R Code

Published 2023-05-11 by Kevin Feasel

Steven Sanderson has become Dr. Moreau. Part 1 shows how to call R code from VBA:

This line defines a subroutine called “CallRnorm”. A subroutine is a block of code that can be executed repeatedly from any part of the code, and it starts with the “Sub” keyword followed by the subroutine name and any arguments in parentheses.

Part 2, as you might expect, covers the obverse:

Yesterday I posted on using VBA to execute R code that is written inside of the VBA script. So today, I will go over a simple example on executing an R script from VBA. So let’s get into the code and what it does.

First, let’s look at the Function called “Run_R_Script”. This function takes four arguments, where the first two are mandatory, and the last two are optional.

Comments closed

Fixing ORA-26086 in Azure Data Factory Pipelines

Published 2023-05-11 by Kevin Feasel

Emanuele Meazzo fixes a problem:

Turns out, ADF is rightfully trying to insert the rows in bulk, but Oracle doesn’t like it when the sink table has triggers, falling back to row by row insertion seems it’s too much to ask, so you end up with the error.
Searching on the good ‘ol wide web you’ll encounter this solution, that basically tells you to disable bulkload for the whole Oracle connection by setting EnableBulkLoad=0 in the connection string.
That wouldn’t work for me, because I’m bulk loading just fine everywhere else, so either I had to suffer slower performance on all the other inserts by disabling the bulk insert, or I had to create an additional and separate Linked Service to Oracle with the bulk insert disabled: doable but adding maintenance overhead when things change (two secrets to manage instead of one).

My solution? Super dumb.

Read on for a copy of the error message and Emanuele’s solution. There are two philosophies with regard to dumb solutions:

  • If a solution is dumb and it works, it isn’t dumb.
  • If a solution is dumb and it works, it’s still a dumb solution and you just got lucky this time.

I’m not quite sure under which category this falls.

Comments closed

Calculating Reservation-Based Savings in Azure

Published 2023-05-11 by Kevin Feasel

Saira Shaik reserves some instances:

I have created this dashboard to display the savings made due to the purchase of Reservations or Savings Plans or by signing the agreement with Microsoft to get Azure Commitment Discounts (ACD).
This dashboard is helpful for Customers who:

  • Purchased Reservations or
  • Purchased Savings Plan or
  • Signed Monthly Azure Consumption Commitment (MACC) and got a special discounted price.


Customers can view their savings by uploading the Amortized files into this Power BI file.

Click through to se what the dashboard includes and how it all works. Then, check out Saira’s GitHub repo for the template.

Comments closed