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 BI, Crystal 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 BI, Crystal 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.