In this article, we will discuss how 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:
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, with some test data to work with as shown below:
Now, launch the PowerBI desktop.
Click on Get Data -> More…
From “Online Services” category select “SharePoint Online List”
Click “Connect”
Specify the URL of SharePoint Online Site
Click OK
Select “Product” & “Product Categories” from Navigator.
Click Load to load the data in the memory of report designer.
Let the designer query the list metadata and build cache.
Under Fields section, we can add “Product” & “Product Categories” to the designer.
Right Click on Product List and Select “Edit Query” to modify the query for fetching the data from lists.
In the Query Editor, click on “Choose Columns” Menu -> Choose Columns
Select the desired columns and from the list to be included in the report. This step is useful from a performance point of view as well so always try to include only relevant columns in the query.
Click OK
We will get the new result set updated based on the query modifications.
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.
Select required properties -- I am selecting “Url” and deselecting “Description”
Make sure you select “Use original column name as a prefix” so that new property name generates based on the original column name.
Click OK
Once we are done with the modification we can see a new column appearing with the name “Product Url.Url.”
Once all the changes have been made click “Close & Apply” command button in the ribbon.
Wait until the query changes have been saved successfully.
Once all the changes have been saved we can see the selected query fields under “Fields” section.
Now, we establish Parent-Child Relationship between “Products” & “Product Categories” list.
Click “Manage Relationships” option from the Ribbon.
On the “Manage relationships” screen Click “New” to add a new relationship between “Products” & “Product Categories” list.
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 the required “Cardinality” as “Many to one”
Once you've finished, click “Ok” to complete the process.
And we will be able to see the new relationships created as shown below.
Click “Close” to close the “Manage Relationship” dialog.
Once this is done we can develop reports using any available visualizations as highlighted below:
Here is the sample dashboard that I have developed based on the data available in SharePoint Lists.
Hope you find it helpful.