Press "Enter" to skip to content

Author: Kevin Feasel

Uses of ROLLUP and CUBE

Published 2020-06-18 by Kevin Feasel

Greg Dodd wraps up a series on ROLLUP and CUBE:

I don’t think ROLLUP has a great use in the real world (feel free to comment below if you have a real use for it). The best I can see is that you have SQL Server emailing the results of a query to someone and they want subtotals. Adding totals can be done by any reporting app, probably more efficiently than SQL will do it, and it’s probably easier to debug there as well.

I do, however, think CUBE has an amazing purpose.

The funny thing is, I disagree and find ROLLUP a lot more useful than CUBE because ROLLUP gives me aggregations along a hierarchy. If I set up my GROUP BY clause correctly, I can see the results of my Product Category –> Product Subcategory –> Product hierarchy quite easily. Yeah, I can also do this in reporting, but if I just need a quick printout or need to do something special with the subtotals, ROLLUP is great. I definitely use GROUPING SETS more than ROLLUP and CUBE combined, but I’ve had production code useROLLUP and, to the best of my recollection, never have I created a procedure or report which is based off of CUBE.

Comments closed

Three Rants for the Price of One

Published 2020-06-18 by Kevin Feasel

Nate Johnson is a few months early for Festivus:

Another thing. This isn’t a “new update” thing; this is a long-standing “Oh my lord I can’t believe they DESIGNED it this way, WHAT were they THINKING?!?!” thing. Files. Sending & receiving files. I get an picture, like a screenshot, from a user (that’s NOT a OneDrive link, because that’s a whole ‘nother can-of-worms). I click to download it. It goes.. where?

OH RIGHT. It goes to my ‘Downloads’ folder. That dumpster-fire, where everything from anywhere goes into, and nobody keeps it organized, and nobody knows how to find anything unless they’ve gotten smart enough to sort by Date Modified descending. Right, that.

I can’t say I disagree about any of what Nate covers.

Comments closed

How SQL Server Stores UNIQUEIDENTIFIERS

Published 2020-06-18 by Kevin Feasel

Randolph West digs into what a UNIQUEIDENTIFIER looks like in storage:

Let’s take our example GUID again: CC05E271-BACF-4472-901C-957568484405. If we look at the table storage for this row, we’ll find it persisted as follows: 0x71E205CCCFBA7244901C957568484405 (alternating octets are highlighted in bold).

If you haven’t been following this series, this is a good place to remind you that SQL Server stores data using little-endian sequencing on disk and in memory. In the vast majority of cases, bytes are stored in reverse order because that’s how Intel CPUs like their data. However GUIDs are persisted slightly differently because of their sort order.

This is probably the most GUIDs I’ve seen in a single blog post.

Comments closed

Differences between Tableau and Power BI Data Models

Published 2020-06-18 by Kevin Feasel

David Eldersveld lays out a few differences between Tableau and Power BI’s data models:

Prior to 2020.2, Tableau had a physical data model that allowed joins between tables. This layer is still present, and it is similar to Power BI’s merge capability within Power Query to join multiple tables into a single table.

With the introduction of the logical model in Tableau 2020.2, a single logical table may consist of one or more physical tables. Two or more logical tables may be related to each other. This approach shares many similarities with Microsoft’s tabular model, but there are currently some core differences that may impact how you approach data modeling in one tool versus another. It’s not always going to be a straightforward scenario to apply knowledge of how to build a data model in one product to building it in the other.

NOTE: This post considers some core differences between Tableau and Power BI modeling as of June 2020. It does not go into detail on announced roadmap features such as Power BI’s enhanced composite models coming later in 2020, which will allow scenarios like combining a “live connection” composite model with additional data sources, or even multiple live connection models.

Read on for four differences David has found.

Comments closed

Improving the system_health Session

Published 2020-06-18 by Kevin Feasel

Aaron Bertrand takes a look at a pre-configured tool and gives us ways to make it better:

I was recently trying to troubleshoot a SQL Server replication-related deadlock that our monitoring tool didn’t capture, and tried to find information about it in the system_health Extended Events session. With the default retention settings and the amount of noise contributed by security ring buffer events, I quickly discovered that the session only had data going back less than two hours. Meaning unless I started investigating an event immediately after it happened, all evidence had rolled out forever.

Read on for Aaron’s guidance around this. The natural next step is to build out your own extended events which capture what you need.

Comments closed

SQL Server Assessment Extension for Azure Data Studio

Published 2020-06-18 by Kevin Feasel

Ebru Ersan announces a new extension for Azure Data Studio:

SQL Server Assessment Extension for Azure Data Studio provides a user interface for evaluating your SQL Server instances and databases for best practices. It uses SQL Assessment API to achieve this. In this preview version, you can:

– Assess a SQL Server or Azure SQL Managed Instance and its databases with built-in rules (Invoke Assessment)

– Get a list of all built-in rules applicable to an instance and its databases (View applicable rules)

– Export assessment results and list of applicable rules as script to further store it in a SQL table

It’s in preview status and requires version 1.19.0 (this month’s version) of Azure Data Studio at a minimum.

Comments closed

Text Customization with ggtext

Published 2020-06-17 by Kevin Feasel

Abdul Majed Raja shows an example of using the ggtext library:

ggplot2 is go-to R package for anyone who wants to make beautiful static visualizations in R. But most ggplot2 gplots look almost the same and little many data analysts or data scientists care about customizing it, primarily because it’s not very intuitive to do so. That’s where ggplot2 extensions come in very handy. ggtext is an R package (by Claus O. Wilke) that helps in customizing the text present in ggplot2 plots. It could be the text outside the plot canvas or the text (annotation) within the plot canvas.

Click through for the code sample and video. H/T R-Bloggers.

Comments closed

Measuring the User Experience

Published 2020-06-17 by Kevin Feasel

Kayode Osinusi takes us through different methods for measuring user experience:

Task success rate is one of the most widely used and easily understood UX metrics. It shows the percentage of participants that successfully complete a task and helps designers identify user experience issues. As long as tasks have clearly defined goals, success rates can be measured.

Tasks like completing a signup process or adding a specific item to a shopping cart are well-suited for this metric. Keep in mind, success rate doesn’t explain how well users perform tasks or why they fail them.

Read on for several additional measures and frameworks.

Comments closed

Improving Async Stats Update Concurrency

Published 2020-06-17 by Kevin Feasel

Dimitri Furman announces a change in Azure SQL Database:

In Azure SQL Database and Azure SQL Managed Instance, the background process that updates statistics asynchronously can now wait for the schema modification lock on a low priority queue. This improves concurrency for workloads with frequent query plan (re)compilations.

New behavior is enabled with the ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY database-scoped configuration. This feature is currently in public preview.

Dimitri does a good job of explaining what this means and how it can make life a little better for people querying tables with statistics updates.

Comments closed