Part of: Scale, large lists and multi-site consolidation

Combine SharePoint lists from multiple sites into one table

Twelve project sites, one RAID log template, and a portfolio report built by hand every Monday. How a per-site prefix or one merged table puts every site's list in SQL.

Relational databasesScaleDesktopCommand line

Our example is an operations lead at an engineering firm who looks after twelve project sites. Every one was created from the same site template, so every one has the same RAID log, a SharePoint list of risks, actions, issues and decisions with an Entry Type, an Owner, a Status, an Impact rating and a Due Date. On Monday morning the programme board wants the open high-impact risks across all twelve projects on one page. So on Friday afternoon she opens twelve sites, exports twelve lists to Excel, pastes them into one workbook and fixes the columns that do not line up. By the time the board meets, two project managers have already closed risks that are still on her sheet.

She has tried the obvious shortcuts. A Power BI report with twelve SharePoint list queries appended together worked for a month, until one project manager renamed Impact to Impact Rating and the refresh stopped on a Changed Type step looking for a column called Impact. Search-driven rollups such as the Highlighted content web part can show items from several sites, but they show SharePoint’s item cards, and the board wants a table it can sort and count.

What we do instead is replicate all twelve copies of the list into one database, either as twelve tables with a prefix each or as one merged table. The portfolio report then becomes a SQL query.

How do you roll up a SharePoint list across sites?

In SharePoint, a roll-up usually means a web part that shows items from several sites on one page. The Highlighted content web part and the News web part on a hub work that way. They are good for a landing page that people browse. They do not give you rows you can filter, count and join, which is what a board pack needs.

A reporting roll-up needs the list data itself in one place. SPSync does it with one replication that names every site and writes into one database, in one of two shapes:

SPSync does not generate a cross-site view over separate per-site tables. If you want a single name to query without writing a view, the merged table is the shape that gives it to you. The rest of this post sets up both and shows where each one fits.

Combining the same SharePoint list from multiple sites

SPSync reads lists through Microsoft Graph and keeps a copy current in a database you run. It reads from SharePoint and never writes to it. A single replication can cover many sites, and each site in it is a scope with its own list selection. For the RAID log, all twelve scopes ask for the same list by name.

Inside the database, each list becomes a table with the shape you would design by hand. Single-value columns are ordinary columns. A person column such as Owner becomes owner_user_id, which joins to one shared sp_users table holding each person’s display name and email. A multi-choice column becomes a child table with one row per value. Every row also carries a set of metadata columns, among them sp_item_id, sp_site_id, sp_modified and sp_deleted_at. A deleted item keeps its row with sp_deleted_at set, so last quarter’s totals still add up.

The column names come from SharePoint’s internal names, decoded and lowercased. A column someone typed as “Due Date” has the internal name Due_x0020_Date, and SPSync writes it as due_date. Renaming the column’s display name later in SharePoint does not rename the database column, which matters more than it sounds when twelve project managers each feel free to tidy their own list.

Setting up multi-site replication with a batch file

For twelve sites you do not want twelve setup sessions. From the command line, a batch file names the destination once and lists the sites under one replication:

raid.yaml
client_id: <entra-app-client-id>
tenant: common
destination:
  kind: postgres
  connection_string_env: PORTFOLIO_DB_URL
  dataset_name: portfolio
replications:
  - output: configs/raid.yaml
    pipeline_name: portfolio_raid
    prefix: proj
    naming: merge_same_name
    create_reporting_views: true
    sites:
      - site_url: https://contoso.sharepoint.com/sites/harbour-bridge
        lists_by_name: ["RAID Log"]
      - site_url: https://contoso.sharepoint.com/sites/northgate-depot
        lists_by_name: ["RAID Log"]
      # ...ten more sites, one entry each
Terminal window
spsync batch create raid.yaml
spsync run configs/raid.yaml

spsync batch create writes one replication config per entry, and spsync run does the first full load, then reads only changes on every run after that. The connection string stays in the PORTFOLIO_DB_URL environment variable, so the YAML can go into source control and be reviewed like anything else.

If you would rather pick the sites on screen, the app’s bulk selection screen lets you filter and tick them, choose between the two naming options, and export the same batch file with its Export batch (YAML) button. When a thirteenth project starts next quarter, it is one more entry under sites.

One setting is worth adding for a UK firm. Due Date is a date-only column, and SharePoint stores it as local midnight, which Graph hands over as a UTC instant. Set site_time_zone: Europe/London in the replication config and the dates come through on the right day all year.

Choosing between a per-site prefix and one merged table

The naming setting decides how twelve lists called RAID Log share one database. The two options write to different table names and shape every query you write afterwards, so make the choice before the first run.

With per_site_prefix, the default, each site keeps its own prefix and its own table. When a replication names more than one site, the prefix is built from yours and the site’s short name, so you get one table per project, along the lines of proj_harbour_raid_log and proj_northgate_raid_log. Each table is the list exactly as that site has it. A project that closes can be dropped from the replication without touching anyone else’s rows. The cost arrives in the report, where a portfolio view is a UNION ALL across twelve tables, and every time one site’s columns change you edit the view to keep the columns lined up.

With merge_same_name, every scope uses the replication’s own prefix, so all twelve copies go into one table, proj_raid_log. A SharePoint item id is only unique within its own list, so item 14 exists on every site. SPSync adds sp_site_id to the table’s primary key as soon as it sees two sites sharing a table, and it does the same for the child tables behind multi-value columns. You do not configure any of that. The table holds one row per item per site, and the report reads one name.

For a portfolio rollup across sites built from one template, we would choose the merged table. Separate prefixes earn their keep when the sites only look alike, for instance twelve client sites whose lists have drifted apart over the years, or when each project’s data has to stay in its own table for access reasons.

The sp_site_id column and a cross-site query

sp_site_id is Graph’s own site id. Graph builds it from the host name, the site collection id and the web id, separated by commas, so SPSync stores it as text rather than as a native UUID or UNIQUEIDENTIFIER. Nobody wants to read that in a board pack, so keep a small table of your own that names each project:

create table portfolio.project_sites (
  sp_site_id   text primary key,
  project_name text not null,
  region       text not null
);

spsync discover sites prints the id of every site it can see, so filling in twelve rows takes a few minutes. With that in place, the Monday report is one query against the merged table:

select p.project_name,
       r.title,
       r.status,
       r.due_date,
       u.display_name as owner
from portfolio.proj_raid_log r
join portfolio.project_sites p on p.sp_site_id = r.sp_site_id
left join portfolio.sp_users u on u.id = r.owner_user_id
where r.sp_deleted_at is null
  and r.entry_type = 'Risk'
  and r.impact = 'High'
  and r.status <> 'Closed'
order by r.due_date, p.project_name;

Counting open risks by region is a group by p.region on the same join. Because create_reporting_views is on, SPSync also creates v_proj_raid_log, a view that leaves out soft-deleted rows, and Power BI or Excel can read that view directly. The board’s spreadsheet becomes a refresh that reads one table instead of paging through twelve lists.

On SQL Server the query runs as written, and the table and column names are the same as on Postgres.

Can you report across a hub site’s associated sites?

Many portfolios are already organised as a hub. The programme office owns a hub site and each project site is associated with it. Microsoft’s hub site planning guidance describes what a hub rolls up: the News, Sites, Events and Highlighted content web parts can show activity from all associated sites or from selected ones, security trimmed for the reader. A hub does not merge the lists on its sites. The twelve RAID logs stay twelve lists, and the hub home page can show recent items from them.

For reporting, treat the hub as the list of sites to replicate. SPSync does not read hub associations, so it will not find a hub’s sites for you. Each associated site goes into the replication as its own entry under sites, as in the batch file above, and the hub’s Sites web part is a quick way to see which ones to add. When a new project site joins the hub, add its entry and the next run brings its rows in.

The same guidance notes that associating a site with a hub does not change its permissions, so access is still granted site by site. With delegated sign-in, the account that signs in needs read access to every site in the replication. With an app registration limited by Sites.Selected, each site needs its own grant. After the first run, count the distinct sp_site_id values in the table and check the number against the hub’s site list. If you run more than one hub, add a hub column to project_sites and group by it.

How do you build one Power BI report on SharePoint lists from multiple sites?

Power BI’s SharePoint Online list connector takes one site URL per connection. Twelve sites mean twelve queries and an Append step, which is the model from the opening story that failed when one column was renamed, and every refresh reads all twelve lists from SharePoint again.

With the replica, Power BI connects to the database. For the merged table the model needs one query: point the PostgreSQL or SQL Server connector at v_proj_raid_log, and bring in project_sites as a second table related on sp_site_id so the report can slice by project and region. The view already carries owner_display_name from sp_users, so the model needs no join for people either. The refresh reads the database, so it spends none of the tenant’s SharePoint budget, however many sites feed the table.

With per-site prefixes, write the UNION ALL view yourself in the database and point Power BI at it, so the model still sees one table:

create view portfolio.raid_all_sites as
select sp_site_id, title, entry_type, status, impact, due_date, owner_display_name
from portfolio.v_proj_harbour_raid_log
union all
select sp_site_id, title, entry_type, status, impact, due_date, owner_display_name
from portfolio.v_proj_northgate_raid_log;
-- ...one select per site

Naming the columns instead of selecting * keeps the view valid when one site adds a column the others lack. When a list changes shape, the view is the one place to edit, and the Power BI model stays as it is.

SharePoint throttling across many sites

Twelve sites can feel like twelve times the load on SharePoint. The limit SharePoint Online applies is per application per tenant, measured in resource units, so all twelve sites draw on one budget whichever tool does the reading. Microsoft’s page “Avoid getting throttled or blocked in SharePoint Online” sets the smallest tier at 1,250 resource units a minute for one app. Twelve Power BI list queries refresh together, each paging its whole list, and nothing paces them against that budget.

SPSync works within that budget. It reads the resource-unit cost on each response, keeps a per-tenant count over the rolling minute and day, and slows down at 80 percent of the budget. When SharePoint does answer with a 429 or a 503, it waits exactly as long as the Retry-After header asks. All the lists in one run share one per-tenant limit on concurrent requests, four by default, so putting twelve sites in a single replication keeps them in one queue.

After the first load, a round is cheap. A delta request with a token costs 1 resource unit, where a page of a full list read costs 2 resource units, so a quiet round across twelve RAID logs costs about 12 resource units. The interval between rounds starts from 5 minutes with delegated sign-in and 1 minute with app-only permissions, never goes below 30 seconds, and stretches when a small tenant has many lists. spsync doctor configs/raid.yaml shows the interval in effect and why. The post on Graph throttling and AI pipelines goes through the arithmetic in more detail.

What happens when one site adds or retypes a column?

Project managers change their own lists, and with twelve of them it happens every few weeks. SPSync handles the common changes without anyone touching the replication.

Say the Harbour Bridge project manager adds a Cost Impact column. The next run adds a matching cost_impact column to the destination. In the merged table, the rows from the other eleven sites hold null in it, as do the rows from that site written before the change. A column removed in SharePoint keeps its database column, so a report that used it goes on running against the history.

A retype is the harder case. Harbour Bridge created Cost Impact as a single line of text, and a month later the project manager changes it to a Number because people have started typing figures into it. On PostgreSQL, SQL Server, MySQL, ClickHouse, Oracle, SQLite and DuckDB, SPSync creates cost_impact_v2 with the new type and leaves cost_impact holding what it already had. The run logs a warning naming both columns, so you hear about it before the board does. A view that picks the numeric column where it is set and casts the old text where it is not keeps the report on one column name.

Not every destination forks a retyped column this way. BigQuery, Databricks and Delta Lake do not create a _v2 column today, so for a portfolio rollup with lists that change, a relational database is the safer home. The schema reference lists the exact behaviour per destination.

Every replication also runs a reconcile, weekly by default, that compares the whole list with the replica. Teams run one as a QA check before a quarterly board pack, or after an outage, with spsync reconcile configs/raid.yaml.

When a simpler rollup is enough

Replication is not the only answer, and for some teams it is more than they need. If you have three sites, a few hundred items in each and columns that stay the same, a Power Query append in Power BI or Excel is fine and costs nothing extra. If people only need to see recent items from several sites on a page, the Highlighted content web part does that inside SharePoint with no database at all. A Power Automate flow that copies new items into one central list works for low volumes, though you then own the flow, its error handling and the edits that happen after an item is copied.

A database starts to pay off once the manual export takes an afternoon and the lists change on their own schedules faster than anyone can keep a workbook in line. The multi-site consolidation use case shows the same pattern with forty project sites and a risk register, and the 5,000-item threshold page covers what happens when one of those lists grows past the list view threshold on its own.

Compared with

Book a call

Book a call to talk through your use case, see further walkthroughs or demos, and set up a trial in your own environment.