A Fine Slice Of SQL Server
Keon Verbeeck writes a regular expression:
I have blocks of text stored inside a SQL Server table. From this text, I need to extract strings that match a specific pattern. I tried using CHARINDEX and PATINDEX, but the pattern is too complicated. Learn how this can be done with the REGEXP_INSTR function.
Read on to see how.
Leave a CommentAndy Brownsword checks out who has access to what permissions:
Item level roles are what we’re digging into here. Before we start, it’s worth defining a simple security model so it’s applied consistently. Let’s be real, the instance might not have a long term future but let’s do it right at least, eh?
Read on for a few high-level suggestions, details on what permissions do not carry over from parent objects, and more.
Leave a CommentChris Webb gives the graph database in Microsoft Fabric a try:
The new Fabric Graph database is now rolling out now and should be available to everyone within the next few weeks if you can’t see it already. The key to learning a new data-related technology is, I think, to have some sample data that you’re interested in analysing. But if you’re a Power BI person why would a graph database be useful or interesting? Actually I can think of two scenarios: analysing dependencies between DAX calculations and the tables and columns they reference using the data returned by INFO.CALCDEPENDENCY function (see here for more details on what this function does); and the subject of this blog post, namely analysing Import mode refresh job graphs.
Read on for Chris’s example.
Leave a CommentAnurag Srivastava, et al, perform a migration:
Apache Airflow 3.x on Amazon MWAA introduces architectural improvements such as API-based task execution that provides enhanced security and isolation. Other major updates include a redesigned UI for better user experience, scheduler-based backfills for improved performance, and support for Python 3.12. Unlike in-place minor Airflow version upgrades in Amazon MWAA, upgrading to Airflow 3 from Airflow 2 requires careful planning and execution through a migration approach due to fundamental breaking changes.
This migration presents an opportunity to embrace next-generation workflow orchestration capabilities while providing business continuity. However, it’s more than a simple upgrade. Organizations migrating to Airflow 3.x on Amazon MWAA must understand key breaking changes, including the removal of direct metadata database access from workers, deprecation of SubDAGs, changes to default scheduling behavior, and library dependency updates. This post provides best practices and a streamlined approach to successfully navigate this critical migration, providing minimal disruption to your mission-critical data pipelines while maximizing the enhanced capabilities of Airflow 3.
Read on to see what has changed between these two major versions of Airflow, recommendations on what to look out for, and a step-by-step migration guide.
Leave a CommentGarry Bargsley tries out DBeaver:
Whether you’re a seasoned DBA or just exploring database tools, DBeaver offers a powerful, cross-platform GUI for interacting with PostgreSQL and many other databases. As a continuation of the previous blog post on installing PostgreSQL, this guide will walk through installing DBeaver and setting up a connection to the PostgreSQL instance we created.
My biggest takeaway the last time I used DBeaver was, SQL Server has a great thing going with SSMS. But in fairness, that was a while ago and things could very well have gotten better in the meantime. Also, if you have to connect to a variety of data platforms, DBeaver is a pretty solid choice.
Leave a CommentFabric Data Warehouse (DW) supports ACID-compliant transactions using standard
T-SQL (BEGIN TRANSACTION, COMMIT, ROLLBACK)and uses Snapshot Isolation (SI) as its exclusive concurrency control model. All operations within a transaction are treated atomically—either all succeed or all fail. This ensures that each transaction operates on a consistent snapshot of the data as it existed at the start of the transaction, which means.
Read on to see what this means, as well as what happens when multiple writers interfere with one another and how to avoid these sorts of issues. My Kimball-coded brain says that, if you have a data warehouse, you should have one data loading process. In that case, it’s not easy for the single data loading process to get tripped up on its own.
Leave a CommentErik Darling talks about indexed views. The set of limitations is huge and painful, just as Erik describes. For every instance in which I was successfully able to build an indexed view, there were probably a couple of dozen instances in which it was a great idea until I hit one or more of the limitations that Erik describes.
Leave a CommentSheil Bakhshi performs a comparison:
The long-running debate around how Power BI calculates totals in tables and matrices has been part of the community conversation for years. Greg Deckler has kept the topic alive through his ongoing “broken totals” posts on social media, often suggesting that Power BI should include a simple toggle to make totals behave more like Excel. His continued campaign prompted a detailed reply from Daniel Otykier in his article “No More Measure Totals Shenanigans“, and earlier, Diego Scalioni explored how DAX evaluates totals internally in his post “Cache me if you can: DAX Totals behind the scenes“.
This blog brings all those perspectives together from a scientific and comparative angle. It looks at how totals are calculated in Power BI and compares that behaviour with Tableau, Excel, Paginated Reports, and even T-SQL. The goal is not to take sides, but to clear up the confusion around what is happening under the hood.
This is a very detailed and dispassionate explanation that helps make sense of the debate.
Leave a CommentOsheen MacOscar builds a package:
In this blog series (this and the next blog) I am going to demonstrate how to use Poetry to create a Python package, set up testing infrastructure and install it. I am going to be creating a wrapper around the Fantasy Premier League API and creating a function which can create a weekly league table.
This is a straightforward example of how to create a new Python package and add a function call to it.
Leave a CommentRick Dobson inserts some data:
There are numerous use cases for multi-file imports of CSV files into a SQL Server table:
- Dynamic SQL Server bulk insert loads are especially appropriate for tasks that extract content from multiple files to a SQL Server table where the source file names change between successive import jobs.
- Static bulk insert loads target scenarios where the source file names do not change between successive import jobs.
Read on for examples of how to implement each. Admittedly, bulk insert has rarely worked all that well in my experience, whether due to permissions mishaps, poor data integrity, or sudden changes in data types between file runs. But it does tend to work a lot better if you have a specified data interchange format and a standardized process to prepare the data and make it available on disk for insertion.
Leave a Comment