2 Replies Latest reply: Jul 9, 2014 5:47 PM by Sree Menon-Oracle RSS

    MDXDatacopy

    Celvin Kattookaran

      Did anyone look at @CalcMgrMDXDataCopy and @CalcMgrMDXDynDataCopy?

       

      I couldn't find any documentation on these ones, however with the limited knowledge I was able to make them work (not really work)

       

      FIX("Actual")
      Jan
      (
      @CalcMgrMDXDataCopy( "admin", "password", false, "Sample", "Basic", "Sample", "BasicT", "SELECT {[100].CHILDREN} on columns, {[Margin].CHILDREN} on rows, {[Qtr1].CHILDREN} on pages, {Budget, Actual} on chapters where ([New York])" , "E:\Oracle\Middleware\user_projects\azgardepmsystem\tmp\MDXDataCopy.log", null, null, null, Houston, "Azgard");
       )
      
      ENDFIX
      

      It needs to be executed from Source (otherwise it'll start complaining about Houston being a member not a string.

       

      This is the usage from the specs

      @CalcMgrMDXDataCopy(user, password, useToken, sourceApplicaionName, srcDatabaseName, targetApplicationName, targetDatabaseName, mdxQuery, loggingFileName, sourceMemberMappings, targetMemberMappings, maxRowsPerPage,tgtPOVCols,server)

       

      I believe source/target(MemberMappings) is something similar to partitioning mappings, how can you define it?

      What is maxRowsPerPage used for (I cannot add a number there, because the script then complains about number present

      What is tgtPOVCols - I'm assuming that this is the target where data will be copied.

       

      The good thing is it is a neatly formatted MDX output (i think it is gridapi)

       

      Query:SELECT {[100].CHILDREN} on columns, {[Margin].CHILDREN} on rows, {[Qtr1].CHILDREN} on pages, {Budget, Actual} on chapters where ([New York])
      User:admin
      useToken:No
      Source:Sample/Basic
      Target:Sample/BasicT
      Signed in User:admin
      Grid Size: Rows(14) Cols(6)
        New York Houston
        100-10 100-20 100-30
      Budget Jan Sales 640.0 #Missing #Missing
      Budget Jan COGS 260.0 #Missing #Missing
      Budget Feb Sales 610.0 #Missing #Missing
      Budget Feb COGS 240.0 #Missing #Missing
      Budget Mar Sales 640.0 #Missing #Missing
      Budget Mar COGS 250.0 #Missing #Missing
      Actual Jan Sales 678.0 #Missing #Missing
      Actual Jan COGS 271.0 #Missing #Missing
      Actual Feb Sales 645.0 #Missing #Missing
      Actual Feb COGS 258.0 #Missing #Missing
      Actual Mar Sales 675.0 #Missing #Missing
      Actual Mar COGS 270.0 #Missing #Missing
      

       

      Bad thing is it just keep going on and repeatedly loads New York in target, not Houston. I had to kill it after sometime.

       

      CalcMgrMDXDynDataCopy has some issue where it cannot generate a correct MDX if more dimensions are specified in axisquery.

       

      So this works

       

      FIX("Actual")
      Jan
      (
      @CalcMgrMDXDynDataCopy("admin", "p@ssword", false, "Sample", "Basic", "Sample", "BasicT", "E:\Oracle\Middleware\user_projects\azgardepmsystem\tmp\MDXDynDataCopy.log", null, null, null, "([New York])", "[100].CHILDREN", null, "Azgard");
       )
      
      ENDFIX
      

      not this

       

      FIX("Actual")
      Jan
      (
      @CalcMgrMDXDynDataCopy("admin", "p@ssword", false, "Sample", "Basic", "Sample", "BasicT", "E:\Oracle\Middleware\user_projects\azgardepmsystem\tmp\MDXDynDataCopy.log", null, null, null, "([New York])", "[100].CHILDREN, [Margin].CHILDREN", null, "Azgard");
       )
      
      ENDFIX
      

      Does these function work?

       

      Regards

       

      Celvin

        • 1. Re: MDXDatacopy
          Sree Menon-Oracle

          Celvin,
            This was done as part a POC where after aggregating, they wanted to move data from BSO to ASO for reporting.
            The CDF was written to take in an MDX and then use the Grid API to push data back to ASO from the result of the MDX query.
            Since this was just a POC, Source/Target mapping does not work like partitioning. It is just source meber to target member (Apr to April). No functions are supported.
            Since the mdx can return large data, for performace, maxRowsPerPage could be set to say 1000 which means as soon as the grid has 1000 rows and update to ASO is done.
            At the end remaining rows are also pushed. If the maxRowsPerPage is set to "-1", then all the data is filled in to one grid.

            sourceMemberMappings could be something like @List("Apr", "Qtr1") and targetMemberMappings could be @List("April", "Quarter1").
            So while filling the grid, the member names of BSO APR and Qtr1 are converted to "April" and "Quarter1" respectively.
           
            So when you put it in a Fix Statement, it is just going to do the same copy over and over again for all the members it process.
            Unless you dynamically generate the MDX based on the current member, it is best to use with RUNJAVA.

           

            Some sample mdx we had tried out based on Sample/Basic (We used EAS to copy Sample/Basic to an ASO):

             SELECT {[100].CHILDREN} on columns, {[Market].CHILDREN} on rows, {[Measures].CHILDREN} on pages, {Year} on sections, {Budget, Actual} on chapters

             SELECT {[100].CHILDREN} on columns, {[East].CHILDREN} on rows, {[Margin].CHILDREN} on pages, {[Qtr1].CHILDREN} on sections, {Budget, Actual} on chapters

             SELECT [100].CHILDREN on columns, [East].CHILDREN on rows, [Margin].CHILDREN on pages where ([Budget], [Jan])

             SELECT Except([100].CHILDREN, {[100-40]}) on columns, [East].CHILDREN on rows, Except([Margin].CHILDREN, {[COGS]}) on pages where ([Budget], [Jan])

             SELECT Except([100].CHILDREN, {[100-40]}) on 0, [East].CHILDREN on 1, Except([Margin].CHILDREN, {[COGS]}) on 2 where ([Budget], [Jan])

             SELECT [Margin].CHILDREN on 0 where ([Budget], [Jan], [100-10], [New York])

           

            With the hybrid BSO coming up in the future, I stopped working on it (may the requirement to move data from BSO to ASO do not exist) and forgot to remove it and hence no doc.

            Initially because we use JAVA API in the CDF, scaling was an issue.


            If you think it is worth pursuing, please email me at sreekumar.menon@oracle.com

          -Sree

          • 2. Re: MDXDatacopy
            Celvin Kattookaran

            Sree

             

             

            Thank you for the update, I was just curious about it. I was looking at MaxLExecute for a blog entry and found out this one. It did interest me. I'll send you an email with my thoughts on it.

             

             

            Regards

             

            Celvin