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

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]

Why Companies are choosing SQList over Power Automate

30 Oct 2023

In the realm of synchronisation services, AxioWorks’ SQList and Microsoft’s Power Automate (earlier known as PowerApps) have emerged as […]
[read article]

AxioWorks Newsletter October 2023: An exhilarating month.

28 Oct 2023

Hello Reader, As we step into the season of autumn, we are thrilled to share with you the latest developments and opportunities unfolding at […]
[read article]

Why Choose SQList for SharePoint Data Extraction Over DIY Solutions

16 Oct 2023

In the ever-evolving world of technology and data management, the question often arises: “Why should I use a product like #SQList when I can […]
[read article]

AxioWorks Newsletter September 2023: An exhilarating month.

30 Sep 2023

Hello Reader, September has been a bustling and exhilarating month for us at AxioWorks, filled with advancements, collaborations, and a whole lot of […]
[read article]

The Collaborative Pathway: Bridging Minds in the AI Ecosystem

19 Sep 2023

In recent days, I found myself immersed in an enlightening conversation with a friend, a fellow entrepreneur steering his ship in the vast sea of […]
[read article]

AxioWorks Newsletter August 2023: A month of milestones and innovation.

28 Aug 2023

Hello Reader, September seems to be a month of unexpected milestones and continued innovations at AxioWorks. As the summer wanes and the autumn […]
[read article]

Use Graph API in Power Automate to read large Excel files from SharePoint

28 Aug 2023

Power Automate, a part of Microsoft’s suite of tools, is increasingly becoming a preferred choice for automating workflows and business […]
[read article]

From Punch Cards to Python: Still Hitting the Keys in my 50s

16 Aug 2023

I spent the entire weekend writing code, not because of deadlines to meet or million-pound dreams, but for the kick that I still get from doing it. […]
[read article]

AxioWorks Newsletter July 2023: Unveiling AxioWorks’s latest successes and exciting innovations

27 Jul 2023

Hello Reader, We hope this monthly update finds you in good spirits. As always, we are eager to share the latest developments and exciting ventures […]
[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

*