3 Replies Latest reply on Aug 6, 2010 4:23 PM by Matthias Heilos

    FDM Type Mappings in ODI

    user650918
      Hi All,

      I have been teaching myself ODI over the last week, and I am having trouble performing simple mappings to transform data before it is loaded into my Hyperion applications.

      I would simply like to be able to perform some mappings with ODI as I would in FDM, but all of my attempts in my interface result in the following type of error.

      org.apache.bsf.BSFException: exception from Jython:
      Traceback (innermost last):
      File "<string>", line 3, in ?
      com.hyperion.odi.common.ODIHAppException: Unexpected token: [ in statement [select   Actual    "Scenario",2008    "Year",YTD    "View",C4_ENTITY='[None]' "Entity",USD "Value",C3_ACCOUNT='ODIChild2' "Account",]


      For instance, I have a simple flat file that I am loading to HFM. I would like all entities in the file to be loaded to the "[None]" member in HFM.

      Thanks for any help or examples you could provide.

      Thanks,

      Nick
        • 1. Re: FDM Type Mappings in ODI
          Matthias Heilos
          Hi Nick,

          There are a couple things that pop out right away:
          1) Make sure to put text in single quotes, e.g. instead of Actual, type in 'Actual' etc.
          2) C4_ENTITY='[None]' --> just type in '[None]' in the Entity field - this will work technically, although I'm wondering why you would want to exclude the entity.

          Hope this gets you to the next step.

          Matt
          • 2. Re: FDM Type Mappings in ODI
            user650918
            Matt,

            This was exactly what I needed. I was missing the single quotes.

            I am guessing I would need to write SQL statement if I wanted to create more complex mappings? For instaance, if I wanted all accounts in a load file that start with "1" to be loaded to the account "Cash", should I use the expression builder?

            Thanks for anything else you can provide.

            Nick
            • 3. Re: FDM Type Mappings in ODI
              Matthias Heilos
              Hi Nick,

              Glad this worked for you.

              To map accounts based on the logic below, you have two options:
              1) write a CASE WHEN clause in the Account mapping field (you can use Expression Editor or just type it in), something like

              CASE
              WHEN Account LIKE '1%' THEN 'Cash'
              WHEN ...
              ELSE ....
              END

              2) Create a mapping file/table as you would have it in FDM. Add a line for each account in there (this way you would have Explicit mappings as they are in FDM) or you can even put more logic in there to also support like mappings as you have them in FDM.

              I would go with option 1 if you only have a handful of mappings. If there is a significant amount of mappings, use option 2 as this will be easier to maintain going forward.

              You can also create a hybrid solution, hardcoding some LIKE mappings and map the remaining ones using a file.

              Hope this helps. Please mark helpful and correct answers accordingly.

              Regards,
              Matt