Energy BI and R: Customized Visuals for Social Community Evaluation


Social community evaluation is shortly changing into an necessary device to serve a wide range of skilled wants. It might inform company targets similar to focused advertising and determine safety or reputational dangers. Social community evaluation also can assist companies meet inside targets: It supplies perception into worker behaviors and the relationships amongst totally different components of an organization.

Organizations can make use of plenty of software program options for social community evaluation; every has its execs and cons, and is fitted to totally different functions. This text focuses on Microsoft’s Energy BI, some of the generally used information visualization instruments at this time. Whereas Energy BI provides many social community add-ons, we’ll discover customized visuals in R to create extra compelling and versatile outcomes.

This tutorial assumes an understanding of fundamental graph idea, notably directed graphs. Additionally, later steps are greatest fitted to Energy BI Desktop, which is simply out there on Home windows. Readers might use the Energy BI browser on Mac OS or Linux, however the Energy BI browser doesn’t help sure options, similar to importing an Excel workbook.

Structuring Information for Visualization

Creating social networks begins with the gathering of connections (edge) information. Connections information comprises two main fields: the supply node and the goal node—the nodes at both finish of the sting. Past these nodes, we are able to acquire information to supply extra complete visible insights, usually represented as node or edge properties:

1) Node properties

  • Form or colour: Signifies the kind of consumer, e.g., the consumer’s location/nation
  • Measurement: Signifies the significance within the community, e.g., the consumer’s variety of followers
  • Picture: Operates as a person identifier, e.g., a consumer’s avatar

2) Edge properties

  • Colour, stroke, or arrowhead connection: Signifies kind of connection, e.g., the sentiment of the put up or tweet connecting the 2 customers
  • Width: Signifies energy of connection, e.g., what number of mentions or retweets are noticed between two customers in a given interval

Let’s examine an instance social community visible to see how these properties operate:

A graph of circles connected by lines of varying widths appears with three distinct sections. The left of the graph has six green shapes of various sizes labeled 1, 2, 3, 4, 5, and 6 in a hexagon. Numbers 1-5 are circles, while 6 is a diamond. They are interconnected by green arrows of varying widths and directions, and some arrowheads are filled green while others are not filled. To the right of the green shapes is the next section: three dark blue shapes arranged in a triangle that are labeled 7, 8, and 9, and are interconnected by blue arrows of varying widths and directions (with some arrowheads filled blue). Nodes 7 and 9 are connected to nodes 3 and 4 with gray arrows of varying widths and directions (with some arrowheads filled gray). In the middle of the graph, below the first two shape groups, is a single light blue diamond labeled 10. It is connected to nodes 5, 4, and 9 by dotted gray arrows of varying widths and directions (with some arrowheads filled gray).
Inexperienced, mild blue, and darkish blue nodes and ranging circle or diamond shapes show totally different node sorts. Numbers with clear backgrounds act because the node picture identifiers, and bigger nodes (similar to Node 4) are extra necessary within the community. Totally different edge sorts are indicated by colour (inexperienced, blue, or grey), stroke (strong or dotted), and arrowheads (empty or crammed); edge width exhibits energy (for instance, the connection from Node 8 to Node 9 is powerful).

We will additionally use hover textual content to complement or substitute the above parameters, as it might help different info that can’t be simply expressed by node or edge properties.

Evaluating Energy BI’s Social Community Extensions

Having outlined the totally different information options of a social community, let’s look at the professionals and cons of 4 standard instruments used to visualise networks in Energy BI.

ExtensionSocial Community Graph by Arthur GrausCommunity NavigatorSuperior Networks by ZoomCharts (Mild Version)Customized Visualizations Utilizing R
Dynamic node dimensionSureSureSureSure
Dynamic edge dimensionNoSureNoSure
Node colour customizationSureSureNoSure
Advanced social community processingNoSureSureSure
Profile pictures for nodesSureNoNoSure
Adjustable zoomNoSureSureSure
High N connections filteringNoNoNoSure
Customized info on hoverNoNoNoSure
Edge colour customizationNoNoNoSure
Different superior optionsNoNoNoSure

Social Community Graph by Arthur Graus, Community Navigator, and Superior Networks by ZoomCharts (Mild Version) are all appropriate extensions to develop easy social networks and get began together with your first social community evaluation.

Many dark blue, light blue, and orange circles (50+ circles) are connected by thin gray lines on a white background. The circles have a solid color border and are filled with small images of various Pokémon that have a white background, and the circles block the view of most of the gray lines. They form a circular shape overall.
An instance visualization made utilizing the Social Community Graph by Arthur Graus extension.

Many blue, purple, and gray circles (50+ circles) are connected by thin gray lines on a white background. The circles are solid and filled, and block the view of some of the gray lines. They form a circular arrangement overall.
An instance visualization made utilizing the Community Navigator extension.

Many large teal and small orange circles (50+ circles) are connected by thin gray lines on a white background. The circles are solid and filled, and most of the gray lines are visible. They form a horizontal wedge shape overall, with more densely populated circles appearing on the right side. On the bottom left of the chart, there are a few widget icons and two labeled circles: a teal circle labeled
An instance visualization made utilizing the Superior Networks by ZoomCharts (Mild Version) extension.

Nonetheless, if you wish to make your information come alive and uncover groundbreaking insights with attention-grabbing visuals, or in case your social community is especially advanced, I like to recommend growing your customized visuals in R.

Many green, blue, and purple circles (50+ circles) are connected by thin lines of varying colors (green, gray, and red) on a white background. The circles are solid and filled with a Pokémon image at their center, and most of the thin lines are visible. They form a spread-out circular shape overall, with the green circles frequently branching out toward smaller blue or purple circles. The top right corner of the chart has the text
An instance visualization made utilizing customized visuals in R.

This tradition visualization is the ultimate results of our tutorial’s social community extension in R and demonstrates the big number of options and node/edge properties provided by R.

Constructing a Social Community Extension for Energy BI Utilizing R

Creating an extension to visualise social networks in Energy BI utilizing R includes 5 distinct steps. However earlier than we are able to construct our social community extension, we should load our information into Energy BI.

Prerequisite: Gather and Put together Information for Energy BI

You may observe this tutorial with a check dataset based mostly on Twitter and Fb information or proceed with your personal social community. Our information has been randomized; you could obtain actual Twitter information if desired. After you acquire the required information, add it into Energy BI (for instance, by importing an Excel workbook or including information manually). Your outcome ought to look much like the next desk:

A table with thirteen alternating gray and white rows appears. It has a title---

After you have your information arrange, you’re able to create a customized visualization.

Step 1: Set Up the Visualization Template

Creating a Energy BI visualization just isn’t easy—even fundamental visuals require hundreds of information. Luckily, Microsoft provides a library referred to as pbiviz, which supplies the required infrastructure-supporting information with just a few strains of code. The pbiviz library may even repackage all of our ultimate information right into a .pbiviz file that we are able to load immediately into Energy BI as a visualization.

The best strategy to set up pbiviz is with Node.js. As soon as pbiviz is put in, we have to initialize our customized R visible through our machine’s command-line interface:

pbiviz new toptalSocialNetworkByBharatGarg -t rhtml
cd toptalSocialNetworkByBharatGarg
npm set up 
pbiviz package deal

Don’t neglect to switch toptalSocialNetworkByBharatGarg with the specified title in your visualization. -t rhtml informs the pbiviz package deal that it ought to create a template to develop R-based HTML visualizations. You will notice errors as a result of we’ve not but specified fields such because the creator’s title and e-mail in our package deal, however we are going to resolve these later within the tutorial. If the pbiviz script gained’t run in any respect in PowerShell, you first might have to permit scripts with Set-ExecutionPolicy RemoteSigned.

On profitable execution of the code, you will note a folder with the next construction:

A File Explorer listing containing eight subfolders (.tmp, .vscode, assets, dist, node_modules, r_files, src, and style) and eight files (capabilities.json, dependencies.json, package.json, package-lock.json, pbiviz.json, script.r, tsconfig.json, and tslint.json). All of the files are 1 KB, except for capabilities.json (2 KB) and package-lock.json (23 KB).

As soon as we’ve the folder construction prepared, we are able to write the R code for our customized visualization.

Step 2: Code the Visualization in R

The listing created in step one comprises a file named script.r, which consists of default code. (The default code creates a easy Energy BI extension, which makes use of the iris pattern database out there in R to plot a histogram of Petal.Size by Petal.Species.) We’ll replace the code however retain its default construction, together with its commented sections.

Our mission makes use of three R libraries:

Let’s substitute the code within the Library Declarations part of script.r to replicate our library utilization:


Subsequent, we are going to substitute the code within the Precise code part with our R code. Earlier than creating our visualization, we should first learn and course of our information. We’ll take two inputs from Energy BI:

  • num_records: The numeric enter N, such that we are going to choose solely the highest N connections from our community (to restrict the variety of connections displayed)
  • dataset: Our social community nodes and edges

To calculate the N connections that we are going to plot, we have to mixture the num_records worth as a result of Energy BI will present a vector by default as an alternative of a single numeric worth. An aggregation operate like max achieves this aim:

limit_connection <- max(num_records)

We’ll now learn dataset as a information.desk object with customized columns. We type the dataset by worth in lowering order to position essentially the most frequent connections on the prime of the desk. This ensures that we select a very powerful data to plot once we restrict our connections with num_records:

dataset <- information.desk(from = dataset[[1]]
                      ,to = dataset[[2]]
                      ,worth = dataset[[3]]
                      ,col_sentiment = dataset[[4]]
                      ,col_type = dataset[[5]]
                      ,from_name = dataset[[6]]
                      ,to_name = dataset[[7]]
                      ,from_avatar = dataset[[8]]
                      ,to_avatar = dataset[[9]])[
seq(1, min(nrow(dataset), limit_connection))]

Subsequent, we should put together our consumer info by creating and allocating distinctive consumer IDs (uid) to every consumer, storing these in a brand new desk. We additionally calculate the entire variety of customers and retailer that info in a separate variable referred to as num_nodes:

user_ids <- information.desk(id = distinctive(c(dataset$from, 
                                     dataset$to)))[, uid := 1:.N]

num_nodes <- nrow(user_ids) 

Let’s replace our consumer info with further properties, together with:

  • The variety of followers (dimension of node).
  • The variety of data.
  • The kind of consumer (colour codes).
  • Avatar hyperlinks.

We’ll use R’s merge operate to replace the desk:

user_ids <- merge(user_ids, dataset[, .(num_follower = uniqueN(to)), from], by.x = 'id', by.y = 'from', all.x = T)[, num_follower := 0][, size := num_follower][num_follower > 0, size := size + 50][, size := size + 10]

user_ids <- merge(user_ids, dataset[, .(sum_val = sum(value)), .(to, col_type)][order(-sum_val)][, id := 1:.N, to][id == 1, .(to, col_type)], by.x = 'id', by.y = 'to', all.x = T)

user_ids[id %in% dataset$from, col_type := '#42f548']

user_ids <- merge(user_ids, distinctive(rbind(dataset[, .('id' = from, 'Name' = from_name, 'avatar' = from_avatar)],
      dataset[, .('id' = to, 'Name' = to_name, 'avatar' = to_avatar)])),
      by = 'id')

We additionally add our created uid to the unique dataset in order that we are able to retrieve the from and to consumer IDs later within the code:

dataset <- merge(dataset, user_ids[, .(id, uid)],
                                by.x = "from", by.y = "id")

dataset <- merge(dataset, user_ids[, .(id, uid_retweet = uid)],
                                by.x = "to", by.y = "id")

user_ids <- user_ids[order(uid)]

Subsequent, we create node and edge information frames for the visualization. We select the model and form of our nodes (crammed circles), and choose the proper columns of our user_ids desk to populate our nodes’ colour, information, worth, and picture attributes:

nodes <- create_node_df(n = num_nodes, 
                        kind = "decrease",
                        model = "crammed",
                        colour = user_ids$col_type, 
                        information = user_ids$uid,
                        worth = user_ids$dimension,
                        picture = user_ids$avatar,
                        title = paste0("<p>Title: <b>", user_ids$Title,"</b><br>",
                                       "Tremendous UID <b>", user_ids$id, "</b><br>",
                                       "# followers <b>", user_ids$num_follower, "</b><br>",

Equally, we decide the dataset desk columns that correspond to our edges’ from, to, and colour attributes:

edges <- create_edge_df(from = dataset$uid,
                        to = dataset$uid_retweet,
                        arrows = "to",
                        colour = dataset$col_sentiment)

Lastly, with the node and edge information frames prepared, let’s create our visualization utilizing the visNetwork library and retailer it in a variable the default code will use later, referred to as p:

p <- visNetwork(nodes, edges) %>%
  visOptions(highlightNearest = checklist(enabled = TRUE, diploma = 1, hover = T)) %>%
  visPhysics(stabilization = checklist(enabled = FALSE, iterations = 10), adaptiveTimestep = TRUE, barnesHut = checklist(avoidOverlap = 0.2, damping = 0.15, gravitationalConstant = -5000)) 

Right here, we customise a couple of community visualization configurations in visOptions and visPhysics. Be at liberty to look by the documentation pages and replace these choices as desired. Our Precise code part is now full, and we should always replace the Create and save widget part by eradicating the road p = ggplotly(g); since we coded our personal visualization variable, p.

Step 3: Put together the Visualization for Energy BI

Now that we’ve completed coding in R, we should make sure modifications in our supporting JSON information to arrange the visualization to be used in Energy BI.

Let’s begin with the capabilities.json file. It consists of a lot of the info you see within the Visualizations tab for a visible, similar to our extension’s information sources and different settings. First, we have to replace dataRoles and substitute the prevailing worth with new information roles for our dataset and num_records inputs:

# ...
  "dataRoles": [
      "displayName": "dataset",
      "description": "Connection Details - From, To, # of Connections, Sentiment Color, To Node Type Color",
      "kind": "GroupingOrMeasure",
      "name": "dataset"
      "displayName": "num_records",
      "description": "number of records to keep",
      "kind": "Measure",
      "name": "num_records"
# ...

In our capabilities.json file, let’s additionally replace the dataViewMappings part. We’ll add situations that our inputs should adhere to, in addition to replace the scriptResult to match our new information roles and their situations. See the situations part, together with the choose part beneath scriptResult, for modifications:

# ...
 "dataViewMappings": [
       "conditions": [
          "dataset": {
            "max": 20
          "num_records": {
            "max": 1
      "scriptResult": {
        "dataInput": {
          "desk": {
            "rows": {
              "choose": [
                  "for": {
                    "in": "dataset"
                  "for": {
                    "in": "num_records"
              "dataReductionAlgorithm": {
                "prime": {}
# ...

Let’s transfer on to our dependencies.json file. Right here, we are going to add three further packages beneath cranPackages in order that Energy BI can determine and set up the required libraries:

    "title": "information.desk",
      "displayName": "information.desk",
      "url": ""
    "title": "DiagrammeR",
      "displayName": "DiagrammeR",
      "url": ""
    "title": "visNetwork",
      "displayName": "visNetwork",
      "url": ""

Be aware: Energy BI ought to routinely set up these libraries, however in the event you encounter library errors, attempt working the next command:

set up.packages(c("DiagrammeR", "htmlwidgets", "visNetwork", "information.desk", "xml2"))

Lastly, let’s add related info for our visible to the pbiviz.json file. I’d suggest updating the next fields:

  • The visible’s description area
  • The visible’s help URL
  • The visible’s GitHub URL
  • The creator’s title
  • The creator’s e-mail

Now, our information have been up to date, and we should repackage the visualization from the command line:

pbiviz package deal

On profitable execution of the code, a .pbiviz file ought to be created within the dist listing. The complete code coated on this tutorial will be considered on GitHub.

Step 4: Import the Visualization Into Energy BI

To import your new visualization in Energy BI, open your Energy BI report (both one for present information or one created throughout our Prerequisite step with check information) and navigate to the Visualizations tab. Click on the [more options] button and choose Import a visible from a file. Be aware: You might have to first choose Edit in a browser to ensure that the Visualizations tab to be seen.

A pane appears with the title

Navigate to the dist listing of your visualization folder and choose the .pbiviz file to seamlessly load your visible into Energy BI.

Step 5: Create the Visualization in Energy BI

The visualization that you just imported is now out there within the visualizations pane. Click on on the visualization icon so as to add it to your report, after which add related columns to the dataset and num_records inputs:

A pane appears with a selected tools icon that has the hover text

You may add further textual content, filters, and options to your visualization relying in your mission necessities. I additionally suggest that you just undergo the detailed documentation for the three R libraries we used to additional improve your visualizations, since our instance mission can’t cowl all use instances of the out there capabilities.

Upgrading Your Subsequent Social Community Evaluation

Our ultimate result’s a testomony to the facility and effectivity of R with regards to creating customized Energy BI visualizations. Check out social community evaluation utilizing customized visuals in R in your subsequent dataset, and make smarter selections with complete information insights.

The Toptal Engineering Weblog extends its gratitude to Leandro Roser for reviewing the code samples offered on this article.

From top to bottom, the words
As a Microsoft Gold Accomplice, Toptal is your elite community of Microsoft consultants. Construct high-performing groups with the consultants you want—wherever and precisely whenever you want them!


Please enter your comment!
Please enter your name here