Articles for the Month of December 2016

Power BI and R Links

I presented Power BI and R today, and for those who were not able to attend, when available my engagement page will have the recording as all of my recorded presentations are always listed

Links for Ropensourcerlogo

There were a lot of links used both for R and for R and Power BI integration. All items needed are included here as well as a brief description.

Comprehensive Resource Archive Network [CRAN] is where one can download Open Source R, packages and contains lots of information about R.

Microsoft R Open which is a fully CRAN compatible version created using the Intel MKL for improved performance can be downloaded here.

Microsoft R Client used with SQL Server 2016. R Server is included in SQL Server 2016. The R Client is needed to connect to R Server even if they are on the same computer.

R Studio is the most popular IDE for developing R.

Visual Studio R Tools are required for Visual Studio 2015 to become and R development environment

CRAN Package List from Microsoft not only contains the list of all of the packages, but the ability to go back in time to look at previous versions of the packages

Power BI Custom R Visuals are found in a tab separate from the other Power BI Visuals on this page. There are six visualizations available.

CRAN R Library for Forecasting model is needed for Power BI Visualizations and the download is required for this visual.

I hope that you found this one stop location for everything you need to use R helpful. For more information on how to use these tools, please take a look at all of my R posts where I describe how to use them.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Data Hierarchies and Drill Through in Power BI

Hierarchies provide a method of organizing data in a table to recognize that one value encompasses all of the values beneath it. One very common hierarchy is a date hierarchy, which is used to show data summarized by year, then all the values for the quarters, then the values for three months in each quarter and each month which at the lowest level includes all the values for dates. There are other hierarchies which may also exist in data, such as sales regions. A sales region could include countries which include states or provinces which include cities which include actual addresses. Because this is how data is categorized, visualizations need to reflect this organization by containing hierarches.

Creating a Data Hierarchy in Power BI

Finding where to create hierarchies is the hardest part of creating them in Power BI, especially if one has ever created datahierarchiespowerbihierarchies in Excel Power Pivot as they are not it the same place. Hierarchies are not in the Relationships data view, instead they are found in the Report view. Right clicking on the ellipse next to any field in a table displays a menu, and the second item on the menu is New hierarchy. Hierarchies can also be created by clicking and dragging a field on top of another field, which also will create a hierarchy. Once the hierarchy has been created, to add another field to the hierarchy, drag a new value on top of the value with the hierarchy icon. If the value added is not added to the location you want it, click on the ellipse next to the field named and move the field up or down as you wish.

Drill Through Reporting in Power BI

There are two ways to do drill through in Power BI, either by adding fields to the group section on a visualization or by adding a hierarchy as a field. If I create a date hierarchy and add the hierarchy to the axis of a bar chart visualization, the top Right and left corners will have arrows in them. Drilling down and back up occurs by selecting either the up and down Arrow keys, then clicking on a bar.powerbibardrilldown

For example, if the down arrow in the left corner is selected, clicking on the bar for 2013 will show data for the 2013 quarters. Repeated clicking will provide data down to the month, then the days in that month. The double arrow buttons provide the ability to show the data in the next level slightly differently. Clicking on the left double arrow will drill down to the next level. Clicking on it would provide a bar visualization for 4 quarters with data from all years, then 12 months of the combined years then the days. The double arrow with the line connecting the arrow only works on the highest level of visualization. When the double arrow line button is selected initially, it will show the quarters listed for every year, then the months and years, then the day, month and year. I hope you found this post helpful in explaining some of the features natively included in Power BI. If you are interested in learning how to expand the visualization capabilities of Power BI by including R visuals, please attend my upcoming webinar.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur



DIY – Using Custom Power BI R-Powered Visuals

In a recent post I introduced using R custom Visuals in Power BI, and this post details how to use the correlation visual in a Power BI Report. The first step in the process is to download R, if it is not on the computer already. if SQL Server 2016 with R integration is installed, there is no need to download R, as it was installed already. If the computer does not have R installed, click here. Once R is installed, go to the Power BI Custom Visualization page and select the R tab to pick one of the six R visualizations. I picked the correlation plot. To use the R visualization, if the machine does not already contain the packages used in the visualization, as screen will prompt you to install some packages also. This may take a little while to do and when it is complete, a window will appear showing the packages were successfully installed. Now the custom Visual can be used in Power BI.

Using the Correlation Visualization in Power BI

Power BI will show the new visual which you can place on the report. It is important to understand a little about R to better understand the error messages received. For example if you are using the fcoorelationplotorecasting tool and have selected the year value instead of the date, you may receive an error about an invalid time series. The underlying code is expecting to receive a date value, and a year is not a date, meaning you have to reference a date field in order to make it work.

Differences between R Visualizations and other Power BI Visualizations

Interacting with R visuals works differently than with other report visualizations as you cannot click on elements within the visualization and filter other items on the page. Other visuals on the page will filter the data contained within the R visual. For example, let’s say my report contains a total field, a slicer which contains years and a correlation plot which contains products. If the slicker is changed to select a year, total field and the data within the R visual will change to reflect that. If on the other hand, I choose to click on the R visual to select one of the product categories, the total field will not change and the R visual will not change. The R visual’s appearance will not change in any way.


One interesting thing to note is if you have created an R visual in Power BI and are working without an internet connection, the report will throw an error when trying to open the report. This occurred when it was a report I created or a sample report, so it appears with these reports an internet connection is required.


Yours Always

Ginger Grant

Data aficionado et SQL Raconteur