Press "Enter" to skip to content

Category: Cloud

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

An Overview of Google BigQuery Pricing Changes

Published 2023-05-10 by Kevin Feasel

Jeremy Pries gets out the checkbook:

Google has announced that a new tiered pricing model for Google BigQuery will come into effect in July. If your organization is already a BigQuery customer or you’re considering it as a data warehouse option, here’s what you need to know about the new pricing structure.

As of July 5, 2023, BigQuery will come with three pricing tiers: Standard, Enterprise and Enterprise Plus. Customers will have the ability to mix and match these editions to suit various workloads.

Read on for what this means and ways you can hope to save some money as a result of the licensing changes. I’m slightly pessimistic about it—only slightly because I’m quite ignorant of BigQuery pricing to begin with, but more than zero pessimism because companies often don’t change licensing terms with the intent of users paying them less.

Comments closed

The Value of PostgreSQL in Azure

Published 2023-05-09 by Kevin Feasel

Grant Fritchey does some explaining:

I’ve had people come up to me and say “PostgreSQL is open source and therefore license free. Why on earth would I put PostgreSQL in Azure?”

Honestly, I think that’s a very fair question. The shortest possible answer is, of course, you don’t have to. You can host your own PostgreSQL instances on local hardware, or build out VMs in Azure and put PostgreSQL out there, some other VM host, or maybe in Kubernetes containers, I mean, yeah, you have tons of options. So why PostgreSQL in Azure, and specifically, I mean the Platform as a Service offering? Let’s talk about it.

The biggest issue I’ve historically had with PostgreSQL or MySQL platform-as-a-service offerings in Azure is that Microsoft is always behind the release curve. With PostgreSQL, it’s not so bad—flexible server offers version 14.7, which is one major version behind Postgres itself (15) but at least the latest minor version. They’ve caught up on MySQL, but for a while, they were way behind.

Comments closed

Slowly-Changing Dimensions in the Serverless SQL Pool

Published 2023-05-05 by Kevin Feasel

Lilliam Leme is building a serverless warehouse:

As organizations continue to collect and store large volumes of data in their data lakes, managing this data effectively becomes increasingly important. One key aspect of this is implementing Slow Change Dimension type 2, which allows organizations to track historical data by creating multiple records for a given natural key in the dimensional tables with separate surrogate keys and/or different version numbers. In this blog post we will address the following scenario: a customer wants to implement Slow Change Dimension type 2 on top of their data lake.

For this example, we will use Serverless SQL Pool to demonstrate how this can be done. Additionally, in the next post, we will explore how the same approach can be used with Spark.

This turns out to be more work than a classic SQL Server-based solution because of the fact that the serverless SQL pool is read-only, save for CETAS statements.

Comments closed

Azure SQL Updates for May 2023

Published 2023-05-05 by Kevin Feasel

Anna Hoffman gives us the latest news:

Let’s start with Azure SQL Managed Instance, which had several general availability (GA) announcements in April. First, the GA of Link feature for Azure SQL Managed Instance for SQL Server 2016 and 2019 happened. This capability allows you to set up near real-time replication between a SQL Server and SQL MI. You can use this link for scale, migration, read-only workloads, etc. To learn more, review the announcement blog. The team also announced the GA of CETAS. This stands for Create External Table As Select, which essentially means you can create an external table while in parallel exporting the results of a SELECT statement. This has been a customer ask and you can learn how to take advantage of it here.

Read on to learn more about what’s new with the rest of the Azure SQL landscape, and some things happening in the community.

Comments closed

WaitTime in Power BI

Published 2023-05-03 by Kevin Feasel

Chris Webb explains what a new metric means:

What does WaitTime represent? Here’s the technical explanation: it’s the wait time on the query thread pool in the Analysis Services engine before the query starts to run. But what does this mean for you as someone trying to tune DAX queries in Power BI?

Chris provides an examplation of exactly that. This tracking of noisy neighbors is interesting, as it would provide insight if you’re noticing variance in dataset refresh times.

Comments closed

Performing a Cloud Adoption Security Review

Published 2023-05-02 by Kevin Feasel

Daniel Margetic takes a look:

Security is an ongoing journey of incremental progress and maturity, and not a static destination. The Cloud Adoption Framework provides security guidance for this journey by providing clarity to the processes and best practices. This guidance is based on real world experiences of our customers, of Microsoft’s own security journey and lessons learned, and the work with other organizations like NIST (National Institute of Standards and Technology) or CIS (Center for Internet Security).

The outcome is manifested in the Cloud Adoption Framework Secure Methodology which provides a vision of the complete end state of your security journey and follows the Zero Trust principle (assume breach, verify explicitly, use least privilege access).

This assessment gives you the opportunity to self-assess your security journey of your cloud adoption against this secure methodology.

Read on to learn more about how CASRs work and how you can perform one yourself.

Comments closed

Adding Microseconds to ADF Timestamps

Published 2023-04-27 by Kevin Feasel

Rayis Imayev can’t wait for the next second:

The current addToTime function (https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions#addToTime) in Azure Data Factory (ADF) only supports a specific set of time units ranging from Year to Seconds. Since I needed to increment a timestamp by microseconds, I had to find an alternative solution in ADF. Here are my findings on how to do this using an alternate approach.

Click through for Rayis’s solution to the problem.

Comments closed

CETAS to Parquet Files in Azure SQL Managed Instance

Published 2023-04-27 by Kevin Feasel

Michael Bourgon gives CETAS a chance:

TL;DR – the below lines will allow you to query a table on your MI, creating Parquet files in Azure blob storage. And you can query it! Next up is partitioning over time, etc, etc. But this is freaking fantastic. I have a python script I wrote that does it, but it’s nowhere as nice/easy as this.

Why do you care? Because it’s a fantastically easy way to archive older data to blob storage, and I suspect (need to test) that if you do it right, you can then have it go to cool/archive storage via a lifecycle setup, so that if you need it much later, you can.

Yep, this is historically one of the best use cases for PolyBase. Unfortunately, we can’t do this in SQL Server 2022, though you can in pre-2022 versions using the Hadoop process. Given that it’s now available in SQL MI, I wouldn’t be too shocked to see it on-premises at some point, with the big question being in SQL Server 2022 or vNext.

2 Comments