Business Objects: Drill on a union demistified

We can have a single data provider (DP) incorporating a number of queries with union(s), but we already know that.

We also know that we can enable drill on such a report. There's just one caveat, the mechanics of drill on a DP that utilizes unions can be a little confusing. The confusion emerges from the fact that business objects take a different course of action during drill on a union enabled DP.  Let’s examine this peculiarity.

Assume that we have a data provider combining two different data sets (queries) from a single universe with a union. The first one gets sales on a regional structure for Europe while the second accumulates results regarding the USA area. Thus we have two different queries with two discrete hierarchies.

The schema on our universe for the aforementioned scenario is depicted in the following figure. It’s a single fact table with two distinct dimension tables, another variance of the same situation can occur when we have two separate star schemas. 

We create the data provider and combine the two data sets in Webi.

First the EU part:

Then the corresponding  USA query:

If we opt to leave the extra information (e.g city) out of the basic query and allow the report consumers to fetch detailed information as they see fit then we are in for a surprise. While Business Objects "knows" that each query has a different hierarchy (you can check this by changing the scope level) it performs drill down by selecting the next dimension of the hierarchy only for the first query and tries to apply that Dimension to the following queries as well.

In this way when we try to drill down at city level for USA (on the fly) we are going to get unexpected results!

Actually BusinessObjects are traversing the hierarchy for the first query and appling the resulted dimension  (in our case the EU City) to the rest of the queries participating the data provider as well. We can rectify this issue by going back to the data provider and explicitly set the scope of analysis for each query:

Needless to say, that this forced option outwit any decisions taken to make the report more responsive (e.g. late loading of details).

The above scenario holds true for Business Objects XI R3 SP5.

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