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.

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

Change Default Formating in WEBI

In order to change default formating in WEBI XI onwards do the following

1.
Edit the configuration  file defaultConfig.xml which resides at the server uder business objects installation  e.g C:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\classes\AppletConfig\defaultConfig.xml. If you are using rich client you must change the local file.


2.
The thing I want to point out here is the BACKGROUND setting. You see, in Web Intelligence, you cannot set the cells in a table to be transparent. That’s always bugged me. However, you can set the background to be transparent by default by changing the BACKGROUND line to the following:
<BACKGROUND R=”" G=”" B=”"/>

If you do this, you probably want to do the same for the next section, alternate body cells in a table (Alternate Row Shading), so the entire body of the table is transparent. After making this change, your table body will be transparent by default, but you can add color, to the background if you choose.
As you scroll down through the file, you see many things that you can customize in your formats. But one of the coolest options is at the bottom. Scroll down to the bottom, and you will find the settings for the skins. The built in skins are, in my humble opinion, ugly. Well, you can add your own skins. The skins are stored as images in the following folder:

C:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\images\
I added a new image to this folder called globe.jpg. Then I added the following toward the bottom of the file:

<KEY VALUE=”cell_skin5,report_skin4,section_skin5,bloc_skin5?>
<SKIN NAME=”Globe” TYPE=”stretch”>
boimg://globe.jpg
</SKIN>
</KEY>

Now, when I go to select a skin for a report, Globe is listed among the options.

Source http://michaelwelter.wordpress.com/ 

Faster WEBI XI Reports

::
:: Faster WEBI Reports ::
::

<< Universe Level >>

* Modifying the Array Fetch parameter (advanced parameters window of connection. Just edit connection in Universe and click twice Next. or we can control this parameter in CS.CFG file. Bigger number = faster query but it should be set up according to capacity of memory)
* Allocating table weights
* Using shortcut joins
* Using aggregate functions
* Creating and using aggregate tables
* Minimizing usage of derived tables

<< Report Level >>

* If you insert the Page Number/TotalNumberOfPages cell or the TotalNumberOfPages cell into a large report with many pages, Web Intelligence takes longer to return the data to the report.
* Opting for Refresh At-Will over Refresh-On-Open
* Not associating the List of Values (LOV) Objects which you don't want to use as a prompt
* Using universe Condition Objects, try to restrict/filter the data at universe end rather than at the report end.
* Keeping Complex Calculations at universe side.
* Minimizing usage of Report variables/formulas
* Auto height and auto width on report cells sucks in resources. setting cell sizes to fixed increases performance.

<< CMC Level >>

* Increasing Maximum Allowed Size of cache (CMC / servers / service categories / webi / Properties of webiprocessing server / Maximum Document Cache Size (KB) <<---- increase the default!!!)
* Increasing Minutes Before an Idle Connection is closed
* Increasing File Polling Interval in seconds
* Adjust Connection timeout, keep it as small as possible (webi report server attribute)

<< Config File Level >>

* freeing up unused system resources
keep the session time out as small as possible (default is 20 mins)
--XIR2--
for Java: web.xml file found here:
"..\Tomcat\webapps\businessobjects\enterprise115\desktoplaunch\WEB-INF\web.xml"
by changing the <session-timeout> 20 </session-timeout> entry.
--XIR2--
for .NET: web.config file found here:
"..\Business Objects\BusinessObjects Enterprise 11.5\Web Content\Enterprise11\InfoView\Web.config"
by changing the timeout=" 20 " entry.

* Changing the SQL works for specific RDMBS.
By default, Business Objects (Ver 4.1.3 Onwards) sorts the tables in the
‘from’ clause in ascending order of the row count i.e., the table with
minimum number of rows is placed at the beginning. Sybase uses the same order as Business Objects and so there is no effect on performance. But Oracle sorts the tables in descending order of the row count. So the table with maximum number of records is placed at the beginning. Oracle's rule-based optimizer looks at the order of table names in the FROM clause to determine the driving table. For optimum SQL execution in Oracle, the ‘from’ clause should be in order from largest to smallest which is the reverse order of that of BO and so affects the performance of the BO reports.Resolution: Table weight is a measure of number of rows in a table. To change the default Business Objects settings, the ORACLE PRM file must be modified as below:
• Browse to directory Business Objects\BusinessObjects Enterprise
XI R2\dataAccess\RDBMS\connectionServer\oracle.
• Open ORACLE.PRM file, change the REVERSE_TABLE_WEIGHT
value to N from Y.
Snippet from oracle.prm file:
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE DBParameters SYSTEM
"../dbparameters.dtd"><DBParameters>
<Configuration>
<Parameter Name="REVERSE_TABLE_WEIGHT">Y</Parameter>
<Parameter Name="BACK_QUOTE_SUPPORTED">Y</Parameter>
<Parameter …
The SQL from clause of all Business Objects reports is now reverse weighted, largest to smallest rows (refer ‘After’ in figure 2.2). Now both BO and Oracle uses the same sort order and so the BO Report takes less than 10 seconds to open.

<< Server Level >>

Horizontal and Vertical Clustering. Load Balancing and Failover.

<< Hardware Level >>

Better hardware. All BOE related servers & DB Servers within the same LAN --if possible--. Gigabit Connections between servers. DB and BOE Servers on physically separate machines.

<< User Requirement Analysis >>

Stick to 6Sigma --if budget allows--. Rest of the approaches are destined to fail.

<< DW Background >>

Learn Datawarehousing methodology. There are many exchellent books on the subject.

<< Other >>

for .NET in IIS minimize the idle time out. (Application pool with Infoview, Properties, Edit the Shutdown worker processes after being idle for (time in minutes): on the Performance tab). Restart IIS, Webi App server, and Webi Rep server.


You can check the full thread at http://www.forumtopics.com/busobj/viewtopic.php?t=155190