A Fine Slice Of SQL Server
Mike Cisneros goes bar-hopping:
Let’s take a closer look at that extra-tall bar chart. I came across a graph like this one when working with a recent client. They created a visual to compare the quarterly sales revenues for each of 25 different sales associates against their individual sales targets.
Mike then gives us two routes to improving the quality of this visual, one which maintains the bar chart style and one which replaces it with a Cleveland dot plot.
Comments closedJordan Witcombe takes us through an example:
This blog will walk you through the entire data lifecycle of ingesting data from SharePoint and Azure Blob Storage, through the lakehouse pattern, and finally to the reporting stage – all using Microsoft Fabric.
As we’re well into summer now, festivals can be a great way of spending time with friends and family. But all too often, we each want to see various acts and activities. How can we make this easier? With this dataset I created, it takes a simple Excel / CSV file with acts, stages, who wants to see this, and a personal rating. We will run this through the lakehouse pattern and finish with a curated/gold layer which can be used for reporting directly on top of. For more information on lake layers, you can see Ed’s blog Medallion Architecture: What is it?. So, you can highlight those clashes and keep everyone happy during your festival fun!
Read on for the showcase.
Comments closedThe Ascending Key Problem relates to the most recently inserted data in your table which is therefore also the data that may not have been sampled and included in the statistics histograms. This sort of issue is one of the reasons it can be critical to update your statistics more regularly than the built-in automatic thresholds.
We’ll look at the problem itself, but also some of the mitigations that you can take to deal with it within SQL Server.
Read on to see how this behaved prior to SQL Server 2014’s new cardinality estimator and what has changed since then.
Comments closedErik Darling has another update:
In this post, I’m going to talk about a couple cool changes to sp_HumanEventsBlockViewer, a procedure I wrote to analyze the blocked process report via Extended Events, and wish I had given a snazzier name to.
You see, when I wrote it, I pictured it as a utility script for sp_HumanEvents, which will set up the blocked process report and an extended event. But it turns out I use it a lot more on its own.
Read on for Erik’s update, including a neat trick around using an aggregate within a window function to generate ordering.
Comments closedChris Webb collects some measurements:
In the first post in this series I described the events in Log Analytics that can be used to understand throughput – the speed that Power BI can read from your dataset when importing data from it – during refresh. While the individual events are easy to understand when you look at a simple example they don’t make it easy to analyse the data in the real world, so here’s a KQL query that takes all the data from all these events and gives you one row per partition per refresh:
Click through for the KQL script, as well as what it all means and what you can get out of it.
Comments closedNikola Ilic gives us a primer on Kimball-style fact and dimensional modeling:
Before we come up to explain why dimensional modelling is named like that – dimensional, let’s first take a brief tour through some history lessons. In 1996, a man called Ralph Kimball published a book “The Data Warehouse Toolkit”, which is still considered a dimensional modelling “Bible”. In his book, Kimball introduced a completely new approach to modelling data for analytical workloads, the so-called “bottom-up” approach. The focus is on identifying key business processes within the organization and modelling these first, before introducing additional business processes.
This is a really good overview of the topic, though I’m saddened that “dimensional bus matrix” didn’t make the cut of things to discuss. Mostly because I like the name “dimensional bus matrix.”
Comments closedErik Darling has been busy. FIrst, sp_QuickieStore:
The first thing on the list that I want to talk about is the ability to cycle through all databases that have Query Store enabled.
If you have a lot of databases with it turned on, it can be a real hassle to go through them all looking for doodads to diddle.
Now you can just do this:
I added high-level disk metrics similar to what’s available in other popular scripts to mine. Why? Sometimes it’s worth looking at, to prove you should add more memory to a server so you’re less reliant on disk.
Especially in the cloud, where everything is an absolute hellscape of garbage performance that’s really expensive.
Click through for both sets of updates and thank Erik for his willingness to give so much to the community.
Comments closedMarc Lelijveld notes a chemical reaction:
Together with the introduction of Microsoft Fabric a long-awaited feature for Power BI also became available. Git integration now allows us to connect your workspace to a git repository to sync the meta data from your Power BI dataset and report between the workspace and the repository.
At the same time, Power BI deployment pipelines are around. The pipelines help you to move content between workspaces from Development to Test and finally to Production. The question that arises, when to use what? And how do they complement each other, or maybe even clash with each other?
Read on for Marc’s thoughts across these two topics.
Comments closedKenneth Fisher creates a diet version of db_ddladmin:
What if I want the user to be able to create VIEWS, TABLES, FUNCTIONS, etc. Basically, I want a db_ddladmin Jr. Something I can give to a developer that is focused on a single schema. Say the reporting schema.
The best practice is, of course, to use a role.
Click through for Kenneth’s script for a db_ddlcreator role.
Comments closedTom Collins distrusts (certificate) authority:
I’m getting this error when connecting Microsoft OLE DB Driver 19 for SQL Server & Microsoft ODBC Driver 18.x for SQL Server.
The certificate chain was issued by an authority that is not trusted
What is the fix for this error ?
Click through for some possible solutions.
Comments closed