2 Replies Latest reply: Jan 24, 2013 3:55 AM by 986592 RSS

    Additional columns with Hard Code values while exporting data from Essbase

    986592
      Hi All,

      I've a Essbase Database with 5 standard dimensions & 2 attribute dimensions.
      Essbase version is 11.1.2.1.103

      1. I've a requirement to export data with 10 columns. Except 5 columns from essbase, I need 2 additional output columns
      with hardcode values, other three 3 additional columns with Blank values.

      2. On top of that, in essbase database, I've Market Dimension with 200 members. As per requirement, I need to export
      data for each market. Then there are 200 files to be generated dynamically through essbase export where files name
      will be corresponding member names of Market dimension.

      Ex: Let say - East, West, North & South are members of Market Dimension.

      Then export files will be East.txt, West.txt, North.txt, South.txt respectively.


      How to solve the prob with Report Script / Calc script ? Or, if there any other way around, please inform.
      Any help will be appreciated. It's a urgent requirment.

      Regarding issue no 1, if it's not possible in Calc script/Report script, then I've an idea.
      If I add 2 members in A/c dimension as Text Measures, then I can get hard coded value defined in Text list associated
      with that members.

      Expecting a quick response from Gurus....
        • 1. Re: Additional columns with Hard Code values while exporting data from Essbase
          CL
          My first, and strongest comment to you is that maybe doing this in the report writer isn't the best thing in the world. But that's just me and I have used the report writer to do weird things.
          I've a requirement to export data with 10 columns. Except 5 columns from essbase, I need 2 additional output columns
          with hardcode values, other three 3 additional columns with Blank values.
          ^^^Have you taken a look at the MASK command?
          http://docs.oracle.com/cd/E17236_01/epm.1112/esb_tech_ref/rw_mask.html
          I've Market Dimension with 200 members. As per requirement, I need to export
          data for each market. Then there are 200 files to be generated dynamically through essbase export where files name
          will be corresponding member names of Market dimension.
          ^^^This is pretty weird for report writer but here are some thoughts:

          One approach would be to have 200 report scripts (ugh) with a name of 1.rep to 200.rep (bear with me on this). Then, in MaxL, have 200 calls to those 200 reports and use a parameter variable to write out the 200 output files. Ugh.

          Another would be to use a substitution variable to drive the market -- I think you might have to restart the database to get that to stick but maybe not. That would give you one reports script (much better) but I don't think you can query a substitution variable in MaxL to give you the file name for output, so you'd still have 200 MaxL export commands (that's how you run a report script).

          And yet another (and one I like quite a bit more) is to create a single report script and then use ! to delimit the reports. If you leave the reports absolutely the same except for Market, and then stick in the Market name with a ! after it you do not need to repeat what sets up the page and columns and everything else. The report writer engine will just take all of the previous settings and go. What's nice about this is you could use the TEXT command to give you a header that you could then parse on (actually, you could use any header you like) and extract out as many separate files as you want.

          Probably what makes the most sense is to write out the file as above but use headers (they're built in and you don't even need TEXT) and get all of the Markets through a hierarchical operator.

          Regards,

          Cameron Lackpour