Power BI Analytics Powered By R Integration

In this article we will have discussion and demo on how to integrate “R” scripts with “PowerBI” using PowerBI R Connector.

In order to utilize this article you should some basic understanding with R Programming.

Before getting into demo lets discuss the concept using following Solution Architecture Diagram-

1

Data Sources Layer shows few of the available data source that I have tested this solution with

Data Crawlers & Connectors Layer shows a generic set of Data Connectors provided by Reporting Engines and Custom Crawlers.

CSV Raw Data Sources shows the set of output files that can be used as input files by R APIs

R Programming Interface shows the R APIs and & Packages that can be utilize to perform statistical operations on the incoming data and generated a meaningful output.

Computed/Analytical Data Output shows the Output files generated by R APIs

Reporting Engines shows a couple of famous reporting engines that can be utilized to develop reports based on the input received from R APIs

In this demo I am not covering the Crawl process since there is nothing new about it. I am considering a CSV Raw Data Source is available to be consumed by R APIs as shown below-

In this raw data source we have some data related to the File Shares. It is showing number of files that are available within each file share for a specific quarter.

2

Now the scenario is to consume this data for developing trend analysis report and the obvious problem is that it is not in desired format to be consumed directly reporting engine.

That’s where we are utilizing R Programming Language which is quite in fashion these day and is well known for its Statistical Capabilities.

Though I am not displaying any hefty R Code here but rest assured you are all free to take this data transformation up to any level as per your requirements.

In the following R snippet I am reading data from CSV Files and group it based on the Quarter Column to display quarter wise Files Count.

3

For example this could be helpful if we need to develop some trend analysis on sizing capabilities of a disk.

You can see the outcome of this script as highlighted below-

Column “Group.1” shows Quarters & Column “x” shows total number of files per quarter

4

Once the R script is ready and validated we can start designing report using any of the report designers support R connectors or if they don’t we can save the R output as CSV/Database file and can feed the report designer with this output.

For this demo I am using PowerBI and interesting thing about it is having inbuilt R connector for rendering R scripts with in the report designer.

  • Launch Power BI desktop application

5

  • Click on Get Data Menu -> Click on More…

6

  • Select “Other” category of available connectors
  • Select R Script connector under “Other”
  • Click Connect

7

  • Paste the R Script in the script designer
  • Click OK.

8

As soon as script designer able to parse the script, it will show you all the possible outputs that were stored in R Variables inside the script

  • Select source as required
  • Click Load to load the required data source in designer memory

9

In the report designer we can see data source and its related fields listed under Fields section as highlighted

Under Visualizations we can see the possible Charts/Graphs available to design the dashboard.

Choose as per requirement and design the report

10

Here is demo dashboard that I have developed using this data source.

11

The important take away from this demo is to understand the right selection of technology and possibility of technology fusion to get desired output.

Hope you find it helpful.

SharePoint Developer Tools: How To Test & Debug SharePoint REST API Endpoints (POST Requests)

This is the second are article in the series of using Fiddler as Debugging & Testing Tool for SharePoint REST API EndPoints.

You can read the article on GET Request here:

SHAREPOINT DEVELOPER TOOLS: HOW TO TEST & DEBUG SHAREPOINT REST API ENDPOINTS (GET REQUESTS)

POST requests are different in nature than GET requests. They require more authentication layers to get through in order to push the data to SharePoint Lists and Libraries.

In order to run the POST request successfully we need an additional request header “X-RequestDigest” which is not but the User Authentication Token.

In order to request this token from SharePoint we need to make of “contextInfo” endpoint that will return the “FormDigestValue” containing the required user authentication token.

Now let see how we can request Authentication Token from SharePoint

Get Authorization Token

http://<Host Name>_api/contextinfo

1

2

Once we get the Authentication Token from SharePoint, we can add this token information in the Request Header of each of the POST requests

Request Headers

Accept: application/json;odata=verbose
Content-Type: application/json;odata=verbose
X-RequestDigest: 0xE1AE266A42214DA2940689826F68426D10620220CEDD3093CA2C234993E4ECA265BA57D357E8D3BD32F56660613CADBF72495F2C858B38F7C9B9C3CAD797F6D5,06 Feb 2017 01:22:08 -0000

Once we are ready with Request Headers we can start issuing POST Requests as shown below-

Add Data to List

Let’s consider we have a list called Categories as shown below-

3

First see the XML return based on querying schema for Categories List using following URL

http://<Host Name>/_api/Web/Lists/getByTitle('Categories')

4

Then we will see the XML return based on querying for Categories List Items using following URL

http://<Host Name>/_api/Web/Lists/getByTitle('Categories')/Items

5

Next step is to prepare the Request Body and we have to include following properties to add the items.

Please note that I am taking properties that are required for this list to add the category and add any desired number of properties to the Request Body as per the schema of the target list.

Request Body

"__metadata": { type: " SP.Data.CategoriesListItem" },
Title: "Category From Fiddler",
CategoryID: 9,
Description: “New Category Added from Fiddler”

6

Once we execute this request we can inspect the response to ensure that the request item has been added successfully to the Categories List.

7

Also we can validate this new item added by browsing Categories List

8

Update List Item

http://<Host Name>/_api/Web/Lists/getByTitle('Categories')/Items(9)

For update request you have to include “eTag” value that was returned with the item during the initial query to the Request Body. SharePoint uses this value to determine if there is any updates made to the item since it is last queried.

“If-Match: *” can be used to match any “eTag” value resulting in the operation being performed regardless of the actual value.

“X-Http-Method: PATCH” is to override the existing values

So the request body would be like this

IF-MATCH: *
X-Http-Method: PATCH
{
    "__metadata": {
    type: "SP.Data.CategoriesListItem"
},
Title: "Category From Fiddler - Updated",
Description: "New Category Added from Fiddler - Updated"
};

9

Once the request executed successfully we can see the item is updated in the Categories List

10

Delete List Item

http://<Host Name>/_api/Web/Lists/getByTitle('Categories')/Items(9)

Delete operation is more or less similar to Update operations.

11

In case of delete we will use of “X-Http-Method: DELETE” in the Request Body

Request Body

IF-MATCH: *
X-Http-Method: DELETE

12

Once the request executed successfully we can validate the item is deleted from the list.

13

Add New List

http://<Host Name>/_api/Web/Lists

Adding a new SharePoint List involve a little bit more of configuration information in Request body apart from request headers

Request Headers

Accept: application/json;odata=verbose
Content-Type: application/json;odata=verbose

Request Body

Content-Length: 0
{
"__metadata": { type: "SP.List" },
"AllowContentTypes":true,
"ContentTypesEnabled":true,
"Description":"This is Task List Created using Fiddler",
"BaseTemplate": 107,
"Title": "Task List By Fiddler"
}

14

Once this request has been executed successfully we can see the Response Body holding information about newly added SharePoint List

15

Also we can see this new list added to SharePoint by browsing the respective site

16

17

Also we can verify the “AllowContentTypes” & “ContentTypesEnabled” properties are configured as expected by browsing the Advanced Properties of the new List as shown below-

18

Delete List

http://<Host Name>/_api/Web/Lists/getByTitle('Task%20List%20By%20Fiddler')

Deleting a list is rather simpler than adding it. It takes “X-Http-Method: DELETE” to be added to the request header and rest will be done for you.

 Request Headers

Accept: application/json;odata=verbose
Content-Type: application/json;odata=verbose
Content-Length: 0
IF-MATCH: *
X-Http-Method: DELETE

19

Once the request has been completed, it will delete the required list from SharePoint Lists Collection.

20

Hope you find it helpful.

SharePoint Developer Tools: How to Test & Debug SharePoint REST API Endpoints (GET Requests)

In this article we will understand how utilize a famous developer productivity tool called fiddler as REST API Test Client for SharePoint (though the target system could be anything with a valid REST API Endpoint)

Fiddler is primarily used as a Web Proxy that can allow you intercept REST API Request – Response Cycle. The usage of this tool has increase with shift in modern SharePoint development paradigms that favors more if Client Side Development Techniques/Strategies/Platforms rather than traditional Farm Solutions.

In this upcoming section of this article I will guide on how to use Fiddler to test REST API Call against SharePoint Data.

In this article we will explore only GET type of Requests only.

To start with this demo launch Fiddler and go to “Rules” Menu and Select “Automatically Authenticate”, this will let Fiddler to authenticate you against SharePoint based on the User Token stored once.

1

If this setting is not enabled you might encounter “401 UNAUTHORIZED” as shown below-

2

Also notice the request headers that are required to execute the SharePoint REST API Endpoint

GET Requests

http://<Host Name>/_api/<SharePoint Endpoint>

Request Headers
Accept: application/json;odata=verbose
Content-Type: application/json;odata=verbose

Get Web Object

http://<Host Name>/_api/web

  • Click on “Compose” Tab
  • Select request type as “GET” from dropdown
  • Specify the Request URL as http://<Host Name>/_api/web
  • Click on “Execute” Button

3

Once the request is issued using Fiddler “Composer“, we can see the request details in the left pane

4

When you click on the request in the left pane we can see the details breakdown in the Right Pane

For instance we can click on “Inspectors” tab and then click on “JSON” tab.

JSON Tab will display the response received from SharePoint in JSON Format.

5

Similarly we can execute other GET Requests as shown in upcoming Screen Shots-

Get List Object

http://<Host Name>/_api/Web/Lists

6

7

Get Lists which are not hidden and have Items

http://<Host Name>/_api/Web/Lists?$select=Title,Hidden,ItemCount&orderby=ItemCount&$filter=((Hidden eq false) and (ItemCount gt 0))

Encoded Version of Request URL

http://<Host Name>/_api/Web/Lists?$select=Title,Hidden,ItemCount&orderby=ItemCount&$filter=((Hidden%20eq%20false)%20and%20(ItemCount%20gt%200))

8

9

Get Web filtered by Title

http://<Host Name>/_api/Web/?$select=Title

10

11

Get Web and Lists using Look Properties Expanding Lists Collection

 http://<Host Name>/_api/Web/?$select=Title,Lists/Title,Lists/Hidden,Lists/ItemCount&$expand=Lists

12

13

Get Web and Lists using Look Properties Expanding Users Collection

http://sp-2016-ddev/_api/Web/?$select=Title,CurrentUser/Id,CurrentUser/Title&$expand=CurrentUser/Id

14

15

That is all for this demo.

Hope you find it helpful.

SHAREPOINT ONLINE/OFFICE 365: HOW TO COPY EMAIL ATTACHMENTS TO SHAREPOINT DOCUMENT LIBRARY USING MICROSOFT FLOWS

In this article we will see how we can utilize Microsoft Flows to copy email attachments from Outlook Emails to SharePoint Document Library.

To continue with this demo we can start with adding a document library to SharePoint site, let’s call it “Outlook Attachments”

1

Now go to Microsoft Flow designer and start with adding a workflow

As I explained in my earlier articles we need to start with selecting a trigger for this workflow

Since we need to copy the email attachments to document library so we have two Cloud Services “Office 365 Outlook” and “SharePoint Online” to deal with

Select “Office 365 Outlook” as connector and “Office 365 Outlook – When a new email arrives” as trigger to kick off this workflow

2

Choose the Outlook folder or subfolder that you want to monitor. Here I am selecting “Inbox” as target folder. This means workflow will trigger only if the email arrives in Inbox folder and rest of the emails will be ignored.

3

Set “Has Attachment” = Yes, this settings will further filter out the incoming emails based on if they have attachments or not.

We are only monitoring only emails with attachments and that’s what these filter will achieve for us.

Set “Include Attachments” = Yes, this is required so that Workflow runtime include the binary information of each the attachments and make it available to be written to SharePoint Library.

4

Add another action with “SharePoint” as connector

Select “SharePoint – Create file” as trigger, this action will be responsible to write down the attachment binaries to SharePoint Library

5

Enter Site URL where we have “Outlook Attachments” document library sitting

Select document library name from the lookup in the Folder Path Field

6

Select “Name” property in the File Name field, this property represents the name of the attachment

7

Select “Content” in the File Content Field, this property will hold the binary data for the attachment being written to the SharePoint Document Libraries.

Here it is important to note that if we does not set the “Include Attachments” = Yes, then Content property will be null and will cause not write operations takes place

8

Add another action to the workflow, which will be responsible to send email after the attachment has been copied to the SharePoint Library

9

Select “Office 365 Outlook” as connector

Select “Office 365 Outlook – Send an email” as trigger, this trigger is responsible for sending emails

10

Choose or any valid email id in “To”, here I am just selecting the email of me

Specify Subject as applicable

Specify the Body content as required. This content can consist of dynamic tokens provided by Workflow runtime as shown below-

11

Saves the Workflow and complete it.

In the following screens we can see the complete workflow steps in one go-

Step-1

12

Step-2

13

Step-3

14

Now let’s test the workflow by sending an email with attachments to myself.

15

Once I received the email containing attachments in Inbox Folder, Workflow gets triggered

16

Just after that we can see workflow entered into the debug mode as shown below-

17

And as soon as it is completed we can see the completion notification email as shown below-

18

And we can also see the attachments copied to the “Outlook Attachments” SharePoint Library as shown below-

19

Hope you find it helpful.