The Situation:

• Planning 11.1.2.1
• Client has two Plan types (Finance & FTE)
• Detail employee data is held in the FTE cube and summary employee data is held in the Finance cube

Requirement:

Move data from FTE cube to Finance cube at the summary level as efficiently as possible.

Solution:

The @XWRITE function does just what it implies.  It pushes the data from the source cube to the target cube rather than pulling the data from the source cube to the target cube.  This pushing vs. pulling is beneficial for efficiency and block creation.  Without it, I have had to utilize the CreatNONMissingBLK setting which we all know to be extremely resource intensive and can significantly increase run times. In addition, @XREF will go through all the possible blocks to pull the data, while @XWRITE will push the data only from the blocks you have defined.  I have found that this can also shorten run times.

The setup:

Finance Cube Dimensions: (TARGET)
TARGET Cube Alias: “_RevCube_”

Dimension          Member
Composite              No_Composite
Account                  50250000
Entity                      1010
Year                         FY14
Period                     Jan
Scenario                 Budget
Version                   Draft1
Currency                USD

FTE Cube Dimensions: (SOURCE)
SOURCE Cube Alias: “_BsCube_”

 Dimension          Member
MarketSector          MarketSector_NA
Employee                All_Employees
Account                   Salary_Expense
Entity                       1010
Year                          FY14
Period                      Jan
Scenario                  Budget
Version                    Draft2
Currency                 USD

Using @XREF:

The @XREF function pulls the data to the TARGET cube so your business rule / calc script will be based in the TARGET cube and the FIX statement will be based on the TARGET cube dimensions.

Set your FIX statement to include all of the common dimensions and select the members you want to use for any unique dimensions in the TARGET cube or members which will differ between cubes.  In this example the Composite dimension is unique to the TARGET cube and the version dimension is common between the two cubes but we are moving the data from Draft2 in the SORUCE cube to Draft1 in the TARGET cube.

@XREF Syntax:

FIX (Common Dimensions, Unique TARGET Dimensions, Common Dimensions but Different Members selected for TARGET cube)

Open TARGET Account block(

@XREF(“SOURCE Cube Alias”, SOURCE Account member, “Members from unique SOURCE   cube Dimensions”);

Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud

Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.

Get the Guide

)

ENDFIX

@XREF Example:

FIX (No_Composite,1010,FY14,Jan,Budget,Draft1,USD)

“50250000”(

@XREF(“_BsCube_”, “Salary_Expense”, “All_Employees”,”MarketSector_NA”,”Draft2”);

)

END FIX

Using @XWRITE:

The @XWRITE function pushes the data to the TARGET cube so your business rule / calc script will be based in the SOURCE cube and the FIX statement will be based on the SOURCE cube dimensions.

Set your FIX statement to include all of the common dimensions and select the members you want to use for any unique dimensions in the SOURCE cube or members which will differ between cubes.  In this example the MarketSector dimension is unique to the SOURCE cube and the version dimension is common between the two cubes but we are moving the data from Draft2 in the SORUCE cube to Draft1 in the TARGET cube.

@XWRITE Syntax

FIX (Common Dimensions, Unique SOURCE Dimensions, Common Dimensions but Different Members selected for SOURCE cube)

Open SOURCE Account block(

@XWRITE(SOURCE Account member, TARGET Cube alias, TARGET Account member, Unique TARGET Dimension members);

)

ENDFIX

@XWRITE Example:

FIX (1010,FY14,Jan,Budget,USD,MarketSector_NA,Draft2)

“Salary_Expense”(

@XWRITE(“IBD”,”_RevCube_”, “50250000”, “No_Composite”,”Draft1”);

)

ENDFIX