Press "Enter" to skip to content

Month: December 2019

Creating a Custom Power BI Filter Pane

Published 2019-12-30 by Kevin Feasel

Tomaz Kastrun walks us through creating a custom filter pane in Power BI:

Having as much possible Power BI estate when it comes to putting the visuals for data exploration, is everyone’s dream. But the slicers for “slicing and dicing” the data is also very important. Having all the slicers hidden has always been mine go-to design.

Several similar ideas have been shown, Guy in a cube (Adam and Patric) have both done similar ideas over past years, many questions have also been answered on Power BI community website. And mine requirements were similar:

1. have the ability to hide the filter pane
2. have the selected slicer items listed

Read on to see how Tomaz took care of the issue.

Comments closed

When OBJECT_NAME() Can Block

Published 2019-12-30 by Kevin Feasel

Andy Mallon shows us that the OBJECT_NAME() function does not care about your READ UNCOMMITTED transaction isolation level:

That’s just going to sit there and wait, and wait, and wait, and wait. Because the table was created inside a transaction, the metadata about the table is uncommitted, and thus unavailable to the second session. The result is that my second session waits & waits & waits until the first session is committed (or rolled back).

OK, so it’s being blocked due to an uncommitted transaction. We could try doing dirty reads, right? I’ll kill my second session that’s been blocked, and I’ll throw in SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;. That’s like just like using a nolock hint:

USE AM2_WTF;
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT OBJECT_NAME(581577110);

What the… It’s still being blocked by the uncommitted CREATE TABLE. 

Read on to understand why, and what you can do about it.

Comments closed

The JOIN Function in SSRS

Published 2019-12-30 by Kevin Feasel

Tim Mitchell explains what the JOIN() function does in SQL Server Reporting Services:

The JOIN() function in SQL Server Reporting Services is a handy tool that allows you to turn a list into a delimited string value. This function accepts two parameters, a list and a delimiter with which to separate the output, and returns a string with that list separated by the specified delimiter character.

It’s not an inaccurate name, but I’m surprised it’s not named CONCAT_WS() or something like that given how the term “join” has such a strong connotation in the relational database world.

Comments closed

Query Concurrency + Azure Synapse Analytics

Published 2019-12-30 by Kevin Feasel

James Serra takes us through query concurrency with Azure Synapse Analytics:

A common question I here from customers is because of the performance of Azure Synapse Analytics (formally called Azure SQL Data Warehouse or SQL DW), can they run Power BI dashboards against it using DirectQuery (and not have to use Azure Analysis Services (AAS), Import the data into Power BI, or use Power BI aggregation tables), avoiding having another copy of the data (saving money), and having data “real time” (as of the last refresh of the data warehouse)?

There are two things to think of in considering an answer this question. The first is if you will get the performance you need (discussed in my last blog), the second is if a certain amount of concurrent queries or connections will cause a problem (the subject of this blog).

Read the whole thing.

Comments closed

Diagnosing Power Query Steps

Published 2019-12-30 by Kevin Feasel

Chris Webb takes us through the Diagnose Step button in Power Query:

As you might have guessed, it’s closely related to the Query Diagnostics functionality that was introduced back in October. Whereas the existing Query Diagnostics functionality allows you to see what happens inside the Power Query when a query is executed, this new feature does something similar but allows you to run a query up to a specific step. This is useful in scenarios where you want to reduce the diagnostics data you are collecting to a subset of the steps in the query without having to comment out a lot of M code.

It looks pretty useful.

Comments closed

TempDB Usage and WhoIsActive

Published 2019-12-30 by Kevin Feasel

Josh Darnell takes us through a weird scenario where WhoIsActive can’t catch the real culprit:

Let’s say you are informed that tempdb is getting hammered on a production SQL Server instance (in the “lots of reads and writes” sense, not the “lots of shots of tequila” sense), and it’s disrupting other workloads on the system. You may have found this out through the power of monitoring (tempdb files are growing or full), or your favorite DMV queries, or just from being really smart.

You spring into action to find the offending query, and run EXEC sp_WhoIsActive but get…nothin’:

I did not successfully guess why this might be, but Josh explains it well.

Comments closed

How Spark Runs on YARN with HDFS

Published 2019-12-27 by Kevin Feasel

Sarfaraz Hussain explains how some of the pieces of the Hadoop ecosystem fit together:

Once it verifies that everything is in place, it will assign a Job ID to the Job and then allocate the Job ID into a Job Queue.

So, in Job Queue there can be multiple jobs waiting to get processed.

As soon as a job is assigned to the Job Queue, it’s corresponding information about the Job like Input/Output Path, the location of the Jar, etc. are written into the temp location of HDFS.

Read the whole thing.

Comments closed

Testing Kafka Streams

Published 2019-12-27 by Kevin Feasel

Jukka Karvanen takes us through TopologyTestDriver in Apache Kafka Streams:

Similarly to testing a single record, it is possible to pipe Value lists into a TestInputTopic. Validating the output can be done record by record like before, or by using the readValueToList() method to see the big picture when validating the whole collection at the same time. For our example topology, when the test pipes in the values, it needs to validate the keys and use the readKeyValueToList() method.

Testing streams of data (regardless of the product) is enough of a mental shift that it’s not an easy problem. For that reason, I welcome any tool which simplifies the process.

Comments closed

Auditing the Power BI Activity Log

Published 2019-12-27 by Kevin Feasel

Brett Powell shows how to query the Power BI Activity Log:

The new Power BI Activity Log makes it much easier for Power BI administrators to access audit log data to monitor and analyze activities occurring within the tenant they support. This blog post describes one solution for accessing, processing, and loading Power BI activity log data as part of an automated process to support ongoing Power BI administrative analysis and reporting.

Brett has a very nicely defined solution and lays it out step by step for us.

Comments closed

Columnstore Versus Page Compression

Published 2019-12-27 by Kevin Feasel

Aaron Bertrand compares columnstore and page compression on a specific table:

Recently someone at work asked for more space to accommodate a rapidly growing table. At the time it had 3.75 billion rows, presented on 143 million pages, and occupying ~1.14TB. Of course we can always throw more disk at a table, but I wanted to see if we could scale this more efficiently than the current linear trend. Sounds like a great job for compression, right? But I also wanted to try out some other solutions, including columnstore – which people are surprisingly reluctant to try. I am no Niko, but I wanted to make an effort to see what it could do for us here.

Note that I am not focusing on reporting workload or other read query performance at this time – I merely want to see what impact I can have on storage (and memory) footprint of this data.

Here is the original table. I’ve changed table and column names to protect the innocent, but everything else is relatively accurate.

Page compression won, and I’ve got a pretty good idea why (though some of the diagnostic info is gone): Aaron has several VARCHAR and NVARCHAR columns, and those blow up the columnstore dictionary pretty fast. Aaron has more to go in this series, so stay tuned.

Comments closed