Press "Enter" to skip to content

Month: April 2023

Azure Synapse Analytics March 2023 Update

Published 2023-04-06 by Kevin Feasel

Ryan Majidimehr has an update for us:

We are excited to announce that Multi-Column Distribution (MCD) for Azure Synapse Dedicated SQL pools is now Generally Available! MCD is highly desirable for easing migrations, promotes faster query performance, and reduces data skew. 

You can choose to distribute data on multiple columns to balance the data distribution in your tables and reduce data movement during query execution. Multi-Column distribution will allow you to choose up to eight columns for distribution. 

Click through for the full list of changes. It seems like there’s a decent spread between the four major pool types, with the emphasis on Data Explorer pools this month.

Comments closed

PolyBase Generic ODBC Bug in 2019 CU19 and 2022 CU2

Published 2023-04-06 by Kevin Feasel

Nathan Schoenack warns us of a bug in SQL Server:

After you install SQL Server 2019 CU19 or SQL Server 2022 CU2, external data sources using generic ODBC connector may no longer work. When you try to query external tables that were created before installing the cumulative update, you receive the following error message:

Msg 7320, Level 16, State 110, Line 68

Cannot execute the query “Remote Query” against OLE DB provider “MSOLEDBSQL” for linked server “(null)”. Object reference not set to an instance of an object.

If you try to create a new external table, you receive the following error message:

Msg 110813, Level 16, State 1, Line 64

Object reference not set to an instance of an object.

Click through to learn the cause of the issue and a workaround to resolve it.

2 Comments

Pattern Searches on Numbers

Published 2023-04-06 by Kevin Feasel

Erik Darling does an odd search:

Most of the time, it’s some query that looks like this:

SELECT

c = COUNT_BIG(*)

FROM dbo.Posts AS p

WHERE p.OwnerUserId LIKE ‘2265%’

The cruddy thing here is that… SQL Server doesn’t handle this well.

At first, my thought was, “Why not just use an inequality operator like p.OwnerUserID >= 22650000 AND p.OwnerUserID < 22660000 but then I remembered that we’re probably dealing with multiple orders of magnitude here and the whole thing made my head hurt a little.

Comments closed

Managing Report-Specific Objects in Power BI Datasets

Published 2023-04-06 by Kevin Feasel

Kurt Buhler takes us through some report-specific objects and how we can use them effectively:

These last examples are report-specific objects. They allow flexibility and customization of report visuals. However, report-specific objects create challenges for developers and end-users using the model to create new content. These objects typically only work with specific client tools, visuals or evaluation contexts. In the wrong scenario, they produce incorrect or confusing results. Further, they can quickly overwhelm or pollute the model, confusing the person using it. Even when hiding these objects, they still appear in intellisense when typing DAX. They can also be revealed by right-clicking the “fields pane” in Power BI Desktop and selecting “view hidden”. Because of this, report-specific objects can hurt both the sustainability and adoption of your dataset. How can we use and benefit from report-specific objects, while preventing them from making a model that’s hard to use?

Click through to learn more.

Comments closed

Ranking Functions in SQL Server

Published 2023-04-06 by Kevin Feasel

Chad Callihan gets things sorted:

Have you ever needed a Top 10 list? It may sound like a simple task but not all lists are the same. Consider records where there are ties. Does that mean you need more than 10 records? If two records tie for first does that mean the third record is considered to be ranked second? No matter what type of list you need, SQL Server can help you find your way.

Click through for an overview of three of the four ranking functions in SQL Server. Poor NTILE().

Comments closed

Tooltip Design with CSS

Published 2023-04-05 by Kevin Feasel

Tim Brock takes on tooltips:

While colour-highlighting can be a great way of emphasizing part or parts of a chart or diagram, it doesn’t usually add precise information, which was important to the client. To add this precise information we used a tooltip. But to make them as effective as possible we had to spend a bit of time refining their design.

Click through for several tips on tooltip design, including a technique to minimize tooltip occlusion (in other words, the tooltip blocking important information underneath it).

Comments closed

Comparing HBase to Cassandra

Published 2023-04-05 by Kevin Feasel

The Instaclustr team performs a comparison:

Apache HBase® and Apache Cassandra® are both open source NoSQL databases well-equipped to handle incredible amounts of data–but that’s where the similarities end. 

In this blog, discover the architectures powering these technologies, when and how to use them, and which option may prove to be the better choice for your operations.  

Click through for their overview of the two systems and recommendations on when to use which.

Comments closed

Digging into the OpenLibrary ISBN API

Published 2023-04-05 by Kevin Feasel

Robert Cain is looking for a book:

The format of the Advanced API is slightly different from the simple. Here is template.

https://openlibrary.org/api/books?bibkeys=ISBN:[ISBN Goes Here]&jscmd=data&format=json"

You will replace the [ISBN Goes Here] text with the ISBN number you want to look up. Be aware this can only be digits, you must remove any spaces, dashes, or other characters.

Let’s look at a code example of calling the API and getting all its properties.

Click through for examples and how you can use Powershell to parse the results.

Comments closed

TDE with Customer-Managed Keys in Azure SQL Database

Published 2023-04-05 by Kevin Feasel

Mirek Sztajno announces a public preview:

In this scenario, a key that is stored in a customer-owned and customer-managed Azure Key Vault (AKV) can be used for each database within a server to encrypt the database encryption key (DEK), called the TDE protector. The feature provides the ability to add keys, remove keys, and change the user-assigned managed identity (UMI) for each database. For more information on identity management, see Managed identity types in Azure.

Click through for more details on how it works and what’s currently not supported in the public preview.

Comments closed

Hiding Power BI Pages Is Not for Security

Published 2023-04-05 by Kevin Feasel

Marc Lelijveld points out that, just because the kitten covered its eyes and can’t see you don’t mean that you can’t see it:

Most of us probably know, but every now and then, I still run into cases where hiding pages in a Power BI report is used for security purposes. Although hiding pages might seem like a security feature, it is not intended to be used as one since it can be easily bypassed. Users can find hidden pages by simple URL tweaking or right-clicking on the tabs. The article provides an example of how users can use the Reports-Get Pages API to get all report pages and report sections without having to write a single line of code. The default page when publishing a report is the one that is open, even if it is hidden.

Yeah, hiding pages is really more of a convenience thing, especially if you’re making heavy use of drillthrough and other cross-page functionality.

Comments closed