Press "Enter" to skip to content

Month: July 2019

Auditing Databases in Use

Published 2019-07-09 by Kevin Feasel

Jason Brimhall shows how you can use Extended Events to figure out if that database is still in use:

Here we can see there are indeed some databases that are still in use on this server. If we are looking to retire the instance, or migrate relevant databases to a new server, we have better information about how to go about planning that work. Based on this data, I would be able to retire the ProtossZealot database but would be required to do something with the ZergRush and BroodWar databases. In the case of the ProtossZealot database that is not being used, we now have evidence to present back to the team that the database is not used. It is now up to those insisting on keeping it around to justify its existence and document why it must remain in tact.

Click through for the script Jason used to determine this.

Comments closed

IsNumeric in Power Query

Published 2019-07-09 by Kevin Feasel

Matthew Roche (with an assist from Imke Feldmann) shows how you can check if a value—or part of a value—is numeric in Power Query:

Using Imke’s approach, this is what I ended up doing:

1. Extract the last two characters from the source column
2. When the extracted characters contain a number, convert them to numeric
3. Standardize the resulting value to represent the correct year

Read on to see how Matthew pulled it off.

Comments closed

Feeding IoT Data into Delta Lake

Published 2019-07-08 by Kevin Feasel

Saeed Barghi shows how you can stream sensor data from Azure IoT Hub into Databricks Delta Lake:

IoT devices produce a lot of data very fast. Capturing data from all those devices, which could be at millions, and managing them is the very first step in building a successful and effective IoT platform.

Like any other data solution, an IoT data platform could be built on-premise or on cloud. I’m a huge fan of cloud based solutions specially PaaS offerings. After doing a little bit of research I decided to go with Azure since it has the most comprehensive and easy to use set of service offerings when it comes to IoT and they are reasonably priced. In this post, I am going to show how to build the architecture displayed in the diagram below: connect your devices to Azure IoT Hub and then ingest records into Databricks Delta Lake as they stream in using Spark Streaming.

Click through for the instructions.

Comments closed

Copying Cassandra Data to HDFS

Published 2019-07-08 by Kevin Feasel

Landon Robinson shows how you can use Spark to extract data from Cassandra and move it into HDFS:

Cassandra is a great open-source solution for accessing data at web scale, thanks in no small part to its low-latency performance. And if you’re a power user of Cassandra, there’s a high probability you’ll want to analyze the data it contains to create reports, apply machine learning, or just do some good old fashioned digging.

However, Cassandra can prove difficult to use as an analytical warehouse, especially if you’re using it to serve data in production around the clock. But one approach you can take is quite simple: copy the data to Hadoop (HDFS).

Read on to learn how.

Comments closed

Creating a Slicer Plus Histogram in Power BI

Published 2019-07-08 by Kevin Feasel

Matt Allington shows how you can create a combination slicer and histogram in Power BI:

I was doing some online shopping last week and saw a price slicer with a histogram to show the concentration of price points.  I thought it was pretty cool, so decided to see if I could build this in Power BI.  I came up with a solution that works, and in this article I show you how you can do it yourself.

Click through for instructions and a video.

Comments closed

RegEx Capture Names in Powershell

Published 2019-07-08 by Kevin Feasel

Jeffery Hicks shows how you can use regular expressions to create capture names in Powershell:

Once you have matching patterns, you can define them with a name. The general layout looks like this:

(?<capture-name>Your-Pattern)

The parentheses are key. My datetime pattern can be defined as a named capture:

(?<date>\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}Z)

Click through to see how you can combine these and turn them into Powershell objects.

Comments closed

Power BI and SSRS Drillthrough

Published 2019-07-08 by Kevin Feasel

Paul Turley continues a series on drillthrough. First, from a paginated report into Power BI:

In the recipe titled “Drillthrough from Power BI to Paginated Report” I demonstrate how to use report parameters and a dynamic URL address to navigate between reports and pass parameter values in the URL from a Power BI report to detailed data in an SSRS paginated report. Using a similar technique, we can navigate from a paginated report to a Power BI report.

Power BI is very flexible and does not require you to define parameters like you would in a paginated report. You can literally filter a report using any field in the dataset.

After that, Paul posted a follow-up on the wherefore:

I recently published two blog posts to share some of my work-in-progress for the recipe book: Drillthrough from Power BI to an SSRS Paginated Report, and: Drillthrough from Paginated Report to Power BI. Both of these posts demonstrate navigation from one of the report tools to the other, essentially allowing users to work with these two capable tools in a full circle.  As the newer and more modern data analysis tool, Power BI fills and important gap but it is not a replacement for the type of multi-page grouped and repeated style of reporting that SSRS is optimized for. Likewise, Power BI excels as an interactive tool for data discovery and self-service analysis. SSRS and Power BI were borne from the same platform and and have common architectures but also many differences. Used together, the realm of possibilities is expansive.

It is important to understand that the techniques used to navigate and pass filter context between these report tools is limited to the capabilities of web query string parameterization. In my experience, this is rarely a show-stopper but I do occasionally encounter folks pushing the limits – both practically and technically.

It’s good to see interoperability between these two tools.

Comments closed

Sleeping Sessions Holding Locks

Published 2019-07-08 by Kevin Feasel

Jonathan Kehayias walks us through an interesting scenario:

A recent consulting engagement was focused on blocking issues inside of SQL Server that were causing delays in processing user requests from the application. As we started to dig into the issues being experienced, it became clear that from a SQL Server standpoint, the problem revolved around sessions in a Sleeping status that were holding locks inside of the Engine. This is not a typical behavior for SQL Server, so my first thought was that there was some sort of application design flaw that was leaving a transaction active on a session that had been reset for connection pooling in the application, but this was quickly proven not to be the case since the locks were later being released automatically, there was just a delay in this occurring. So, we had to dig in further.

Click through to learn what the root cause was.

Comments closed

Estimating Backup Footprint

Published 2019-07-08 by Kevin Feasel

John Morehouse has a Powershell script to estimate how much disk space you’re using with backups:

Recently, I needed to be able to determine the total size for all the most recent full backups for certain servers. Luckily for me these particular servers were using Ola Hallengren’s Maintenance Solution which simplified things since each back up had it’s own specific directory. Powershell has the flexibility to easily provide what I need with little effort.

Let’s take a look!!

John shows specifically the latest full backup, but you can extend this to include other sets as well.

Comments closed

Connecting Excel to Power BI Premium Datasets

Published 2019-07-08 by Kevin Feasel

Gilbert Quevauvilliers shows how to connect Excel to a Power BI Premium dataset directly, using its XMLA endpoints:

I have had seen a few questions in the Power BI Community, as well as from other people asking if it is possible to connect directly from Excel to a Power BI Premium dataset from Excel. Without having to log into the Power BI Service or download any .ODC files.

I am going to show you how to do this below.

Read on for a screenshot-fueled demonstration.

Comments closed