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

My Impressions of Microsoft Flow: A Developer’s Perspective

13 May 2024

Recently, I had the opportunity to explore Microsoft Flow, a tool that promises to facilitate the rapid development of software applications through […]
[read article]

Power Pages Unleashed: Creating Dynamic Websites with Microsoft Copilot

28 Apr 2024

In our latest tutorial, we delve into the capabilities of Microsoft Copilot, with a focused exploration of Power Pages. Our aim? To showcase how […]
[read article]

Harnessing the Power of Microsoft CoPilot in Power Automate Flows: A Comprehensive Tutorial

25 Mar 2024

In the ever-evolving landscape of technology and business automation, Microsoft has introduced a game-changer that is set to revolutionize how we […]
[read article]

Embracing AI: Transforming Jobs and Unlocking New Opportunities (Claude 3 Opus version)

18 Mar 2024

The rapid advancements in Artificial Intelligence (AI) have sparked both excitement and fear among workers worldwide. Many people are concerned that […]
[read article]

The Evolution of Work in the Age of AI: A New Era of Collaboration (ChatGPT 4 version)

18 Mar 2024

In the annals of human progress, pivotal inventions such as the steam engine, the tractor, and the internet have revolutionarily altered our way of […]
[read article]

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]

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