5 Replies Latest reply on Jun 18, 2015 1:31 AM by Francisco Amores

    FDMEE BefImport Script from SQL

    user730580-Oracle

      Hi,

       

      My current process is to manually initiate the import of data from SQL views to the Open Interface table, and then I login to FDMEE to import the data into Essbase. I'd like to automate this process and want to convert my SQL code for inclusion in the BefImport event script. I have seen the sample in the FDMEE admin guide, but am new to FDMEE (and scripting) and am unable to follow the conversion of my SQL query to the correct code needed in the event script. Does anyone have an example of a SQL query before and then what it looks like after it was converted to the BefImport event script? Any help or direction would be greatly appreciated.

       

      Thanks!

        • 1. Re: FDMEE BefImport Script from SQL
          Francisco Amores

          Hi,

           

          I would suggest you start building your script from scratch using the admin guide sample.

           

          For example:

           

          1. Build a BefoImport script that just connects to your source database

          you can use fdmAPI log functions to write success messages or errors captured by exceptions

          2. add more functionality to that script like querying your view

          3....

           

          at the end of each iteration you should get your script more and more complete.

           

          That's the best way of learning.

           

          Write > Test > Fail > Troubleshooting > Fix > Test > Success :-)

           

          CHeers

          • 2. Re: FDMEE BefImport Script from SQL
            user730580-Oracle

            Hi Francisco,

             

            You're correct and that was the best way to start writing the script! Following that approach, it looks like the script is failing when it gets to the SELECT statement. I have noted it below:

             

            sourceConn = sql.DriverManager.getConnection("jdbc:oracle:thin:@server","fdmee","password");

             

            selectStmt = "SELECT POSTED_TOTAL_AMT,

            ACCOUNTING_PERIOD,FUND_CODE,ACCOUNT,

            CLASS_FLD,DEPTID,DEPT_DESCR,PROJECT_ID,

            DESCR,PROGRAM_CODE,CHARTFIELD1,FISCAL_YEAR,LEDGER

            FROM FDMUSER.VW_EDW_BUDGET_INQ_FYAP_DETAILS"

             

            I'm guessing it's failing because I'm connecting to the SQL view in  a different schema? However, the "fdmee" schema that I'm connecting to has access to the "fdmuser" user. Is there a different syntax for this type of connection?

             

            Thanks!

            • 3. Re: FDMEE BefImport Script from SQL
              Francisco Amores

              which is the error?

               

              you don't need to use ; at the end of the line.

               

              If you want to have select statment in multiple lines you will have to use triple double quoted

               

              select = """ SELECT

                          ...........

                        ...........

              """

               

              Please if you find any answer as helpful then mark it so others can see its feedback

              • 4. Re: FDMEE BefImport Script from SQL
                user730580-Oracle

                Hi Francisco,

                 

                Yes, that was the issue that I didn't have the SELECT statement in triple double quotes (since it was on multiple lines). I will mark this answer as helpful!

                 

                I'd like to ask one last question related to the same SELECT statement. I have included the full SELECT statement below which includes a WHERE clause. I've tried the syntax a couple of different ways, but still no luck. Would you be able to provide guidance as to what part of this syntax is incorrect? Unfortunately, the Admin guide didn't have such an example for reference.

                 

                selectStmt = selectStmt + "WHERE FISCAL_YEAR = 2015 AND BUDGET_PERIOD = 2015 AND ACCOUNTING_PERIOD = 10 AND LEDGER IN ('STAGE_EX','REVEST_RC')"

                 

                Thanks so much for your help with those of us not as skilled in this area!!!

                • 5. Re: FDMEE BefImport Script from SQL
                  Francisco Amores

                  Jython use parametrized select:

                   

                  select = """ SELECT ...

                  FROM ...

                  WHERE x = ? and y = ?

                  """

                   

                  then you run it for example: fdmAPI.executeQuery(select, [param1value, param2value])

                   

                  There are some examples in the admin guide.