By TopDown Team
November 30th, 2015
I came across a requirement for an XWRITE calc script that I have not encountered before and wanted to document the solution in case someone else runs into this.
With an XWRITE script, the calc is executed on the source database, and transfers data to the target. In this scenario, I needed to transfer data from Dynamic Calc Parents in both the Account and Period dimensions, to Level0 stored members in the target.
In the Account dimension, I needed to transfer from summary accounts (pictured below) in the source to natural GL accounts in the target.
In the Period dimension, the source database goes down to the weekly level, whereas the target cube only goes down to months.
My calc script looked like this, and even though the script would validate, it would not execute complaining about the fix on virtual members (Jan:Dec)
Fix ( Budget, Working, &BudgetYear, Jan:Dec, US_Region )
@XWRITE(Total_Beverage_Sales, _TgtApp_, "455800");
@XWRITE(Total_Dining_Supplies_Sales, _TgtApp_, "434855");
@XWRITE(Total_Grocery_Sales, _TgtApp_, "475600");
After a few hours of testing many different approaches, the most elegant solution I found was to tag the origination members in the source as Dynamic Calc and Store. In the cube I was working with, Account and Period are the only dense dimensions. Although the logical block size did not change, the stored block size did increase a little bit, so make sure to take that into consideration.
At first, I had tagged only the Periods (Months) as Dynamic Calc and Store and although the calc would then execute, no data was transferred to the target. After setting the Accounts as well, data was transferred as intended.
The source Accounts are pictured below and have been changed to Dynamic Calc and Store.
The source Periods are pictured below and have also been changed to Dynamic Calc and Store.
No change to the calc script was required with this approach.