Informatica PowerCenter Primer: Default and Null Values

Following is some basic Info about the behavior of informatica PowerCenter regarding default and NULL values.

Starting with default values, there is a major distinction on how Informatica PowerCenter treats expression ports versus mapping variables/parameters.  Expression ports with no value are being assigned Null values during initialization, where mapping variables are being given a default value depending on their datatype.
More specifically, Ports per datatype

And default values assigned to variables per datatype

It might seem basic but you can avoid many well hidden bugs that more than often occur based on that trivial information.

Another caveat regarding Null values and PowerCenter is the difference in behavior between"DECODE" and "IIF" expressions when evaluating NULL operands.

"IIF" expressions return FALSE,  i.e: 
IIF(NULL=NULL, 1, 2)  
returns 2. 

On the other hand "DECODE" evaluates Null operands as equal,  i.e:
DECODE(NULL, NULL, 1, 2)
returns 1. 
That seems different to a common RDBMS's decode expression, since SQL returns false on nulls decode i.e:
SELECT CASE WHEN NULL = NULL THEN 1 ELSE 2 END 
Returns 2

The above behavior can be customized by fiddling with the TreatNullInComparisonOperatorsAs at the corresponding integration service properties. 

The aforementioned option change the behavior of sorters and aggregators as well. It is best advised to take care of this behavior on the mapping itself and not depend on server specific settings that can be different between environments. 

BI 4.0 Mass Query Refreshes without any queries being scheduled

This can be the compendium of bugs or the pinnacle of features. It resides on the eye of the beholder. Nevertheless it can be from annoying to dangerously close to halt your production DW DB. You can say that business objects are trying to refresh every single LOV in a universe by looking at the queries that are being send at the target DB. All of them should include the magic Clause: Distinct.

The source of all that hassle is an application that scans the system and maintains an index for search porposes from within BI Launch pad (formerly known as InfoView). The Platform Search application's options can be found  at CMC. Log in to CMC. Go to Applications. 

Then open platform Search application options. The solution to the issue is simply to change the default options for the crawler.

The Platform Search application is configured by default to "Continuously crawl".

Further more the Scope of Indexing is set to Full Content and all resources are being checked.

Change continues crawl to schedule mode and select a time appropriete (e.g during the night cycle). Further more you can deselect universes and change full content to Platform metadata only. The final result should be something like that:

INFORMATICA Error connecting to the DTM at [%s : %d]: [%s]

When you are trying to debug via Informatica Powercenter Developer there is a chance that during debug initialization you might get:

Error connecting to the DTM at [%s : %d]: [%s] 
Server execution failed, forcing shutdown of the debugger... 
Debugger shutdown complete. 

The first step towards the resolution to this common issue resides at the hosts file.

Thus open hosts file under c:\windows\system32\drivers\etc\ and enter the entry corresponding to Powercenter Integration Service Node

xxx.xxx.xxx.xxx <Full Domain Name>

Most of the time this will resolve the issue. Please note that you need to add the Full Domain Name and not just the Hostname.

If the aforementioned solution does not work then check the ports that you are using for the debugger, maybe there is a firewall blocking the ports or the port is in use).
At designer Navigate at Tools>Options>Debug Tab.

You can change the ports used to communicate with integration service during debug and test again.

If all of the above fails, its time to put some custom properties out of our toolbox. 

Open Admin Console, selected the appropriate integration service and click Edit under the custom properties band. 

The click Add and enter the following Name/Value pairs:
Name: DebuggerDTMHostName  Value: <The name of the server as it is declared in HOSTS file>
Name: DebuggerMaxPort             Value:<min port e.g 11326 >
Name: DebuggerMinPort              Value: <max port e.g 11226 >

The result should resemble something like that:

Make sure that the values for min and max ports are matching those defined at the previous step (under tools>option in Designer) and there are free ports among them, you can check port usage with netstat -an or use a utility like TCPView.

Finally restart the Integration service.

Debug Window in SAP BI 4.0

This quick tip is applicable on SAP Business Objects 4.0 Webi and Rich client as well. It enables a hidden debug window to be shown, containg information about the current BO session. It is mainly design to debug when working with the Java SDK but its cool to know that is there even when you dont actually develop with it.

The aforementioned debug window can be shown by holding down left shift and moving cursor down left, just before the tab name.

If you are using webi be sure that you are at design mode before trying to enable debug. At the displayed window you can see:

The first (and most) usefull thing that you can see its the allocated memory and the heap size. This information can be also utilized in sizing endeavors with respect to report data / Server memory allocation.

The report output tab displays the report output as HTML generated by the server.

History information coresponding to the events triggered.

History of user actions on the document

The component-level paint event. This event is a special type which is used to ensure that paint/update method calls are serialized along with the other events delivered from the event queue.
A Paint event procedure is useful if you have output from graphics methods in your code. With a Paint procedure, you can ensure that such output is repainted when necessary.

SAP BOXI, BI 4: Formating Universe Descriptions using HTML

As consultants we want to keep our customers (AKA End Users) happy. Their main point of contact in a Business Objects environment its the universe. So its only natural to provide efficient documentation on universe objects.

Since the desktop intelligence is out of the scope (BI 4) we can now focus on enriching web Intelligence experience. One great way of doing so its formating object descriptions at universe level by utilizing HTML tags like <font>, <b>,<br> even or you can even include IMG tag to add some pictures :-)

Open Designer and alter the description of an object. 

Expiriment with varius tags i.e:

<font face="verdana" color="red" size="5">
<b>Description:</b><br>
Sin City is an urban area (a city or part of) that caters to various vices.These vices may be legal (depending on area) or illegal activities which are tolerated.</font>
<hr />
<font face="verdana" color="black" size="5">
<b>Example:</b><br>
perhaps the most well known example being the Las Vegas metropolitan area, USA.</font><br>
<img src="http://upload.wikimedia.org/wikipedia/commons/thumb/8/8b/Las_Vegas_Strip.png/320px-Las_Vegas_Strip.png" alt="Sin City of LA" height="70" width="100" />



And you can go from this:

To that:

SAP Business Objects Change of Priorities, Mobile First!

It seems that SAP its going to prioritise development towards mobile devices than the clasic PC platform. SAP's vision concists of three distinct user experiences


Structured mobile analytics experience
AKA existing mobile applications.


Semi-structured mobile analytics experience 
Focused on new content. Right now we have SAP BusinessObjects Explorer.  Its going to be enchanced in 2012 and the name its going to change to high-definition analytics.


Open mobile analytics experience
This is the only new addition. According to SAP at this user experience slot we are going to see simple design tools  where a user can define any mobile analytic/BI app he or she dreams up, like having a mobile analytics design studio.


Finally SAP BusinessObjects Dashboards (the product formerly known as Crystal Xcelsius) its going to be launched  in HTML 5 version. Its clear that we are going to enter the post-flash era late this year.


Bellow you can find a slide with Sap's mobile BI Roadmap. 

Source: Putting Mobile First and the New Business Intelligence Priorities

INFA: How to convert binary fields to series of 1s and 0s

There are some cases that require to convert a binary field to a sequence of bits. 
Lets say that we have a source system that stores a series of flags in just one field by using a binary, data type. This is a common way of storing flags in ERP systems since you can later add more fields without altering the DB schema, by just utilizing unused bits. During ETL we want to convert this binary datatype to a string so we can check the value that a position has.

e.g convert to 01000000 and then get the value at position 2, at this example 1 denotes that the customer is active.

An easy way to achieve this, is by using the ENC_BASE64 encoding function (introduced at Infa 8.x).
Base64 is an encoding scheme that represents binary data in ASCII characters. It is used mainly for binary data transfer through emails. Lets see some basics about base64 Encoding.

Base64 Primer

1. "=" at base 64 encoding stands for filler char that has zero value.
2. Encoding and decoding of base64 algorithm is based in sets of six bits thus the filler charachters when appropriate.
3. Every ASCII Char at the BASE64 sequence has a coresponding value that stands for (see Char Sequence table below.)

Building the reusable transformation

The whole process consists of two distinct steps:

1. Encode the incoming Bynary data to BASE64 format
2. Decode the emerged data to a string of bits

The above steps are going to be encapsulated at a reusable transformation. The aforementrioned expression transformation accepts as input a binary field (binary(4) at this example) and returns a varchar(32) that contains the bit series. As a plus i added an optional input port. That if feeded with 'Y' reverses the bit sequence.

Now lets start with the theory behind the implementation. You can download the transformation at the end of the post.

All the above steps are being encapsulated at a reusable transformation that you can download and try it Here.