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

AxioWorks Newsletter February 2024: Partnerships, Upcoming SQList Update, and Summit Invitation

27 Feb 2024

Hello Reader, This February has been pivotal for AxioWorks as we’ve focused on laying strong foundations for the year. Here are the key […]
[read article]

How AxioWorks SQList Aligns With the Priorities of Software Buying Decision Makers

26 Feb 2024

As outlined in the “2024 Global Software Buying Trends” report by Gartner Digital Markets, the criteria that predominantly influence […]
[read article]

Maximising Power BI Reporting from SharePoint: The Strategic Advantage of AxioWorks SQList over Native Connectors

15 Feb 2024

In today’s data-driven environment, making informed decisions rapidly can offer a significant competitive edge. This is where Power BI comes […]
[read article]

AxioWorks Newsletter January 2024: A Promising Start to the New Year!

29 Jan 2024

Hello Reader, We hope this newsletter finds you well and thriving. At AxioWorks, we are embracing the new year with great enthusiasm and are […]
[read article]

Exciting New Partnership and Innovative SharePoint Tools from AxioWorks and Lightning Tools

17 Jan 2024

AxioWorks is excited to announce a strategic partnership with Lightning Tools, a leader in creating innovative SharePoint components. This […]
[read article]

AxioWorks Newsletter November 2023: ESPC23, new partnership, and SQList update!

30 Nov 2023

Hello Reader, Welcome to this month’s edition of the AxioWorks newsletter! We’re thrilled to bring you updates on our recent […]
[read article]

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]

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

*