OBIA Best Practices

Best Practices and Important Notes

DAC Notes

1. To check the pmcmd command sent by DAC to Informatica to execute a task, click on the Status Description in the Task Detail window. We can run this from the Linux box (where the Informatica server resides)
2. Use the Analyze Repository Tables (Tools > DAC Repository Management > Analyze Repository Tables) when it has been made a considerable change to the core metadata -> this helps to speed up the steps that follow
3. Micro-ETL:
   - scheduled to run every couple of hours and it can be running even when users are using the OLTP system or running BI reports
   Steps:
     [] create a new Subject Area and add the workflows that we are interested in (i.e.: 1 fact or 1 dimension only) + Assemble
     [] create a new Execution Plan and add the Subject Area created before. Tick Micro-ETL. This is VERY IMPORTANT as the process is this: when Micro-ETL is ticked, the new data added in the OLPT comes into DW even when the users are still logged on and run reports, so no truncate of the staging or target tables happens. Also, the indexes are not dropped. The next time the Incremental is run (involving these tables), it brings in everything from the last incremental run (in a proper way), with dropping and recreating indexes, just to make sure that nothing was
   missed during the Micro-ETLs run and that the statistics are up to date.
   Note: It does not make sense to Analyze nor to drop/create indexes in the properties of the execution plan, all we want is to bring in those new records (so ideally we want the Micro-ETL to run in a couple of mins only - fastest as it can)
     [] Generate parameters and Save
     [] Build and Run 
4. Deploy to PROD

• The DAC client could be used to connect to many DAC servers.
• In terms of migrating all the changes from DEV to PROD: export the custom container only (Logical, no other tick boxes checked) and import it in PROD. Note that when DAC server was created, an empty DAC repository has been created. so we only need to bring in the changes (ie custom container, custom subject areas, custom execution plans etc) and we setup the parameters and the connections to use the PROD connection details.

Export DAC customisations from DEV:     DAC > Tools > DAC Repository Management > Export  > tick only Logical -> Select container > Export.

Import DAC customisations into PROD:    DAC > Tools > DAC Repository Management > Import

 
Informatica Notes

1. It is good practice to use versioning of the changes so check-in must be made under a specific revision.
2. Before making any changes to the mappings, it is advisable to keep a copy of the original one. Export SDE > Save as XML and put it under a revision control system. This will ensure that if the SDEs get messed, we could always revert to the original by Importing it to the custom folder (address the conflicts with Replace)
3. If we want to make add some conditions to the query only for the Full load, instead of updating the mapping (SDE), just change the Workflow query (Full) - as the mapping is used by both Workflows (incremental and full) and we only want the change in one of them so it does not make sense to create another mapping just to reflect our condition.
4. Running Workflows directly from Informatica: when creating a Mapping as a copy of an existing one, right-click Mapping/Mapplet > Declare Parameters and Variables > always give an Initial Value for the params so that when running it in the Workflow Manager it will use the default values and it will not error.

In Informatica Workflow Manager: don't forget to replace the Parameters with the OLTP/DW actual values

- drag WF and on the task > right click > go to Properties

                      -- $Source Connection Value -> change to be whatever we want to point out to (Use Object and select object from the list)
                      -- $Target Connection Value -> change as above
               - NOTE there is always the option to revert to the original (Use Connection Variable)
               - Edit Task, go to tab Mapping > Connections > make sure there are no variables used but the actual values are passed. Save and run the workflow
 

DEV NOTE: to see large mappings just right click on the grey area in the Mapping Designer and select Arrange All Iconic