Press "Enter" to skip to content

Day: May 2, 2019

UpSet Plots for Set Analysis

Published 2019-05-02 by Kevin Feasel

Laura Ellis digs into the UpSetR package:

UpSet plots have a very cool parameter called queries. Queries can be used to define a subset of the data that you would like to highlight in your graph. The queries property takes in a list of query lists which means that you can pass multiple queries into the same graph. Each query list allows you to set a number of properties about how the query should function.

In this example we are viewing the Cycle and Walk set intersection (query and params). We want the query to be highlighted in a nice pink (color). We want to display the query as a highlighted overlap (active) and we will give it a name that we add to the chart legend (query.name)

I’ve not seen an UpSet plot before but it dumps a lot of information into a relatively small space. I’ll have to spend some time learning more about these plots.

Comments closed

Improving Diagrams

Published 2019-05-02 by Kevin Feasel

Stephanie Evergreen gives us some tips for improving diagrams:

As ubiquitous as diagrams are, they have some significant shortcomings.

Diagrams tend to be most useful as a mental organizing activity for the people who make the diagram. Outsiders coming in fresh have a much harder time seeing how the elements of the diagram fit together and make sense. I think that is generally because diagrams lack enough narrative to explain what is going on. We often use diagrams without explaining them. Or, more precisely, we use diagrams without connecting the diagram pieces to their associated parts of our narrative.

Read on for an example.

Comments closed

The CosmosDB Emulator

Published 2019-05-02 by Kevin Feasel

Hasan Savran has a way to let you play with CosmosDB without dropping any cash on it:

CosmosDB Emulator is a must have tool if you develop applications for Azure CosmosDB. Also, it’s a great tool to have if you like to learn about Azure CosmosDB but you have limited access to Azure for any reason. Azure CosmosDB team constantly works to make all available tools better including the emulator. Currently, emulator supports SQL, Cassandra, MongoDB, Gremlin and Table API. Data Explorer feature supports only SQL API for now. Emulator’simplementation is different than the service and you should not use it for stress testing, you can not test global replication or latency for read and writes.

It’s also useful for testing scenarios where you want some level of integration testing but don’t want to rely on an external service.

Comments closed

Minimal Logging When Inserting into Heaps

Published 2019-05-02 by Kevin Feasel

Paul White gives us the lowdown on minimal logging when performing INSERT..SELECT operations into heap tables:

When inserting rows using INSERT...SELECT into a heap with no nonclustered indexes, the documentation universally states that such inserts will be minimally logged as long as a TABLOCK hint is present. This is reflected in the summary tables included in the Data Loading Performance Guide and the Tiger Team post. The summary rows for heap tables without indexes are the same in both documents (no changes for SQL Server 2016):

But it’s not quite that straightforward, as Paul shows. Read the whole thing.

Comments closed

Reverse Engineering the Key Influencers Visual in Power BI

Published 2019-05-02 by Kevin Feasel

Chris Webb learns how the Key Influencers Power BI visual works, including some interesting undocumented functions:

A fascinating insight into how Power BI works, but is this any practical use to us? Let me be clear: I don’t think you should be using any of these functions yourself in a real-world report. I’m sure all this would be documented and publicised if Microsoft did want us to use it ourselves! Another consideration is that these new functions return tables and that makes them awkward to use in regular .pbix Power BI reports – I guess we could create calculated tables although that’s not as flexible as returning a table from a query as shown above. That said, even though we can’t write our own DAX queries in regular Power BI reports, we can write our own DAX queries in Paginated Reports and we can now create Paginated Reports that use a Power BI dataset as a data source. I tested putting one of the queries generated by the Key Influencers visual into a Paginated Report connected to the same dataset and it worked ok (even after publishing). You can also embed DAX queries connected to a published dataset in Excel too, as I show here. Hmm, plenty to think about then…

Chalk this up as “fun to know but not recommended to use yourself.”

Comments closed

Sort Descending in SSRS

Published 2019-05-02 by Kevin Feasel

Nate Johnson fixes an annoying problem with SSRS:

But you know what’s kinda annoying? You can’t dictate a “first sort direction” — it just assumes that the first time you click the sort-arrows, you want ‘Ascending’ (lowest first). Then you can switch to ‘DEscending’ (highest first). This makes perfect sense for alpha values (strings), but not always for numeric values — at least not when you’re dealing with money, when generally the highest dollar amount is the most important!

So let’s make it real simple for the end-user to get the ‘best’ behavior by default. Let’s try to make it sort by our ‘Revenue’ column in DEscending order first. Ready?

It’s an unintuitive and easy solution to the problem.

Comments closed

Mobile Reports in Power BI

Published 2019-05-02 by Kevin Feasel

Prathy Kamasani shares some thoughts on what makes for a good Power BI mobile report:

Steadily more users are getting interested in Power BI mobile reports, especially when you design reports for executives; they are more interested in mobile view, of course, they do want to explore the detail data but not always.  Until recently, I was someone who doesn’t pay much attention to mobile reporting unless the user explicitly asks for one. So, when I don’t create a layout for mobile when the user opens the report using Mobile App, it automatically opens the report in landscape mode. Which is fine but it’s not designed for that purpose, it misses the sleek look.

Creating a mobile report is pretty easy in Power BI but creating a good one is not that easy. In this blog post, I want to show how I approach mobile reports in Power BI.

For me, the key is glanceability. People looking at a mobile report typically don’t have fine-grained control mechanisms like a mouse—it’s thumbs on a phone screen (or maybe fingers on a tablet if you’re lucky). This means you don’t want fine-grained controls. Instead, lay things out as simply as possible and don’t make people click on tiny sections of the screen.

Comments closed

Searching For Text Across All Columns in Power BI

Published 2019-05-02 by Kevin Feasel

Imke Feldmann has an interesting solution to a text search problem in Power Query:

In the native function, you have to pass in a record with search term and column name. So if you search for “blue” in column “Description”, your formula would look like so:

Table.Contains( YourTableName, [Description = "blue"] )

But that’s not what I want in this case. I want the formula to search through all columns within the table for the occurrence of “blue”.

Read on to see how to do this.

Comments closed

Replacing Text Across SQL Agent Jobs

Published 2019-05-02 by Kevin Feasel

Max Vernon shares a script to perform a find-and-replace across SQL Agent jobs:

Once in a while you might need to make common changes to a lot of SQL Server Agent Jobs. For example, if you change the path where you store SQL Server backup files, you might need to update many jobs to point at \\SERVERB\Backups instead of \\SERVERA\Backups. The script below provides a simple instance-wide find-and-replace for SQL Server Agent job-step commands. It modifies the command text for all jobs that contain the matching @Find parameter, replacing it with the provided @Replace value. You can exclude jobs by adding them to the list of values in the #excludeJobs table.

Click through for the script.

Comments closed