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

Harnessing the Power of Microsoft CoPilot in Power Automate Flows: A Comprehensive Tutorial

25 Mar 2024

In the ever-evolving landscape of technology and business automation, Microsoft has introduced a game-changer that is set to revolutionize how we […]
[read article]

Embracing AI: Transforming Jobs and Unlocking New Opportunities (Claude 3 Opus version)

18 Mar 2024

The rapid advancements in Artificial Intelligence (AI) have sparked both excitement and fear among workers worldwide. Many people are concerned that […]
[read article]

The Evolution of Work in the Age of AI: A New Era of Collaboration (ChatGPT 4 version)

18 Mar 2024

In the annals of human progress, pivotal inventions such as the steam engine, the tractor, and the internet have revolutionarily altered our way of […]
[read article]

AxioWorks Newsletter February 2024: Partnerships, Upcoming SQList Update, and Summit Invitation

27 Feb 2024

Hello Reader, This February has been pivotal for AxioWorks as we’ve focused on laying strong foundations for the year. Here are the key […]
[read article]

How AxioWorks SQList Aligns With the Priorities of Software Buying Decision Makers

26 Feb 2024

As outlined in the “2024 Global Software Buying Trends” report by Gartner Digital Markets, the criteria that predominantly influence […]
[read article]

Maximising Power BI Reporting from SharePoint: The Strategic Advantage of AxioWorks SQList over Native Connectors

15 Feb 2024

In today’s data-driven environment, making informed decisions rapidly can offer a significant competitive edge. This is where Power BI comes […]
[read article]

AxioWorks Newsletter January 2024: A Promising Start to the New Year!

29 Jan 2024

Hello Reader, We hope this newsletter finds you well and thriving. At AxioWorks, we are embracing the new year with great enthusiasm and are […]
[read article]

Exciting New Partnership and Innovative SharePoint Tools from AxioWorks and Lightning Tools

17 Jan 2024

AxioWorks is excited to announce a strategic partnership with Lightning Tools, a leader in creating innovative SharePoint components. This […]
[read article]

AxioWorks Newsletter November 2023: ESPC23, new partnership, and SQList update!

30 Nov 2023

Hello Reader, Welcome to this month’s edition of the AxioWorks newsletter! We’re thrilled to bring you updates on our recent […]
[read article]

Why Support Legacy SharePoint Versions Like SharePoint 2010?

16 Nov 2023

In the fast-paced world of technology, where new versions and updates are continuously rolled out, a recurring question often arises: “Why […]
[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