The Data Analysts Toolkit Day 5: How do R and Power BI fit together?

How do R and Power BI fit together?

Technically, it is about munging data around between R and Excel and the Power BI components. 
You can use RODBC to connect to data between R and SQL Server, or R and Excel. Alternatively you can import data in.
Why else might you use R?

  • Pivot Tables are not always enough
  • Scaling Data (ScaleR)
  • R is very good at static data visualisation but Power BI and Excel are very good at dynamic data visualisation
  • You want to double check your results or do further analysis
They complement one another; they do not replace one another.
You may have heard my story about one organisation calculating the median incorrectly. 
The truth is, people don’t often check their data. I help design data warehouses all the time, and I don’t always hear people talk about reconciliation. I do hear about people racking up SSAS and diving straight in.
Just because something works technically, does not mean it is correct.
Upworthy and Facebook use R. A lot. So why not you? It is achievable.
Why R, and not some other package?
  • R most widely used data analysis software – used by 2M + data scientist, statisticians and analysts
  • Most powerful statistical programming language
  • used with RStudio, it can help you for the purposes of productivity
  • Create beautiful and unique data visualisations – as seen in New York Times, Twitter and Flowing Data
  • Thriving open-source community – leading edge of analytics research
  • Fills the talent gap – new graduates prefer R.
  • It’s fun!
Excel is used by an estimated 1.3 billion people on the planet. That sounds really impressive, until you think that many people are often using it wrong!
R just helps you to do that double check, the sanity check, to see if your data is correct.

The Data Analysts Toolkit Day 4: Getting to know R a bit better

So what actually is R? Let’s take a look at running some commands, and we can talk about it as we go along.

R is a powerful environment for statistical computing
It is like a calculator and its power is: 
… it lets you save results in variables. Let’s take a look:
> x
> y
> z = 4
> x + y + z
Ok, so we can run little commands and it gives us numbers back. What’s all the fuss about?
We can save data in files. They are saved as .RData files. R lets us import and export data in lots of different ways. If it sounds fun, it is because it is! Let’s take a look.
> someData
> save(someData, file = “D:/DataAnalystsToolkit/someData.Rdata”) 
> rm(someData) 
> load(“D:/DataAnalystsToolkit/someData.Rdata”) 
> print(someData)
Well, that is great if our data is in RData format. But normally we need to import data from different sources.
Import from CSV File
Read.csv()
MyDataFrame

print(MyDataFrame)
Alternatively we can load data in directly from RStudio. 
Go to Tools in RStudio, and select Import Dataset. 
Select the file CountryCodes.csv and select the Import button.
In RStudio, you will now see the data in the data pane.
The console window will show the following:
> #import dataset
> CountryCodes <- directory="" files="" header="F)
>   View(CountryCodes)
Once the data is imported, we can check the data.
dim(CountryCodes)
head(CountryCodes)
tail(CountryCodes) 
So how do we connect R to SQL Server?
The Package RODBC provides R with a connection to ODBC databases:
> library(RODBC) 
> myodbcConnect
myQuery

# or read query from file 
# myQuery <- ataanalyststoolkit="" myquery.sql="" nchars="99999) 
myData myodbcConnect

, myQuery, errors=TRUE) 

odbcCloseAll()
I hope that the rather bizarre colour coding will help you to see how the variables add up together to create a query.
RODBC also works for importing data from Excel files:
library(RODBC) 
filename
myxlsFile <- filename="" odbcconnectexcel="" readonly="FALSE) 
sqlSave(myxlsFile, a, rownames = FALSE) 
b
odbcCloseAll()
Next, we will look in more detail how R and PowerBI complement one another.