Press "Enter" to skip to content

Category: Power BI

Subset And Apply Problems

Published 2017-01-11 by Kevin Feasel

Tom Martens explains a class of generic data processing problems:

Subset and Apply means that I have a dataset of some rows where due to some conditions all the rows have to be put into a bucket and then a function has to be applied to each bucket.

The simple problem can be solved by a GROUP BY using T-SQL, the not so simple problem requires that all columns and rows of the dataset have to be retained for further processing, even if these columns are not used to subset or bucket the rows in your dataset.

One quick example of this is running totals of orders for each customer, which Tom answers using T-SQL, R, and Power BI.  Click through for those three solutions.

Comments closed

Wanted: Sorting Stacked Charts In Power BI

Published 2017-01-10 by Kevin Feasel

Melissa Connors would like to control stack order for a stacked chart in Power BI:

Currently, it’s displaying the stacked column in alphabetical order from the bottom up (coyote/red on the bottom, fox/black in the middle, and wolf/gray on the top). I want the ability to display it by the count for each type, not the name. In this example, I want the order of coyote, wolf, and fox. I believed that there must be a way to control the order within a stacked column, so I clicked all over the UI and fired up the Google.

Read the whole thing and vote for this Power BI idea if it’s interesting.

Comments closed

Why Care About The Data?

Published 2017-01-04 by Kevin Feasel

Rob Collie explains some of the benefits of Power BI with respect to its ability to dig into data problems:

Many problems/opportunities can ONLY be addressed by making thousands of small behavior changes, on an ongoing basis, out in the “trenches.”  In other words, there are some which CANNOT be addressed by a small number of smart decisions made at the top of the org.  Improved pricing behavior by the sales force for instance.  Improved quality of service by field technicians, for another.

How do you make that happen?  How do you influence a large number of people to behave differently on an ongoing basis, especially when they operate at a distance (both organizationally and physically) from you?

Fancy management consulting firms charge six-seven figures to do this, but with Power BI and/or Power Pivot, it’s no longer necessary to make that outlay.  We’ve seen organizations crack this nut with in-house resources, modest budgets, and short timelines – even though the impact is absolutely enterprise-wide.  It takes some thought and iteration, for sure, but primarily, you “just” need to build a good scorecard.

This is a non-technical post, but I like it a lot because it helps motivate decisions and gives you a good reason to investigate whether your tools are doing the work you need them to do.

Comments closed

Horizontal Funnel

Published 2016-12-30 by Kevin Feasel

Devin Knight shows off the horizontal funnel Power BI custom visual:

In this module you will learn how to use the Horizontal Funnel Power BI Custom Visual.  The Horizontal Funnel functions somewhat similar to the traditional funnel but it allows you to display a secondary measure and has a few more customizations than you would normally get. You’ll find that the Horizontal Funnel is great for displaying a flow of data.

One of the better non-sales uses of funnels I’ve seen is tracking completion rates on multi-page forms or multi-step processes.  If you see a huge drop-off at one step in the process, it might indicate a bug in the form or some incongruity with the end user’s expectation.

Comments closed

Linear Gauge Custom Visual

Published 2016-12-22 by Kevin Feasel

Devin Knight shows off the linear gauge custom visual in Power BI:

In this module you will learn how to use the Linear Gauge Power BI Custom Visual.  The Linear Gauge would often be used to visualize a KPI. It gives you the ability to compare an actual vs target as well as showing up to two trend lines.

This can be a very useful visual.  The tricky part is that the bars aren’t scaled the same, so when your eyes want to compare bar lengths, it can get a little confusing.

Comments closed

Connecting Apache Drill To Power BI

Published 2016-12-20 by Kevin Feasel

Bryan Smith shows how to connect Apache Drill to Power BI:

Clicking Next takes me to the From ODBC dialog.  Here, I click on the Advanced options item, ignoring the Data Source Name (DSN) drop-down, and enter a connection string with the appropriate substitution for the host parameter:

driver={MapR Drill ODBC Driver};connectiontype=Direct;host=maprcluster-3xrrusnk-node0.westus.cloudapp.azure.com;port=31010;authenticationtype=No Authentication

Notice the connection string employs a Direct connection type, indicating that the app will speak directly to one of the nodes in the cluster (as identified by the host parameter) and not to the ZooKeeper service. ZooKeeper is in use on the cluster but is not exposed externally, given the network security group changes made during my earlier deployment.  Even if ZooKeeper were exposed, it tracks the nodes of the cluster using their internal names so that any app outside the virtual network containing the cluster would not be able to leverage the information in ZooKeeper to form a connection.  The only option that works here is the Direct connection type.

It’s worth reading the whole thing, as well as checking out the UserVoice suggestion for implementing full Apache Drill support.

Comments closed

Power BI Alerts

Published 2016-12-20 by Kevin Feasel

Nicolo Grando shows how to create an alert in Power BI when a measure reaches a certain mark:

Set alerts to notify you when data in your dashboards changes beyond limits you set. Alerts work for numeric tiles featuring cards and gauges. Only you can see the alerts you set, even if you share your dashboard. Data alerts are fully synchronized across platforms.

How to do it?

That’s useful for turning Power BI dashboards into partial alerting systems.

Comments closed

Power BI On-Prem In 2017

Published 2016-12-19 by Kevin Feasel

Paul Turley points out a blog post from the Reporting Services team:

When will we have this next Technical Preview?

We’re targeting January 2017 to release this next Technical Preview.

What’s the release vehicle for a production-ready version?

We plan to release the production-ready version in the next SQL Server release wave. We won’t be releasing it in a Service Pack, Cumulative Update, or other form of update for SSRS 2016.

When will we have a production-ready version?

We’re targeting availability in mid-2017.

That makes it sound like they’re pushing it to coincide with the vNext release.

Comments closed

Power Query And M In Tabular

Published 2016-12-19 by Kevin Feasel

Chris Webb notes that Analysis Services Tabular will get Power Query and M support:

I’ve just argued why Microsoft was obliged to include this functionality in SSAS v.next but in fact there are many positive reasons for doing this too. The most obvious one is to do with support for more data sources. At the moment SSAS Tabular supports a pretty good range of data sources, but the world of BI is getting more and more diverse and in order to stay relevant SSAS needs to support far more than it does today. By using Power Query/M as its data access mechanism, SSAS v.next will immediately support a much larger number of data sources and this number is going to keep on growing: any investment that Microsoft or third parties make for Power BI in this area will also benefit SSAS. Also, because Power Query/M can query and fold to more than just relational databases, I suspect that in the future this will allow for DirectQuery connections to many of these non-relational data sources too.

Read the whole thing.

Comments closed

PowerQuery Contains Filtering

Published 2016-12-19 by Kevin Feasel

Reza Rad explains two different methods of filtering which may look similar but can result in quite different outputs:

The script tells the whole story. Despite the fact that you typed in “Dan” and Power Query showed you all FirstNames that has “Dan” in it. the script still use equity filters for every individual FirstName. For this data set there won’t be any issue obviously, because all FirstNames with “Dan” is already selected. However if new data rows coming in to this table in the future, and they have records with FirstNames that are not one of these values, for example Dandy, it won’t be picked! As a result the filter won’t work exactly as you expect. That’s why I say this is misleading.

Read on for notes on how to put the Contains operator to good use.

Comments closed