Create and Manage a Remote Partition (Analysis Services)

Article
06/13/2017

When partitioning a measure group, you can configure a secondary database on a remote Analysis Services instance as partition storage.

Remote partitions for a cube (called the master database) are stored in a dedicated Analysis Services database on the remote instance of Analysis Services (called the secondary database).

A dedicated secondary database can store remote partitions for one and only one master database, but the master database can use multiple secondary databases, as long as all the secondary databases are on the same remote instance of Analysis Services. Dimensions in a database dedicated to remote partitions are created as linked dimensions.

Prerequisites

Before you create a remote partition, the following conditions must be met:

You must have a second Analysis Services instance and dedicated database to store the partitions. The secondary database is single-purpose; it provides storage of remote partitions for a master database.
Both server instances must be the same version. Both databases should be the same functional level.
Both instances must be configured for TCP connections. Analysis Services does not support creation of remote partitions by using the HTTP protocol.
Firewall settings on both computers must be set to accept outside connections. For information about setting the firewall, see Configure the Windows Firewall to Allow Analysis Services Access.
The service account for the instance running the master database must have administrative access to the remote instance of Analysis Services. If the service account changes, you must update permissions on both the server and database.
You must be an Analysis Services administrator on both computers.
You must ensure your disaster recovery plan accommodates backup and restore of the remote partitions. Using remote partitions can complicate backup and restore operations. Be sure to test your plan thoroughly to be sure you can restore the necessary data.

Configure remote partitions

Two separate computers that are running an instance of SQL Server Analysis Services are each required to create a remote partition arrangement that designates one computer as the master server and the other computer as the subordinate server.

The following procedure assumes that you have two server instances, with a cube database deployed on the master server. For the purposes of this procedure, the cube database is referred to as db-master. The storage database containing remote partitions is referred to as db-storage.

You will use both SQL Server Management Studio and SQL Server Data Tools (SSDT) to complete this procedure.

Note

Remote partitions can be merged only with other remote partitions. If you are using a combination of local and remote partitions, an alternative approach is to create new partitions that include the combined data, deleting the partitions you no longer use.

Specify valid server names for cube deployment (in SSDT)

On the master server: In Solution Explorer, right-click the solution name and select Properties. In the Properties dialog box, click Configuration Properties, then click Deployment, and then click Server and set the master server's name.
On the subordinate server: In Solution Explorer, right-click the solution name and select Properties. In the Properties dialog box, click Configuration Properties, then click Deployment, and then click Server and set the subordinate server's name.

Create and deploy a secondary database (in SSDT)

On the subordinate server: Create a new Analysis Services project for the storage database.
On the subordinate server: In Solution Explorer, create a new data source pointing to the cube database, db-master. Use the provider Native OLE DB\Microsoft OLE DB Provider for Analysis Services 11.0.
On the subordinate server: Deploy the solution.

Enable features (in SSMS)

On the subordinate server: In SQL Server Management Studio, right-click your connected Analysis Services instance in Object Explorer and select Properties. Set both Feature\LinkToOtherInstanceEnabled and Feature\LinkFromOtherInstanceEnabled to True.
On the subordinate server: Restart the server by right-clicking the server name in Object Explorer and selecting Restart.
On the master server: In SQL Server Management Studio, right-click your connected Analysis Services instance in Object Explorer and select Properties. Set both Feature\LinkToOtherInstanceEnabled and Feature\LinkFromOtherInstanceEnabled to True.
On the master server: To restart the server, right-click the server name in Object Explorer and select Restart.

Set the MasterDataSourceID database property on the remote server (in SSMS)

On the subordinate server: Right-click the storage database, db-storage, point to Script Database as | ALTER To | New Query Editor Window.

Add MasterDataSourceID to the XMLA, and then specify the cube database, db-master, ID as the value. The XMLA should look similar to the following.

<Alter ObjectExpansion="ExpandFull" xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">  
<Object>  
   <DatabaseID>DB-Storage</DatabaseID>  
</Object>  
<ObjectDefinition>  
   <Database xmlns:xsd="http://www.w3.org/2001/XMLSchema" 400"   
      <ID>DB-Storage</ID>  
      <Name>DB-StorageB</Name>  
      <ddl200:CompatibilityLevel>1100</ddl200:CompatibilityLevel>  
      <Language>1033</Language>  
      <Collation>Latin1_General_CI_AS</Collation>  
      <DataSourceImpersonationInfo>  
<ImpersonationMode>ImpersonateAccount</ImpersonationMode>  
         <Account>*********</Account>  
      </DataSourceImpersonationInfo>  
      <MasterDataSourceID>DB-Master</MasterDataSourceID>  
   </Database>  
</ObjectDefinition>  
</Alter>

Press F5 to execute the script.

Set up the remote partition (in SSDT)

On the master server: Open the cube in Cube Designer and click Partitions tab. Expand the measure group. Click New Partition if the measure group is already configured for multiple partitions, or click the browse (. . ) button in the Source column to edit the existing partition.
In the Partition Wizard, in Specify Source Information, select the original Data Source View and fact table.
If using a query binding, provide a WHERE clause that segments the data for the new partition you are creating.
In Processing and Storage Locations, under Processing Location, choose Remote Analysis Services data source and click New to create a new data source that points to your subordinate database, db-storage.

Note

If you get an error indicating the data source does not exist in the collection, you must open the project of the storage database, db-storage, and create a data source that points to the master database, db-master.
On the master server: Right-click the cube name in Solution Explorer, select Process and fully process the cube.

Administering remote partitions

Analysis Services supports both parallel and sequential processing of remote partitions. The master database, where the partitions were defined, coordinates the transactions among all the instances that participate in processing the partitions of a cube. Processing reports are then sent to all instances that processed a partition.

A cube that contains remote partitions can be administered together with its partitions on a single instance of Analysis Services. However, the metadata for the remote partition can be viewed and updated only on the instance of Analysis Services where the partition and its parent cube were defined. The remote partition cannot be viewed or updated on the remote instance of Analysis Services.