Press "Enter" to skip to content

Curated SQL Posts

SSAS and Database Loading

Published 2021-01-25 by Kevin Feasel

Nigel Foulkes-Nock explains why SSAS might not be available even if the service is running:

When starting SQL Server Analysis Services (SSAS) Tabular, the Service is quick to report that it has started. In my opinion, this Status is not entirely accurate – SSAS may be running but you cannot access data until it has loaded all associated SSAS Databases into memory and performed its consistency checks. This can take a long time.

After starting SSAS, if you try to browse the Databases using SQL Server Management Studio (SSMS) then SSMS becomes unresponsive. You will receive errors if you try to query a SSAS Database. It’s busy but it doesn’t report as such and doesn’t give any clue of how long it’ll take.

Read on for the explanation.

Comments closed

Hiding Excel using Powershell

Published 2021-01-25 by Kevin Feasel

Mikey Bronowski shows how you can hide an Excel worksheet, as well as specific rows and columns, using Powershell:

This is part of the How to Excel with PowerShell series. Links to all the tips can be found in this post.
If you would like to learn more about the module with an interactive notebook, check this post out.

MS Excel offers many different functionalities and one of them is making things to disappear like hiding worksheets or columns and rows, even cells.

Read on to see how.

Comments closed

Change Tracking vs Change Data Capture with Partitioning

Published 2021-01-25 by Kevin Feasel

Erik Darling explains a difference:

One thing I’ve seen people run into is that these two technologies have very different relationships with Partitioning.

Now, I know this isn’t going to be the most common scenario, but often when you find people doing rocket surgeon stuff like tracking data changes, there are lots of other features creeping around.

Click through to see which one works better.

Comments closed

Memory Grant Internals

Published 2021-01-25 by Kevin Feasel

Deepthi Goguri starts a series on memory grant internals:

SQL Server have different types of components (Plan Cache, Buffer pool and other memory components) in the memory area and requires memory for many things. We also require memory to process our queries. Each operator in the execution plan requires memory to process the rows from one operator to the other operator. Some of the iterators need to be buffered as the rows come into them before sending them to the next iterator in the execution plan. These iterators needs more memory. These are called memory consuming iterators. The grants that we given to these memory consuming iterators are called Query memory grants.

Click through for more.

Comments closed

Using the Cloudshell Code Editor

Published 2021-01-25 by Kevin Feasel

Hamish Watson shows off the cloudshell code editor in Azure:

This post is about code which is an online editor you can use with cloudshell.

I live in the Azure platform all day (almost) every day. What this means is I need to be able to run scripts from just about any device – there are times where I will be scaling a Hyperscale database at 10:30pm from my phone….

So I use cloudshell a LOT in Azure

Click through for more details.

Comments closed

AzureCosmosR

Published 2021-01-22 by Kevin Feasel

Hong Ooi takes us through an R library for working with Cosmos DB:

Among other features, Azure Cosmos DB is notable in that it supports multiple data models and APIs. When you create a new Cosmos DB account, you specify which API you want to use: SQL/core API, which lets you use a dialect of T-SQL to query and manage tables and documents; MongoDB; Azure table storage; Cassandra; or Gremlin (graph). AzureCosmosR provides a comprehensive interface to the SQL API, as well as bridges to the MongoDB and table storage APIs. On the Resource Manager side, AzureCosmosR extends the AzureRMR class framework to allow creating and managing Cosmos DB accounts.

AzureCosmosR is now available on CRAN. You can also install the development version from GitHub, with devtools::install_github("Azure/AzureCosmosR").

Hong provides examples for us using three of the Cosmos DB APIs, so check it out.

Comments closed

Logical Separation in Azure Data Factory

Published 2021-01-22 by Kevin Feasel

Rayis Imayev is at a crossroads:

I was raised listening and reading fairy tales where the main character would reach a crossroad with a large stone that had some directions written on it – turn right and you will lose your horse, turn left and you will lose your life, walk straight and you will find your happiness. 

Also, growing up in a small Ukrainian industrial city, closely situated to a railroad hub, I was always fascinated to see many colorful rail traffic lights, trying to imagine where a myriad of rail tracks would lead trains on them.

Similarly, Azure Data Factory (ADF) provides several ways, to control/direct/filter your pipeline workflows; it’s all conditioned and constrained to the boundaries of my “crossroad stone” writings.

As one of my intellectual heroes is purported to have said, if you see a fork in the road, take it.

Comments closed

Using AT TIME ZONE

Published 2021-01-22 by Kevin Feasel

Chad Callihan walks us through using AT TIME ZONE in SQL Server:

Dealing with time zones in general can be a headache. Thankfully, SQL Server 2016 included a new clause to make working with time zones more manageable. Let’s look at a couple examples:

I’m in Eastern Standard Time so we’ll start here. Keep in mind that when no offset information is included, SQL Server is going to assume that the date value is in the target time zone.

This works best when your dates are stored in UTC, but Chad does show how to convert between two other time zones. This does not perform all that well when you need to convert a lot of rows, but if you’re doing one or two conversions, it’ll do just fine.

Comments closed

Handling Fuzzy Match Lookup Failures in Power BI

Published 2021-01-22 by Kevin Feasel

Imke Feldmann continues pulling on a thread:

In a previous article I have described how to use Fuzzy merge to match misspelled data to valid categories. With the sample data at hand, all values could be matched to existing categories. But what can you do if there are some entries that cannot be matched to existing categories?

I would create an “unmatched” value to the categories table before loading to the data model and assign the “unmatched”-value to all unmatched entries. Then I would create a measure that counts the entries within the unmatched category. I would then create a data driven alert, based on this measure. That would trigger an e-mail to the person responsible for maintaining the list. Or, if responsibilities are a bit more complex, trigger a flow in Power Automate that could handle certain conditions to be followed as well.

Click through to see it in action.

Comments closed