Our use of cookies

We use cookies to tailor your experience, gather analytics, and provide you with live assitance. By clicking "Accept" or continuing to browse our site you agree to the use of cookies. For more details please read our Cookie Policy.

SharePoint Online List vs SQL Server as Data Source in Power-BI

Lists are primarily used as a source of data storage in the world of SharePoint and in most scenarios these lists can grow really fast especially if we are dealing with transactional data or a large store of reference data. As the SharePoint List grows and eventually goes over 5000 items, it becomes what we call a Large List. Large lists have their fair share of obstacles most of which can be dealt with using list views and indexing columns however when it comes to reporting on these lists using a tool like Power BI there is no easy way to avoid the performance deficiency and the overhead.

In this blog, we will take a brief look at different connection modes available in Power BI for SharePoint List and SQL Server data source along with their benefits. We will generate a Power BI report on live data coming from a Large List using SharePoint Online list data source which uses Import data connectivity mode. Finally, we will generate the same report using SQL Server database as a data source and Direct Query data connectivity mode and we will look at which data source performs well.

AxioWorks SQList continuously export SharePoint lists and libraries as normalised SQL Server tables, making live SharePoint data available to reporting tools like Power BICrystal Reports, or SSRS.

Data Connectivity modes in Power BI

Data Connectivity mode defines the way for Power-BI to connect and get the data from the selected data source. There are two data connectivity modes available namely Direct Query and Import. Some data sources provide the option to select either at the time of connection whereas others are fixed with only one option.

Direct Query

  • Data not imported or copied into Power BI Desktop.
  • Recommended for reports using very large datasets like a Large List from SharePoint.
  • Data is refreshed as you interact with the report.


  • Loads and stores all the data into Power BI before using it in the report.
  • Refreshing data can be cumbersome and time-consuming.
  • 1GB Dataset limit.

With SharePoint List Data-source, we only have Import available as connection mode however SQL Server data source provides the option to select between Direct Query or Import as a data connectivity mode.

Generating Report using a Large List from SharePoint Online

For this demo, we will use a large list that holds 10,000 items to simulate a large list. You can use the scripts from the link below to simulate a large list on your SharePoint site.


Create a Database in SQL Server

  • First off, we need a database that will be used for replicating the data coming from SharePoint large list.
  • Open SQL Server Management Studio and create a new database named “AxioWorksLargeList”.

Replicate and Synchronize data to Database

  • Secondly, we need to replicate the data from SharePoint list into SQL Server database. We will use AxioWorks SQList to achieve that.
  • Open SQList Manager and click on “Create a new replication”.
  • Create a new SharePoint connection, enter the connection name and Table prefix
  • Select the SharePoint site which contains the large list and click next.
  • Next, select the replication large list.
  • Next, enter the details for the destination SQL database connection which is the database we created earlier.
  • Once the replication is created, SQList replication service will start synchronizing the SharePoint large list and SQL database.
  • Now create a sql view based on the large list table which we will later use in the report data source.

Note: SQList will keep the database in-sync with the SharePoint lists which means any updates made in the lists will be replicated in this database as well.

Generate a report in Power BI using SharePoint List

  • Let’s open up Power BI and select SharePoint Online List from the available data sources.
  • Enter the Site URL of the site which contains the large list.
  • Select the large list from the site lists and press load.

  • You will notice that it will take some time to load the data since it using Import which is the default and only available connection mode for SharePoint Online.

  • Once all the data is imported now you can build a report by selecting table visual form the visualizations section.
  • Now, if you will refresh the data source you will see that power bi will import the data again from SharePoint to get the latest which will take same amount of time as the first time when we loaded the data. This can be very cumbersome and time consuming if you have a large dataset that require frequent refresh.

Generate a report in Power BI using SQL Server

  • Open a new Power BI instance and this time select SQL Server as a data source. You will notice that now you have an option to select data connectivity mode. Select Direct Query.

  • Select the SQL View which we created earlier in the database and press Load.

  • You will see that the data loading is instantaneous since we selected DirectQuery. This means there will be no data imported in Power-BI and data will be fetched from the data source based on the interaction with the report.
  • Now go back to the SharePoint site and add an item to the Large List. Now if you refresh the data source you will see the refresh is instantaneous as well and the latest updates will be reflected in the report thanks to the live-synch feature of SQList.

To Summarize

AxioWorks SQList continuously export SharePoint lists and libraries as normalised SQL Server tables, making live SharePoint data available to reporting tools like Power BICrystal Reports, or SSRS.

We explored the two different data connectivity modes available in Power-BI i.e. Direct Query and Import and then we used both these modes to generate a report using data from a Large List in SharePoint. It is clear that Direct Query is the most efficient and performance-driven mode of data connectivity when it comes to Large Lists and since SharePoint Online Data source doesn’t support Direct Query, therefore the only way to use it is to Replicate the data in SQL Server Database with the help of SQList and then use it as a data source in Power BI.


Latest articles

A pragmatic approach to obtaining value from your SharePoint data

20 Jan 2023

Organisations have been storing data in SharePoint for more than 20 years.  This data can be as simple as a List used by a small team or a complex […]
[read article]

Power BI Report Server : An On-Premises Report hosting Suite

16 Jan 2023

Power BI has become the most widely used tool for reporting due its user-friendly interface which allows business users to generate sleek and […]
[read article]

AxioWorks Newsletter December 2022: Good bye 2022, welcome 2023!

29 Dec 2022

Hello Reader, Welcome to the December issue of our monthly Newsletter! Each month, we try our best to bring to you content that is relevant and […]
[read article]

PowerApps Debugging and Troubleshooting made easy with Monitor

28 Dec 2022

Troubleshooting and debugging are an integral part of building and supporting a resilient app especially when it is built using a low-code […]
[read article]

AxioWorks Newsletter November 2022: Is it almost Xmas already?

29 Nov 2022

Hello Reader, Welcome to the November issue of our monthly Newsletter! Each month, we try our best to bring to you content that is relevant and […]
[read article]

Future of SharePoint in the world of Power Platform

29 Nov 2022

Microsoft SharePoint has been the driving force for enabling virtual team collaboration and the modern workspace revolution over the last three […]
[read article]

Power Pages: A website builder like no other 

31 Oct 2022

There is no denying the fact that low-code/no-code software development tools have gained a significant market segment of software development and […]
[read article]

AxioWorks Newsletter October 2022: It’s been a while!

26 Oct 2022

Hello Reader, Welcome to the October issue of our monthly Newsletter! Each month, we try our best to bring to you content that is relevant and […]
[read article]

AxioWorks SQList is now available online

27 Sep 2022

We have launched SQList Online! The cloud-based software as a service (SaaS) version of SQList, our on-prem application that makes it extremely easy […]
[read article]

MS Purview : One-Stop-Shop for data governance across your data estate

30 Aug 2022

Every organization utilizes various sources of data often scattered across cloud and on-premises systems. Over time, these data sources continue to […]
[read article]

Get SharePoint and SQL news to your inbox

Stay up-to-date with industry news and trends, SQL and SharePoint innovations and all the latest from AxioWorks by subscribing to our monthly newsletter.

Subscribe to the AxioWorks newsletter