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 September 2023: An exhilarating month.

30 Sep 2023

Hello Reader, September has been a bustling and exhilarating month for us at AxioWorks, filled with advancements, collaborations, and a whole lot of […]
[read article]

The Collaborative Pathway: Bridging Minds in the AI Ecosystem

19 Sep 2023

In recent days, I found myself immersed in an enlightening conversation with a friend, a fellow entrepreneur steering his ship in the vast sea of […]
[read article]

AxioWorks Newsletter August 2023: A month of milestones and innovation.

28 Aug 2023

Hello Reader, September seems to be a month of unexpected milestones and continued innovations at AxioWorks. As the summer wanes and the autumn […]
[read article]

Use Graph API in Power Automate to read large Excel files from SharePoint

28 Aug 2023

Power Automate, a part of Microsoft’s suite of tools, is increasingly becoming a preferred choice for automating workflows and business […]
[read article]

From Punch Cards to Python: Still Hitting the Keys in my 50s

16 Aug 2023

I spent the entire weekend writing code, not because of deadlines to meet or million-pound dreams, but for the kick that I still get from doing it. […]
[read article]

AxioWorks Newsletter July 2023: Unveiling AxioWorks’s latest successes and exciting innovations

27 Jul 2023

Hello Reader, We hope this monthly update finds you in good spirits. As always, we are eager to share the latest developments and exciting ventures […]
[read article]

AI and Data: The Symbiosis of Interaction and Accuracy

13 Jul 2023

Artificial Intelligence (AI) has rapidly emerged as a revolutionary technology that fundamentally alters the nature of human-machine interactions. […]
[read article]

AxioWorks Newsletter June 2023: SQList v8 Release, Webinars, and New Product Development

30 Jun 2023

Hello Reader, Greetings to all our valued customers and subscribers! We are thrilled to share some exciting updates and developments with you in this […]
[read article]

Safeguarding Secrets with Secure Strings and Environment Variables in the Power Platform

26 Jun 2023

In today’s interconnected digital landscape, safeguarding sensitive information and maintaining robust security measures is of paramount […]
[read article]

Case study: Streamlining Medical Trial Data Analysis in the Pharmaceutical industry

15 Jun 2023

In the ever-evolving landscape of the pharmaceutical industry, conducting medical trials and analysing the resulting data are critical for the […]
[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