A Fine Slice Of SQL Server
Gilbert Quevauvilliers knows that time is a flat circle:
I have seen in the past Week-on-Week comparisons but one of the challenges is what happens when it overlaps years. Especially at the start of a year like it is now Feb 2021, the users want to compare week-on-week for the past 3 months.
My challenge was to find a way to have a continuous week number over multiple years.
Click through to learn how.
Comments closedBrent Powell has a new series for us:
Between the Power BI PowerShell modules and the Power BI REST APIs administrators have a rich set of tools to efficiently administer Power BI environments. Custom administrative and monitoring solutions based on these technologies have been featured on this blog before but today we will start the first of a two-part series highlighting simple script examples that Power BI administrators can use to address common scenarios.
The six PowerShell script files (.ps1) for today’s examples are available in my GitHub repo.
Check out those scripts, as well as Brent’s walkthrough of each.
Comments closedDavid Eldersveld gives us ways to go beyond simplistic tables in Power BI:
Conditional formatting for a table or matrix is an easy way to keep the grid and display raw numbers while providing visual cues that our brains process faster than reading numbers. Background color, font color, icons, and data bars help jazz up bland tables. It’s an easy compromise that brings some data visualization to table detail.
Click through for more examples of this.
Comments closedErik Darling shows why you might not want to set ANSI_DEFAULTS ON:
Rather than just setting the required options, which was apparently a lot of typing, someone had just set all the ANSI defaults on.
Read on to learn what the problem is and an alternative to using ANSI_DEFAULTS.
Nisarg Upadhyay wants to move files around in SQL Server on Linux:
In this article, I am going to explain how we can move the SQL database files to another location in Ubuntu 18.04. For the demonstration, I have installed Ubuntu 18.04, SQL Server 2019 on Linux on my workstation. You can read SQL Server 2019 on Linux with Ubuntu to understand the step-by-step installation process of the SQL Server 2019 on Linux. We will move database files of AdventureWorks2019 and Wideworldimportors database.
Click through for the process. It’s really similar to Windows in this respect. And, well, in most respects.
Comments closedMatthew McGiffen does the math:
I mentioned in my previous post about manually updating statistics that you can specify whether they’re updated using a full scan, or you can specify an amount of data to sample, either a percentage of the table size, or a fixed number of rows. You can also choose not to specify this, and SQL Server will decide for you whether to do a full scan, or to sample a certain amount of data.
I thought it would be interesting to look at what the sample sizes are that SQL will choose to use, depending on the amount of data in your table.
Click through for the result of Matthew’s analysis.
Comments closedDeepthi Goguri takes us through a fairly new feature in SQL Server:
With SQL Server 2017 and below versions, SQL Server always estimates one row for table variable as the table variable data gets inserted during the run time, so optimizer doesn’t know how many values it can expect coming out of the table variable. Due to this bad estimation, performance of the queries is effected.
Click through to see how this has changed in SQL Server 2019.
Comments closedDan Morris, et al, walk us through a survival analysis scenario:
In contrast to other methods that may seem similar on the surface, such as linear regression, survival analysis takes censoring into account. Censoring occurs when the start and/or end of a measured value is unknown. For example, suppose our historical data includes records for the two customers below. In the case of customer A, we know the precise duration of the subscription because the customer churned in December 2020. For customer B, we know that the contract started four months ago and is still active, but we do not know how much longer they will be a customer. This is an example of right censoring because we do not yet know the end date for the measured value. Right censoring is what we most commonly see with this form of analysis.
Click through for an intro as well as a half-dozen notebooks.
Comments closedPatrick LeBlanc explains why star schemas are so important for Power BI:
Are you using a flat table in your Power BI report? You may be missing out! Patrick explores what a STAR SCHEMA is and why you should be using it.
Click through for a video, as well as some guidance. That Kimball fellow sure was a sharp one, he was.
Comments closedHugo Kornelis starts a new sub-series within an existing series:
Welcome to part sixteen of the plansplaining series. The first of a few posts about how temporal tables affect execution plans. In this post, I’ll build on the last four posts on data modifications, building on the more generic discussion of data modification in the previous four posts. Later posts will look at data retrieval and some specific scenarios.
Hugo hits the highlights of temporal tables and how they handle insertion, deletion, and updating scenarios.
Comments closed