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. 

17 comments:

  1. 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. 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. 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. 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. 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. 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. 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. 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. did you found the solution for this issue Custom calculation terminated with essbase error 1241109?

      Delete
  6. Is this available on 11.1.2.1?

    ReplyDelete
  7. 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
  8. 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
  9. How do you copy across multiple dimension? ie copy prior year actual to current year budget

    ReplyDelete
  10. 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. 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