8 Replies Latest reply on Jun 14, 2011 7:52 PM by 854880

    Multiple Amount Columns

    854880
    854880 Jun 13, 2011 10:17 PM
      I'm setting up an import format for a text file (to load data into Essbase) that has more than one column with an amount in it. e.g. column 1 is expense dollar amount, and column 5 is hours. the expense gets loaded to the account shown in column 2, and the hours in column 5 get loaded to an account called "Hours".

      I have another flat file, with about 12 amount columns, each loading to a different stat account. Same situation.

      (before you tell me that I should use a load rule and load to essbase, I know, but we're not doing it that way...)

      So my question is, how do I set up the import format to load more than one data column, where I know specifically what the account for the second columns is? I don't mind having to do some scripting, but I might need and example to follow.

      thanks.
      • 159 Views
      • Tags:
        • 1. Re: Multiple Amount Columns
          SH_INT
          SH_INT Jun 14, 2011 8:33 AM (in response to 854880)
          You can do this with an import script. You will need to parse out the multiple account values so that they any additional values over and above the intial account value are added to the current work table at the import stage.
          1 person found this helpful
          • 2. Re: Multiple Amount Columns
            854880
            854880 Jun 14, 2011 2:08 PM (in response to SH_INT)
            Hi SH,

            Thanks for confirming this as a possibility. I understand to some extent how to write the import script you describe - I have several that parse sql views and do other sorts of things. But...

            Are you saying that I need to read in and parse the text file entirely through the import script, similar to the way I do a sql view, except read line by line instead of record by record? I'd prefer not to got completely custom on this and just manage the added data column(s) using script.

            I understand if you don't want to post specific code, but I'm a bit vague on what you're saying above. Perhaps a little pseudo code and the specific (data pump, integration?) script type and API objects I would need to use would be very helpful.

            Thanks
            • 3. Re: Multiple Amount Columns
              SH_INT
              SH_INT Jun 14, 2011 3:21 PM (in response to 854880)
              It can be done in an import data pump script. You would need the following step sequence in your import script

              1) Get name of the current FDM worktable (RES.PstrWorkTable)
              2) Create a recordset based on that table - look at Data Access class of the DW API component for recordset methods
              3) For Each additional account you want to load a value for you will have to append a new record to the recordset, populate all the fields you require (the original account/amount combo specified in the import format will load as per normal process). There is a scripting section in the FDM Admin guide which lists all the worktable fields.
              4) Call the update method on the recordset to feed the new data rows into the worktable.

              This process will execute for every line of data in your original load file. Hope that helps you make a start.
              1 person found this helpful
              • 4. Re: Multiple Amount Columns
                854880
                854880 Jun 14, 2011 4:28 PM (in response to SH_INT)
                Awesome SH, Thanks.

                I did actually try the datapump approach and your thoughts agree with what I was thinking.

                The doc is a bit thin on the following, would you validate or correct me here?:

                I create a new datapump script "MyDataPump",

                The RES object (containing properties such as location key(PlngLocKey) and category key(PlngCatkey)) is valid in the context of the MyDataPump function
                The DW object from which I will access the table [DW.DataAccess.farsTable(strWorkTableName)] is valid in the contet oof the MyDataPump Function

                That all being ok, the one thing I still cannot figure out is where I get the name of the table where I'm loading the data (strWorkTableName)? Its passed as an arg in the Integration scripts, but not in the Datapump Script.

                Aslo, since I'm updating the table with the data for this extra amount field myself, what value do I return from MyDataPump, and how is it used by the import process that calls the script (IOW since I inserted the record myself, I don't really want FDM to do anything with this field after the script is run.
                • 5. Re: Multiple Amount Columns
                  SH_INT
                  SH_INT Jun 14, 2011 4:30 PM (in response to 854880)
                  You can get the worktable name from the following property:

                  RES.PstrWorkTable
                  1 person found this helpful
                  • 6. Re: Multiple Amount Columns
                    854880
                    854880 Jun 14, 2011 5:09 PM (in response to SH_INT)
                    What do I return from the data pump function?
                    I'm also not sure what field name to use on the import format screen in the bottom grid, do I create a second "Amount" field? I don't want to clobber the amount loaded in the first amount field.
                    • 7. Re: Multiple Amount Columns
                      SH_INT
                      SH_INT Jun 14, 2011 5:33 PM (in response to 854880)
                      Just attach the import script to one of your existing fields in your impoert format definition, you don't need to add another entry. As you are loading multiple account values maybe attaching it to that field would be logical. In terms of what you return from the scipt just return the field you assigned in the entry which should be the strField parameter passed to the script. Remember you want the import format to behave as normal for the original account, it will just be the addtional account/amount combinations you will be adding via use of the recordset in the script.
                      • 8. Re: Multiple Amount Columns
                        854880
                        854880 Jun 14, 2011 7:52 PM (in response to SH_INT)
                        Perfect! Thank you.