Press "Enter" to skip to content

Author: Kevin Feasel

Differences between Tableau and Power BI Data Models

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

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

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

Measuring the User Experience

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

Text Customization with ggtext

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

Improving Async Stats Update Concurrency

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

Batch Mode on Rowstore in SQL Server

Monica Rathbun introduces us to one of the biggest internal improvements for SQL Server 2019:

Under compatibility level 150, in both SQL Server 2019 and Azure SQL Database, you now can use batch mode for CPU-bound analytic type workloads without requiring columnstore indexes. There is no action needed to turn on batch mode aside from being on the proper compatibility mode. You also have the ability to enable it as a database scoped configuration option (as shown below), and you can hint individual queries to either use or not use batch mode (also shown below). If you recall in my earlier blogs on columnstore, it is batch mode in conjunction with page compression that drastically increases query performance. This feature, Batch Mode on Rowstore, allows all operators enabled for batch mode to operate as such in SQL Server.

What does this mean? It means that query operations can process data faster, more efficiently and mimic what makes columnstore so fast. Instead of reading row by row (row store mode) it can read in chunks i.e. batches of 900 rows at a time. The performance impact of this can be tremendous which effectively uses CPU more efficiently.

Read on to see the limitations and benefits.

Comments closed

Handling Messages with Service Broker

Chris Johnson continues a series on Service Broker:

This stored procedure is used by the queue to handle messages that arrive. When a message arrives the queue will execute the procedure over and over until there are no more messages on the queue. Your stored procedure therefore needs to be removing messages as it goes. You have the option to have multiple versions of the query executing at once, to clear down a queue faster or to keep up with a high volume of messages, using the MAX_QUEUE_READERS setting. You can turn the stored procedure on or off using the STATUS, while this is set to OFF nothing will happen but as soon as it is set to ON the query will start processing messages again. Finally you need to specify what user the query will execute under. The options here are SELF, as the current user (the person who runs the CREATE or ALTER script), OWNER, as the person who owns the queue, or a username that the current user has impersonate permissions for.

Chris provides us an example of what a procedure could look like and also covers briefly the concept of poison message handling.

Comments closed

Frequently Asked Availability Group Questions

Sean Gallardy answers the voices swarming in his head:

Q3: Do my secondary replicas really need to be the same level of hardware as my primary replicas?
Sean: They should, this assumes you are eventually going to fail over to them or they might need to run your workload. It does *not* assume they will be used for readable workloads. Remember that the secondary replica is already accepting and writing log data, redoing that data, and then if you’re running a bunch of read workloads on top of that it might actually be *busier* than your primary. Yes, I’ve witnessed secondary replicas getting hammered harder than their associated primary.

Read on for informative and entertaining answers, and avoid those rusty nails.

Comments closed