Press "Enter" to skip to content

Category: Power BI

Decomposing Power BI Desktop Files

Reza Rad wants to see exactly where the M scripts in a Power BI Desktop file are stored:

Talking about Power Query; DataMashup file is all you need. It includes everything from the structure of queries, tables, parameters, list, to the actual M scripts behind the scene. You can Fetch all of these information from this single file. Let’s look at the structure of this file. If you open this file with a text editor. you will see some binary things first (which are related to the zipped nature of this file), and also some XML information. Yes, this is a zipped file. Let’s start with unzipping it into a folder. I’ve done that with 7-zip application.

This is an interesting peek under the covers of a PBIX file.

Comments closed

Creating Nicer Reports

Reid Havens has a few tips for making Power BI reports look nicer:

This is less of a single applied step as it is multiple formatting practices applied throughout the report. I’ve already hit on this subject a little bit in the two previous Power BI visual design practices in regards to using complimentary colors. The two key takeaways in this section are object formatting and color coordination.

Of all my best practices I’m showcasing here I’d say this one is the most subjective. However I think that maintaining complimentary colors goes a long ways to creating a professional looking report. I also have a strong dislike for the default title design for visualizations in Power BI. By default it is left aligned and a grey color (AGAIN…hard to read!). I center that sucker and color the background. An added benefit to coloring the title background is it actually forces me to make sure my objects are aligned, otherwise it is VERY noticeable now if they aren’t.

Definitely read the comments on this one, as some of these tips are subjective.

Comments closed

Using Hive As A Power BI Data Source

Ust Oldfield shows how to use Hive via Azure HDInsight as a data source for Power BI:

As Hive is part of the Azure HDInsight stack it would be tempting to select the HDInsight or Hadoop connector when you’re getting data. However, note HDFS in brackets beside the Azure HDInsight and Hadoop File options as this means that you’ll be connecting to the underlying data store, which can be Azure Data Lake Store or Azure Blob Storage – both of which use HDFS architectures.

But this doesn’t help when you want to access a Hive table. In order to access a Hive table you will first of all need to install the Hive ODBC driver from Microsoft. Once you’ve downloaded and installed the driver you’ll be able to make your connection to Hive using the ODBC connector in PowerBI.

Read the whole thing.  Connecting to Hive is pretty easy.

Comments closed

Trimming The Fat From Power BI Data Models

Philip Seamark shows how things like getting rid of ID columns can reduce a Power BI data model’s size significantly:

Once saved, the Power BI file size was 289MB!  Is this good for 10 million rows?  It’s certainly better than the 360MB CSV file but not by much.  Certainly not close to the 10:1 compression claimed to be achievable using the SSAS Tabular engine used by Power BI.

I think we can do better than that….

Read on to see the specific optimizations, turning this from a 289 MB data model into a 9 MB data model.

Comments closed

Kerberos Constrained Delegation On Power BI Report Server

Regis Baccaro shows how to set up constrained delegation when connecting Power BI Report Server to a SQL Server instance or Analysis Services cube:

In many demo cases, you will have an all-in-one server where you have installed Power BI Report Server, SSAS (tabular or multidimensional) and SQL Server. In those cases you don’t need any form for credentials delegation since the Report Server is on the same box than the data source.

But there are scenarios where you have a distributed environment like the one I have on my VMs demo domain and for jumping around servers and passing credentials around, you need to setup Kerberos Constrained Delegation. Furthermore you will need protocol transition for it to work in Power BI Report Server.

Read on for step-by-step instructions showing how to do this.

Comments closed

Play Axis Custom Visual

Devin Knight continues his Power BI custom visuals series:

In this module you will learn how to use the Play Axis Power BI Custom Visual.  The Play Axis visual works like a dynamic slicer that animates your other report visuals without needing to click every time you want to change your filter value.

This is a valuable custom visual when dealing with time series data, but as Devin shows, you can iterate through other sets, like a set of employee names.

Comments closed

Building A Concatenated Tooltip In Power BI

Devin Knight has started a new series, walking through problems his clients have faced implementing Power BI solutions.  In this edition, Devin wants to build a comma-delimited list to display on a tooltip:

This works perfectly for Stock because it automatically summarizes the value but, you’ll notice above that the tooltip for Subcategory has an interesting behavior. Rather than displaying the list of the values in Subcategory it actually just show the very first value. This happens because the Tooltip field requires that any column used in it be able to aggregate or roll up the values into what’s shown on the chart. Since Subcategory is just a text field Power BI automatically applies the FIRST function to return back the first value that appears. You could optionally change this from FIRST to either LAST, COUNT, or COUNTDISTINCT.

So the real problem I want to solve here is rather than only showing the first subcategory how do I list all the subcategories in a comma separated list in the tooltip? Let’s walk through a couple possible designs to this solution.

Read on for two different designs, including the code to implement the solutions.

Comments closed

Installing Power BI Report Server

Adam Saxton has a video showing how to install and configure Power BI Report Server:

In this video, I look at how to install and configure the May 2017 Preview of Power BI Report Server. Power BI Report Server has a new standalone install experience and this product allows for Power BI reports to be rendered in the web portal along with paginated reports.

This will get you started with the new version.

I was really excited about this preview until I realized that, for now, it only works for Analysis Services data sources.

Comments closed