Press "Enter" to skip to content

Author: Kevin Feasel

Temporal Tables and Azure DevOps Deployments

Published 2022-01-27 by Kevin Feasel

Rayis Imayev notes a problem with Azure DevOps deployments:

Here is one thing that still doesn’t work well when you try to alter an existing temporal table and run this change through the [SqlAzureDacpacDeployment@1] DevOps task, whether this change is to add a new column or modify existing attributes within the table. Your deployment will fail with the “This deployment may encounter errors during execution because changes to … are blocked by …’s dependency in the target database” error message.

Read on to see what causes this problem and what we can do to work around it.

Comments closed

January 2022 Updates for Azure Synapse Analytics

Published 2022-01-27 by Kevin Feasel

Saveen Reddy has an update for us:

You can now easily add data quality, data validation, and schema validation to your Synapse ETL jobs by leveraging Assert transformation in Synapse data flows. Add expectations to your data streams that will execute from the pipeline data flow activity to evaluate whether each row or column in your data meets your assertion. Tag the rows as pass or fail and add row-level details about how a constraint has been breached. This is a critical new feature to an already effective ETL framework to ensure that you are loading and processing quality data for your analytical solutions.

Read on for the full list of changes.

Comments closed

Combining Transparent Data Encryption and Instant File Initialization

Published 2022-01-27 by Kevin Feasel

Tom Collins performs a test:

Instant File Initialization (IFI) enabled  on  SQL Server, for data files only, leads to faster  execution on  certain file operations, as  it bypasses the zeroing procedure while reclaiming disk space. Instead, disk content is overwritten as new data is written to the files.

But do these benefits continue  when Transparent Data Encryption (TDE) is enabled ?   

Click through for the answer.

Comments closed

Go/No-Go Indicators for Oracle Migrations to Azure

Published 2022-01-27 by Kevin Feasel

Kellyn Pot’vin-Gorman lays out some guidance on Oracle to Azure migrations:

When migrating an Oracle database to another platform, there are the common indicators and discussion topics around PL/SQL conversions, data types, application rewrites, etc., as being roadblocks to refactoring, but being successful also has to do with the SIZE of the workload coming from Oracle.  I find this is often dismissed, even though this is one of the quickest ways to identify if an ENTIRE Oracle database, (not even by schema or a subset of the Oracle database) can run on a Platform as a Service, (PaaS) solution.

Click through for more information on PaaS limits for Oracle databases in Azure.

Comments closed

Using Proprietary Database Features

Published 2022-01-27 by Kevin Feasel

Lee Markum does some thinking:

I was recently thinking of SQL Server temporal tables and how there is a perspective that you shouldn’t use proprietary features of a product because it locks you into that product. I want to be your guide on this matter.

I agree completely with Lee’s take on this. I can count on one hand the number of platform migrations I’ve been a part of through the years. These are painful enough experiences even if you use 100% “portable” code because optimization patterns change. And then you get to the utterly absurd: indexes in Oracle and SQL Server behave differently (e.g., clustered indexes are a practical must in SQL Server and the equivalent to clustered indexes in Oracle is so rarely used that it’s not even worth talking about to most Oracle people), so are you going to avoid indexes so you can have “truly” portable code? If the answer to that question is “yes,” you are wrong.

I do understand that there are companies which create code bases which need to be installable on multiple platforms. In that case, I do understand trying to keep things as common as possible, especially for fairly simple apps. But this is also part of why vendor databases are, as a general rule, awful.

Comments closed

Using the Azure Form Recognizer

Published 2022-01-26 by Kevin Feasel

Cem Ayberkin shows off the Azure Form Recognizer:

Shopping malls are facing strong competition and effective loyalty programs boost customer retention. The primary goal of the loyalty scheme is to promote loyalty at the mall, increase footfall whilst understanding shopping habits. With large number of stores and various receipt formats in a mall, the process of manual checking and verification of the data submitted in place did enable rewards to be issued, but proved slow, expensive, inconsistent, and non-scalable. It did not include the valuable line item/product information the mall needed to understand the shopping habits. Therefore, one of the largest shopping malls used Azure Form Recognizer automating receipt scanning and data extraction and feeding the data as rewards points into the customer’s loyalty program, which greatly improved customer shopping experience.

I was pleasantly surprised with how the Form Recognizer works. It’s not perfect but it is useful.

Comments closed

Great Features in R 4.0.0

Published 2022-01-26 by Kevin Feasel

Colin Gillespie looks at a few new features in R version 4:

R version 4.0.0 was released almost two years ago. The change in the major version, 3.x.y to 4.0.0, represented significant and potentially breaking changes. For an organisation to start using these new features, everyone in the company must have access to that version; otherwise code isn’t shareable. This naturally slows down adoption.

We moved our internal R projects to depend on version R 4.0.0 around twelve months ago – a few months after the release date. Over the last year we’ve also assisted a number of clients in making the move; typically with Shiny applications. This post aims to highlight some of the features we’ve found useful and also some of the potential pitfalls.

It’s crazy to think that it’s been so long

Comments closed

Synapse and ADF Pipeline Dependency Diagrams

Published 2022-01-26 by Kevin Feasel

Kamil Nowinski uses one of my favorite tools for diagram creation:

Documenting objects dependencies of ETL processes is a tough task. Regardless it is SSIS, ADF, pipelines in Azure Synapse or other systems. The reasons for understanding the current solution can vary either: handover to other team/member of the team, troubleshooting, refactoring, debugging, investigating dependencies due to error, performance issue or others, as well as keen to remove selected/duplicated pipelines or logic.

But there is never a good time to make documentation, and even if that has been done – no one knows how much it’s up to date. The situation is not improved by the fact that quite often there is a lack of (free or built-in) tools for generating such documentation. Sounds familiar? I bet it does.

Click through to learn more and to see how to use that tool (Mermaid).

Comments closed

Using the Power BI Embedded Playground

Published 2022-01-26 by Kevin Feasel

Gilbert Quevauvilliers shows off the Power BI Embedded Playground:

One of the great things about Power BI is how they make things a lot easier and better to use.

I have been answering some questions in the Power BI Community and other people have been looking into using Power BI Embedded.

This led me to find out that there is a quick and easy way to test out Power BI Embedded. The best part is that I can use my own reports and do a drag and drop test!

Click through to see how.

Comments closed

Row Yielded No Match during Lookup in SSIS

Published 2022-01-26 by Kevin Feasel

Nick Edwards plays match-maker:

Have you ever been faced with the SSIS error “Row yielded no match during lookup”? If so, this blog is for you!

A customer of ours recently faced the same issue in one of their SSIS packages and asked us to investigate this for them. Initial investigations on their side highlighted that when they replicated the lookup component using a standard join in T-SQL (similar to the image below) it returned the expected results.

So why was SSIS reporting an error and ultimately causing the package to fail?

Read on to learn why. For bragging rights (and a demonstration of how much SSIS pain I’ve suffered through the years), I got it in one.

Comments closed