Performance issue in Power BI report . Data source: SharePoint List

Power BI is SAAS based application which helps an organization to integrate data from across platform , perform ETL and data modelling operations and create interactive and informative visuals on top of it which can be shared across organization and to external users.

User Story : Specifically users feel performance issues while SharePoint list are used as data source considering the constraints of Data size associated with SharePoint List. SharePoint list size will keep on increasing the organization are looking for an alternative to get an optimized solution to improve performance of such reports.

Approach: Approach to resolve this will be a multidimensional approach where Solution Architect/Power BI Architect has to study the existing setup , conduct phase wise analysis of the environment and provide an easy to use approach which business can understand. Approach discussed in this article is a real time solution which was designed and implemented in one of our project.

1.Solution Architect should review AS IS state of the SharePoint list , like size , the way updates are happening in list and discuss the probable use cases in which these list can be leveraged in future.

Outcome: Above step will provide a picture of usage methodology of related list and give vital information about the list data types, size, relevant fields required for KPIs in report and then the update methodology.

2. AS IS state analysis of existing Power BI report : BI architect should review the data model of existing reports and check if developer has followed the governance and best practices of Data modelling and visualization usage. In addition a review of performance analyzer is required.

Outcome: This will give insights if their is scope of improving the existing Data modelling and designing like use of DAX , limit use of calculated column etc . In addition it gives how the report is performing basis Performance analyzer report.

Designing Phase : Here as per the current COE (Center of Excellence team) setup, BI Architect will have meeting with Data lake/Data warehousing team and discuss creating a solution where data will be sourced form list in table available in Data Warehousing. In our solution we have used a unit based on-premise Azure SQL server and Power Automate to create a Flow which trigger with an Item is updated /inserted. (As this document purpose it to just cover the solution overview , the details of how to create such flow is not discussed in detail)

Power Automate tool can help to create an ETL job to transfer data from SharePoint List to SQL server tables and we can add action item basis triggering action. It is very easy to create and its graphical GUI gives an extra edge where BI developer with minimum understanding of ETL can create a manage flow. In addition these flow can be shared across teams.

Challenge: We do have to consider a scenario where an user is updating a flow item and in parallel flow execute in such case the flow trigger condition will skip and the new entry/update will not be recorded. So on weekly basis we created a separate flow which support Truncate and load operation so that we can include any insert/update missed.

Development Phase : At last developer has to change the connections from SharePoint List to SQL server tables and recreate the transformation and data modelling exercise and rework on DAX queries( Incase the filed names changes)

Final Analysis : Do a performance testing and check if report performance is within the standard timelines as per the Microsoft and then publish the report is services .

Note :- As we have used Azure SQL On-prem. we need to create a Gateway (best Enterprise Gateway) .

Conclusion: Power Automate provide lot of features which can help on organization to resolve performance issues related with Microsoft flow. Flows are very easy to use and its quite easy to troubleshoot issues .