Monday, June 24, 2013

Data Copy in ASO Applications using calculation script

Data copy in a BSO application ( Ex: copy data from one version to another ) is straight forward. You can write a calculation script, and use DATACOPY command. Easy, Peasy, Japaneesy!!!

But, ASO is a different story.
We usually export the required data from ASO application and import it back using rule file by changing the member names in rule file.

This approach is not faster and needs extra automation scripts.

But, things have changed after Oracle introduced calculation scripts in ASO application. We can do things like simple math, minor allocations and datacopy  using ASO calculation scripts.

Even though it is not a straight forward calculation script in BSO, you can achieve it with little MDX scripting knowledge and patience (Patience because, it will take some time to write MDX expression to make sure you are not "fixing" on derived, parent or dynamic cells in POV section).

Let’s see how we can do it.

Take ASOsamp:sample as an example.

If we want to copy all data from "Prev Year" to "Curr Year", We have to create a  MAXL script like below,

execute calculation on database ASOSamp.Sample with local script_file "/hyperion/Test_ASO_Calc.csc"
 POV " Crossjoin(Descendants([MTD],Time.Levels(0)),
Crossjoin({[Original Price],[Price Paid],[Returns],[Units],[Transactions]},
Crossjoin(Descendants([Transaction Type],[Transaction type].Levels(0)),
Crossjoin(Descendants([Payment Type], [Payment Type].Levels(0)),
Crossjoin(Descendants(Promotions, promotions.Levels(0)),
Crossjoin(Descendants(Age, Age.Levels(0)),
Crossjoin(Descendants([income Level],[Income Level].Levels(0)),
Crossjoin(Descendants(Geography, Geography.Levels(0)),
Crossjoin(Descendants(Stores,Stores.Levels(0)),
Descendants(Products,Products.Levels(0)))))))))))"

SourceRegion "{[Prev Year],[Curr Year]}";



My custom calculation script, Test_ASO_Calc.csc look like

[Curr Year] := [Prev Year];


This process runs faster like DATACOPY in BSO. 
Posted by at
Labels: , , , , , , , , , , ,

20 comments:

  1. amarnathAugust 9, 2013 at 12:30 AM

    Was this tried on a application with many dimensions and members.

    Because, I faced an issue where with so many CrossJoins the number of combinations have exceeded and the script is not getting executed.

    ReplyDelete
    Replies
    1. Sundeep ReddyAugust 9, 2013 at 11:23 AM

      Yes, I have successfully tested this on application with 15 dimensions. I know designing cross dimension is hectic process.
      But this approach is very quicker than exporting and importing data.

      Let me know if you need some assistance in your MDX design.

      Delete
    2. AnonymousMay 13, 2014 at 10:07 PM

      i included all my dims@lev 0 and I get an error ...
      Query is too large and cannot be executed. The product of member counts across all dimensions in the query exceeds 2^64
      should i just use smaller sets?
      -Patick

      Delete
  2. AdrienneFebruary 6, 2014 at 1:11 PM

    I have version 11.1.2.2 but I don't see the option to create Calc scripts in an ASO cube. Can you please tell me where I can find this option. In my case I need to copy data from member1 to member2 everytime there is a data load on member1

    ReplyDelete
  3. Sundeep ReddyFebruary 6, 2014 at 2:23 PM

    We do not have a option to create calculation script in ASO application directly from EAS (as of 11.1.2.2 version). Just create a file with .csc extension with required code ( there is no way to verify syntax), place it in your server and pass it to maxl statement.

    ReplyDelete
  4. AdrienneFebruary 6, 2014 at 7:33 PM

    So I wrote a txt file and saved it on the server and did the maxl command as below but I got the error as following

    MAXL> execute calculation on database ASO_LF_A.LoadFcst with local script_file "Z:/EPM/Test_ASOcalc.txt";

    ERROR - 1051050 - You must upgrade your client software to perform this function..



    ReplyDelete
    Replies
    1. Sundeep ReddyFebruary 9, 2014 at 10:33 PM

      Unfortunately i did not came across this issue before, but i am assuming you have to update your EAS client if you are running from your local machine.

      Delete
  5. AnonymousMarch 6, 2014 at 12:51 PM

    Hello, I tried doing this, but I keep getting an error saying "unable to open file '...\Test.csc' Custom calculation terminated with essbase error 1241109. Do you know what might be causing this issue?

    ReplyDelete
    Replies
    1. AnonymousAugust 4, 2014 at 9:55 AM

      did you found the solution for this issue Custom calculation terminated with essbase error 1241109?

      Delete
  6. AnonymousApril 21, 2014 at 1:42 PM

    Is this available on 11.1.2.1?

    ReplyDelete
  7. UnknownJune 25, 2014 at 5:49 PM

    If you are going behind levels, then use NONEMTPY, but I would think leaves makes better sense here. Leaves will do the same nonempty trick on fly. (since it is ASO you cannot perform a calc on any other level)

    ReplyDelete
    Replies
    1. AnonymousJuly 11, 2024 at 2:36 PM

      how can i use the NONEMPTY and where to include it in the above statement as without suppress #missing, my script is taking huge amount of time and not finishing

      Delete
  8. Sundeep ReddyJuly 31, 2014 at 9:22 PM

    thanks Celvin

    ReplyDelete
  9. AnonymousAugust 4, 2014 at 9:58 AM

    Hi, I tried doing this, but I keep on getting an error like "unable to open file '...\Test.csc' Custom calculation terminated with essbase error 1241109

    ReplyDelete
  10. AnonymousDecember 18, 2014 at 6:08 PM

    How do you copy across multiple dimension? ie copy prior year actual to current year budget

    ReplyDelete
  11. UnknownJune 10, 2016 at 2:15 PM

    Sundeep, I have been working on an ASO copy in an app that has 13 dimensions. I can get it to work in many different situations but not at the top level of all dimensions. I know I must not have my cross join correct. The dimensions are a mixture of stored/dynamic/MH enabled. The MDX works with below fix stmt:
    POV "Crossjoin({[JUL.FY16]},
    Crossjoin({A500100},
    Crossjoin({Descendants([HFM_Account],[HFM_Account].Levels(0))},
    Crossjoin({Descendants([Market],[Market].Levels(0))},
    Crossjoin({Descendants([Company],[Company].Levels(0))},
    Crossjoin({[USD]},
    Crossjoin({Descendants([Function],[Function].Levels(0))},
    Crossjoin({[Data]},
    Crossjoin({Descendants([CDK_GLOBAL],[BU].Levels(0))},
    Crossjoin({[S102],[S170],[S130],[S300],[S320],[S330]},
    Crossjoin({Descendants([TOTAL_PRODUCT],[Product].Levels(0))},
    {Descendants(ALL_REGIONS,Region.Levels(0))})))))))))))"

    SourceRegion "({[ACTUAL.FINAL]})";

    BUT as soon as I try to add more periods or accounts it doesn't work, it only does some of the intersections. And there are way too many accounts to list them all. I figure I must have a tuple incorrect somewhere. Any help would be appreciated.






    ReplyDelete
    Replies
    1. AnonymousFebruary 3, 2017 at 2:57 PM

      Any workarounds/best practice to circumvent the dreaded error ?

      ERROR - 1200613 - Internal error: Query is too large and cannot be executed. The product of member counts across all dimensions in the query exceeds 2^64..

      ERROR - 1241190 - Custom Calculation terminated with Essbase error 1200613 in SOURCEREGION.

      Delete
  12. VicsenseSeptember 13, 2018 at 2:53 PM

    Hello I am trying to create an script based on the code you are sharing, but I have a question if I need to copy [fy18][scenario1] to [fy19][scenario2], could I write it like this: SourceRegion "{([fy18][scenario1]),([fy19][scenario2])}"; and the same on the calc script?, thanks hope you can answer me.

    ReplyDelete
  13. AnonymousJanuary 31, 2022 at 3:52 AM

    Baccarat Rules | Online casino games, bonuses and tips - Wil
    › online-casino-games › online-casino-games Jun 30, หาเงินออนไลน์ 2016 — Jun 30, 2016 How to Play Baccarat: 바카라 If the dealer takes the three cards, the card is the winner. The dealer then draws a single card. The player with the two cards receives three 메리트카지노

    ReplyDelete

Subscribe to: Post Comments (Atom)