How to Easily Copy Data Between Cubes in Planning
There are several ways to move data between cubes in planning. Data Synchronization (Data Sync) is a new tool in Planning that allows the user to move data between BSO and/or ASO cubes.
What I like about Data Sync is that it’s quick to set up and you can easily select the intersections to copy over. Additionally, Data Sync is free to use in On Premise, as well as PBCS environments and it can be scheduled as well.
One useful application of Data Sync would be to copy data from a BSO cube to a reporting cube for use with Financial Reporting. This is common practice when constructing environments that rely heavily on reporting. Data Synch is also useful for maintaining a historical cube. Unlike DATACOPY and XWRITE, Data Sync does not require access to Calculation Manager.
In this article, I will cover how to set up and utilize Data Sync and provide an easy and quick way to copy one data between cubes.
First, access Data Management, go to “Setup” and navigate to “Source System.” Set up a Source System if there isn’t one already.
Under “Target Application”, select which application you want the data to be moved to. (In this example, I am moving the data to an ASO reporting cube.)
Go “Import Format” under “Integration Setup” and click “Add” to add a new Import Format that has “Source Type” as EPM (application) and “Target Type” as EPM (application). I named the Import Format IF_15.
In “Location”, under “Integration Setup”, click “Add” to add a new location or choose an existing location. I named this location “Test_Location15”:
Next, navigate to “Integration Setup” and click on “Period Mapping".
In this case, “Global Mapping” has already been populated with multiple timeframes that can be utilized in “Application Mapping” and “Source Mapping”.
In the “Application Mapping” tab, select the target application and add period keys for the months to which you will be transferring data.
If your application does not already have Global Mapping populated, then you will have to define the Global Mapping in order to enter values in Application Mapping.
Go to “Category Mapping” under “Integration Setup”. “Global Mapping” contains Scenarios that have been mapped and can be used in “Application Mapping”.
In the “Workflow” tab, set the location to the location that you previously created (in this case, I used Location_15).
In the “Workflow” tab, create a new data load rule in the “Data Load Rule” section by clicking “Add”.
The rule has been named “DLRule_DataSync”.
I used “Like” mapping (1 to 1 mapping) since the data members in both cubes are the same.
Once the rule has been created, set the filters for each dimension in “Source Options”. The filters allow you to select different slices of data from each dimension. This filter feature, in my opinion, is the primary advantage of Data Sync. With all of the dimension names and filter conditions laid out before you, it is easy to see which members you are selecting for the sync. You can use functions like @IDescendants to select wide ranges of members.
I’ve found that all of the dimensions must have filters or the rule will result in an error when it is executed.
“Execute” the rule as you would any other FDM rule. Once the rule has successfully run, verify that the data has been pushed to the new cube via a Smart View retrieve or in a form.
Data Synchronization is a quick method for moving chunks of data between cubes. Members in the filter conditions can be changed easily, making it easy to edit the rule to your liking. Data can be synced from BSO to ASO and vice versa and it is a useful alternative to methods such as DATACOPY, XWRITE, or Smart Push.
By Jean-Luc Mosley of Key Performance Ideas.
Posted on Fri, April 22, 2016
by Anne Stein