A Fine Slice Of SQL Server
Business users often need to know how fresh the data is that they see on a Power BI report. This requirement can be interpreted to mean either “when was the last date & time that the source data was updated?” or “when was the last date & time that the data model was refreshed?”
Read on for Paul’s current solution to the problem of knowing the last time this data was refreshed.
Comments closedRyan Majidimehr shares an overview of what the Synapse team has been working on:
ADX contains native support for detecting anomalies over multiple time series by using the function series_decompose_anomalies(). This function can analyze thousands of time series in seconds, enabling near real time monitoring solutions and workflows based on ADX. Univariate analysis is simpler, faster, and easily scalable and is applicable to most real-life scenarios. However, there are some cases where it might miss anomalies that can only be detected by analyzing multiple metrics at the same time.
For some scenarios, there might be a need for a true multivariate model, jointly analyzing multiple metrics. This can be achieved now in ADX using the new Python-based multivariate anomaly detection functions.
The themes for this month are Spark, Data Exploration (via Kusto), and data integration.
Comments closedMichael Bourgon shares some thoughts on Azure SQL Database Auditing:
A couple of notes when using Azure SQL DB Auditing
1) this is actually kinda cool, overall. I plan on mining this in the future for code changes, etc.
2) You can filter it! predicateexpression is the term you’re looking for, available via the powershell module
Read on for more thoughts on the topic.
Comments closedSuppose you are trying to approximate some number x and you’ve got it sandwiched between two rational numbers:
a/b < x < c/d.
Now you’d like a better approximation. What would you do?
The obvious approach would be to take the average of a/b and c/d. That’s fine, except it could be a fair amount of work if you’re doing this in your head.
Read on for a separate approach taking the mediant (not median) of the two fractions.
Comments closedSebastian Sauer plots some tables for us.
The post is a little too terse to grab a lede from, but it does show a good way of generating a formatted image of a table using the ggplot2 library in R.
Comments closedPaul Hernandez gets Inspector Cluseau’d:
The Kusto Detective Agency was created last year (2022) to let people learn KQL solving funny puzzles at the same time. For me it has been a kind of scape room game, where you can exercise your solving problem capabilities and learn ADX at the same time. If you want to participate just follow this link
Click through for the scenario which got Paul.
Comments closedKevin Wilkie puts on the flannel and grabs his database lumberjack axe to split some strings:
But, sometimes, you want a small list of data inherent to a query in SnowFlake. And that’s what I want to talk about today.
In SQL Server, you would create a temp table and then insert the data into it. But in Snowflake, there may be a better / easier way.
Let’s use the function SPLIT_TO_TABLE. Shockingly, it does what’s on the label – it split data and puts it into a table.
Click through for an example. Also check out the Snowflake documentation, where they make use of the lateral operator (the ANSI version of APPLY()) to generate results for multiple strings and make use of the SEQ column.
Lukas Eder hits on an interesting data point:
I’ve found an interesting question on Twitter, recently. Is there any performance impact of using
FILTERin SQL (PostgreSQL, specifically), or is it just syntax sugar for aCASEexpression in an aggregate function?
Click through for the answer and your daily reminder that SQL variants aren’t pure fourth generation languages—if they were, the optimizer would take all possible constructs of a given desired operation and generate the same execution plan for all of them.
Comments closedKevin Chant deploys some database objects:
In this post I want to cover using Azure SQL Deploy v2 for dedicated SQL Pool deployments using GitHub Actions. Which is the GitHub Action that is also known as sql-action.
This post provides an overview of what is new and provides plenty of links along the way.
Check out what’s new and the way Kevin handles the deployment.
Comments closedImke Feldmann has a clever solution to a problem:
When you reference another query in Power Query you will automatically get the results of the final step. But what if you want to reference a step that sits within that referenced query?
One solution would be to split up that query into 2 and reference the final result of the splitted query. But that could result in additional query time, as the data might be pulled from the source twice (once for each query).
Read on for a better way to do it.
Comments closed