Looping of Workflows in Informatica

Scenario:

You have 3 Oracle source schemas: test_schema1, test_schema2 and test_schema3. Each schema contains 3 tables EMP, DEPT and MANUFACTURERS. The structure of tables is exactly same in each schema. You have a target schema test_schema_target which contains 3 tables: EMP_TARGET,DEPT_TARGET and MANUFACTURERS_TARGET. The stucture of tables is exactly similar as those to source schemas.

Now you have to provide an Informatica solution wherein the the tables in target schema are loaded from corresponding tables in source schema dynamically. i.e there should be only 1 set of mappings for each loading and this set should be called 3 times(since there are 3 source schemas). Everytime the schema name should should dynamically change.

Solution:

Assumption: The user in target schema has the access to tables in all 3 source schemas.

Create a control table as follows:

Build a mapping (m_par) which will select 1 row from the control table as follows:

The port v_Schema_Name will be set as SETVARIABLE($$schema_name,SCHEMA_NAME)

where $$schema_name is a mapping parameter. The source qualifier query is as follows:


SELECT
CONTROL_TABLE.SCHEMA_NAME,
CONTROL_TABLE.FLAG
FROM
 CONTROL_TABLE
WHERE
 CONTROL_TABLE.FLAG='N'
and rownum=1


The idea here is, to assign the value of schema name returned from above query to the mapping parameter $$schema_name. This mapping parameter will then be assigned to a workflow variable. This workflow variable then will be assigned to the mapping parameters of EMP, DEPT and MANUFACTURERS mappings. The flag will be updated for each successful mapping and the workflow will be called again using a CMD task. This whole process is explained below:

Now, build a mapping for each target: EMP_TARGET, DEPT_TARGET and MANUFACTURERS_TARGET.

The mapping screen shot for EMP is shown below:

The source qualifier query will be as follows:


SELECT
$$schema_name_e.EMP.EMPNO, $$schema_name_e.EMP.ENAME,
$$schema_name_e.EMP.JOB,
$$schema_name_e.EMP.MGR, $$schema_name_e.EMP.HIREDATE, $$schema_name_e.EMP.SAL,
$$schema_name_e.EMP.COMM, $$schema_name_e.EMP.DEPTNO
FROM
$$schema_name_e.EMP

Here $$schema_name_e is a mapping parameter defined in this mapping

There will be a Post-SQL on EMP_TARGET as follows:

update CONTROL_TABLE set FLAG='Y' where CONTROL_TABLE.SCHEMA_NAME='$$schema_name_e' and CONTROL_TABLE.TABLE_NAME='EMP'

Similar mapping will be created for DEPT and MANUFACTURERS also. these mappings will also have mapping parameters as $$schema_name_d and $$schema_name_m and will have the similar Source qualifier and Post-SQL in target queries.

The workflow (wf_child)  screen shot is shown below:

Here s_par is the session pointing to Control table mapping (m_par).
s_emp is pointing to EMP mapping.
s_dept is pointing to DEPT mapping.
s_manufacturers is pointing to MANUFACTURERS mapping.

In the properties of the workflow; create a wf variable as shown:

Now within the workflow, doble click on session s_par and go to Components tab:

Click on "Post session on Success Variable assignment" and assign the value $$Schema_name to $$schema_name1.

This effectively assigns the schema name extracted from control table to the workflow variable.

Create a decision task (dec_count) as $s_par.SrcSuccessRows>0

So, if there is any row pending in control_table, this workflow will proceed.

After dec_count place the sessions s_emp, s_dept and s_manufacturers in parallell. Double click on s_emp and go to components.

Set the "Pre-session variable assignment" as follows:

This effectively assigns the value of Workflow variable(derive in the previous step) to the mapping parameter
$$schema_name_e before the actual run of this session. As a result the source qualifier query will execute with this parameter value.

Repeat this exactly for s_manufacturers and s_dept to calculate the values of $$schema_name_m and $$schema_name_d.

There will be a final command task in this workflow cmd2 with the command as

pmcmd startworkflow -sv Local_IS -d "domain_name" -u "user_name" -p password -f "folder_name" wf_Parent

This calls another workflow wf_Parent(screen shot below) which in turn calls wf_Child.

The command task s_call contains a call to child workflow:

pmcmd startworkflow -sv Local_IS -d "domain_name" -u "user_name" -p password -f "folder_name" wf_Child

For both the workflows, set the property "Configure concurrent execution" as "Enabled"

The control table will keep on updating the value of FLAG column as the workflow runs for particular schema and table. After all schemas and tables are covered, the FLAG column will be Y for all rows and the loop will stop:

In this way, the Informatica workflow will run in a loop loading tables in different schemas.

Hope this helps.........

Changing your password in OBIEE 11G

In OBIEE 11g, the users can not change their password unless they have administrative privileges. To provide this functinality there is a workaround of using an API of enterprise manager. To check this API:

Go to enterprise manager link and navigate to System MBean browser > Runtime MBeans > Security > Domain > myRealmDefaultAuthenticator as shown in below screen-shot

In order to use this API, you have to:
1. Write a Java Class calling MBean methods to connect to MBean server
2. Write a method in that Java Class and call this API into that method
3. Convert this Java Class into a web service exposing the above built-method
4. Deploy the web-service to the admin server.
5. Make an Action Link on your OBIEE dashboard calling the above built web-service in that action link.

Jdeveloper can be used to built Java Class. The Java Class code is given below:


package obieeweb;



import java.lang.management.ManagementFactory;
import java.net.InetAddress;
import java.util.Arrays;
import java.util.HashMap;
import javax.jws.WebMethod;
import javax.jws.WebService;
import javax.management.MBeanServer;
import javax.management.ObjectName;
import javax.management.remote.JMXConnectorServer;
import javax.management.remote.JMXConnectorServerFactory;
import javax.management.remote.JMXServiceURL;


public class OBIEEWebServices {
          public String OBIEEWebService(String userId, String oldPassword, String newPassword, String confirmPassword) throws Exception {
     
        MBeanServer mbs = ManagementFactory.getPlatformMBeanServer();
        HashMap env = new HashMap();
     
        final String hostname = InetAddress.getLocalHost().getHostName();
        final int port =  your_port_number;
     
        JMXServiceURL serviceUrl = new JMXServiceURL("service:jmx:rmi://"+hostname+":"+port+"/jndi/rmi://"+hostname+":"+port+"/jmxrmi");
     
        JMXConnectorServer jmxConnector = JMXConnectorServerFactory.newJMXConnectorServer(serviceUrl,env, mbs);

        ObjectName securityMBeanName = new ObjectName("Security:Name=myrealmDefaultAuthenticator");
        Object[] params = new Object[] { userId, oldPassword, newPassword};
        String[] signature = new String[] {"java.lang.String", "java.lang.String", "java.lang.String"};
        if (newPassword.equals(confirmPassword)) {
        try
          {
          mbs.invoke(securityMBeanName, "changeUserPassword", params,signature);
          }
        catch(Exception e)
          {
           return e+"|Error changing Password.";
          }
          return "Password changed successfully.";
        }
        else
        {
            return "Passwords don't match. Try again.";
        }
     }


    public static void main() throws Exception {
        OBIEEWebServices ow = new OBIEEWebServices();
        ow.OBIEEWebService( userId, oldPassword, newPassword, confirmPassword);
}
}

Steps to build a web service, in Jdeveloper:

1. Right click on your Java Class and select Create Web Service

2. Select the deployment platform as per your requirements and click Next:

3. Give the service name and port and click next:

4. Choose the binding method and click next:

5. This screen should give the method that you have written in your Java Class to invoke the API:

Click Next on the other screens and finish building your Web Service.

Once WebService is built, you need to deploy it to Application server:

1. Right Click on Project and select Deploy:

2. Choose Application server. Click on the + button to add a new server:

3. Give the connection name :

4. Give username and password:

5. Give the configuration details:

6. Click test Connections and deploy.

Once the web service is deployed , you should be able to check it in Admin server under deployments.

Now, we have to create a dashboard and add a new action link:

Create a dashboard and drag a new action link. select Invoke Web Service:

Give the web Service URL and Select the webservice. It will open the arguments screen. The number of arguements will be the same as the method you have used in your Java Class to invoke the API:

Note that in first arguement for User Id, we have selected a Session variable USER and made it hidden so that its not prompted and its value is automatically assigned to the User which has signed in.

The dashboard looks like: