R | ErpCoder

Dynamic colored R Diagram in Power BI using Earthtone

4. October 2016 Leave a comment

Power BI integrates R to perform complex analysis and sophisticated visualization. Earthtones is an R library which takes a screenshot from Google Maps of certain geo coordinate and extracts the landscape colors. Earthtones can be used to color diagrams based on the local color schema.

Earthtones

The package can be found on github. There is also a description how to donwload and install the package. Using earthtones is easy. The function get_earththones takes the parameters longitude and latitude, zoom and the number of colors to extract. The earthtones for Steyr look like this:

get_earthtones(latitude=48.045,longitude=14.422,zoom=15,number_of_colors=8)

Power BI Data Model

The data model in this example is very simple. There are two excel sheets, one for the revenue by city and item group, another for the geo coordinates (longitude / latitude) and optimal zoom level per city.

The Power BI model is very simple, both data sources are linked by the city name

R Boxplot diagram in Power BI

In this example a simple boxplot is used to visualize the revenue by item group. A data slicer for the column city is used to filter the data. The R diagram takes the following columns as input:

Longitude
Latitude
Zoom
City
Price
Group

If only one city is selected, the R script shall gather the cities earthtone colors and format the diagram. If more than one city is selected, the diagram shall be formatted in red, blue and green. The following script loads the earthtone library and gets the distinct number of city names from the dataset. If there is more than 1 distinct name in the dataset the color variable is set to red,blue,green. Otherwise, earthtone is used to get the city typical color schema.

library(earthtones)

numCities <- length(unique(dataset$Stadt))
if(numCities > 1) {
color <- c(“red”,”blue”,”green”)
} else {
color <- get_earthtones(latitude = dataset$Lat[1],
longitude=dataset$Lon[1],
zoom= dataset$Zoom[1],
number_of_colors=3,include.map=FALSE)
}

boxplot(Preis~Gruppe,dataset,col=(color),ylab=”Revenue”,xlab = “Item Group”)

The R script in Power BI looks like this:

If a city is selected, for example San Francisco, the diagram is formatted in the colors blue, gray and brown.

The colors fit the blue sea, the bay and the city seen from space.

If another city, for example Cairo, is selected the diagram gets formatted in dark green, dark- and light brown.

That fits the cities local color schema, the brown buildings, the green plants along the Nile and the desert sand.

Filed under BI, PowerBI, R Tagged with Dynamics Color, PowerBI, R

Using R in SQL 2016 to calculate the distance between Cities

6. September 2016 Leave a comment

Since SQL Server 2016, R can be used in T-SQL statements to perform sophisticated calculations. One example I was facing, was to calculate the distance between two cities. Although there are many ways to solve this tasks, R can also be used to perform a exact calculation.

Prerequisites

R services need to be installed in order to execute R scripts within T-SQL. To calculate the distance between two geo coordinates, the geosphere library is required. The procedure to install additional packages is documented at MSDN.

Data model

This example contains 2 tables, Cities and DistanceTable. The Cities table contains the name and geo coordinates of a city, while the DistanceTable contains two references FromCity and ToCtiy to the Cities table.

Column	Datatype
CityID	int (Primary Key)
Name	nvarchar(128)
Longitude	real
Latitude	real

Column	Datatype
JournalID	int (Primary key)
FromCity	int (Foreign key)
ToCity	int (Foreign key)

For example the two Austrian Cities Linz and Vienna look like this:

An entry in the distance table looks like this:

I’ve added another view to output the geo coordinates from both cities which are referenced in the DistanceTable

CREATE VIEW [dbo].[DistanceViewLonLat]
AS
SELECT DT.JournalID,
FC.Longitude AS FromLon, FC.Latitude AS FromLat,
TC.Longitude AS ToLon, TC.Latitude AS ToLat
FROM
dbo.DistanceTable AS DT
INNER JOIN dbo.Cities AS FC ON DT.FromCity = FC.CityID
INNER JOIN dbo.Cities AS TC ON DT.ToCity = TC.CityID
GO

A record from the view looks like this

R Script

The following R script takes a record from the view as input and calculates the distance between two points and rounds the result from meter to kilometer.

exec sp_execute_external_script
@language =N’R’,
@script=N’
library(sp)
library(geosphere)
sqlvalues <- as.matrix(InputDataSet);

getDistKm <- function(row)
{
p1 <- c(row[1], row[2])
p2 <- c(row[3], row[4])

d <- distGeo(p1,p2) / 1000
c(row[1], row[2], row[3], row[4], d)
}

km <- apply(sqlvalues,1,getDistKm)
km <- t(km)

OutputDataSet <- as.data.frame(km)
‘,
@input_data_1 =N’select FromLon, FromLat , ToLon, ToLat from DistanceViewLonLat where JournalID = 1;’
with result sets (([fromlng] real, [fromlat] real, [tolng] real, [tolat] real, [km] real not null));
go

The result looks like this:

Filed under Development, R Tagged with R, SQL Server 2016

How to Develop a C++ DLL for R in Visual Studio 2015

15. June 2016 4 Comments

The programming language R is great for statistics and analysis. R is becoming more and more relevant for business analysis. Microsoft has already integrated R in SQL Server 2016, Power BI and offers an open source R Implementation and Visual Studio integration. There are many tutorials for C interop with R but most of them use Linux tools and not Visual C++. This is a walkthrough how to build a C++ DLL and use it in R all in Visual Studio.

Microsoft R Open https://mran.microsoft.com/download/
Microsoft Visual Studio R Tools https://www.visualstudio.com/en-us/features/rtvs-vs.aspx
Visual Studio Community Edition https://www.visualstudio.com/en-us/products/visual-studio-community-vs.aspx

Visual C++

Create a new empty solution in Visual Studio. Add a new Visual C++ Win32 project.

In the C++ Application dialog, choose a DLL

A new C++ project is created include a header file and a C++ file.

Open the stdafx.h file and add the definition for function foo() in the header file.

// stdafx.h : include file for standard system include files,
// or project specific include files that are used frequently, but
// are changed infrequently
//

#pragma once

#include “targetver.h”

#define WIN32_LEAN_AND_MEAN // Exclude rarely-used stuff from Windows headers
// Windows Header Files:
#include <windows.h>

/// export symbols for DLL and specify C naming conventions

extern “C” __declspec(dllexport) void __cdecl foo(double *in, double *out);

Open the .cpp file and add the following implementation.

void foo(double *in, double *out)
{
double value = in[0] * 2;
out[0] = value;
}

Make sure to change the architecture to x64 before building.

Build the solution. The resulting DLL will be outputed in the x64 folder in the project folder(!) not in the Debug folder where a C# DLL would be.

R Project

Add a new R project to the solution which already contains the C++ project. Go to the R Interactive Window.

Load the DLL from your output directory.

dyn.load(“C:\\PATH_TO\\YOUR.DLL”)

Declare 2 variables for input and output and assign values. For example input value 21 and a default value 0 for the output variable. Call the DLL function and output the result