Corporate Rollup

In the corporate rollup model, multiple primary fragments maintained at remote sites are consolidated into a single aggregate replicate table at a central site.

The corporate rollup model has distributed primary fragments and a single, centralized consolidated replicate table. The table at each primary site contains only the data that is primary at that site. No data is replicated to these sites. The corporate rollup table consists of rolled-up data from the primary sites.

The corporate rollup model requires distinct replication definitions at each of the primary sites. The site where the data is consolidated has a subscription for the replication definition at each primary site.

Replication Agents are required at the primary sites but not at the central site, since data will not be replicated from that site.

These tasks must be performed to create a corporate rollup from distributed primary fragments:

Create the table in each primary database and in the database at the central site. The tables should have the same structure and the same name.
Create indexes and grant appropriate permissions on the tables.
In each remote database, allow for replication on the table with the sp_setreptable system procedure.
Create a replication definition for the table at each remote site.
At the headquarters site, where the data is to be consolidated, create subscriptions for the replication definitions at the remote sites.

Corporate Rollup Model with Distributed Primary Fragments
Figure 3-5 shows an example of the corporate rollup model with distributed primary fragments. The headquarters site consolidates data changes from each of the three remote sites.

Figure 3-5 shows an example of the corporate rollup model with distributed primary fragments. The headquarters site consolidates data changes from each of the three remote sites.

This figure illustrates a salesdetail table with a corporate rollup at a headquarters site. The headquarters site receives data from the remote sites via three subscriptions.

Table with Multiple Primary Fragments
Figure 3-6 illustrates a sales detail table with a corporate rollup at a headquarters site. The headquarters site receives data from the remote sites via three subscriptions, one from each remote site.

Figure 3-6 illustrates a sales detail table with a corporate rollup at a headquarters site. The headquarters site receives data from the remote sites via three subscriptions, one from each remote site.