Picture this: a multinational firm has embraced SharePoint, storing its global reference lists in a central hub site. Around it, hundreds of regional or project-specific subsites are used to manage day-to-day data such as sales, targets, and resources etc.
A BI analyst wants to know the sales trend for the last quarter. He opens up Power BI to try and answer a simple question, “How did EMEA sales trend last quarter?”, but is immediately hit by three brick walls:
- SharePoint’s 5,000-item view limit means the firm’s half-million-row “Sales” list can’t be pulled in one go;
- each region’s data sits in a separate site collection, so SharePoint can’t natively join them;
- and having already anticipated these challenges and scheduled a pre-emptive overnight import, he finds the numbers are already stale by 9 a.m. and the gateway has chewed through an hour of refresh time.
So, what started off with a simple question quickly turned into frustration, with the BI analyst juggling CSV exports, REST calls and VLOOKUPs and no answer in sight.
Sounds familiar?
Sadly, it’s a story we know too well here at AxioWorks and one we recently shared in our webinar we co-hosted with Microsoft MVP Knut Relbe-Moe.
When SharePoint first arrived it felt like a god-send: lists, versioning, permissions and workflows all in one place. Yet anyone who has tried to do any serious analytics on that data will know the sinking feeling that follows.
The infamous 5,000-item view limit, aggressive throttling, and the simple fact that lists in different sites cannot be joined natively soon turn even the most elegant SharePoint estate into a reporting headache. Night-time exports to Power BI or Excel are the usual sticking-plaster, but they leave today’s changes hidden until tomorrow and clog up your gateway along the way.
So, what can be done about it?
Enter SQList – a quiet service with a huge impact
We created SQList, a SharePoint to SQL Server synchronisation tool. It’s a lightweight Windows service that sits in your own environment and continuously mirrors every chosen SharePoint list to SQL Server or Azure SQL.
It only copies the deltas, so the load on SharePoint is small, and every lookup column becomes a proper foreign key in the replicated tables. The moment someone edits a record in SharePoint, the change appears in SQL within seconds.
Once the data lives in SQL analysts can:
- point Power BI at it with DirectQuery for truly live dashboards,
- write any SQL they like – joins, aggregates, window functions, the lot,
- let other tools (Excel, SSRS, Python, R) connect natively without extra licences or connectors.
Too good to be true? We promise you it’s not.
Now, let’s go back to the scenario we just mentioned. Same multinational, same challenges. Only this time the story has a different outcome because the BI Analyst has already discovered and installed SQList.
Here’s how his day pans out…
He opens his global SharePoint site that stores master data (regions, countries, products) and four regional subsites that each hold hundreds of thousands of sales rows.
What would usually involve multiple REST calls, Excel exports (and plenty of coffee), he opens up SQList and points it at the master site and all subsites. Connecting Power BI to the new SQL database via DirectQuery he wants to answer the same simple question as before, “What’s the total sales in EMEA for 2024?”.
Able to run DAX or SQL against half-a-million sales rows with no SharePoint limits, he finds his answer in seconds. Even when his colleague from Canada makes a live update to the data in SharePoint, pressing refresh he can see it within seconds, with no manual import required.
Adding an AI Layer to Your SharePoint-Powered Analytics
With the data now flowing into SQL, our analyst can explore more advanced options. He’s recently deployed Copilot for Microsoft 365 (Copilot/Microsoft Copilot) and wants to test its potential. Because the SharePoint data is already structured and live in SQL, he can use Copilot to query it using natural language with no retraining or reformatting needed.
In this new scenario the BI analyst simply has to ask Copilot:
“What were our total sales in EMEA in 2024?”
…and he’ll receive a response in seconds, pulled live from the SQL Server and based on the latest SharePoint updates. This is precisely what was demonstrated during our recent webinar, which you can watch and see for yourself here (add link).
Thanks to SQList’s real-time delta syncing, any change in SharePoint – such as a newly added sale – is reflected almost instantly in Power BI and available to the AI model. No retraining, no cache clearing, no manual refreshes. Just answers.
What About Microsoft Copilot?
If you’re among the growing number of organisations embracing Microsoft Copilot, you’ll be pleased to know that it can tap into the same capabilities – with no extra effort.
Copilot can natively generate SQL queries behind the scenes using Power Platform tools like Copilot Studio and Power Automate. And if your data resides in SQL Azure, SQList fully supports this as a target.
Simply point SQList to an Azure SQL instance and let it replicate your SharePoint lists, complete with relationships and incremental updates. Once in place, Copilot can access your live, relational data and respond to natural-language prompts across Teams or even embedded chat interfaces.
Key benefits of this setup include:
- Seamless integration with Microsoft 365 environments, including Copilot
- Always up-to-date answers thanks to SQList’s fast sync
- No need to rebuild schemas or dashboards for AI compatibility
As our co-host and Microsoft MVP Knut Relbe-Moe put it:
“Everyone on the Microsoft Copilot train can also use and benefit from SQList.”
SQList in Action: Real-World Industry Scenarios
Now these aren’t just theoretical capabilities. Organisations across many industries are already using SQList to solve very practical, large-scale data challenges. Let’s take a look:
Pharmaceuticals – Cross-Trial Reporting
Pharma companies often store trial data in isolated SharePoint subsites. One customer creates a subsite for each medicine trial, making cross-trial analysis nearly impossible in native SharePoint. With SQList, all subsites are synchronised into a central SQL database. Now, their analysts can compare outcomes, monitor trends and ensure consistency – all while meeting strict security requirements by running SQList entirely within their controlled environment.
Project Management – Enterprise-Scale Oversight
A global customer managing thousands of projects uses SharePoint to organise tasks, resources, and timelines – one site per project. But when leadership needs a consolidated view across all projects, SQList comes to the rescue. All data flows into one database, enabling real-time dashboards on project progress, resource allocation, and performance, which would be unmanageable using SharePoint alone.
Oil & Gas – Document Search at Scale
In the heavily regulated oil and gas sector, documentation is vast and scattered. One customer uses SharePoint libraries to store everything from safety procedures to compliance reports across dozens of rigs. SQList synchronises this metadata – and even full documents if needed – into a searchable SQL repository. The result? Instant access to documents that used to take hours to locate.
Benefits of using SQList
There are many benefits to using SQList, here we’ve highlighted some of our favourites…
- Limitless list sizes – By exiting SharePoint’s REST pipeline and hitting SQL instead, you can sidestep the 5 000-item cap entirely.
- Cross-site joins – Hub look-ups unify reference data; SQL foreign keys join autonomous sites with no code.
- Delta-only replication – SQList’s change-tracking keeps the SharePoint tenant happy (no long-running export jobs) and delivers sub-second currency downstream.
- DirectQuery viability – When source latency is low, DirectQuery becomes a practical alternative to scheduled imports, vastly simplifying refresh architecture.
- AI readiness – Copilot, Azure OpenAI, Fabric Data Activator – all assume your data is in SQL, not CAML. SQList supplies that relational substrate automatically.
SQList Q&A – Your Questions Answered
Does SQList support multi-value lookup columns?
Yes. Single lookups become foreign keys in the SQL schema. Multi-value lookups are handled as proper many-to-many tables – fully normalised for reliable joins.
Can SQList export document libraries?
Absolutely. Document libraries are treated like standard lists, with all metadata included. You can also choose to store actual documents as BLOBs in SQL Server if needed for downstream systems or apps.
Where does SQList run?
It’s a lightweight Windows service. Install it on-premises or in a cloud VM (e.g. Azure). No SharePoint-side changes are needed. It doesn’t require internet access, making it ideal for secure, isolated environments.
Which connector do you use in Power BI?
None. You simply connect directly to SQL Server using Power BI’s built-in SQL connector. This avoids SharePoint’s 5,000-item threshold and enables DirectQuery for live reporting.
Is SQList secure?
100%. All operations happen within your own environment. No data is stored in transit, and nothing is cached or exposed externally. SQList uses standard SharePoint and SQL credentials and can be scoped to only see specific lists.
Can it use Azure AD service principals?
Yes. The latest release supports modern authentication using Azure AD app-only (service principal) credentials – ideal for environments aligning with Microsoft’s evolving security standards.
Can I try SQList for free?
Yes, a full-featured 30-day trial is available. No limitations, no commitment – just a quick way to see the value for yourself.
So, if you’re stuck exporting SharePoint data into Excel just to make sense of it or you’ve hit performance limits trying to build Power BI dashboards directly on top of SharePoint – SQList is your escape route.
SQList turns your SharePoint data into a live, SQL-ready source that plays beautifully with Power BI, AI models, and the Microsoft Power Platform. Whether you’re aiming for faster reporting, deeper insights, or AI-driven query interfaces, SQList helps your organisation unlock the full value of its SharePoint investment – without replacing or rewriting anything.
Start small. Sync one bulky list. Build one report. Even ask one question with Copilot.
You’ll never want to go back.
See for yourself. Take your free 30-day trial today!
