Press "Enter" to skip to content

Author: Kevin Feasel

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

Replicating Linear Models

Published 2019-07-05 by Kevin Feasel

John Mount has an interesting post looking at replicating linear models without training data:

Let’s work an example in R. Suppose we are working with a linear regression model and from our donor system we have extracted the following representation of the model as “intercept” and “betas”.

intercept <- 3 betas <- c(weight = 2, height = 4)

Our goal is to build a linear regression model that has the above coefficients. The way we are going to do this is by building our own synthetic data set such that the regression fit through this data set yields these coefficients.

It’s fairly straightforward to do this for linear models; as things get more complicated, however, the difficulty level spikes.

Comments closed

An Overview of Convolutional Neural Networks

Published 2019-07-05 by Kevin Feasel

Beth Ebersole explains what convolutional neural networks are and how they work:

Let’s quickly review neural networks.

Neural networks are universal approximators. This means that with enough neurons and time, a neural network can model any input/output relationship, to any degree of precision.

A standard feed forward neural network receives an input (vector) and feeds it forward through hidden layers to an output. SAS PROC NNET, for example, trains a multilayer perceptron neural network. As the name “multilayer” implies, there are multiple layers. Below we see the inputs (features), one hidden layer and the output (response, target). Each neuron is simply a mathematical function.

This is a complicated topic explained well. It’s also an overview more than a tutorial.

Comments closed

Tracking Remaining Drive Space in SQL Server

Published 2019-07-05 by Kevin Feasel

David Fowler has a new stored procedure for you:

We love xp_fixeddrives here, it’s a quick and simple way to see how much space you’ve got available on your drives. But there are just a couple of things that I really wish it would do better.

Firstly, I’d love to see the total size of the drive and possibly even a percentage of free space left.

Secondly, mount points. If you’ve got any databases that are on mount points, it’s not going to give you any idea of what you’ve got left on there.
This is the reason that I put together sp_drivespace.

Click through for the script. It would be interesting to see if this works on Linux as well.

Comments closed

Microsoft’s R Roadmap

Published 2019-07-05 by Kevin Feasel

David Smith has a review of Microsoft’s R roadmap, focusing on Azure:

The post references this guide to the machine learning services in Azure, along with their supported languages. Services that currently support R include Azure Machine Learning Studio, SQL Server Microsoft Machine Learning Service, Microsoft Machine Learning Server, Azure Data Science Virtual Machine, Azure Databricks, and more.

David links to this strategy post:

The R and Python programming languages are primary citizens for data science on the Azure AI Platform. These are the most common languages for performing data preparation, transformation, training and operationalization of machine learning models; the core components for one’s digital transformation leveraging AI. Yet they are fundamentally different in many aspects, directly affecting not only deployed solutions IT architectures but also but also corporate strategies for developer skills and product supportability.
 
This series of articles is designed help you understand the options your company and customers have to support and evolve their R strategy.

It’s good to see some of this out in the open for planning purposes.

Comments closed

Chaos Engineering and KubeInvaders

Published 2019-07-05 by Kevin Feasel

Andrew Pruski wants to play a game:

KubeInvaders allows you to play Space Invaders in order to kill pods in Kubernetes and watch new pods be created (this actually might be my favourite github repo of all time).

I demo SQL Server running in Kubernetes a lot so really wanted to get this working in my Azure Kubernetes Service cluster. Here’s how you get this up and running.

I got to see Andrew show it off at SQL Saturday Cork and it was as fun as you’d expect.

Comments closed

Drillthrough from Power BI to SSRS

Published 2019-07-05 by Kevin Feasel

Paul Turley shows how you can drill through from a Power BI dashboard into an SSRS report:

This recipe primarily involves Power BI report design techniques. I’m not going to get into the details of Power BI report design but will cover the basics with a partially-completed report to get you started. If you are less-experienced with Power BI you can use this as an example for future report projects.

The sample database and files will be available in the forthcoming book: SQL Server Reporting Services Paginated Report Recipes, 2nd Edition (working title).

These instructions are provided as an example but refer to files that will be available when the book is published. Please contact me in the comments with questions and feedback.

You can’t get the files just yet, but you can see what Paul does to get this working.

Comments closed