Using R In Power BI For More Than Displaying Visuals

Kevin Feasel

2018-11-28

Power BI, R

Patrick Mahoney shows us that you can do more with the R Visual component in Power BI than display visuals:

If you really like a certain R visual, you can also package it as a pbiviz file to share with others. Once you set up the foundation to create the first pbiviz, it is easy to crank out many more just by replacing the R code and repackaging it (into a different pbiviz file). See instruction here.

But this post isn’t about making charts. It turns out you can hijack the R visual to do lots of other things too. Below are a few examples:

Note: I am no R expert. The examples below are relatively simple and cobbled together from similar things online.  They may be a little clunky, but worth it, in my opinion, to be able to dynamically leverage many more of the R capabilities through Power BI.

Read on for some interesting examples.

Migrating Queries To Power BI Dataflows

Matt Allington shows us how to move a query from Power BI desktop into a Power BI Dataflow:

Power Query is a user friendly ETL tool (Extract, Transform and Load).  Traditionally ETL has been done using more complicated tools (such as SQL Server Integration Services – SSIS) and the resulting data is stored in a data mart or data warehouse for consumption by anyone that needs a standard view of the data.  Power BI Desktop can consume tables directly from a data warehouse and simply load the table into Power BI – dead easy.  But Power Query is also a powerful ETL tool in its own right, and it can be used to transform and reshape the source data directly inside Power BI Desktop (and then PowerBI.com).  This is very useful if :

  1. You don’t have a data warehouse and/or
  2. You need some variation of what is in your data warehouse.
  3. You have other data sources that are not in a data warehouse but are still important to you.

Taking this approach (manipulate in Power Query) is perfectly fine if you have a single workbook, but what if you have 10 similar workbooks all needing the same transformation?  Worse still, what if you are one of many people in a company all doing the same thing with multiple workbooks?

Read on for the solution.

Creating Custom Power BI Themes

Cathrine Wilhelmsen shows us how to create a custom theme for Power BI dashboards:

Power BI comes with several built-in themes and a whole gallery full of custom themes available for download. But what if you still can’t find the perfect look for your reports? No problem! Just create your own custom Power BI themes 🙂

…sounds simple enough, right? It only takes a few minutes to create a custom Power BI theme with a color palette of your choice. Whoosh – instant custom branding!

But if you are like me, simple color changes might not be enough. Maybe you want finer control of borders, fonts, labels, or other visual elements. Or maybe you just don’t want to keep changing the same settings over and over and over again in multiple visualizations and reports. (Please don’t do that.)

You can control all of these things in custom Power BI themes. It is, however, not quite as simple as creating a color palette… yet. (You never know when the Power BI product team will blow your mind with a new update!) But for now, we need to define custom themes in JSON files.

Click through to learn how to do some of these changes through the power of editing JSON files.

Tracking Errors In Power BI

Reza Rad has a lengthy post covering how you can track errors in Power Query:

To build a robust BI system, you need to cater for errors and handle errors carefully. If you build a reporting solution that the refresh of that fails everytime an error occurs, it is not a robust system. Errors can happen by many reasons, In this post, I’ll show you a way to catch potential errors in Power Query and how to build an exception report page to visualize the error rows for further investigation. The method that you learn here, will save your model from failing at the time of refresh. Means you get the dataset updated, and you can catch any rows caused the error in an exception report page. To learn more about Power BI, read Power BI book from Rookie to Rock Star.

There’s a lot of work, but also a lot of value in doing that work.

Using Power BI DMVs

Kasper de Jonge shows off a few Power BI dynamic management views:

Today a quick one that I came across while writing a different blog post that I will blog later. I know we have talked about it again and again but a good best practice is to remove any high carnality columns you don’t necessary need. This trick is not new and has been blogged about before in different places but I wanted to emphasize it again due to the importance.

I was finishing up my next blog post and wanted to upload the sample file. While doing that I noticed the file was 150MB large. That is rather large for such a simple file. The largest table has 500,000 rows and none of them are unique. What is going on?

There are some interesting DMVs in Kasper’s post, including one which shows cardinality by column.

Using Power BI Dataflows To Create Common Data Sets

Alexander Arvidsson shares an interesting use case for Power BI Dataflows:

There are several more use cases for a dataflow, but one that is very useful is the ability to share a dataset between apps. Previously we had to duplicate the dataset to each and every app that needed to use it, increasing the risk that one dataset was ignored, not refreshed properly or otherwise out of sync with reality. By using dataflows we can have several apps rely on the same dataflow (via a dataset), and thus it is quite possible to have a “master dataset”.

Click through for a walkthrough, as well as an understanding of the process’s limitations.

The Good And Bad Of Dataflows

Teo Lachev gives us the lowdown on Dataflows in Power BI:

There is a lot to like about dataflows. I can think of two primary self-service scenarios that can benefit from dataflows:

  • Data staging – Many organizations implement operational data stores (ODS) and staging databases before the data is processed and loaded in a data warehouse. As a business user, you can use data-flows for a similar purpose. For example, one of our clients is a large insurance company that uses Microsoft Dynamics 365 for customer relationship management. Various data analysts create data models from the same CRM data, but they find that refreshing the CRM data is time consuming. Instead, they can create a dataflow to stage some CRM entities before importing them in Power BI Desktop. Even better, you could import the staged CRM data into a single dataset or in an organizational semantic model to multiple data copies and duplicating business logic.

  • Certified datasets – One way to improve data quality and promote better self-service BI is to prepare a set of certified common entities, such as Organization, Product, and Vendor. A data steward can be responsible for designing and managing these entities. Once in place, data analysts can import the certified entities in their data models.

Read on for some more positives and negatives.

What Power BI Costs

Eugene Meidinger delineates what is free versus paid with respect to Power BI:

The Power BI Service, think powerbi.com, allows for free users. These free users can create reports and upload them, but with a significant number of limitations. The biggest is you only have one way of sharing content to others. Specifically with Publish to Web, which essentially makes your entire report free to the public.

You also only have one way of privately consuming other people’s reports, and that’s if someone places content in Power BI Premium. Otherwise, other users can’t share their reports directly with you. Power BI Free users are truly and island to themselves.

One other thing worth nothing is that you can’t sign up with a personal email. David Eldersveld has a good blog post on the issue. As of this writing, the uservoice request to change this has 2,800 votes.

See here for some more limitations of the free version of Power BI.

Read the whole thing.

Running R Scripts In Power BI’s Query Editor

Kevin Feasel

2018-11-20

Power BI, R

Brad Lewellyn walks us through the process of executing an R script against a table in Power Query:

If you aren’t able to open the R Script Editor, check out our previous post, Getting Started with R Scripts.  While it’s possible to develop and test code using the built-in R Script Editor, it’s not great.  Unfortunately, there doesn’t seem to be a way to develop this script using an external IDE like RStudio.  So, we typically export files to csv for development in RStudio.  This is obviously not optimal and should be done with caution when data is extremely large or sensitive in some way.  Fortunately, the write.csv() function is pretty easy to use.  You can read more about it here.

It’s not a perfect experience, but Brad does show us how to get it done.

Inactive Relationships In Power BI

Reza Rad explains the value of inactive relationships and shows how you can implement this in Power BI:

As you can see this new type of relationship is different. It is dashed line, compared to the active, which was a solid line. This is an inactive relationship. You can only have one active relationship between two tables. Any other relationships will become inactive.

An inactive relationship doesn’t pass filtering. It doesn’t do anything by itself. I still see many people creating inactive relationships in their model thinking that just the inactive relationship by itself will do some filtering. It doesn’t. If I use the FullDateAlternateKey from the DimDate table to slice and dice the SalesAmount from the FactInternetSales table, which field I’m filtering based on? The field that is related through an Active relationship of course. Here is a result for that (which is apparently same as what you have seen in the previous example because the inactive relationship doesn’t do anything. It is just the active relationship that passes the filter);

Read the whole thing.

Categories

January 2019
MTWTFSS
« Dec  
 123456
78910111213
14151617181920
21222324252627
28293031