Optimization of Queries with SharePoint Online List as Data Connector

[Anonymous]

Optimization of Queries with SharePoint Online List as Data Connector

Background: We had a business case scenario where the portal has been developed with SharePoint Online Provider Hosted app and Ad-Hoc reports has been handled using Power BI Online and Desktop. I would like to share my experience of using Power BI for reporting.

As we all know Microsoft promotes Power BI as the revolution in Analytics and a strong competitor for other BI Tools in the market like Tableu etc. But there are many gaps using SharePoint Online list as a Data source connector. Where the Microsoft says that -yes we have ongoing issues with SharePoint Online List Data connector which will be taken care in near future but God know when. But the sad part is we have moved our reports to Live environment and we ended with Schedule Refresh errors. So we started applying multiple workarounds and seems like few were fruitful. Cheers to the users of Microsoft community we shared their feedback and helped each other in rectifying few errors.

So coming to the issues that were noticed from the time Power Bi reports has been created, I would like other users to be cautious in using the product and follow these guidelines if they seem to be helpful.

Issues:

• 429-Bad Request Error:
  • Microsoft site claims that this issue occurs when SharePoint Online threshold limit exceeds and it no longer allows the requests to be processed
• Dataset refresh failure due to Invalid Credentials:
  • Microsoft claims that this issue occurs because of Mid Air token failure. Post-July this midair token expiry will be raised to 2 hrs.

Hope Microsoft takes care of this issues in the future releases. Below are the few workarounds that will optimize the time for refresh failure.

Working with Person or Group Columns:

 If the scenario involves displaying “Title” of Person or Group Column, when the data is extracted to Power BI, the column values will be displayed as record. Power BI treats this column values as Different table and we need to extract “Title” value from Record table.

This becomes an additional step and it creates overhead while executing the queries against Data Source. Imagine a scenario where there are many Person or Group column in SharePoint list.

Workaround: Instead of extracting “Title” from record value of Person/group column, extract columns from “Field Values as Text” column. This will help in avoiding the expand step for multiple person or group columns and increase efficiency by selecting all person related columns as Text values

Working with Data Time Format and Currency Format:

Never change the format of the columns in Edit Queries by right click on the column, select  “Change Format” and Select “Date”. This will also be overhead as it creates an additional step while changing the format.

Instead follow the below steps:

• Exit from “Edit Query” menu.
• Select column under fields whose data type is Date
• Select “Modelling” and change “Data Type” to Date
• In the Format select the desired format.

 

• By this way it doesn’t create additional steps on the dataset

Encourage more usage of DAX queries then creating Custom columns and Conditional Columns in Edit Query window

For any custom logic building by creating columns or measures, try to achieve more using DAX rather than creating columns by using OOTB Conditional Columns.

Effective Use of Manage Relationships:

By Constructing relationships between the tables, this will give more flexibility of drag and drop fields in to single visual from two tables

This will avoid creation of unnecessary custom tables using Merge Queries.

Limit creation of Custom Tables using Merge Queries and Append Queries: This will create huge impact on processing the dataset as it depends on other tables for the data refresh to complete

Limit the number of tables in each Dataset:

Based on business cases, split the number of tables in each dataset and go for multiple dataset approach in each app workspace.

Hope this helps in leveraging maximum output through Dataset refresh. Expect Microsoft to come with new functionality implementations in their upcoming released for SharePoint List connector.

Encourage everyone to share their thoughts as well so that everyone in the community will be aware of the issues and possible workarounds.

Cheers!!

[v-yuezhe-msft]

@Anonymous ,

Thanks for your sharing.

Regards,