Press "Enter" to skip to content

Author: Kevin Feasel

Importing Code into Polyglot Notebooks

Published 2023-05-18 by Kevin Feasel

Matt Eland brings some code to the party:

We’ve seen that Polyglot Notebooks allow you to mix together markdown and code (including C# code) in an interactive notebook and these notebooks allow you to share data between cells and between languages. However, frequently in programming you want to reference code that others have written without having to redefine everything yourself.

In this article we’ll explore how Polyglot Notebooks allows you to import dotnet code from stand-alone files, DLLs, and NuGet packages so your notebooks can take advantage of external code files and the same libraries that you can work with from your code in Visual Studio.

The syntax, by the way, is very similar to the F# Interactive (and the short-lived C# Interactive) tool, particularly #i and #r.

Comments closed

Recursive Common Table Expressions in Snowflake

Published 2023-05-18 by Kevin Feasel

Kevin Wilkie is too fancy for simple joins:

Today, I want to talk about that fun edge case when you’re having to join a table to itself in Snowflake. Does it happen often? Not unless your architect just hates you.

Let’s use the normal pieces of data that everyone uses for this kind of thing – employee/manager relationships. We have our employee table that we’ve been working off that we’ll play with for this example.

The syntax is a bit different from T-SQL, but the concept is still the same.

Comments closed

Encrypting SQL Server Backups

Published 2023-05-18 by Kevin Feasel

Matthew McGiffen lays out the requirements:

When we talk about protecting our at-rest data, the item that we are likely to be most concerned about is the security of our backups. Backups are generally – and should be – stored off the server itself, and often we will ship copies offsite to a third party where we don’t have control over who can access the data, even if we trust that that will be well managed.

From SQL Server 2014 the product has included the ability to encrypt data while creating a backup. This feature is available in both the standard and enterprise editions of SQL Server, so it is something you can use even when TDE may not be a feature that is available to you.

Click through for a primer on the topic.

Comments closed

Against Keys in Fact Tables

Published 2023-05-18 by Kevin Feasel

Marc Lelijveld searches for keys under the lamppost:

Another blog post based on recent client experiences. Last week, I visited a client where we had extensive discussions on data model optimization. As you might know, data modeling in Power BI is one of my favorite topics, so I had an excellent day. It’s also not the first time that I blog about anything data modeling and optimization. If you haven’t read it yet, I recommend reading my previous blog on this topic.

This blog will focus on the need of keys in your tables and primarily your fact tables in your data model. I keep running into data models at customers which are flooded with keys in all tables. For each of them you should ask, do I really need this and could I save it in a different data type for further optimization. In this blog, I will further elaborate on keys in your data model, typical use cases and how these cases can be solved in different manners.

Read the whole thing. The really short version is classic Kimball-style advice: keys for dimensions, not for facts. And in Power BI, removing a unique column from a fact table can speed things up by shrinking the compressed fact table size.

Comments closed

Oracle Connectors for SSIS

Published 2023-05-18 by Kevin Feasel

Meagan Longoria talks to Oracle:

We needed to make sure we had the most current version of the drivers required to make the package work.

This turned out to be an adventure involving 3 different drivers and plans to refactor, so I’m documenting some of it here in case it helps someone else.

Read on for your options.

Comments closed

Updates to SqlPackage and DacFx

Published 2023-05-17 by Kevin Feasel

Drew Skwiers-Koballa has an update for us:

As the primary command-line interface to DacFx, SqlPackage often benefits from dependency changes in DacFx, including the Microsoft.Data.SqlClient driver. In SqlPackage 162.0.52, the SqlClient driver has been updated from v5.0.1 to v5.1.0, bringing support for TLS1.3 encrypted connections to the .NET Core builds of SqlPackage and the ServerCertificate connection string setting for validation of SQL Server’s TLS/SSL certificate. 

Many of the fixes in this release were triaged out of issues submitted on the DacFx GitHub repository and are visible in the release milestone.  The full list of SqlPackage fixes from the release, including eight deployment-related fixes, are also listed in the SqlPackage release notes. To improve our ability to service and enhance SqlPackage, SqlPackage now collects usage data, including anonymous feature usage and diagnostic data. For more information, see the documentation section on Usage data collection.

Read on for the full set of changes.

Comments closed

Migrating Multiple SSRS Reports to Power BI Paginated Reports

Published 2023-05-17 by Kevin Feasel

Olivier Van Steenlandt doesn’t do things one at a time:

A few weeks ago I released a blog post about migrating SSRS Reports to Power BI Paginated Reports. At that point in time, I wasn’t aware of any way to migrate multiple SSRS Reports in one go.

Meanwhile, I have done some research and experimented a bit. In this blog post, I will be going through 2 different ways to migrate multiple SSRS Reports at once to Power BI.

Click through for Olivier’s findings and how you can migrate en masse.

Comments closed

SandDance in Polyglot Notebooks

Published 2023-05-17 by Kevin Feasel

Matt Eland continues a series on dotnet Polyglot Notebooks:

As I’ve been doing more and more dotnet development in notebooks with Polyglot Notebooks I’ve found myself wanting more options to create rich data visualizations inside of VS Code the same way I could use Plotly for data visualization using Python and Jupyter Notebooks.

Thankfully, Microsoft SandDance exists and fills some of that gap in terms of doing rich data visualization from dotnet code.

In this article I’ll talk more about what SandDance is, show you how you can use it inside of a Polyglot Notebook in VS Code, and show you a simple way you can use it without needing a Polyglot Notebook.

Most of my experience with SandDance was with Azure Data Studio, but it’s nice to see this capability in notebooks as well.

Comments closed

Troubleshooting Issues with Full-Text Indexing

Published 2023-05-17 by Kevin Feasel

Jose Manuel Jurado Diaz digs into a customer problem:

Today, we got a new service request where our customer asks about the time spent populating the full-text. Following, I would like to share with you some lessons learned during this process, specifically working with Azure SQL Database.

For this example, we are going to use a General Purpose database with 8 vCores. Let’s get started with the creation of the table and fulltext.

Read on for a walkthrough of setting up full-text indexing and figuring out what the indexing engine is doing at any point in time.

Comments closed