SharePoint Online: How Develop Analytics Using Power BI
In this article we will 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:
https://howtodowithsharepoint.files.wordpress.com/2017/08/1.jpg?w=800
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:
https://howtodowithsharepoint.files.wordpress.com/2017/09/1.png?w=800https://howtodowithsharepoint.files.wordpress.com/2017/09/2.png?w=800
Now launch the PowerBI desktop.
https://howtodowithsharepoint.files.wordpress.com/2017/09/3.png?w=800
Click on Get Data -> More…
https://howtodowithsharepoint.files.wordpress.com/2017/09/4.png?w=800
From “Online Services” category select “SharePoint Online List”.
Click “Connect”.
https://howtodowithsharepoint.files.wordpress.com/2017/09/5.png?w=800
Specify the URL of SharePoint Online Site.
Click OK.
https://howtodowithsharepoint.files.wordpress.com/2017/09/6.png?w=800
Select “Product” & “Product Categories” from Navigator.
Click Load to load the data in the memory of report designer.
https://howtodowithsharepoint.files.wordpress.com/2017/09/7.png?w=800
Let the designer query the list metadata and build cache.
https://howtodowithsharepoint.files.wordpress.com/2017/09/8.png?w=800
Under Fields section we can “Product” & “Product Categories” added to the designer
https://howtodowithsharepoint.files.wordpress.com/2017/09/9.png?w=800.
Right Click on Product List and Select “Edit Query” to modify the query for fetching the data from lists.
https://howtodowithsharepoint.files.wordpress.com/2017/09/10.png?w=800
In the Query Editor, click on “Choose Columns” Menu -> Choose Columns.
https://howtodowithsharepoint.files.wordpress.com/2017/09/11.png?w=800
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.
https://howtodowithsharepoint.files.wordpress.com/2017/09/12.png?w=800
We get the new result set updated based on the query modifications.
https://howtodowithsharepoint.files.wordpress.com/2017/09/13.png?w=800
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-
https://howtodowithsharepoint.files.wordpress.com/2017/09/14.png?w=800
Select required properties like we are selecting “Url” and deselecting “Description”.
Make sure to select “Use original column name as prefix” so that new property name generate based on the original column name.
Click OK.
https://howtodowithsharepoint.files.wordpress.com/2017/09/15.png?w=800
Once we are done with the modification we can see a new column appears with the name “Product Url.Url”.
https://howtodowithsharepoint.files.wordpress.com/2017/09/16.png?w=800
Once all the changes have been made click “Close & Apply” command button in the ribbon.
https://howtodowithsharepoint.files.wordpress.com/2017/09/17.png?w=800
Wait till the query changes have been saved successfully.
https://howtodowithsharepoint.files.wordpress.com/2017/09/18.png?w=800
Once all the changes have been saved we can see the selected query fields under “Fields” section.
https://howtodowithsharepoint.files.wordpress.com/2017/09/19.png?w=800
Now we establish Parent Child Relationship between “Products” & “Product Categories” list.
Click “Manage Relationships” option from the Ribbon.
https://howtodowithsharepoint.files.wordpress.com/2017/09/20.png?w=800
On the “Manage relationships” screen Click “New” to add a new relationship between “Products” & “Product Categories” list.
https://howtodowithsharepoint.files.wordpress.com/2017/09/21.png?w=800
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.
https://howtodowithsharepoint.files.wordpress.com/2017/09/22.png?w=800
And we can be able to see the new relationships created as shown below.
Click “Close” to close the “Manage Relationship” dialog
https://howtodowithsharepoint.files.wordpress.com/2017/09/23.png?w=800
Once this is done we can develop reports using any available visualizations as highlighted below-
https://howtodowithsharepoint.files.wordpress.com/2017/09/24.png?w=800
Here is the sample dashboard that we have developed based on the data available in SharePoint Lists.
https://howtodowithsharepoint.files.wordpress.com/2017/09/25.png?w=800
Hope you find it helpful.