A Fine Slice Of SQL Server
Steven Sanderson wants to check for row existence:
Hello, fellow R users! Today, we’re going to explore a common scenario you might encounter when working with data frames: checking if a row from one data frame exists in another. This is a handy skill that can help you compare datasets and verify data integrity.
There are several ways to do it. Click through for two of those ways.
Leave a CommentKurt Buhler performs comparative analytics:
In a previous article about format strings, we showed an example of how format strings can improve visualizations. The visualizations in that article compared the performance of a company’s marketing videos on a streaming platform to the average of all their videos released that year. In this article, we explain how to conduct this analysis yourself in DAX, where you compare series that occur in different periods.
Click through for an explanation, both in video and in blog form.
Leave a CommentSam Garth troubleshoots an issue:
On a recent case, a customer was trying to restore a database from a storage account using a SAS token when they received the below error.
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)Additional information:
Cannot open backup device
‘https://storage.blob.core.windows.net/container/dbbackup_2024_03_21_121901.bak‘
Operating system error 86(The specified network password is not correct.).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)
Read on for the troubleshooting steps Sam followed to solve the problem.
Leave a CommentCary Huang explains how row visibility works in Postgres:
Simply put, the visibility refers to whether a row of data (Heap Tuple by default) should be displayed to the user in certain states, backend processes, or transactions.
For example,
- A user changes a data record from ‘A’ to ‘B’ via UPDATE
- PostgreSQL handles this by INSERTing a new record call ‘B’ while marking record ‘A’ as ‘invisible’
- Both records exist in the system, but ‘B’ is visible, while ‘A’ is not.
- A deleted or invisible record is also referred as a ‘dead’ tuple
- One of the responsibilities of VACUUM process is to clear these ‘dead’ tuples to free up space.
Read on to learn more about various visibility indicators and how they ultimately tell us whether a row should be visible or not.
Leave a CommentRyan Booz explains how recursive common table expressions work:
The first two articles in this series demonstrated how PostgreSQL is a capable tool for ELT – taking raw input and transforming it into usable data for querying and analyzing. We used sample data from the Advent of Code 2023 to demonstrate some of the ELT techniques in PostgreSQL.
In the first article, we discussed functions and features of SQL in PostgreSQL that can be used together to transform and analyze data. In the second article, we introduced Common Table Expressions (CTE) as a method to build a transformation query one step at a time to improve readability and debugging.
In this final article, I’ll tackle one last feature of SQL that allows us to process data in a loop, referencing previous iterations to perform complex calculations: Recursive CTE’s.
Given that Postgres allows for materialized common table expressions, I’m a bit curious about how recursive common table expressions perform compared to SQL Server.
Leave a CommentNicky van Vroenhoven performs a search:
You probably also use the same method as I did to search through the Admin portal and tenant settings: CTRL + F from your browser. It does the trick, but not very well.
For example, it only searches the titles of the settings, not the descriptions.
Next to that, you also can get a lof matches that you have to scroll or loop through, which makes it not very clear because more often than not, you don’t know in what section of the tenant settings you ended up.
Read on for an alternative method of searching. Or, I guess, two of them because without Nicky’s post, it can be easy to confuse the two search boxes.
Leave a CommentSteven Sanderson takes a slice:
Explanation: – We use
nrow(my_df)to get the total number of rows in the data frame. – Then, we use indexing ([nrow(my_df), ]) to extract the last row.
Read on for a simple example of getting the last row using base R, dplyr, and data.table. Then, we kick it up a notch to get the second-to-last row, with the idea being that you could substitute “second-to-last” with any arbitrary number.
Leave a CommentDavid Eldersveld talks licensing:
To facilitate a smooth transition from Power BI to Fabric (new capabilities), Microsoft ensured customers could access these new Fabric workloads as well as Copilot for Power BI on their existing Power BI Premium capacity P SKUs.
However, with the introduction of Azure-billed pay-as-you-go and annual reservation F SKUs for Microsoft Fabric, Microsoft recently announced the eventual retirement of the Power BI Premium per capacity SKUs that needs to consider an organization’s Enterprise Agreement (EA) timing.
Read on to learn more, especially if you currently have a Power BI Premium P1 (or higher) SKU.
Leave a CommentJeff Iannucci announces a new tool:
Maybe you have some scripts you found on the internet to check some SQL Server security settings or look for odd permissions. Or maybe you don’t. Well, sp_CheckSecurity checks about 40 different objects, configurations, and permissions for possible issues. You can read more details about it on the sp_CheckSecurity page.
I’m glad to see a new tool in the security space. Chris Bell used to have sp_woxcompliant when he was still consulting, but that’s lost to history now (though I do have a copy on my PC, as one does). You can run CIS checks in dbachecks and Chrissy LeMaire has a Powershell module for DISA STIG auditing, but I’m not sure how easy that is for a DBA or consultant to use.
H/T Jeff Iannucci, who gave me the friendly reminder to add the blog.
Leave a CommentNikola Ilic reminds us to keep Ralph Kimball’s Data Warehouse Toolkit book at hand:
But, what is a star schema in the first place? I have good and bad news for you:)…The bad news is: I’m not covering it in this article, because this one focuses on explaining how to implement a star schema in Power BI (assuming that you already know what star schema is). The good news is: I’ve already written about it, so go and read this article first, if you’re not sure what star schema represents in the world of data modeling…
Now, let’s get our hands dirty and build a star schema!
Read on for the demo.
Leave a Comment