Press "Enter" to skip to content

Author: Kevin Feasel

Updating Word or Powerpoint with Power BI Data

Published 2021-02-24 by Kevin Feasel

Stephanie Bruno solves an interesting problem:

In my case, colleagues are required to develop reports every quarter with the most updated data we have and with a lot of commentary included. For example, they create the same 60-page document every quarter with the same tables and charts, and then modify the narrative depending on the data. For this reason, paginated reports may not be the right solution because the narrative varies so much. They also have a very particular format for the charts and tables that is hard to reproduce in Power BI. Finally, the data may be changing up to the day before the report is due. Their process in the past was to export the data they needed from Power BI, open it in Excel, copy and paste to another file where they had all of their charts built, export/copy/paste from more visuals, tweak the charts, then copy and paste the charts into their Word document. Then work late into the night doing this a few more times as the data is updated.

To help free them from this tedium, we worked out a new process to get their Word and PowerPoint files automatically updated in the format they required, using our good friend, “Analyze in Excel.”

Click through to learn how.

Comments closed

Documenting dm_db_missing_index_group_stats_query

Published 2021-02-24 by Kevin Feasel

Erik Darling does a good deed:

When I was checking out early builds of SQL Server 2019, I noticed a new DMV called dm_db_missing_index_group_stats_query, that I thought was pretty cool.

It helped you tie missing index requests to the queries that requested them. Previously, that took a whole lot of heroic effort, or luck.

This has long been a pain point for index-based query tuning. You can guess what the types of queries can look like based on the suggested columns, but one risk of that guessing is that index order matters but the columns get returned in the order in which they exist on the table, not necessarily the order in which they would be most useful.

Comments closed

Tips and Tricks for VS Code and Azure Data Studio

Published 2021-02-23 by Kevin Feasel

Steve Jones shares some advice:

I really like Visual Studio Code. The more I use it, the more I get comfortable in it, and the more I appreciate the design and flow of using the editor. I didn’t feel that way early on, preferring the comfort of Visual Studio, but now I most often use VS Code.

The other day I saw an article from one of the PMs for the product, with a number of tips and tricks. I like some of these, and have already found a few to be handy in my work. 

Click through for more info. Switching to a new IDE can be challenging, but searching out tips and tricks like this can smooth out the transition period bumpiness and potentially unlock efficiencies you wouldn’t even have known about.

Comments closed

SSD Bit Rot

Published 2021-02-23 by Kevin Feasel

Jonathan Kehayias explains why SSDs aren’t good long-term backup devices:

Back in 2012, I wrote a post titled Looking at External Disk Performance using USB 3.0 and eSATA with SSD, where I tested a number of external drive caddy’s with SSD’s that I had replaced and just had sitting around. Ultimately I started to use those SSD’s for storing information that I didn’t really need to have on my laptops and when they were full, they ended up in my desk drawer, where they have sat, unplugged and “safe” for the last 7-8 years. Or so I thought. With cheer competitions season in full swing, one of the things I love to do is shoot photos of my kids and their teammates competing, and storing RAW files that are 25-35MB per photo when you shoot 1000+ photos in a weekend across four different teams starts to take up a lot of space, so I figured I would pull out the old SSD’s and see what was on them that was worth keeping, delete what wasn’t and I could then move last years RAW files over to them and archive them for safe keeping. WRONG!!!  Of the four SSD’s I had stored data on, 100% of them had data loss due to a phenomenon known as bit rot. One of them wouldn’t even show up in Disk Manager in Windows and had to be low level formatted and reset using diskpart’s clean command due to partition table corruption.

This is a nasty scenario. Read on to learn more about how you can detect the issue and keep in mind the 3-2-1 rule: 3 copies of your data in at least 2 storage media, at least 1 of which is offsite.

Comments closed

Using Dataflows to Speed Up Power BI Refreshes

Published 2021-02-23 by Kevin Feasel

Reza Rad shows an interesting use case of Power Query Dataflows:

No matter what caused the data source to be slow (the old technology, performance issues, slow connector, limitations, etc), it will cause the data refresh of the Power BI dataset to become slow. Even if you have an incremental refresh setup, it might not still help much, because sometimes the query folding doesn’t happen. Slow refresh time will not only be bad for the service, but it will be also bad for the developer who has to wait a long time for the data to be available after each refresh.

Read on to see how you can use Dataflows to speed up refresh times (though not speeding up the slow data source itself). Reza also has a video on the topic.

Comments closed

Deprecated Features to Ignore

Published 2021-02-23 by Kevin Feasel

Aaron Bertrand begins a series:

The issue in this specific case is that they spent that time contemplating a decision based on incomplete information. Are those I/Os reads or writes? If the user has an open transaction and has merely read a lot of data, there is far less impact in rolling that transaction back than if they have changed a lot of data. So, instead of sys.sysprocesses, let’s see what the more modern DMV, sys.dm_exec_sessions, can show us about this session:

On a slightly different topic, Mala Mahadevan looks at old but not-deprecated features, some of which you shouldn’t use:

I am part of a weekly talk show we run at the TriPASS user group, called ‘Shop Talk’. Shop Talk was the brainchild of Kevin Feasel, our key user group lead..we meet on a bi weekly basis and discuss random tech topics related to sql server. Some of these are questions from our audience, and some are just ideas for discussion that one of us come up with. I am constantly amazed and grateful for how much I learn by being part of this show – from my co hosts and from the very intelligent audience we are blessed with. Last week, we discussed Brent Ozar’s blog post on ‘What SQL Server Feature Do You Wish Would Go Away?’. The recording of our discussion (this topic starts around 26:00) is here.

And if you want a quick synopsis of the last link, I have the notes from each episode of Shop Talk, including the one Mala references.

Comments closed

Enabling Always Encrypted in SQL Server

Published 2021-02-23 by Kevin Feasel

Scott Klein takes us through the process of enabling Always Encrypted:

Always Encrypted provides a separation between those who own the data and can view it, and those who manage the data but should have no access. Always Encrypted does this by allowing clients to encrypt sensitive data inside client applications while never revealing the encryption key to the database engine. 

One of our clients utilizes Always Encrypted to protect sensitive data. In our work for this client, we realized that a walkthrough documenting the end-to-end process of enabling Always Encrypted would be helpful for everyone.  

Click through for a walkthrough of the process.

Comments closed

Tidying the Confusion Matrix in R

Published 2021-02-22 by Kevin Feasel

Gary Hutson has a new package for us:

The package aim is to make it easier to convert the outputs of the lists from caret and collapse these down into row-by-row entries, specifically designed for storing the outputs in a database or row by row data frame.

This is something that the CARET library does not have as a default and I have designed this to allow the confusion matrix outputs to be stored in a data frame or database, as many a time we want to track the ML outputs and fits over time to monitor feature slippage and changes in the underlying patterns of the data.

I like the way caret shows the confusion matrix when I’m reviewing result on my own, but I definitely appreciate efforts to make it easier to handle within code—similar to how broom reads linear regression outputs. H/T R-bloggers

Comments closed