A Fine Slice Of SQL Server
Matt Allington wants to change a bunch of column names at once with Power BI:
This is not the first time I have shared this concept. In my previous article I showed how it is possible to add a prefix to every column in a table. This article today is slightly different. Today I am removing text from multiple columns all at once using some M code. The trick you need to learn to solve this problem is “how to create a list of lists”.
Click through for a video to see it in action.
Comments closedDaniel Janik treats Azure Purview scans like pets rather than cattle:
If you’ve ever been a DBA and seen the mess that you get with SQL Agent Jobs without a clean naming standard for your job schedules and job names then you’ll appreciate this tip.
If you haven’t been a DBA that’s OK too. Years ago I came up with my own naming standard for SQL Agent artifacts and I’ve always felt better when the messy room was clean. No Really! That’s exactly what this is like. A messy room where you are pretty sure you put the item you’re looking for in but you just can’t seem to find it until you clean 95% of the mess and then you’re so exhausted that you don’t have time to do what you wanted to in the first place. Ever been there?
Read on to see what the scans look like by default, as well as some thoughts Daniel has regarding a better way to do things.
Comments closedMeagan Longoria gives us a warning:
Each resource type in Azure has a naming scope within which the resource name must be unique. For PaaS resources such as Azure SQL Server (server for Azure SQL DB) and Azure Data Factory, the name must be globally unique within the resource type. This means that you can’t have two data factories with the same name, but you can have a data factory and a SQL server with the same name. Virtual machine names must be unique within the resource group. Azure Storage accounts must be globally unique. Azure SQL Databases should be unique within the server.
Since Azure allows you to create a data factory and a SQL server with the same resource name, you may think this is fine. But you may want to avoid this, especially if you plan on using system-defined managed identities or using Azure PowerShell/CLI. And if you aren’t planning on using these things, you might want to reconsider.
Click through for a demonstration of how you might get into trouble with this.
Comments closedTomaz Kastrun has a script for us:
So the following function does just that; if goes through the list of functions for all the installed packages in particular environment and finds duplicates. Giving the R programmer the insights where to be cautious and refer to scope and namespace.
This is a cautionary tale to use full object naming, like coin::show() rather than just show().
Soheil Bakhshi reminds me of DB/2:
So, here is my scenario. I received about 10 files, including 15 tables. Some tables are quite small, so I didn’t bother. But some of them are really wide like having between 150 to 208 columns. Nice!
Looking at the column names, they cannot be more difficult to read than they are, and I have multiple tables like that. So I have to rename those columns to something more readable, more on this side of the story later.
Fortunately, there’s a way to fix this; click through for that way.
Comments closedLandon Robinson works around an annoyance:
But DataFrames have not been given the same, clear route to convenient renaming of cached data. It has, however, been attempted and requested by the community:
– https://forums.databricks.com/questions/6525/how-to-setname-on-a-dataframe.html
– https://issues.apache.org/jira/browse/SPARK-8480However, with the below approach, you can start naming your DataFrames all you want. It’s very handy.
Read on to see the solution in action.
Comments closedChris Webb shares some thoughts on the power of names:
What’s wrong with this picture? Look at the names:
– The tables and columns have the same names that they had in the data source, in this case a SQL Server database. Note the table name prefixes of “Dim” for dimensions and “Fact” for fact tables.
– The column and measure names either don’t have spaces or use underscores instead of spaces.
– What on earth does the measure name _PxSysF even mean?
Chris mentions that some of the ideas in the post may be controversial, but to be honest, I don’t think any of them are. The important thing here is to keep your audience in mind.
Comments closedDaniel Janik shares a naming scheme for schedules in systems like SQL Agent and Azure Data Factory:
This tip comes from my DBA days working with SQL Agent Job schedules. If you’ve ever worked on a server where many people created job schedules you’ll know exactly what I mean when I say the schedule names can be really annoying.
This is because the names are not meaningful at all. They are either a GUID thanks to SSRS or something useless like “Schedule 1” or you have 6 different versions of “Every 5 min” when the schedule actually only runs every 15 min on Mondays.
The Linux nerd in me says “Could’ve just used cron naming.” I think Daniel’s naming scheme takes a little bit of time to get used to, but it makes sense.
Comments closedJames Serra clears a few things up:
I see a lot of confusion among many people on what features are available today in Azure Synapse Analytics (formally called Azure SQL Data Warehouse) and what features are coming in the future. Below is a picture (click to zoom) that I describe below that hopefully clears things up:
I tend to just say “Azure Synapse Analytics SQL Pools” for the product formerly known as Azure SQL Data Warehouse and save “Azure Synapse Analytics” to include Spark + hyperscale (James’s v3).
Comments closedMarco Russo and Alberto Ferrari team up to share a standard for naming temporary columns in DAX:
The formula works just fine, but it violates one of the golden rules of DAX code: you always prefix a column reference with its table name, and you never use the table name when referencing a measure. Therefore, when reading DAX code, [Sales Amt] is a measure reference, whereas ‘Product'[Sales Amt] is a column reference.
Nevertheless, in our DAX example ProdSalesAmt is a column of a temporary table (SalesByProduct) created by the FilteredSalesAmount measure. As such, ProdSalesAmt is a temporary column that does not originate from any column in the model and does not have a table name you can use as a prefix. This situation creates ambiguity in the code: it is not easy to discriminate between a column reference and a measure reference. Therefore, the code is harder to read and more error prone.
Read on for their standard, which is pretty easy to follow.
Comments closed