Press "Enter" to skip to content

Category: Power BI

Power BI Row-Level Security With External Users

Published 2016-10-07 by Kevin Feasel

Patrick LeBlanc shows how to implement row-level security within Power BI for people without direct access to an underlying Analysis Services cube:

Before I explain how to fix this, let’s take a look at what’s happening behind the scenes.

  1. When jdoe@adventureworks.com opens the dashboard a connection string is created including the effectiveusername property, which is expected behavior.

  2. The value specified for this property is jdoe@adventureworks.com.

  3. The connections string including the queries are sent via the On-Premises gateway to the SSAS server that hosts the data needed to view the report.

  4. Once the connection is established, using the username and password specified in the Data Source settings, all queries are executed usingjdoe@adventureworks.com.

Read on for the solution.

Comments closed

Hurricane Matthew Tracking With Power BI

Published 2016-10-07 by Kevin Feasel

Chris Albrektson has a Power BI report tracking Hurricane Matthew:

We’ve got company and it’s not the type of company that you want! As most Floridians are preparing for Hurricane Matthew I thought it might be neat to track the storm using PowerBi. So I went out and found some public data online and brought that into PowerBi, created a couple calculations and some visualizations.

My goal for this was to create a report where I could track the storm no matter where I was. I also needed the ability to see the latest data without any manual intervention. PowerBi can handle all of this for me utilizing the PowerBi Mobile app and a few other cool features.

Good use of Power BI here.

Comments closed

Documenting Tabular Models

Published 2016-10-06 by Kevin Feasel

Meagan Longoria shows how to document a tabular model using Excel and Power BI:

The Database Info tab uses cube functions to display the values shown. The rest of the tabs use pivot tables and slicers.

You can download and use this model if you have Excel 2016 or 2013 with Power Query. Just download the file, change the values in the TabularInstanceName and TabularDBName queries, and refresh. I was able to open the file and update the queries with no issues and no changes in Excel 2013.

This is great if you just need documentation or a quick way to look up specific information. But we could really analyze this using visualizations other than tables in Power BI Desktop.

That’s pretty fancy.  Check it out.

Comments closed

Stream Graphs

Published 2016-09-28 by Kevin Feasel

Devin Knight continues his visualization series with the Stream Graph:

Key Takeaways

  • Works and looks similar to a Stacked Area Chart but with a wiggle feature that gives it a more fluid look and feel

  • Great for displaying data that changes over time

At first, I read this as “Steam Graph,” which made it sound like a steampunk visualization with unnecessary pipes and mechanical accouterments, but alas, it was not meant to be.  I do like the stream graph visual, though.

Comments closed

SSAS And Power BI Performance Issue

Published 2016-09-27 by Kevin Feasel

Chris Webb describes an issue with SSAS Multidimensional and Power BI-generated DAX causing a performance problem:

This query has something in it – I don’t know what – that means that it cannot make use of the Analysis Services Storage Engine cache. Every time you run it SSAS will go to disk, read the data that it needs and then aggregate it, which means you’ll get cold-cache performance all the time. On a big cube this can be a big problem. This is very similar to problems I’ve seen with MDX queries on Multidimensional and which I blogged about here; it’s the first time I’ve seen this happen with a DAX query though. I suspect a lot of people using Power BI on SSAS Multidimensional will have this problem without realising it.

This problem does not occur for all tables – as far as I can see it only happens with tables that have a large number of rows and two or more hierarchies in. The easy way to check whether you have this problem is to refresh your report, run a Profiler trace that includes the Progress Report Begin/End and Query Subcube Verbose events (and any others you find useful) and then refresh the report again by pressing the Refresh button in Power BI Desktop without changing it at all. In your trace, if you see any of the Progress Report events appear when that second refresh happens, as well as Query Subcube Verbose events with an Event Subclass of Non-cache data, then you know that the Storage Engine cache is not being used.

This doesn’t look to be a quick fix, so do read the whole thing to help figure out how to avoid this issue.

Comments closed

Slicer Filter Workaround

Published 2016-09-27 by Kevin Feasel

Reza Rad has a workaround for cases in which you want to filter a Power BI slicer:

The idea of this blog post came from a question that one of students in my Power BI course asked to me, and I’ve found this as a high demand in internet as well. So I’ve decided to write about it.

You might have too many items to show in a slicer. a slicer for customer name when you have 10,000 customers isn’t meaningful! You might be only interested in top 20 customers. Or you might want to pick few items to show in the slicer. With all other visual types (Such as Bar chart, Column chart, line chart….) you can simply define a visual level filter on the chart itself. Unfortunately this feature isn’t supported at the time of writing this post for Slicers. However the demand for this feature is already high! you can see the idea published here in Power BI user voice, so feel free to vote for such feature :)

As Reza notes, this might get resolved fairly soon.  Until then, check out his solution.

Comments closed

Power BI Gateway Troubleshooting

Published 2016-09-26 by Kevin Feasel

Adam Saxton has four tips to assist with troubleshooting On-Premesis Data Gateway issues within Power BI:

We know that the problem is remote applications being unable to connect to SQL Server. I want to simplify this and get the gateway out of the picture.

A lot of times we can become fixated on the app we are looking at and ignore other possibilities. You may know that locally works and remote does and start looking at why the gateway specifically isn’t working. Maybe you start to think something on the Power BI side is broken, or maybe we can’t talk to the Azure Service Bus. This is why I like to remove the gateway from the picture. Or, if you had a custom application, let’s remove that from the picture. Simplify as best you can. This helps to exclude a lot of potential complexity.

How do we do that? You can use the same test that you used locally! Use something like a UDL file or another tool to see if it can connect. Because it is SQL Server, I’d really like to try Management Studio. Management Studio uses .NET as does the gateway. To start, it doesn’t necessarily have to be the same machine as the gateway. If you have a different machine that has Management Studio installed, use that! If that works, then we may want to do the test specifically from the gateway machine.

This is a good troubleshooting guide, dealing with some of the more likely causes before moving on to the esoteric issues.

Comments closed

Power BI, Excel, SharePoint, And Data Refreshing

Published 2016-09-20 by Kevin Feasel

Chris Webb shows how to ensure that data refresh works when you store multiple Excel workbooks in SharePoint or OneDrive:

I can hear you yawning already – yet another blog post on getting data from multiple Excel workbooks in Power Query and Power BI. Just about everyone who has ever written a blog post on Power BI has written about this subject, including me. However there’s a twist this time: what if your Excel workbooks are stored in SharePoint or OneDrive For Business? If they are, then your dataset may not refresh successfully after you have published unless you load your data in a particular way.

Read the whole thing if you work with Power BI.

Comments closed