SharePoint Online: How Develop Analytics for SharePoint Online using POWER BI

In this article we discuss to use data stored in SharePoint Online Site to develop analytics using PowerBI.

Here is the architecture diagram to understand it in a better way:

1

For details on this architecture you can visit: POWER BI ANALYTICS POWERED BY R INTEGRATION

In order to start with this demo, we will start with two lists namely Products & Product Category having some test data to work with as shown below:

12

Now launch the PowerBI desktop

3

Click on Get Data -> More…

4

From “Online Services” category select “SharePoint Online List”

Click “Connect”

5

Specify the URL of SharePoint Online Site

Click OK

6

Select “Product” & “Product Categories” from Navigator

Click Load to load the data in the memory of report designer

7

Let the designer query the list metadata and build cache

8

Under Fields section we can “Product” & “Product Categories” added to the designer

9

Right Click on Product List and Select “Edit Query” to modify the query for fetching the data from lists

10

In the Query Editor, click on “Choose Columns” Menu -> Choose Columns

11

Select the desired columns and from the list to be included into the report. This step is useful from performance point of view as well so always try to include only relevant columns in the query

Click OK

12

We get the new result set updated based on the query modifications

13

Now modify any of the available lookup fields or Composite fields like URL in SharePoint to include the required property into the final result set

Click on the icon next to “Product Url” as indicated below

14

Select required properties like I am selecting “Url” and deselecting “Description”

Make sure you select “Use original column name as prefix” so that new property name generate based on the original column name

Click OK

15

Once we are done we are done with the modification we can see a new column appears with the name “Product Url.Url”

16

Once all the changes have been made click “Close & Apply” command button in the ribbon

17

Wait till the query changes have been saved successfully

18

Once all the changes have been saved we can see the selected query fields under “Fields” section

19

Now we establish Parent Child Relationship between “Products” & “Product Categories” list

Click “Manage Relationships” option from the Ribbon

20

On the “Manage relationships” screen Click “New” to add a new relationship between “Products” & “Product Categories” list

21

Select Primary Key & Foreign Key Columns on the “Create Relationship” screen as shown below

Select “ProductCategoryId” column from “Products” List as Foreign Key and “Id” column from “Product Categories” List as Primary Key

Also select “Cardinality” as required as “Many to one”

Once done click “Ok” to complete the process

22

And we can be able to see the new relationships created as shown below.

Click “Close” to close the “Manage Relationship” dialog

23

Once this is done we can develop reports using any available visualizations as highlighted below-

24

Here is the sample dashboard that I have developed based on the data available in SharePoint Lists

25

Hope you find it helpful.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s