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