A Fine Slice Of SQL Server
Cecilia Brusatori gives us a date dimension created with DAX:
We are going to follow the steps to manually create a dimension Date table with basic DAX. First thing, we need to create a new table.
Click through for the demo. There’s a lot more you can include in a date dimension, but this gives you the starting point.
Comments closedMartin Surasky looks at what SQL Server 2016 changed with respect to High Availability options:
AlwaysOn Availability Groups, first introduced in SQL Server 2012 is a feature that is conceptually similar to database mirroring. I’m going to assume you already know what AlwaysOn Availability Groups are in general, their main purpose and how they are different (essential aspects at least) from other technologies to provide replication such as Database Mirroring.
In SQL Server 2014, the significant enhancement to availability groups was the increase in the number of supported secondary replicas from three to eight. SQL Server 2016 includes a number of new enhancements
The biggest thing about it, as I recall, was stability: I wouldn’t have recommended too many places go into production with Availability Groups in 2012, but by 2016, many of the biggest bugs were ironed out.
Comments closedFor
VARCHAR, some of you might be thinking that it was “1” until recently when SQL Server 2019 introduced the “_UTF8” collations. Nope. The last time “1” was correct forVARCHARwas back in SQL Server 7.0, before SQL Server 2000 introduced the Windows collations which offered some Double-Byte Character Sets.For
NVARCHAR, some of you might be thinking that it was “2” until SQL Server 2012 introduced the “_SC” collations that fully support Supplementary Characters (UTF-16). Sorry, still incorrect. “2” was never technically correct forNVARCHAR, it was only temporarily correct for the first few years (until Supplementary Characters were defined in Unicode 3.1, released in March, 2001). Ever since SQL Server 7.0 introduced theNCHAR,NVARCHAR, andNTEXTdatatypes, it has been possible to store whatever UTF-16 byte sequences you want, even if they are currently undefined. The older collations do not recognize surrogate pairs / Supplementary Characters, but that’s not related to SQL Server’s ability to store and retrieve any 16-bit code point. As long as you are using a font that supports Supplementary Characters, they should display correctly.
Solomon is one of a handful of people I’ve met who has collations and characters down cold.
Comments closedSebastian Sauer has a two-parter on waffle plots. The first part is an introduction:
A waffle diagram is a variant of (stacked) bar plots or pie plots. They do not have great perceptual properties, I’d suspect, but for some purposes they may be adequate. This is best explored by example. This post draws heavily from the introduction of hrbrmstr to his Waffle package.
The second part uses emojifont to show pictograms as well:
A Pictogram may be defined as a (statistical) diagram using icons or similar “iconic” graphics to illstrate stuff. The waffle plot (see this post) is a nice object where to combine waffle and pictorgrams. Originally, this post was inspired by HRBRMSTR waffle package, see this post, but I could not get it running.
Maybe the easiest way is to work through an example (spoiler: see below for what we’re heading at).
This type of plot doesn’t work for everything, but I can think of a few places where it’d be the right choice.
Comments closedBrett Powell has a process to find Power BI V1 workspace owners:
As most readers of this blog likely know, there are two very different kinds of workspaces in Power BI – V1 or ‘classic’ workspaces which are tied to Office 365 groups and V2 or ‘modern’ workspaces which are not. V2 workspaces have many advantages beyond their independence from Office 365 which you can read about elsewhere but for a bit of context you can read the GA announcement of V2 workspaces from back in April.
Since upgrading to V2 workspaces has been a manual process thus far, most Power BI tenants contain a mix of V1 and V2 workspaces. You may also have read the recent announcement of a new feature in the Power BI service available to workspace admins to upgrade their V1 workspaces. This blog post is all about identifying these V1 workspaces and their admins.
Click through for the process, as well as Brett’s recommendation regarding migration to V2 right now.
Comments closedChris Webb hits an interesting edge case with SQL Server Analysis Services Multidimensional:
Recently I was involved in troubleshooting a mysterious Analysis Services Multidimensional performance problem for a customer: the team worked out that certain queries run by certain users were extremely slow, and that these users were members of roles where dimension security was applied, but the amount of slowdown – queries going through the role were taking over 10 minutes compared to a few seconds when run as an administrator – was unlike anything I had seen before. It turned out that the cause was having the Enable Visual Totals box checked on every attribute on the dimension where security was applied, not just the attributes whose members were secured.
Read on for a reenactment of the problem.
Comments closedAnthony Nocentino gives us another option for upgrading SQL Server on containers:
Yesterday in this post I described a method to correct permissions when upgrading a SQL Server 2017 container using Data Volumes to 2019’s non-root container on implementations that use the Moby or HyperKit VM. My friend Steve Jones’ on Twitter wondered if you could do this in one step by attaching a shell (bash) in the 2017 container prior to shutdown. Absolutely…let’s walk through that here in this post. I opted to use an intermediate container in the prior post out of an abundance of caution so that I was not changing permissions on the SQL Server instance directory and all of the data files while they were in use. Technically this is a-ok, but again…just being paranoid there.
Click through for that process. The good news is that with upgrading from SQL Server 2019 to SQL Server 202x, I wouldn’t expect that we’d need to go through this again, as the process would stay non-root forevermore.
Comments closedMatthew Roche announces an interesting tool:
This new tool was included as part of the BRK2046 session on Power BI Premium at MBAS. The whole session is valuable, but the tool itself comes in around the the 32 minute mark. There’s a demo at the 37 minute mark. The tool is available today on github.
This tool will help Power BI Premium customers better plan for how their specific workloads (reports, dashboards, datasets, dataflows, and patterns of access) will perform on a given Premium capacity.
Click through for instructions and a description of how it works.
Comments closedLook, I’m not saying that you’re the type that would make a change in production while users are working.
But suppose that you would want to add an identity column to dbo.Demo, and change the clustered index to include that identity column, and make the index unique? Because it’s the table’s clustered index, you’re effectively talking about rebuilding the table (remember, the clustered index is the table), which involves reorganizing all of the rows into a new b-tree structure. While SQL Server is busy doing that, nobody will be able to read the contents of the table.
Daniel mentions a read-only table, though you could also do this with a read-write table as long as you have triggers to keep the two tables in sync until go time. That adds to the complexity, but it is an option if you need it.
Comments closedJosh Smith shows how you can enable MSDTC in a buttoned-down environment:
This is just a fancy way of saying you need to be better friends with who ever is managing your enterprise firewall. I hadn’t had to touch the DTC until a recent vendor insisted their application wouldn’t work without it (despite their only having a single data store). The MSDTC was developed to coordinate transactions that would span multiple machines and was originally introduced in SQL Server 2000.
In theory it’s not super complicated: just enable the DTC service/communication on the servers in question and turn on some built in firewall rules on the servers right? Almost.
Read on for the full set of instructions.
Comments closed