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.

Import

  • 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.

http://support.axioworks.com/utilities

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

AxioWorks Newsletter November 2021: What has AxioWorks been up to?

03 Dec 2021

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

What went down in MS Ignite 2021 : Power BI Edition

30 Nov 2021

When we talk about the biggest annual tech conferences of the year, Microsoft Ignite comes to mind where not only we get a view of all their product […]
[read article]

AxioWorks Newsletter October 2021: Focus on customer service and a webinar on its way

01 Nov 2021

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

Gain valueable Insights into your PowerApps using Application Insights

25 Oct 2021

Power Platform provides a great way for citizen developers to develop Apps using low-code /no-code tools like PowerApps, PowerAutomate, and PowerBI. […]
[read article]

AxioWorks Newsletter September 2021: Focus on our sales effort

18 Oct 2021

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

Why use SQList to generate Power BI reports from SharePoint lists, rather than using the Power BI native connector to SharePoint?

18 Oct 2021

The main reason is that in practical terms using the native connector only works for small amount of data and simple queries. When you start using it […]
[read article]

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

15 Sep 2021

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 […]
[read article]

AxioWorks Newsletter August 2021: We’re working hard

30 Aug 2021

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

Get the Power of Location-based analytics in Power BI using ArcGIS Maps

16 Aug 2021

In today’s world, data analytics has to become pivotal for making informed decisions and decision-makers are always keen to find new and innovative […]
[read article]

AxioWorks Newsletter July 2021: We’ve expanded!

01 Aug 2021

Hello Reader, Welcome to another exciting issue of our monthly Newsletter! Each month, we try our best to bring to you content that is relevant and […]
[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

*