Calculated Tables and Role-Playing Dimensions with role playing dimensions, which are found when you have say multiple dates in a table and you want to relate them back to a single date table, have always been problematic in SQL Server Analysis Services Tabular. Tabular models only allow one active relationship to a single column at a time. The picture on the left shows how tabular models represent a role playing dimension, and the model on the right is the recommended method for how to model the relationships in Analysis Services Tabular as then users can filter the data on a number of different date tables.

TabularRolePlaying dimension Modeling



The big downside to this is one has to import the date table into the model multiple times, meaning the same data is imported again and again. At least that was the case until SQL Server 2016 was released. This weeks TSQL topic Fixing Old Problems with Shiny New Toys is really good reason to describe a better way of handling this problem.


Calculated Columns: The solution for Role Playing Dimensions

SQL Server 2016 provides a new method of solving the role playing dimension problem, using a calculated column. Instead of copying in the source from the date table, instead create a formula to get a copy. First switch to the data view, of the model. Then select Table->New Calculated Table. ThSSASScreenCalcTablee screen will change to the new table screen and the cursor will be pointed to the formula.

In my model I have one table called date. I am going to add a calculated table called order date. The DAX is couldn’t be simpler. Just select the table named ‘Date’ which is shown in the picture below. Rename the table to something more meaningful, like Order Date and that is it. The modeling required is the same, but now the model size does not increase to accommodate all of the date tables needed, as there is only one copy of the date table referenced multiple times. If you are using Power BI this same concept can be used for handling role playing dimensions as well.

SQL Server 2016 had a lot of great new features, and in addition to the flashy ones like R there are a lot of great enhancements to the Tabular model that are worth investigating as well.


Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Context Switching in R Server

R code tends to be very memory intensive as R processes primarily in memory. If you want R to perform well, you want as much memory as you can get your hands on to run your code, especially with larger datasets. This is a problem as many individual laptops have pitifully low memory capacity, and unless you have a computer with say as much memory as you can put in this one, if you are analyzing large datasets you may run out of memory. If a new computer is not in the budget, why not develop on the server? You may be thinking that there is no way the administrator of the box is going to provide you the means to be able to use the server memory. Well, if you have a SQL Server 2016 with R Server installed, chances are you can use the memory capacity of the server by connecting your R process to run on it from your computer, without the need to install anything on the server.

Microsoft’s R Server contains some specialized functions which are not part of the standard CRAN R installation. One of the ScaleR functions, RxInSqlServer will allow code to be processed on the server from the client. To make this work, you must have R Server and R Client installed. If you are doing a test on a local machine, you will need both R Client and R Server installed on that computer.

How to use the Server Memory not Local memory for Running R

If you are developing R in your IDE of choice, either R Studio or Visual Studio with R Tools, here is the code you need to make that work, which includes code running on the server

#First you will need to install ('RevoScaleR') if not there already as context switching is included in that library
if (!require("RevoScaleR")) {
#Load the library
#Create a connection to your SQL Server 2016 server instance. Note the double slashes which I needed to identify the instance name
sqlConnString <- "Driver=SQL Server;Server=DevSQLServer\\SS2016;Database=TestR;Uid=ReadDataID;Pwd=readd@t@!!!"
#Set the variable containing RxInSQLServer. Note All specific R Server libraries start with Rx
serverside #Set the Compute context to SQL server. After this the code will run using Server Memory, not local memory
#Check to see what the compute context is. Not this is for informational purposes. You do not need to do this to make anything work.
#If you want to change the compute context back to your computer run this command
#Until the context is switched back, I am now running on the server, not locally.
#Here I am going to take a look at a table in my TestR database called AirlineDemoSmall
sqlsampleTable <- "AirlineDemoSmall"
sqlPlaneDS<- RxSqlServerData(connectionString = sqlConnString, verbose = 1,table = sqlsampleTable )
#To take a look at the content of the data, I am going to take a look at 30 rows in table in my TestR database called AirlineDemoSmall
rxGetInfo(data = sqlPlaneDS, getVarInfo = TRUE, numRows = 10)
#To visually investigate the data, this command will plot a histogram displaying the frequencies of values in #one of the columns, CRSDepTime
rxHistogram(~CRSDepTime, data = sqlPlaneDS)

Here’s the output I get back in the R interactive Window.

Data Source: SQLSERVER
Number of variables: 3
Variable information:
Var 1: ArrDelay, Type: character
Var 2: CRSDepTime, Type: numeric
Var 3: DayOfWeek, Type: character
Data (10 rows starting with row 1):
ArrDelay CRSDepTime DayOfWeek
1       -14 16.283333   Monday
2       -1   6.166667   Monday
3       -2   7.000000   Monday
4         0 10.266666   Monday
5         0 13.483334   Monday
6       -10 16.833334   Monday
7       -10 19.949999   Monday
8       350 14.650001   Monday
9       292   9.416667   Monday
10       M   6.000000   Monday



Let me know if you found this post helpful, by posting a comment. Thanks also to Mario, who asked me about context switching which gave me the idea to answer his questions on this site. If you are interested in seeing more information about SQL Server and R, please subscribe as I tend to answer more of the questions I receive here.





Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Using Data Analysis to Pick Super Bowl Winners

I know that there is no way to compete with the major sports networks in the compilation of statistics about the two teams footballplaying in the super bowl. Instead I am going to focus on one feature, self-interest. Like many people, I have money in the stock market and I want my investments to make money next year. For this reason, I am an unqualified supporter of the Atlanta Falcons in the 2017 super bowl. The single data point I am using for my analysis is the fact that the falcons are an NFC Team, and when the NFC wins the stock market goes up. Go Falcons!

Correlation without Causation

Correlation does not imply causation is a common term in statistics and data analysis. It means that just because two variables move in relation to one another one does not mean that there is a cause and effect relationship between the two, even though it may seem like it. Just because when I washed my car it rained does not mean that I can control the precipitation patterns in the desert based on my propensity to visit the car wash. You may be thinking that having an NFC team win the super bowl and the stock market is an example of correlation without causation. After all the NFL does not control the world wide financial markets. If you look at the data though, 80% of the time the markets go up when the NFC wins. That is 50 years of data that supports that the winner does impact the market. Why might that be? Perhaps it follows Quantum Mechanics.

Observer Effect of Quantum Mechanics

When studying physics, specifically quantum mechanics researchers noticed that the observation changed the results. This is QuantumMechanicssomething commonly looked at when creating forecasts. Are the forecasts correct because the models are correct or because people believe them enough to make it happen. The superbowl winner impact on the stockmarket is well known. Perhaps it is for this reason that it becomes a self-fulfilling prophesy. This is the entire belief of many self-help ideas. If you believe it will happen, work to make it happen, it will happen. For whatever the reason, one cannot ignore 50 years of data.

Perhaps Patriots fans may think that I am pulling a lot of esoteric facts out of the air because I want the Patriots to lose. In all seriousness though, it is all about the data, and the observable effects of data knowledge. If you are watching the game and your team did not make the playoffs, and you are wondering who to root for because you do not care about the winner, perhaps this post helped you to decide.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur


Appending Data – It is OK to be different



One of the more powerful features of Power BI Desktop is the query feature, which was called Power Query back when Power BI was part of Excel. Using the Query feature, if the data which you want to use is bad, has unneeded columns or contains data formatted differently than desired all of that can be readily fixed. The best thing about the query feature is that it uses the M language and records each step. Mess up a step? No problem just delete it and keep on going.

Appending in Power BI

AppendQueriesRecently I worked on a Power BI project where I needed to merge data provided in spreadsheets. The spreadsheets came from different vendors and while they contained mostly the same data, the columns were not in the same order. I wanted all of the data to reside in one table. In Query, that means that I wanted to Append the data. The files which I were merging were very wide, and I missed the fact until after I was done that some of the columns were in different order. Power BI is smart enough to figure out the order on its own. I didn’t need to change the order of the columns at all, as long as they have the same column names. Here’s an example using three different files.


File 1

File 1


Notice each of these files is a little different








File 2

File 2

File 3

File 3











I want to Append these files together so that all of the columns containing the like information will be in the same column. To do this the columns do not need to be reordered. As long as the column names are the same the contents will merge. I am going to need to modify File 3 to have the same file names, so I will rename Date to Expected Duration in Minutes and Location to Plant.  Since I know that File 3 came from Slingback Central, I am going to want to add that column to File 3 as well.  Othewise I will get a null value in the Maintenance ProvidAdd Custom Columner Column.  I do not need to place the column in any specific location as long as the name is the same. Renaming the columns is pretty easy.  All one needs to do is right click the column and select rename and type in the correct column name.  To add a new column, in Query select the tab Add Column and click on the Custom Column option. As you can see in the window pictured below, the text name Slingback Central has double quotes around it. If you forget to do that, you will get a syntax error.

Putting it All Together

Now that all of my queries have the same file names, I am ready to append them together.  To do that I select one of the queries and from the Home tab click on the icon on the far right side to Append Queries.  Since I want to paste three files together, I select the option for three or more files, and select all of them so that they appear on the right in the Tables to Append section of the screen.

After appending the data together, it merges all of the like columns together regardless of the order of the original files as shown below.


Not having to reorder columns is a great feature as it saved me a lot of time and I hope this post can do the same for someone else.


Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

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

Downloading Power BI Reports and the Impact on Report Creation Process

savepowerbireportWhile the official announcement has not been released, on November 24th the Power BI feature to Download report became available. It is now possible to download Power BI Reports from the Power BI Service. To access the feature, click on the dropdown file menu of a report, and there you will see the option to download a report. If your report was not created recently, this feature may not be enabled. If the report was created November 24th or later, the download menu item should be enabled. After the download option is selected, a message pops up letting you know the report is being downloaded. The message is accurate as it took a good three plus minutes for the report to be downloaded.


There are a couple of points worth mentioning regarding the downloading feature. Downloading is for reports, not dashboards or datasets. **UPDATE: on 11-29 Microsoft released the information about the download feature. It can be turned off if on the Admin Portal exporting data is turned off. Unfortunately there isn’t a way to just turn off downloading the reports, as that one option controls both downloading reports and data.  Apparently  Also the ability to download cannot be turned off, meaning anyone who has edit rights can download the report. After the report was added, I looked on the administration screen to see if there is a way that this feature can be turned off. No option exists to prevent this.

Impact of Report Downloading on Templates and Content Packs

Power BI’s report download feature has the ability to modify the development process in Power BI as it is now possible to not use Power BI Desktop, while still providing the ability to create report versioning. Unfortunately, a report created on the service, cannot be downloaded as only reports which originated as desktop files can be downloaded. This is a problem as these files cannot be sent to SharePoint Online to provide versioning and one has to hope no one broke a report created on the service as there is no method to rollback changes because no file exists. This limits the use of Organizational Content Packs. A Content Pack is designed to be a starting point for report development as it contains everything needed to create a report, including a connection to a dataset, plus dashboards and perhaps a report or two to serve as a style guide. Content Packs are accessed by the arrow at the bottom of the Power BI Menu or by selecting Get Data, then selecting My organization to use content packs created for local data access.

Now that anyone can use a report which has been uploaded to the service as a starting point for a new report, there may be a decreased use of the template feature. Any report created on the desktop can be saved as a template, by selecting save on a desktop report file and changing the file type to a template. Unfortunately, templates do not contain links to the datasource used. The person creating the report must determine what data to use and if there was a dataset presently used which is refreshing the data, or create a dataset and it’s respective refresh features as part of creating a report.  Content packs provide the connections to datasets, but since the reports cannot be saved as a file for versioning, this feature is not often used instead of templates. Downloading the file and then modifying it is does resolve the issue as the starting point is then a working report with a connection to an existing dataset.

Personally I have been waiting for Microsoft to release the download feature for a long time and am very happy to be able to use it. I hope that an some point the download feature will be expanded to reports created on the service as this feature will  improve the report creation process.


Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Admin and Auditing in Power BI

On October 24, 2016 Microsoft has created a new access right in Office 365, the Power BI Admin. This means that no longer does one have to be a Office 365 Administrator, a role with a ton of rights which have nothing to do with Power BI to be a Power BI Administrator. To be a Power BI Adminstrator, the Office 365 Adminstrator needs to run a PowerShell script to make a user a Power BI Administrator. Just change the email address and run this script.

Add-MsolRoleMember -RoleMemberEmailAddress “" -RoleName "Power BI Service Administrator"

After running this script you will almost have everything you need to be a Power BI Administrator. The one thing that the newly minted Power BI Administrator cannot do is review the Audit Logs.

Audit Logs in Power BI

The Audit Logs are the third menu item in the Power BI Admin Portal. As you can tell by looking at a copy of the screen below, Audit Logs are not really part of Power BI. Yes the ability to log all of the content in Power BI exists in the Audit Logs, but so does the ability to review the audit logs for things like Exchange Mailbox Activities and User Administration Activities.

Power BI Admin Screen

If the Office 365 Administrator has granted a user Power BI Administration rights, this is what the newly minted Power BI Administrator will see when trying to access any search activities. It appears that you the user has rights, until that user tries to do anything on the screen. At that point, this error window appears.


Granting a Power BI Administrator the ability to view Power BI Audit Logs

To view audit logs, additional Office 365 access features are required. I did not figure out a way to turn on the audit feature unless one already was an Office 365 Administrator. If you want to view the audit logs, the first step should be to find out if they are turned on, because the steps I am going to show won’t do that. Within the Office 365 Admin center, go to the Exchange admin center and select permissions.


Select the user and then add two roles Compliance Management and Organization Management. It is also possible to create a new role with Compliance Manager rights and add the Audit Logs and View Only Audit Log permissions, since those are the required permissions. None of this has anything to do with Power BI, which means when the permissions granted, users will have the ability to see all of the audit logs. After the new role permissions have been applied, the error message will go away and users will have the ability to see activity logged in Power BI as well as all of the Power BI Administration features.


Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Using Power BI to Display R Visuals

 Microsoft has in the past few months increased the support for R Visuals in Power BI. In the July release, Power BI Service started displaying interactive visuals and in October, six R visuals were made

rvisualsavailable in Power BI which do not require any knowledge of R. Six visuals is a far step away from the wide number of visuals available in R. According to the Comprehensive R [CRAN] as of today there are 9557 packages available for R, and that does not count all of the packages on GitHub.

R Packages Supported in the Power BI Service

According to a September 8th post on the Power BI Blog, there are currently 352 R packages supported in the Power BI Service. For those of you wondering if your favorite R library is included (yes dplyr, ggplot2 and cluster are there) I have included the list of packages here.

abc acepack actuar ade4 AdMit andrews
aod apcluster ape aplpack approximator arm arules
arulesViz assertthat BACCO BaM BAS base64enc bayesm
bayesQR bayesSurv BayesTree BayHaz bbemkr BCBCSF BCE
bclust BenfordTests bisoreg bit bit64 bitops BMA
Bmix BMS0 bnlearn, boa boot bootstrap bqtl
BradleyTerry2 brew brglm, bspec bspmma BVS C50
calibrator car caret, catnet caTools cclust class
clue cluster coda coin CORElearn corpcor corrplot
crayon cslogistic cubature cvTools data.table date dbscan
deal deepnet deldir dendextend DEoptimR deSolve DiagrammeR
dichromat digest dlm DMwR dplyr DPpackage dse
dtw e1071 earth EbayesThresh ebdbNet ellipse emulator
ensembleBMA entropy EvalEst evaluate evdbayes exactLoglinTest expm
extremevalues FactoMineR factorQR faoutlier fBasics fields filehash
fitdistrplus flashClust FME fmsb foreach forecast Formula
fracdiff fTrading futile.logger gam gamlr gclus gdata
gee genetics geoRglm geosphere ggdendro ggmcmc ggplot2
ggthemes glmmBUGS glmnet gmodels gmp gnm GPArotation
gridBase gridExtra growcurves grpreg gss gsubfn gtable
gtools haplo.stats hash hbsae hdrcde heavy HH
HI Hmisc HSAUR ifultools intervals ipred irlba
irr iterators jpeg kernlab KernSmooth KFKSDS kinship2
kknn klaR knitr labeling lars lattice latticeExtra
lava lavaan lazyeval leaps LearnBayes LiblineaR LICORS
limSolve lme4 lmm lmtest locfit locpol LogicReg
lsa lubridate magic magrittr mapdata mapproj maps
maptree MASS MasterBayes Matrix matrixcalc MatrixModels maxent
maxLik mboost mda memoise mi mice microbenchmark
mime misc3d miscTools mitools mixtools mlbench mnormt
MNP modeltools mombf monomvn MSBVAR msm multcomp
munsell mvtnorm NbClust ncvreg nlme nloptr NLP
NMF nnet nnls nortest numbers numDeriv OutlierDC
pacbpred party partykit PAWL pbivnorm pcaPP pdc
PerformanceAnalytics plotmo plotrix pls plyr png polynom
predmixcor PresenceAbsence profdpm proto proxy pryr pscl
psych qap qdapRegex quadprog quantreg qvcalc R.oo
.utils ramps RandomFieldsUtils RColorBrewer Rcpp RcppArmadillo RcppEigen
registry reliampo relimp reshape reshape2 RGraphics rjson
RJSONIO Rmpfr rms robustbase ROCR rpart.plot rrcov
rscproxy RSGHB RTextTools rworldmap SampleSizeMeans SampleSizeProportions sbgcop
scales scatterplot3d sciplot segmented seriation setRNG sfsmisc
SIS SixSigma skmeans slam smoothSurv sna snow
SnowballC snowFT sp spacetime spam SparseM spatial
spBayes spikeslab splancs spls spTimer sqldf sROC
stabledist stabs stepPlr stringdist stringr stsm stsm.class
survival tau tcltk2 tensorA textcat textir tfplot tidyr timeDate timeSeries tm topicmodels tree
tseries tsfa tsoutliers TSP vcd VennDiagram VGAM
VIM whisker wordcloud xgboost XML xts yaml
zipfR zoo



I hope that some day that this list becomes much longer, but it is a good start. If your company has lots R visuals and you wish to migrate them to Power BI, chances are some of the libraries you are using are not here. If you are interested in having your library added to the list of 352, go to the Ideas page of Power BI and request that your library be added, as Microsoft I know looks at this page to determine what to release in the future. Someone has requested that igraph be added, and since it hasn’t received a lot of votes yet (hint) it is probably low on the priority list.

Release Plans for Additional R Library Support in Power BI

In reviewing the Power BI documentation online, it is not known what Microsoft’s plans are for future R packages. Does Microsoft plan on adding a lot more in the future? Will new libraries be added as part of upcoming releases? All questions where the web presently does not provide answers.

The current package support in Power BI does provide the R developer with a method to more securely and widely distribute R visualizations. In my upcoming posts I will review how to use the six R visualizations which do not require R and also how to use R with Power BI.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur