18 Link Cubes Using Partitions and XREF/XWRITE

You can use either partitions or XREF/XWRITE to analyze data across cubes.

You may have more than one cube that you use for business analysis. To share data across multiple cubes, you can connect the cubes by implementing partitions, XREF/XWRITE, or both. Two cubes connected by a partition can be thought of as a source and target pair. When using XREF/XWRITE, it is easiest to think of the local cube and the remote cube.

When partitioning or using XREF/XWRITE functions between cubes on the same Essbase instance, no reference to the host instance or login credentials are required. However, if the cubes you wish to connect are on separate Essbase instances, you will first need to create a reusable connection to link the two instances.

To use partitions or XREF/XWRITE, users must be provisioned on the remote cube as well as the local cube.

The source cube and target cube of a partition must be on the same Essbase version.

If you set up a NAT gateway, when using public and private subnets, the NAT gateway needs to be added to ingress rules in load balancer security rules for partitions to work.

Define a Reusable Connection for Partitions or XREF/XWRITE

This topic shows you how to create a reusable connection between two Essbase instances. Using the connection, you can then create partitions or use XREF/XWRITE functions.

Create connections globally for use with all applications on the system, or at the application level for use within the context of an application. Global connections require system administrator role, whereas application connections require, at minimum, Application Manager role.

  1. In the Essbase web interface, click Sources, and click Create Connection > Essbase to create a global Essbase connection. Alternatively, use the Actions menu on the target or local application and select Inspect, followed by Sources, Create Connection, and Essbase.
  2. In the Name field, enter a name for the saved connection; for example myhost01_conn.
  3. Select the Use URL checkbox, and enter the discovery URL of the remote Essbase instance. The discovery URL is available from your system administrator, and ends in /agent.
  4. Enter a user name, password, and a description. The user defined in the connection must be provisioned for the source application you intend to access on the remote instance. If you have used a global connection, the user will need to be a system administrator or be provisioned for all applications you intend to access using the connection.
  5. Click Test to verify that the connection is valid.
  6. If it is valid, click Create to save the connection.
Now you have a remote Essbase connection defined in the service. You can use this connection to define partitions between the two instances, or combine it with a location alias to enable XREF/XWRITE functionality between the two instances.

Understand Transparent and Replicated Partitions

A partition is a region of a cube that is shared with another cube. You can create a transparent or replicated partition between a target and a source cube, to share congruent cube regions between them. In the Essbase web interface, you create partition definitions in the target cube.

A transparent partition target region is virtual; it pulls data on-demand from a source cube region containing stored data. The source cube can be in the same or another application, or on another Essbase instance.

A replicated partition target region is a physical copy of stored data from the source cube region. Data stored in a replicated partition target must be synchronized when data changes in the source cube. Using the replicated partition, some users access the data in the target, while others access it in the source.

Changes made to the data in a replicated partition flow from the source to the target. If users are permitted to change the data in the target partition region, it is overwritten when the replicated partition is refreshed.

If all cubes involved in a transparent or replicated partition are hosted on the same instance of Essbase, no login credentials are needed as part of the partition setup. However, the user creating the partition must be provisioned on the target application and also the source application. Business users querying the target cube must also be provisioned on both cubes, typically with Read access.

Create a Transparent Partition

This topic shows you how to create a transparent partition. Transparent partitions allow access to data from the data source as though it were stored in the data target. The data source can be in another cube, or on another Essbase instance.

If your source cube is on a different Essbase instance, you must first define an Essbase connection as described in Define a Reusable Connection for Partitions or XREF/XWRITE.
  1. In the Essbase web interface, on the Applications page, expand the target application. In the row for the target cube, click the Actions menu, and click Inspect.
  2. Select the Partitions tab.
  3. Click Create >Transparent.
  4. On the Connection tab, in Source Info, if the source cube is on a different Essbase instance, select the name of the saved connection that you created. If the source cube is on the same Essbase instance, leave the Connection Name field empty. If you have not created any connections, you will not see a Connection Name field.
  5. Provide the source Application and Database name, and an optional Description.
  6. If the source cube is on a different Essbase instance, in the Target Info, type your User name and password.
  7. You need to define at least one area. Go to the Areas tab.
  8. Click Add Area and provide at least one source and target area definition. For example, add a source area of @DESCENDANTS(valid upper-level member specification), and add the same matching target area. If the same member doesn’t exist in both cubes, create an area mapping as described below.
  9. Click Cell Count to see how many cells are in the defined partition area and to ensure that the counts are matching.
  10. Optionally, you can map member names between the target and source cubes within a specific area, using the Areas tab, or for multiple areas, using the Mappings tab.
  11. Click Validate.
  12. If the validation succeeded, click Save and Close.

Create a Replicated Partition

This topic shows you how to create a replicated partition, which duplicates an area of a source cube into the target cube. The data source can be in another cube, or on another Essbase instance.

If your source cube is on a different Essbase instance, you must first define an Essbase connection as described in Define a Reusable Connection for Partitions or XREF/XWRITE.
  1. In the Essbase web interface, on the Applications page, expand the target application. In the row for the target cube, click the Actions menu, and click Inspect.
  2. Select the Partitions tab.
  3. Click Create >Replicated.
  4. On the Connection tab, in Source Info, if the source cube is on a different Essbase instance, select the name of the saved connection that you created. If the source cube is on the same Essbase instance, leave the Connection Name field empty. If you have not created any connections, you will not see a Connection Name field.
  5. Provide the source Application and Database name, and an optional Description.
  6. If the source cube is on a different Essbase instance, in the Target Info, type a provisioned User name and password.
  7. You need to define at least one area. Go to the Areas tab.
  8. Click Add Area and provide at least one source and target area definition. For example, add a source area of @DESCENDANTS(valid upper-level member specification), and add the same matching target area. If the same member doesn’t exist in both cubes, create an area mapping as described below.
  9. Click Cell count to see how many cells are in the defined partition area and to ensure that the counts are matching.
  10. Optionally, you can map member names between the target and source cubes within a specific area, using the Areas tab, or for multiple areas, using the Mappings tab.
  11. Click Validate.
  12. If the validation succeeded, click Save and Close.

Refresh a Replicated Partition

If you have at least Database Manager permission on a replicated partition target application, you can replicate the data from the source.

  1. In the Essbase web interface, on the Applications page, expand the target application containing the replicated partition definition.
  2. In the row for the target cube, click the Actions menu, and click Inspect.
  3. Select the Partitions tab.
  4. From the Actions menu on the replicated partition, select Replicate Data from Source.
  5. Select Update change cells only to update the target only with source data that has been updated since the last update, or select Update all cells to update the target with all source data.

Understand XREF/XWRITE

XREF is a calculation function you use to reference data in another cube, and XWRITE is a calculation function you use to write back data to another cube.

XREF and XWRITE are easiest to understand from the context of the cube containing the XREF or XWRITE formula, called the local cube. The second cube is the remote cube.

To implement XREF, you define a formula in the local cube that pulls values from a remote cube. The member containing the XREF formula can either be stored or dynamically calculated.

To implement XWRITE, you define a formula in the local cube that pushes (writes) values into a remote cube. The remote cube data intersection must be stored, since XWRITE writes values into the remote cube.

When the local and remote cube are on the same Essbase instance, no connection information is needed to implement XREF or XWRITE. However, users of the local cube must also be provisioned on the remote cube. To implement XREF or XWRITE for cubes on the same instance, the application and database name for the source cube are required in the function syntax:

@XREF(appName, dbName [, mbrList])
@XWRITE (expression, appName, dbName [, mbrList])

If the local and remote cubes are on different Essbase instances, a location alias containing connection information must be defined:

@XREF (locationAlias [, mbrList])
@XWRITE (expression, locationAlias [, mbrList])

Create a Location Alias Based on a Defined Connection

This topic shows you how to create a location alias, which you can use when your XREF/XWRITE formulas need to reference data from a cube on a remote cloud instance. You do not provide a user name and password when you create a location alias. You use a saved connection.

This topic assumes you have created a connection as described in Define a Reusable Connection for Partitions or XREF/XWRITE.
  1. In the Essbase web interface, on the Applications page, expand the target application. In the row for the local cube, click the Actions menu, and click Inspect.
  2. Click the Location Aliases tab.
  3. Click Image of create in Location Aliases.
  4. In the Location alias name field, enter a name.
  5. In the Essbase connection field, select a saved connection to the Essbase instance hosting the remote cube.
  6. Select the remote application and database and click Save.
Now you have created a location alias. To use it for read operations from a remote cube to the target, use the @XREF function in a member formula or calculation script on the local cube. To use it to write from the local to the remote cube, use @XWRITE on the local cube.
@XREF (locationAlias [, mbrList])
@XWRITE (expression, locationAlias [, mbrList])