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.

A pragmatic approach to obtaining value from your SharePoint data

by Peter Baddeley | Microsoft Consultant

Organisations have been storing data in SharePoint for more than 20 years.  This data can be as simple as a List used by a small team or a complex system running multiple business processes.  The challenge is that the simplicity of SharePoint can often lead to the creation of data silos.  As with all data, the data stored in SharePoint becomes more valuable when it is interrogated in reports or integrated with other datasets.

Understanding the Problem

In December 2020 it was announced that there were over 200 million users of Microsoft SharePoint.  In the two years since that there is no doubt that this number has significantly grown.  This has been helped by applications including Microsoft Teams and Microsoft Lists, that heavily rely on SharePoint.

Different users and organisations will use SharePoint very differently.  In some cases, the usage will be very basic, where only a few files are stored.  Whilst in other scenarios there will be a significant amount of data stored.  Further, SharePoint can be used for all sorts of purposes, including helpdesks, asset registers, case and claims management, project tracking and many more examples.  Often these solutions hold a significant amount of data, will involve multiple Lists connected by lookups and may even involve custom enhancements.

It is the scenario of complex and large Lists/Libraries we consider in this post.  The problem for many organisations is how to effectively leverage this data held in SharePoint.  They may want to create reports, dashboards and KPIs using that data.  Transformations may be required to that data or it may need to be added to a data warehouse with other organisational data sources.  They may want to take it a step further, using some of the SharePoint data in integration with line of business applications.

Microsoft Options

If we consider this challenge from the perspective of the Microsoft ecosystem there are two potential approaches.  Firstly, there are reporting options within the Microsoft platform that can connect to SharePoint.  Power BI is perhaps the most obvious option, which can be used to create a data model connected to SharePoint data.  However, this is not always an easy task, SharePoint data is not always stored logically.  Further complications can arise when working with large lists that may contain multiple lookups to other Lists.

Alternatively, we could consider that complex and large data does not have a place anymore in SharePoint.  Microsoft has evolved the SharePoint platform to be focused on intranet communication, lightweight document management and collaboration.  People will point to the Dataverse as Microsoft’s preferred platform for storing complex relational data.

Importance of Existing Platforms

There is a strong argument that SharePoint is not the right place to be storing certain types of data.  There are technical limitations such as List View thresholds and the number of certain column types.  Also fundamentally, despite ways to do it, SharePoint is not designed to store relational data.   The Power Platform is where Microsoft is investing in terms of data input, management, and transformation.

The view outlined above is of course completely valid and should be an aspired end state.  However, for many organisations, it is not that simple.  They may have historically invested in the SharePoint platform, developing a solution that now holds a significant amount of data.  The budget may not currently exist to move away from SharePoint or the migration project may be 18-24 months.  Both these scenarios I have seen frequently with customers who are still using On Premises SharePoint, with complex solutions leveraging Lists and custom development.

It is also important to understand that organisations don’t invest in SharePoint in isolation.  They have many other systems, some of which may be bespoke or legacy applications.  Often, they will invest heavily in SQL Server as a platform and use reporting technologies that connect easily to SQL Server.  Further, they may have built up a skill set in being able to report on data stored in SQL Server and are reluctant to change.  Finally, Power BI might not be their reporting tool of choice and instead may have invested in Cognos, Tableau, or QlikView.

An Alternative Approach

The factors outlined in the previous section give a reality check to the position that SharePoint is not suitable for complex and large datasets.  A company may aspire to move away elements of data managed in SharePoint to the Dataverse, Dynamics or a non-Microsoft solution.  However, many factors can result in such a project being expensive and with a significant lead time.

Further, if we consider some of the common sectors affected are banking, pharmaceutical, oil and gas.  The reality is change in these types of organisations comes slowly.  They have many bespoke legacy systems and are naturally risk averse.  They could easily be using their solutions in SharePoint for many years before they are prepared to migrate to another solution.

An alternative approach is to get the data in SharePoint Lists and Libraries in a platform organisations have significant experience in leveraging.  SQL Server is that platform for so many organisations.  They have the skills to manage SQL Server, will have existing infrastructure they can leverage and will have a wide range of reporting options available.

This is where AxioWorks SQList provides organisations with an immediate solution to the challenges described in this post.  Data in SharePoint On Premises and Online can be normalised into a set of SQL tables.  Once the data is in SQL Server, in an easy to understand data structure, it can become very valuable.  There is no need for knowledge in SharePoint to be able to report or integrate that data.

Concluding Thoughts

SharePoint Lists and Libraries continue to be important to many organisations, across a wide range of sectors.  Many solutions built on SharePoint are business critical and organisations are frustrated they cannot easily mine that data.  Ultimately, they will move away from SharePoint but project realities may mean that this is years away.  Normalising the data held in SharePoint into SQL tables allows organisations to quickly get greater value from this data.  This pragmatic low cost approach recognises the practical realities of many organisations and allows them to leverage existing investments.

Tags: #sharepoint #sharepointonline #reporting #powerbi #ssrs #cognos #sqlserver #sqlazure

Latest articles

Our Favourite Picks from Power BI Global Summit 2023

17 Mar 2023

Power BI Summit is the biggest Power BI conference of the year which ran virtually from 6th March to 10th March. It brought speakers from the […]
[read article]

AxioWorks Newsletter February 2023: Development of SQList new release is in full swing

28 Feb 2023

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

MS List Templates bundled with Power Automate Flow

20 Feb 2023

Microsoft Lists previously known as SharePoint Lists is the go-to option for maintaining and storing data in the world of O365 and its usage has […]
[read article]

AxioWorks Newsletter January 2023: Kicking off 2023 with some great content

30 Jan 2023

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]

A pragmatic approach to obtaining value from your SharePoint data

20 Jan 2023

Organisations have been storing data in SharePoint for more than 20 years.  This data can be as simple as a List used by a small team or a complex […]
[read article]

Power BI Report Server : An On-Premises Report hosting Suite

16 Jan 2023

Power BI has become the most widely used tool for reporting due its user-friendly interface which allows business users to generate sleek and […]
[read article]

AxioWorks Newsletter December 2022: Good bye 2022, welcome 2023!

29 Dec 2022

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

PowerApps Debugging and Troubleshooting made easy with Monitor

28 Dec 2022

Troubleshooting and debugging are an integral part of building and supporting a resilient app especially when it is built using a low-code […]
[read article]

AxioWorks Newsletter November 2022: Is it almost Xmas already?

29 Nov 2022

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

Future of SharePoint in the world of Power Platform

29 Nov 2022

Microsoft SharePoint has been the driving force for enabling virtual team collaboration and the modern workspace revolution over the last three […]
[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