calc script on a outline member in BSO outline to ASO source partition?February 20 2013 at 4:43 PM
No score for this post
from IP address 22.214.171.124
Essbase 126.96.36.199. Is it possible to create a functioning outline member formula on a BSO outline that can source data partly from an ASO cube across a transparent partition? The reason being is our plan data is stored at upper level members (to be stored in BSO target) while the bulk of the actuals are stored at level 0 (to be stored in ASO source). The calcscript would be used to calculate the variance between actuals (ASO) and plan (BSO) from a dynamic sparse member on the BSO outline.
I played around a bit to try to get it all in one ASO cube, but I haven't been able to trick ASO into loading upper level data (using dummy level0) members. The upper level data that I loaded to dummy members rolled up all over the place and looked ridiculous (adding plan % from various upper levels) before I could even get to the MDX scripting.
Any advice is appreciated.
Try thisNo score for this post
|February 20 2013, 5:24 PM |
Add dummy "level 0" members in ASO cube and load planning data and load Actual data to appropriate "level 0" members and build transparent partition at parent level that way you avoid showing those dummy members in your target cube which is a BSO cube.
We have the same scenario in our cube and we were successfully able to do it....
thanks for the suggestionNo score for this post
|February 25 2013, 9:40 AM |
I've prototyped the dummy members in ASO (no partition) by using Essbase filters to hide the dummy metadata. Works great and has the same functionality that you described. However, we're loading Plan %, not $, in the upper level and we don't want it aggregate - but it must agg to get the value from the dummy to the parent. ASO seems to insist on aggregating everything. So I'm falling back to the ASO source to BSO via transparent partition where we have more control. If we need to load Plan $ data to upper levels that could naturally aggregate, then I would be able to do either your approach of the meta hide (filter) approach.
Anyway, I'm really impressed with the ASO to BSO transparent partition with the ability to use complex calcscript formulas in BSO that reference ASO aggregated data - no MDX and more control on unpper level loaded data. Performance is better than pure BSO and the formulas still work.