Hash in output header

When using Output Field Names as Header for a Flat File target, a hash symbol [#] is put as the first one in header.

And the result in the file looks like this:

To get rid of the hash sign, the following custom property has to be set:

MS SQL Server: Stored Procedure error not failing parent Workflow

Stored Procedure error not failing parent Workflow

There's an issue I'm investigating: I have a workflow with a stored procedure executed as a post-load. It fails without failing the parent workflow. I've decided to come up with a simple test that would narrow down the cause.

Stored procedure

Here's the stored procedure I'll use for testing:

CREATE PROCEDURE RaiseErrorTest AS
BEGIN
--PRINT 'started'
--SELECT 1
IF 1=1
BEGIN
RAISERROR ('The servers are busy at this time. Please try again later', 16, 1)
RETURN 1
END
PRINT 'Completed'
SELECT 2
END

All it does is perform the RAISERROR that should fail the whole process. There are also two disabled statements - we'll get to those later.

Workflow

I've created a simple workflow that executes SELECT 1 on source and uses a FALSE filter, so nothing gets written to target. It also includes a Post-load stored procedure call to the above SP:

Test 1: Native connector, disabled statements before raising error

Ok, lets start it and see what happens:

Great, session has failed as expected raising the defied error. The workflow has failed.

Test 2: Native connector, enabled statements before raising error

Now, let's enable the two statements back:

ALTER PROCEDURE RaiseErrorTest AS
BEGIN
PRINT 'started'
SELECT 1
IF 1=1
BEGIN
RAISERROR ('The servers are busy at this time. Please try again later', 16, 1)
RETURN 1
END
PRINT 'Completed'
SELECT 2
END

Test 3: ODBC, disabled statements before raising error

Let's revert the stored procedure to have disabled lines 3 & 4 and try the ODBC connection:

Workflow failed as expected - please note the difference in error message due to using ODBC instead of SQL Server Native Client

Test 4: ODBC, enabled statements before raising error

Now for the final test: using ODBC connection for the stored procedure that runs some statements before raising error:

Session (and worflow) executed successfully! Great, right? Well, not quite... The stored procedure fails but this is not escalated to PowerCenter.

Conclusion

While this is handled correctly by Native SQL Server connector, any first result set returned by the stored procedure fools the ODBC into treating this a successful execution. Whatever happens later on is discarded and you may never know your stored procedure failed. 

Unfortunatelly I have no idea how to overcome this issue.

Repository cleanup

The Idea

Inpired by a post on Stackoverflow I've decided to come up with some solution to find unused objects in PowerCenter Repository.

So, the idea is to find all unused objects in Repository, like for example sources and targets not used by any mapping or mappings not used by any session or sessions that do not exist in any workflows. Doing that manually would require a lot of work and even more time. Doing it using Reository Queries is highly complex - if feasible at all.

Therefore I've created a simple app that uses pmrep tool to find all objects and check their dependencies. It is still time consuming, but once configured requires no manual effort at all.

Download link is available at the bottom.

Setup

All you need to do to run the tool after downloading is edit the config file. The properties are quite simple

#Common parameters

[Common]

infaDir = C:\Informatica\9.0.1

Repository = RepositoryName

#Specify Domain OR Host and Port. By default Domain will be used if specified.

Domain = Domain_Name

Host = your.host

Port = 6005

Folder = InformaticaFolderName

User = UserName

#User security domain. By default Native is used.

UserSecurityDomain = 

#Object list - sample below 

#objectTypeList = mapplet, mapping, session, source, target, worklet

objectTypeList = mapplet, mapping, session, source, target, worklet 

The last one - objectTypeList - can contain any number of object types that should be checked. 

Please keep in mind that looking for all possible object types might be really time consuming!

Running

Once set up, you just need to run the executable. It will prompt for repository password (note: the password is not stored anywhere and must therefore be provided on each run).

During runtime the progress is indicated by listing all steps and the number of objects of each type. Here's a sample:

Finally you'll find a folder named UnusedObjectsReport with the output:

• ListOfUnusedObjects.txt - file containing list of all the unused objects found in the repository
• DeleteUnusedObjects.bat - prepared batch to remove all the unused objects. For safety reasons all the lines in the batch are commended with 'rem ' prefix. You should review and choose which objects to remove. Once executed, all the objects should be removed. Note: this works only with unversioned repositories. 

In addition there will be two files per each object type defined in the list, e.g.:

• mapping.txt - list of all the mappings found in the repository
• mapping_dep - list of all dependencies found for each mapping

But these are just temporary files not intended for any further use. Feel free to inspect them if you like.

The tool requires no installation - simply download, unzip, setup and run. 

It can be downloaded from the following download page.

Numeric identity ODBC error

Using Numeric Identity for MS SQL Server target causes ODBC error when trying to update rows.

While running the session you might get an error as follows:

FATAL ERROR : An unexpected condition occured in file [/export/home/builds/pc8x_root/910HF/build/powrmart/common/odl/msodbc/odbcdriver.cpp] line [495].

This reads: I'm sorry, but loading data via ODBC to Numeric Identity port type for MS SQL Server target port is not supported. Please disconnect the port.

Here is a sample mapping causing the error:

All you need to do is disconnect the port or change it's type.

An ETL Framework for Operational Metadata Logging

Below is a copy of a very nice article found on www.disoln.org.

Quite often there is requirement to track runtime information about your ETL jobs such as record count, error count, job run time etc... In general it is a non-functional requirement, required by the IT team to have such information for reconciliation purposes, calculate performance statistics etc... It is important to have a framework, which can capture all the operational meta data you need with out adding too much time to your development cycle.

Here lets talk about building a framework to capture Operational Metadata by leveraging the capabilities provided by Informatica PowerCenter.

Framework Components

Our Framework for Operational Metadata logging will include three components.

1. A Relational Table :- To store the metadata.
2. Pre/Post Session Command Task :- Command task to collect the metadata.
3. Reusable Session :- Session to log the metadata details into the relational table.

I. Relational Table

A relation table will be used to store the operational metadata with the structure as below. Data in this table will be retained for historical analysis. 

• ETL_JOB_NAME : ETL job name or Session name.
• ETL_RUN_DATE : ETL job execution date.
• SRC_TABLE : Source table used in the ETL job.
• TGT_TABLE : Target table used in the ETL job.
• ETL_START_TIME : ETL job execution start timestamp.
• ETL_END_TIME : ETL job execution end timestamp.
• SRC_RECORD_COUNT : Number of records read from source.
• INS_RECORD_COUNT : Number of records inserted into target.
• UPD_RECORD_COUNT : Number of records updated in target.
• ERR_RECORD_COUNT : Number of records error out in target.
• ETL_STATUS : ETL Job status, SUCCESS or FAILURE.
• ETL_CREATE_TIME : Record create timestamp.
• ETL_UPDATE_TIME : Record update timestamp.

II. Pre/Post Session Command Task

Pre/Post session command task will be used to generate a comma delimited file with session run details. This file will be stored into $PMSourceFileDir\ directory with a name $PMWorkflowName_stat.txt. 

Note :

• $PMSourceFileDir, $PMWorkflowName are the session parameter, which gives the source file directory and name of workflow.
• File name generated will always be <WorkflowName>_stat.txt

The comma delimited file will have the structure as below.

• ETL Start time
• ETL End time
• ETL Job name
• Source table name
• Target table name
• Source record count
• Records inserted count
• Records updated count
• Error record count
• ETL Job status

We will be using the built-in session parameters to collect session run details. 

• $PMSessionName : Name of the Informatica session.
• $PMSourceName@TableName : Name of the source table name.
• $PMTargetName@TableName : Name of the target table name.
• $PMSourceQualifierName@numAffectedRows : Number of records returned from source.
• $PMTargetName@numAffectedRows : Number of record inserted/updated into the target table.
• $PMTargetName@numRejectedRows : Number of records error out in target.

Note : SourceName, TargetName, SourceQualifierName will be replaced by corresponding transformation instance name used in the mapping.

Pre Session Command Task

Pre session command task will be used to create the file with the session start time stamp. 

echo %DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2%,
> $PMSourceFileDir\$PMWorkflowName_stat.txt

Post Session Success Command Task

Post session success command task will be used to append the file, which is created in the pre session command with session run details. This will capture the SUCCESS status along with other session run details.

echo %DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2%,$PMSessionName,
$PMSTG_CUSTOMER_MASTER@TableName,
$PMINS_CUSTOMER_MASTER@TableName,
$PMSQ_STG_CUSTOMER_MASTER@numAffectedRows,
$PMINS_CUSTOMER_MASTER@numAffectedRows,
$PMUPD_CUSTOMER_MASTER@numAffectedRows,
$PMINS_CUSTOMER_MASTER@numRejectedRows,
SUCCESS,
>> $PMSourceFileDir\$PMWorkflowName_stat.txt

Post Session Failure Command Task

Post session failure command task will be used to append the file, which is created in the pre session command with session run details. This will capture the FAILURE status along with other session run details.

echo %DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2%,$PMSessionName,
$PMSTG_CUSTOMER_MASTER@TableName,
$PMINS_CUSTOMER_MASTER@TableName,
$PMSQ_STG_CUSTOMER_MASTER@numAffectedRows,
$PMINS_CUSTOMER_MASTER@numAffectedRows,
$PMUPD_CUSTOMER_MASTER@numAffectedRows,
$PMINS_CUSTOMER_MASTER@numRejectedRows,
FAILURE,
>> $PMSourceFileDir\$PMWorkflowName_stat.txt

Note : 

• Pre/Post session commands need to be changed based on Informatica server operating system.
• Highlighted part of the script need to be change based on the source, target table instance name used in the mapping.

III. Reusable session

Reusable Mapping

A reusable mapping will be created to read data from the comma delimited file generated by the pre/post session command task. Below is the mapping created with an expression transformation to populate additional columns required in the target table 'ETL_PROCESS_STAT'

• ETL_RUN_DATE :- TRUNC(SESSSTARTTIME)
• ETL_CREATE_TIME :- SESSSTARTTIME
• ETL_UPDATE_TIME :- SESSSTARTTIME
• ETL_START_TIME :- TO_DATE(LTRIM(RTRIM(ETL_START_TIME)),'YYYY-MM-DD HH24:MI:SS')
• ETL_END_TIME :- TO_DATE(LTRIM(RTRIM(ETL_END_TIME)),'YYYY-MM-DD HH24:MI:SS')

Reusable Session

A reusable session will be created based on the mapping created in the last step. Session will be configured to read data from the file created by the pre/post session command as shown below.

Note : Make sure the Source File Directory and Source File name are given correctly based on the file generated by pre/post session command

Framework implementation in a workflow

Shown below is a workflow using Operational Metadata logging framework. Pre/Post session command to generate file with session run details will be in the first session, The reusable session will be connected after to read data from the file and load into ETL_PROCESS_STAT table.

XML Analyzer for Informatica PowerCenter

It is my pleasure to present to you the

XML Analyzer for Informatica PowerCenter

This is a tool created to fulfill the following purposes:

• Create Source-to-Target dependency analysis
• Create Technical Solution Documentation
• Perform automated code validation

All in an automated fashion and with (almost) a single click. The tool is now available in few flavours:

• Online tool - available without installation, anywhere you'd need it.
• Server version - you can have your own instance available in intranet. Available to all developers within the company without having to send code over the Internet.
• Offline executable - a simple executable tool that allows to generate html Technical Solution Documentation in batches.

All of the above versions are general in their purpose. They cover sample checks and report some general issues if found. Feel free to contact me if you'd need a tailored solution, covering company- or project-specific validation and reports. Use the features listed to come up with any requirements that would suit your business or technical requirements.

Source-to-Target dependency analysis

Here's a description of how it has all started and what was the original goal fot the tool. Or go directly to this source-to-target analysis sample table where you can see a sample mapping analysis. All Source columns with their corresponding Target columns. You can easily check what target colums are influenced by a given source to perform impact analysis, as well as check where do the values come from for any given target column. You can click on column names to sort the table. Shift+click for multiple selections.

Technical Solution Documentation

The Source-to-Target analysis is performed for all mappings found withing the Workflow XML file. This - along with workflow, worklet (if any) and details for all the sessions creates a technical report covering all implementation details. All available at a glance, with a table of contents, can be used to create documentation for whole project.

Automated Code Validation

While creating the report, different checks are performed to ensure the high development quality. This is very helpful in large scale projects with many developers involved and allows all team members to follow the same practices. All developers can have their code quickly checked to make sure there are no typos in log file names, or any debugging-mode options left on before having their code released to production environment. Here is the list of current check performed by the tool. As said, it can be easily expanded to accomodate any additional quality tests - please contact me if you'd need a dedicated solution.

• Comparing workflow log file with workflow name
• Checking if a session is valid
• Checking if a mapping is valid
• Tracing level
• Overridden session properties
• Hardcoded connections
• Comparing session log file with session name
• Checking if session is not DISABLED
• Expression containing comment (--)
• Filter condition containing comment (--)
• Router group expression containing comment (--)
• Lookup SQL Override containing comment (--)
• Source Qualifier SQL Override containing comment (--)
• Connections with names containing 'tmp', 'temp' or 'test'

Check the main XML Analyzer page for up to date feature list!

PowerCenter Best Practices

Introduction Below you’ll find some simple rules one should consider when building PowerCenter Workflows. This list may be further extended - it definitely doesn’t cover everything. And it probably never will. However it may be further extended to include other Best Practices, examples and explanations. It’s also possible that some of the guidelines below may be reviewed and altered anytime in the future. Feel free to read, ask and comment. Keep in mind that the author holds no responsibility for whatsoever :) General Limit the number of rows as early in the mapping as possible (SQ, aggregators, joiners, filters) - the more you limit at the beginning of the data flow, the less rows will be processed by all downstream transformations. For generating IDs use IDENTITY columns or DB triggers. There is a number of reasons: fewer PowerCenter objects reduce development time and maintenance effort IDs are PowerCenter independent, so any other app used to populate the target table will not cause any issues while migrating the code between environments there is no need to worry about the sequence value If for some reason you need the ID on PowerCenter side, use Sequence Generator rather than Stored Procedure call for performance reasons - Sequence Generator is really fast as it simply gives you numbers in IS memory. Stored Procedure call requires connecting to DB engine, invoking the Stored Procedure, and sending the number back to IS. The communication overhead is huge and may heavily impact performance. This is also a factor of the connection speed and distance. If you’re afraid of losing the generated sequence number (which might happen when exporting/importing xml without the Retain Sequence Generator Value option), store the last ID in DB and use it as a start value adding to the sequence and update it with the last ID generated this way. There should be no unused ports in any transformation (except Source Qualifier) - Source Qualifier should match Source Definition 1-to-1. But for any other transformation there should be no unused ports. Why fetch and process the data for half of the data flow and use extra memory, if you don’t need it? Avoid implicit conversion, as it is slower and might lead to errors - There are number of issues with implicit conversion (e.g. when you link SQ string port to decimal port in the following expression): it’s simply not visible. Anyone checking the mapping will not notice that there is any conversion of datatypes the conversion might behave not as expected (e.g. roundings, trailing/leading spaces or zeros, etc.) there may be unhandled conversion errors Therefore use appropriate conversion functions (e.g. TO_DECIMAL) explicitely. Remove all the 'Error (Transformation error)' messages from default values in Expression transformations - you will avoid getting unwanted error messages in logs. It will not be checked at session initialization. Consider adding Expression transformation right after each SQ and right before each Target - pass-through expressions do not cause additional computation. But I couldn’t count how many times I had to make “little change” before or after the whole data flow in an existing mapping. Having this dummy expression transformation helps a lot. It’s also very useful when relinking all ports after some changes (using Autolink by name). Aggregators: Use as soon as possible - it limits the number of rows processed by any transformation downstream. Use 'Sorted Input' only if possible, but do NOT sort just for aggregation - This one is a bit tricky. Aggregator with a “Sorted input” property works faster and uses little cache. But aggregating data does not require sorting. Therefore, if dealing with unsorted data, Aggregator (without Sorted Input, large cache) will perform better than Sorter (large cache) + Aggregator (with Sorted Input, small cache). If you do add The Sorter, the cache will not disappear - it’ll be created for the Sorter. Adding Sorter is a good idea only if you need to sort the data anyway. Expression: Use local variables for common computation (especially if complex) - If you need to perform some computation for many output ports (e.g. datatype conversion on one of the input ports), you can define a variable port doing it once (e.g. TO_DECIMAL(input_port)) and use the port in expressions for the output ports. This way it will be done once. This is especially important, when the common logic is complex, because: the performance gain is even grater it’s easier to maintain - there is one place you need to check for any errors and implement any changes Avoid local variables if no code reuse opportunity is present - If the output port does some computation and it is not common for other output ports, use the output port expression. Do not add a variable port. Input -> Variable -> Output makes the expression not visible from Mapping Designer (i.e. without opening Expression Transformation window) Filter / Router: Any complex expressions should be done in an Expression transformation before the filter / router - it is very important in case of Router. Calculate once, then compare - instead of calculating many times. Remember, that for every input row, all group expressions are checked (Router can have many output for one input row). It is a good idea to do the same for Filter, as for example you can use variable ports in Expression while it is not possible in Filter. Keep in mind that router executes all expressions against each input row Floats: When to use? Only for Physics, where accuracy is not that important. Keep in mind that floating point numbers are not accurate! E.g. 0,1 is stored as ~0.0999999999999999991, which is very close to 0,1, but not equal. You can end up having 0 kept as -0,00000001 (real-life example). This may cause a lot of trouble when selecting all rows greater or equal to zero. For any financial, quantitative - almost any calculations use decimals. Lookup: Should have only the ports that are being used - The more ports in Lookup, the more data is fetched. It takes more time and memory to build the cache. Limit the number of rows fetched using filter - Use the Lookup Source Filter. This will limit the amount of data to fetch and keep in memory. Parameter files: Use one parameterfile per workflow - There are many ways to use parameters and parameter files. You can define General parameters for all sessions in a workflow. You can have each session use it’s own parameter file. What is the best way? There is no simple answer. You might need different setup for some purposes. However in general it’s good to have one parameter file per workflow. You won’t affect other workflows when changing the parameters. The file will be quite simple (in most cases). Consider using auto generated parameter files using values stored in DB - Workflows can start (or end) with a generic session creating the parameter file. This would allows easy maintaining all parameter files, parameters, do any checks for naming conventions, etc. Sorters: Avoid if possible - do not sort the data unless it is really needed. Use as soon as possible - if the sorting is needed, sort data early in the mapping and try to take advantage of it using Sorted Input for as many transformations downstream as possible. Use as long as possible - Try to arrange the mapping in a way that all transformations sharing the sort type come one after the other. Avoid resorting separately for every transformation. Take advantage on DB sorting if covering index is available - If covering index is available in DB, sort the data using Source Qualifier and use Sorted input for the transformations downstream. Don’t: Sort just for aggregator - Although Aggregator works faster and doesn’t use caching (almost) with “Sorted Input” property checked, it is not worth sorting data just for aggregation. This will just move the need for cache from Aggregator to Sorter and in fact will slow down the overall performance as the aggregation algorithm doesn’t require sorting. Sort for joiner (especially each sorter!) - Do not use two Sorters for Detail and Master groups of Joiner Transformation. In such case both pipes will require caching while for unsorted data just the Master input is cached. Source Qualifier: Connect only the necessary ports from SQ - Source Qualifier determines what really needs to be fetched from source. While it should match Source Definition 1:1 with all ports linked (to avoid future mistakes and misunderstandings), it reads only the data for ports linked to the next transformation. This can be observed while generating SQL statement (Properties->Sql Query->Generate SQL) - only the used ports will be listed in SQL statement. This is especially important for wide sources, as this way the amount of data transferred can be limited. Avoid using SQL override statements - Using the Generate SQL property overrides any and all other settings (i.e. port order, sorting, filters). This is also not visible at the first glance and reduces the code transparency. This should be therefore avoided. If you need to: sort the data - use "Number Of Sorted Ports" property with the proper ports order filter the data - use "Source Filter" property join multiple homogeneous sources - use "User Defined Join" property SQL override should be used only when some complex statements are needed (e.g. use of DB-specific functions or subquery) The above is also applicable (in general) to lookup transformation. All ports between Source Definition and SQ should be connected - As mentioned above, it is good to have SQ matching Source Definition as it reduces the number of future mistakes. All the datatypes should match Source Definition - In general implicit datatype conversion should be avoided as it may lead to errors and unexpected behavior. Update Strategy: Don’t use DD_REJECT - Don't process unneeded data (unless really needed in .bad file, use "Forward Rejected rows"). Otherwise use a Filter transformation to drop unneeded rows of data. Sorting: DB or Sorter transformation? When considering performance, a case-by-case analysis is needed. There is no “best-for-all” approach. Database as well as network performance must be considered. Database and Integration Service load must be considered. Database setup must be considered. Finally, Database and Integration Service hardware configuration must be also considered. There are however some general rules that can be applied: Use DB if covering index is available - Try to take advantage of existing DB indexes. It happens quite often that the source tables are indexed and all that needs to be done is setting the appropriate order of the ports in Source Qualifier and the “Number Of Sorted Ports” property. If not: Check if it's possible to create covering index - Sometimes the DB index is not available, but can be created. The advantage of it is huge enough, that It’s always good to check. Of course there are scenarios, where you can’t afford to use an index (e.g. there are lots of inserts / updates on a table with rare selects). Investigate if it is possible - if not: Use Sorter transformation - If no index on source is available and data needs to be sorted, use a Sorter transformation. Keep in mind the remarks discussed above.