Sunday, 16 August 2015

Use of the property FORWARDING REJECTED ROWS in an Update Strategy transformation??

When we use the update strategy transformation we have the option to reject the row based on a criteria. We usually use DECODE or IIF to specify the criteria for rejecting the row[Treat Source Row should be selected as Data Driven at the session level]

IIF( ( RESIDENT_SK=0), DD_REJECT, DD_UPDATE )

The following table lists the constants for each database operation and their numeric equivalent:
Insert
DD_INSERT (Numeric Value 0)
Update
DD_UPDATE (Numeric Value 1)
Delete
DD_DELETE (Numeric Value 2)
Reject
 DD_REJECT (Numeric Value 3)

Now coming to the Forwarding Rejected Row Option in update strategy, this option is used to forward the rejected rows into the bad file which we define at session level. If you disable this option, the rejected records won't be available in the bad file (reject file).

However the rejected rows will be available in session log (Normal logging level) even though the forwarding rejected row option is enabled\disabled.

These records in the reject file help us to understand the reason for rejection and validate the records not satisfying the business requirement. This helps to change the input data the next time we load the target

Structure of Informatica Bad Files or Reject Files
There are two types of Indicators in the reject file. One is the Row Indicator and the other is the Column Indicator.
List of Values of Row Indicators:
Row Indicator
Indicator Significance
Rejected By
0
Insert
Writer or target
1
Update
Writer or target
2
Delete
Writer or target
3
Reject
Writer
4
Rolled-back insert
Writer
5
Rolled-back update
Writer
6
Rolled-back delete
Writer
7
Committed insert
Writer
8
Committed update
Writer
9
Committed delete
Writer

Saturday, 27 June 2015

Calculated Percentage Column always showing result as 0% Zero Percentage in OBIEE 11g

You may also face similar issue while upgrading OBIEE to a newer version or if you upgrade some of your dashboards from OBIEE 10.1.3.4 to 11.1.1.5. The issue is that, some of the calculations under certain columns in the reports were showing zeros instead of the calculated values.  The requirement was to calculate a match rate % for some data based on another field that showed 'MATCH' or 'NO MATCH'. 
 The real-time scenario was
(SUM(CASE Table1.Column1 WHEN 'MATCH' THEN 1 ELSE 0 END)/COUNT(Table1.Column1))*100

However, this was returning 0% as the result. With the help of some blogs and questions raised on OBIEE threads I came to know that this happens when you have an integer in a calculation. The solution which I used is very simple and was successful in achieving the requirement. The very easy fix to this issue is to multiply the calculation by 1.0.  The updated calculation which works is shown below and provides a meaningful result.
(don't use 1, as this will not work.  Use 1.0)

((1.0*SUM(CASE Table1.Column1 WHEN 'MATCH' THEN 1 ELSE 0 END))/(1.0*COUNT(Table1.Column1)))*100
Related Posts Plugin for WordPress, Blogger...

ShareThis