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.

Power BI using SharePoint List VS SQL Tables

Power BI is a no code analytics and reporting tool from Microsoft which allows business users to generate sleek and customizable reports and interact quickly and easily with their data using simple drag-and-drop features. It is an extremely feature rich yet user-friendly reporting tool which can also be used for creating live dashboards and then sharing them with your colleagues or embed them in your app, website, or SharePoint portal. It is compatible with over 65 different data sources out of the box including both on-premises and cloud products.

It comes in two flavors, Power BI Desktop which is the free version available through your Microsoft Account and provides support to various data sources and provides lots of features to satisfy your reporting needs. Power BI Pro is the more advanced and paid version which will allow you to take your reports to the next level with massive reporting functionality along with some cool collaboration features. In this article, we are going to use Power BI Desktop with SharePoint List and SQL Server as a data source to set up a simple report and then analyze which one outweighs the other in terms of ease of use, performance, and features.

Using SharePoint List as a Data source

Power BI helps the SharePoint admins and users to analyse data stored in SharePoint lists and libraries both online and On-Premises. Let’s have a look at how to setup SharePoint list as a data source and use the data to generate a bar chart.

Open power BI and Select SharePoint List as Data Source from the list of data sources:

A prompt will open up, enter the SharePoint site URL. Note: Don’t include the list in URL.

Then enter the credentials you want to use to connect to the site or use Windows Credentials. Then select the list which contains the data you want to generate report for and press load.

This will load all the fields of the list in the right pane and now your list is ready to be used in your report. By default, it will load all the columns so in order to reduce the loading time, remove the columns you don’t need by going to edit data source.

Next, Select a visualization type from Visualizations Pane e.g. bar chart. In the Axis, drag and drop the column you want to appear on y-axis and In Value, drag and drop the column you want to use for showing the values. (In our example, we will use product name in axis and quantity in value).

That’s all you need to do to configure the bar chart. You can apply all types of formatting and customization from format section.

Using SQL Server database as a Data source

If you think using SharePoint list as a data source was exciting, then wait till you see what you can do with your data in a SQL Server database. So, let’s start by selecting SQL Server as data source and enter Server name and Database.

You can either Import data which will fetch the entire dataset and store it in Power BI or use Direct Query to get the data from database on runtime.

Next, you will see all the tables and views available in the database and you can select multiple tables to generate report from or if you already have a defined view then you can use it as a data source. This is where your SQL skills will really come in handy and you can define your view in the database and use it for your reporting purposes here. In this example we will select multiple tables.

That’s pretty much it as far as setup goes and the data from the selected tables will be imported as well as the relationship associated with the tables which you can see by going to Home- > Manage Relationships.

In order to setup a report, add a new Page and then add new visual table. Drag and drop the columns you want to appear in your report from related tables to generate your report.

In order to make the report more interactive, you can add columns in drill down section which allows user to further drill into the data to gain more insights.

There you have it, as you can see with Power BI it’s fairly straight forward to setup reports and fetch data from SharePoint List and SQL Server. Now, let’s compare them to see which one offers more benefits.

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

Pros and Cons of Power BI using SharePoint List

Pros

  • Allows users to generate reports from SharePoint Lists and Libraries data.
  • No Coding is required to configure.
  • Can select multiple lists.
  • Define relationships between the lists.

Cons

  • Can’t use SharePoint user defined views.
  • User information doesn’t come by default and can only be available by using User Information list.
  • Need to define relationships for multiple lists.
  • Can’t import data into Power BI which means visualizations might run slow when dealing with large lists.

Pros and Cons of Power BI using SQL Server

Pros

  • Multiple tables can be used to each comes within its own dataset and can be merged as well.
  • SQL Views can be used for generating reports.
  • Relationships between the tables are loaded by default.
  • Data can be imported into Power BI which can then be used for creating visualizations offline.
  • Can be used by business users to generate report without any knowledge of SQL.

Cons

  • Writing queries in Power BI can be difficult because the syntax is different from SQL.
  • Difficult to migrate existing reports from SSRS.

AxioWorks SQList allows you to export SharePoint lists & libraries as normalised SQL Server tables, and build powerful reports on SharePoint data using SSRS, Crystal Reports, Power BI, on an other reporting tool.

Final Verdict

Power BI is a very intuitive and user-friendly tool which provides business users the ability to generate reports without writing any code or SQL queries. Its integration with SharePoint lists and libraries is impressive but you can do a lot more if you use it with SQL Server which makes it easier to generate reports by utilizing the existing defined relationships and views in SQL Server. Ability to import data into Power BI will allow users to generate visualizations offline. The best part is that it’s free, which means there is no downside of using it if you need a reporting tool.

 

Latest articles

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]

SharePoint Spaces: Empowering enterprises to build Virtual Reality solutions

31 Jul 2021

The World is moving towards virtual reality which is considered by most tech gurus as one of the technologies of the future. The capabilities offered […]
[read article]

AxioWorks Newsletter July 2021: Building up a successful release in June

03 Jul 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]

We are looking for a Sales and Marketing Manager!

18 Jun 2021

We are looking for someone with experience in the SharePoint and BI arenas to take on the role of Sales and Marketing Manager. You will be able to […]
[read article]

Mover : Only Migration tool you will ever need to move your cloud content to SharePoint

11 Jun 2021

One of the biggest conundrum of any organization planning to move to SharePoint is how to move all their documents stored in various different […]
[read article]

New version of AxioWorks SQList released (7.0.0.0)

04 Jun 2021

A new version of AxioWorks SQList is out today! We have been really busy for the past few months, but here we are with a brand new release of […]
[read article]

AxioWorks Newsletter June 2021: May has been a month of new releases

01 Jun 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]

Form Configuration Editor: Lightweight and Built-in SharePoint List Form editor

16 May 2021

SharePoint Lists provides an easy and robust way of storing and manipulating data in SharePoint.  When you set up a new custom list, you also get OOTB […]
[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

*