Accessing ODI Physical Schema via FDMEE

Story time! Recently I was migrating FDMEE from 11.1.2.3 to 11.1.2.4 at a client that has extensive scripting in FDMEE. Lot's of neat stuff going on here. One of the things they were doing was accessing the ODI physical schema to obtain the login information for their Essbase cube so that they could run dynamically generated clears targeted at locations where new data was coming in.

query = "SELECT USER_NAME, PASS, DSERV_NAME FROM SNP_CONNECT WHERE CON_NAME='Essbase-Server-PROD'"
conn_pass = execQuery('GETSTRING', query, [], 'PASS', 'NO')
conn_pass = DwgObject.snpsDecypher(conn_pass)
conn_user = execQuery('GETSTRING', query, [], 'USER_NAME', 'NO')
conn_server = execQuery('GETSTRING', query, [], 'DSERV_NAME', 'NO')

conn_home = IEssbase.Home.create('11.1.2')
conn_domain = conn_home.signOn(conn_user, conn_pass, False, None, 'Embedded')
conn_olap_server = conn_domain.getOlapServer(conn_server)
conn_olap_server.connect()
conn_cube = conn_olap_server.getApplication(TGTAPPNAME).getCube(TGTAPPDB)

The first thing I noticed, since I am doing a migration, is that the SQL query is using the name from the physical schema which means I am going to have to update this since I am migrating to the new development environment and I can't have it connecting to old production! So I popped open DataGrip, my SQL client of choice when not working with MSSQL, and started connecting the logical schema to the physical schema by way of the default context (DEF_CONT) and came up with the following updated query.

select sc.USER_NAME, sc.PASS, sc.DSERV_NAME 
from SNP_LSCHEMA l
  inner join SNP_PSCHEMA_CONT pc on l.I_LSCHEMA = pc.I_LSCHEMA
  inner join SNP_PSCHEMA p on pc.I_PSCHEMA = p.I_PSCHEMA
  inner join SNP_CONNECT SC on p.I_CONNECT = SC.I_CONNECT
  inner join SNP_CONTEXT c on pc.I_CONTEXT = c.I_CONTEXT and c.DEF_CONT = 1
where l.LSCHEMA_NAME = 'ESS.APP.DB'

Once I had that working I realized I had a second issue with this code, its making a separate call to the database for each item its returning (execQuery('GETSTRING', ...).) Obviously this works but its a bit clumsy, so after a bit of refactoring I was able to get to the same information with only a single database call and the code follows the context/logical schema which means when I migrate to test and production it will just work.

sql_query = """select sc.USER_NAME, sc.PASS, sc.DSERV_NAME
               from SNP_LSCHEMA l
                 inner join SNP_PSCHEMA_CONT pc on l.I_LSCHEMA = pc.I_LSCHEMA
                 inner join SNP_PSCHEMA p on pc.I_PSCHEMA = p.I_PSCHEMA
                 inner join SNP_CONNECT SC on p.I_CONNECT = SC.I_CONNECT
                 inner join SNP_CONTEXT c on pc.I_CONTEXT = c.I_CONTEXT and c.DEF_CONT = 1
               where LSCHEMA_NAME = ?"""
result_set = fdmAPI.executeQuery(sql_query, ['ESS.APP.DB'])
result_set.next()
conn_user = result_set.getString('USER_NAME')
conn_pass = DwgObject.snpsDecypher(result_set.getString('PASS'))
conn_server = result_set.getString('DSERV_NAME')

conn_home = IEssbase.Home.create('11.1.2')
conn_domain = conn_home.signOn(conn_user, conn_pass, False, None, 'Embedded')
conn_olap_server = conn_domain.getOlapServer(conn_server)
conn_olap_server.connect()
conn_cube = conn_olap_server.getApplication(TGTAPPNAME).getCube(TGTAPPDB)