More tips & tricks:

Siebel Analytics

Oracle

SQL Server

Contact ClearPeaks:

ClearPeaks (headquarters)
Valencia 266 3, 1
08007 Barcelona, Spain
Tel +34 93 272 1546

Editor
Angela Carson

Contributors
Alex Caminals
Emma Gorgori
Gianluca Nacci
Oswaldo Salazar
Jose Antonio Hernandez
Moïse Kameni

Simple workaround for a ‘where’ clause in 2nd-tier variables
Author: Emma Gorgori

Creating a chart with calculated variables

In this tip, we will create a chart where one of the variables of the axis is calculated.  The calculated variable is based on a measure from the universe and contains a formula, in our case a ‘where’ clause.  Since our chart will be built using a 2nd-tier variable, we need to find a workaround in BusinessObjects in order to create our chart as requested by management.

For our example:

• Measure from the universe: Total Net Revenue  in US$
• Calculated variable i (to obtain the K$ variable): Net Revenue K$ = <Total Net Revenue in US$>/1000
• Calculated variable ii: EMEA Net Revenue
• Formula applied (using a ‘where’ clause) to obtain the calculated variable ii: =<Net Revenue K$> where (<Region Name>="EMEA")
• Dimension required: Region Name
• Values returned from the dimension: EMEA, Americas, Asia Pacific

The project

Management has requested a chart to show the top 20 accounts based on Net Revenue K$. The following variables will be used to create the chart:

• Account Name (Dimension)
• Net Revenue K$ (Measure)

The slice and dice panel shows how the top 20 rank is calculated. The ranking formula is applied to the dimension and based on the measure:

The results for our top 20 formula are displayed in HTML format.

Since our requirement is part of a regional dashboard, our manager also wants to see the top 20 accounts within our region.  To do this, the top ranking formula is changed to base it on EMEA Net Revenue variable. This EMEA Net Revenue variable is created by applying a ‘where’ clause to Net Revenue $, which in turn is not a direct measure from the universe, resulting on what we can call a 2nd–tier variable. 

Since our chart will be built using a 2nd-tier variable, the only modification required in the chart is to replace the measure variable in our top formula.

The problem

One immediate problem we see is that the EMEA Net Revenue variable does not show up in the drop down list of variables that can be applied to a top ranking formula.  This is an issue with BusinessObjects and we need to find a workaround to fix it in order to create our chart as requested by management.

Note that our EMEA Net Revenue variable is not in the drop down list.

Solving our problem

We can conclude that our top ranking formula only works for 1st-tier variables. For this reason, the top ranking formula does not work with our variable that uses a ‘where’ clause based on a variable that is derived from a measure.

Steps to resolve this issue:

• Edit the variable and remove the ‘where’ clause, leaving only the first part of the formula. That is, edit “EMEA Net Revenue=<Net Revenue K$> Where (<Region Name>="EMEA")” in order to convert it into the new variable “EMEA Net Revenue =<Net Revenue K$>”.

After modifying the variable, the EMEA Net Revenue variable now appears in our drop down list of variables, allowing us to apply a top ranking formula:

Now the EMEA Net Revenue variable appears as part of our list of options.

• Apply the ranking to this variable and close the Slice and Dice panel.
• Go to the variable editor and modify the EMEA Net Revenue variable formula, to convert it back to the initial variable:

5. Validate the results in our chart.

Final chart results of the EMEA Net Revenue variable now appear correctly after applying our workaround to the variable:

• It is possible that after tricking BusinessObjects with our workaround that it appears there is no variable used at all when checking the top ranking formula.  This is because we have changed the original variable used in the top ranking formula. Even if we don’t see the variable, this is understood in the chart and correctly applied in the ranking.

When editing the top 20 formula, the “Based on” option is empty:

Conclusion

Although it is true that problems can arise from using a Top ranking formula applied to a 2nd-tier variable in Business Objects, it is possible to overcome this conflict by creating a custom “workaround” and essentially tricking Business Objects.

Go to top

Say goodbye to pivot tables by super-charging your .txt (or .xls) files
Author: Jose Antonio Hernandez

We are all familiar with the benefits and user-friendliness of BusinessObjects (BO) when it comes to working with databases.  This is particularly true if you happen to be a sales (or finance, or business, or purchasing or anything-without-IT-in-the-title) manager.

What isn’t as commonly known is that this same power and analytical simplicity can also be used with something as humble as a .txt or .xls file. For many business managers, Excel is the default choice and pivot table is the buzz word. The truth is that BO beats Excel in power of analysis and versatility, especially if your file happens to have 65,537 lines!

To demonstrate this point, let’s use an excel file with the daily platinum quote, in Euros, since January 2000.  In this BO Tips & Tricks example we will:

1. Load an excel file with the platinum quotes into BO.
2. Create a variable: “Quote Month”.
3. Create a report with the average quote for each month.

Load the excel file

The first thing we do after opening BO is create a new report:

Select Generate a standard report:

Specify Personal Data Files as the Data Access and click finish:

Then specify the type of file to load (excel in this case), where the file is located, and if the first row contains the name of the columns. Click on view to make sure that the data is loading correctly. If the data looks correct, click “OK”:

To change the properties of the data source (PD1 in Platinum EUR.xls) and the different objects (Date, EUR per oz), click on the Definition tab and then in each of the objects, changing the following properties:

• Rename data source to Platinum Price (much more simple than PD1 in Platinum EUR.xls)
• Set the EUR per oz “Aggregate this Measure” to “Average”
• Rename the Date object to Excel Date

Once satisfied with all the changes, click “OK” and… Voilá! The first phase of our BO Tip & Trick is complete.  We have managed to load the data file into BO and create our first report:

Create a variable called Quote Month

In order to aggregate the dates by month we will use a date field. The Excel Date field loaded from the file was set up automatically, in this case as a character field. Therefore, we will create a date variable from the Excel Date with the name Quote Date.

To do this, select Data from the main menu. Then click on Variable. A new window will open; click on “Add Variable”:

A new window will open.  Complete the information for this new variable:

Denifinition tab > Name = Quote Date.  Be sure to select the Dimension field:

Formula tab > =ToDate(<Excel Date> ,"dd.mm.yyyy"):

Next create a new Month variable in order to aggregate the data at month level (handier than the nearly 2000 lines we currently have).

Repeat the previous process to create a Quote Month variable with the formula: =FormatDate(<Quote Date> ,"yyyy-mm"). In this way, data will be automatically sorted by year and then month:

Create a report with the average quote for each month

Now, go back to the original report and show only the Quote Month and the EUR per oz.  The information will collapse at month level and the EUR per oz field will display the average of all the quotes for a given month (this is because in step 5 of the section “Load the Excel file” we requested BO behave this way). We can now use this information, amongst other things, to create a simple trend:

As you can see, creating sophisticated reports in BO from data files is as simple as a 1-2-3 process. Although it might seem trickier than pivot tables at first sight, we assure you that after you have created a few reports, the process will become as easy as speaking your native language!

NOTE: This information was sourced from oanda.com a currency trading online tool.

Go to top

The ABCs of ZABO
Author: Moïse Kameni

1. ZABO Overview – Q&A

ZABO stands for Zero Administration BusinessObjects. It is a mix between full and thin client architecture in which you have most of the BusinessObjects full client capabilities with no client administration.

ZABO combines the full client power of BusinessObjects with the web-based architecture of WebIntelligence (ease of tool access as well as deployment).  BusinessObjects in 3-tier mode enables you to view BusinessObjects (.rep) documents via a web browser. 

Some features and limitations:

- Save documents as *.rep, *.pdf, *.xls, *.txt, *.rea, *.bqy, *.htm etc...

- ZABO can be installed from a CD or via the web.

- No ZABO client on UNIX.

- No Data Access Pack on the client required for ZABO to work.

 Web installation limitations:

- No OLAP, FreeHand SQL or Stored procedures data access.

- No VBA automatically installed.

How are reports handled?

When you open a full client report in ZABO, a copy of the document is downloaded from the server and stored in the client machine.

How are universes handled?

Similarly to documents, a copy of the universe is downloaded and stored in the client machine.  The universe is downloaded to BusinessObjects\Universe\ZaboServerName\Universe.

How are queries handled?

1. The queries are generated on the client like any BusinessObjects report.

2. The SQL is then sent to the ZABO Server through HTTP.

3. On the server, WIQT processes the query and connects to the database.

4. The results are sent back to the client through HTTP and the report is created.

2. ZABO Installation

BusinessObjects in 3-tier mode can be installed on a client machine either through the standard BusinessObjects installation CD or by downloading it from InfoView.

If the Business Objects server is upgraded, users are automatically prompted to upgrade their version of BusinessObjects on their desktop.

What are the differences between the 2 installation modes?

ZABO is designed to be installed over the web but it can also be installed from a CD.

Please keep in mind that by doing a web install for ZABO:

• There is no OLAP data source access
• There is no VBA automatically installed and MS Office VBA not compatible
• Stored procedures not available
• Free-hand SQL not available

Are there situations where ZABO cannot be downloaded?

Yes, the user should have registry write permissions on his machine in order to download and install ZABO.

Installing from ZABO Server (Via Infoview)

To install ZABO from the ZABO Server, you will need to have WebIntelligence Reader rights, and have previously installed the Web Installer Component (server product) on that server.  In order to activate “ZABO” do the following:

1. In WebIntelligence, Option pages > Create/Edit

2. Select BusinessObjects as Document type > Apply

3. Attempt to create a new document or edit an existing Full Client document, then launch the ZABO setup:

Note:By selecting the ‘BusinessObjects’ option in ‘View Documents’ Tab  and attempting to view a full client report via the web, ZABO install is triggered.

What happens during the installation?

1. The Web Installer is downloaded: ActiveX (IE) or a Plug-In (Netscape)
2. Operating System files are checked and updated.
3. ZABO file is downloaded (approx. 9MB).
4. ZABO file is decompressed.
5. ZABO setup is run.

Warning!!  ZABO Enterprise 6 will automatically detect previous versions of the software.  It is important to note that the existing version will automatically be removed.  Note that Supervisor and Designer V5 can not connect to a V6 repository.

By changing the parameters on the server, the interface would look like the following:

Then:

Installing ZABO via the CD

ZABO can be installed a CD as Full client.  This method allows for the installation of the Data Access packs and the ability to have all of the Full Client functionalities.

To install from the CD, select Custom Installation > Desktop Products > BusinessObjects > 3-tier BusinessObjects, then click Next and follow instructions until the installation is complete.

3. Launching ZABO

Launching from Infoview:

1. Open a BusinessObjects document in Infoview, set the option to view BusinessObjects document with BusinessObjects module.
2. Your personal setting is set to the default configuration, simply click on Edit for a full client document to automatically launch BusinessObjects in ‘ZABO Mode’.
3. Be sure to look at the “Security features” section to block an Infoview user from editing a BusinessObjects report published in the corporate document.
4. Note the title bar: “frenh@bcnnwswebi01” identifying this session as a ZABO mode one (UserName@ZaboServerName).

Launching from busobj.exe (Start Program Menu)

Having 5.1 Full Client installed already, launching BusinessObjects in ZABO mode.

You can also launch BusinessObjects in ZABO mode from the busobj.exe client executable.  With this option you can select your ZABO server. You’ll notice the distinct icon specifying your ZABO server. This information comes from the .rkey file (explained below).

4. Client Files downloaded

Connectivity Files

When eZABO is launched on a client for the first time, a subdirectory with the name of the ZABO server is created under the Documents and Settings entries of the user that called “The name of the server” full path:

C:\Documents and Settings\Administrator\Application Data\Business Objects\Business Objects 6.0\remote\<server name>\

Some connectivity files (.prm, .sbo and .rss) will be copied here.  Note that ZaboServerName_driverEN.SBO is always downloaded since this is the file that contains the description of all BusinessObjects drivers.

In the screen shot below, when connecting to the ZABO Server “bcnnwswebi01”and viewing a document based in Oracle Server, for example, the files bcnnwswebi01_ODBC.SBO and bcnnwswebi01l_microsft.RSS were copied. (The exact files copied will depend on the database engine specified in the connection).

Security Files

In addition to the files above, security files are also created on the client machine.  ZaboServerName.key and ZaboServerName.lsi are your regular .key and .lsi files. Note that these files are automatically recreated if you delete them and try to login (info from server).

ZaboServerName.rkey - A text file containing an URL for the ZABO Server.

Example: bcnnwswebi01.rkey

[Core]

Version=1.0

[Portal]

RootURL=http://bcnnwswebi01/wiasp/bin/iswi.dll/

Authentication=1

Note: If you remove or rename this .rkey file, you’ll still be prompted to login through the equivalent .key file, however you will not connect in ZABO mode but in the regular BusinessObjects session.

The file “server name”.key is also downloaded into a separate folder:

Universes downloaded/files location:

Universes downloaded on the client machine are stored in the folder specific to each user login name.  The users can use universes stored locally on the client, as well as universes exported to the repository. On the client, these universes must be in:

$PROFILEDIR\Application Data\Business Objects\Suite 6.0\universes

The default $PROFILEDIR is c:\Documents and Settings\<user OS* login name>.

(* Operating System)

5. Working without ZABO Server (Offline mode)

In order to login in an offline mode, you will need to log in in normal mode first.  By doing this, your security profiles are downloaded onto your local machine. *.key and *.lsi files which stands for “local security information”. 

Basically the *.key is the initial key needed for connecting in online mode, then whenever this is done, and *.lsi key is generated (storing the latest security information related to all the users that have connected on that machine).

Subsequent offline mode logins will be authenticate via the *.lsi file.

Case 1: You have installed ZABO from the Server (Download) and you have no data access pack installed on your machine.

• Within the application you will not be able to refresh any Document or create a new one using the universe.
• The connection link to the Server is non-existent, hence unable to make use of the data access pack stored on the server.
• The user gets logged in because his security profile is stored locally from the first time he logs in through online mode to the application.
• The user can not create, edit nor refresh any reports.
• The user can not retrieve any corporate documents and is only able to view those documents stored on his/her local machine.

Case 2: You have installed ZABO from the Server (Download) and you have the data access pack installed on your machine.

As in the previous case, there is zero interaction with the Server and you will be unable to retrieve/view any corporate document, and any interaction with the repository is non-existent.  However, in this case any document saved locally could be refreshed having a copy of the universe stored locally as well as all the database connections strings properly configured (e.g. tnsnames.ora).

Case 3: You have installed ZABO from the Server (Download) and you have Full Client 6 on the same machine.

Even with Full Client 6 already installed on your machine, you can still run BusinessObjects in ZABO mode.  The choice is made at the login window; select the adequate domain from the drop down list.  The full client domain has a PC icon, whereas the ZABO domain can be identified by the globe icon.  As in case 2, you can still refresh locally stored documents because the data access pack has already been installed. Note that the universe needs to be on the local machine as well.

Conclusion

As you can see, it is not possible for a User to login through an “offline mode” not having previously, at least once, logged in normally.

6. Version migration & Upgrade

How does a BusinessObjects 5.x user migrate to ZABO 6.5?

By default, the Setup utility uninstalls any previous version of BusinessObjects, and then installs version 6.x. Users are not offered a choice of whether they want to uninstall the previous version.

Note! You can change this default, so that:

• Version 5.x BusinessObjects remains in place, alongside the newly-installed 6.x.
• Users are asked whether they prefer uninstalling or keeping version 5.x.

To make these changes, use the command-line mode of the administrative installation:

• To force 5.x to remain in place, set the MIGRATION feature to a value of 0.
• To offer users the choice of whether they want to uninstall version 5.x, set the “MIGRATIONPROMPT” feature to a value of 1.

To set these features open the ZABOInstallPopup.asp file stored on the following location on WebI server (C:\Program Files\Business Objects\BusinessObjects Enterprise 6\nodes\<server name>\mycluster\IIS\1\wiasp\scripts), and have the values in as follows:

<param name="strParams" value="THREETIERBUSOBJ=1 WEB=1

INSTALLLANG=<%= sLocale %> MIGRATION=1 MIGRATIONPROMPT=0

VBAINSTALL=0">

7. ZABO and OLAP

Can ZABO clients access OLAP servers directly?

If you do a CD-install you can access OLAP sources directly from the client machine.  With the CD-install you will still be able to use all the RDBMS middleware on the server.

If you do a web-install of ZABO, the ZABO client will not be able to access OLAP sources directly.

Basically in the case, the RDBMS middleware is installed on the client.

Can ZABO access OLAP servers via the web?

Since ZABO uses the query engine of BusinessObjects Enterprise 6 it cannot access OLAP servers via the web. However, once the report is created, web-installed ZABO clients can format and analyze this report at will. Also, if they need to refresh the report they can schedule a refresh using Broadcast Agent, Report Messenger or directly from InfoView.

Can ZABO format a report built from an OLAP data source?

Yes, you can do everything that you can do in BusinessObjects Enterprise 6 like Create and modify tables, charts, formulas, local variables, etc.

Can ZABO perform analysis on a report built from an OLAP data source?

Yes, you can do everything that you can do in BO V6 like drill, slice and dice, etc.

Can ZABO refresh BusinessObjects reports that access OLAP data sources?

If you do a CD-install you can refresh OLAP sources directly.

If you have a web-installed ZABO then the recommended workflow is to first refresh the document from InfoView or to schedule the refresh using Broadcast Agent or ReportMessenger (in both cases the refresh is done through the BusObj executable running on the server) and then open it in ZABO. In these latter cases the appropriate OLAP access packs need to be installed on the server.

How does ZABO refresh multiple data source reports including OLAP?

If ZABO is installed from a CD it can operate in either 2-tier (ZABO client and data sources) or 3-tier mode (ZABO client, ZABO server, and data sources). If the CD-installed ZABO is in 2-tier mode, you can directly refresh a report containing an OLAP and another (e.g., RDBMS) data source only if the appropriate middleware for the other data source is installed on the client. If this ZABO is operating in 3-tier mode, then you can directly refresh such a report using the middleware on the ZABO server. If you have a web-installed ZABO you cannot directly refresh OLAP sources.

8. Grid Summary:

9. Various Change in Terminology with Version 6.x

Go to top

Apply universe visibility rules to keep your data safe
Author: Moïse Kameni

Overview

This tip is based on the “Island Resort Marketing” universe, a common universe available for all BusinessObjects users once the application is installed.

It is assumed that a repository is available with the “Island Resort Marketing” universe exported in the universe domain; hence a new secured connection has been made pointing to the Demo “Island Resort Marketing” database.

1. The problem/Context scenario:

There is a security requirement to ensure that country/regional managers in Island Resort Marketing only have access to data related to their area of supervision.

Right now, anyone with access to BusinessObjects can report on data from all regions, countries (USA & France) and effectively view all sales and reservations activities details.

2. Universe settings:

The key table in this universe for our exercise is “Resort_Country” (see figure 1), where all the countries associated to the resorts are stored.  The idea is to make sure all the objects available in this universe are linked to that table. As a result, the above table will be added automatically to any query made on BusinessObjects.

How to do it?

• Select any object in the universe and go to its properties
• Click on the “Tables” button and select the “Resort_Country” table, resulting in two tables being selected (see figure 2).
• Do that for all the objects in the universe (see figure 3), Save and Export to the repository.

Figure 1. Key Table: Resort_Country.

Figure 2. Add the key table to all objects.

Figure 3. More objects.

3. Supervisor settings

For this scenario a brand new repository has been created on a simple access database. The universe settings will force the automatic addition of the table “Resort_Country” to any query made on BusinessObjects. The actual data restriction is applied via the Supervisor module.

How to do it?

This will be accomplished by creating logical reporting groups (countries in this case), end-user accounts and appropriate group definitions, and by applying data restrictions to each group: Select the group> Universe tab> Double click on the universe (Universe properties)> “Rows” tab> “Add” (new rows restriction) button > Insert the table and where clause.

Create groups and end-user accounts (see Figure 4):

- Groups: France, USA; subgroups of “Island Resort Marketing” repository top group

- End-user accounts: frcountrymgr (France), uscountrymgr (USA) and generalmgr (directly under the top group)

Figure 4. Create groups and end-user accounts.

Applying restrictions to groups (see Figure 5):

- France:

Table: “Resort_Country”

Where Clause: “Resort_Country.country='France'”

Figure 5. Apply restrictions to groups.

- USA (see Figure 6):

Table: “Resort_Country”

Where Clause: “Resort_Country.country='US' “

Figure 6. Entering row restrictions for US.

Confirm all => Click on “Check All” button. Status “OK” (see below):

Note: the user “generalmgr” has no data restriction applied to the profile because it inherits its rights from the top group “Island Resort Marketing.”

4. Results in BusinessObjects report

Users: frcountrymgr, uscountrymgr and generalmgr

frcountrymgr logs in and refreshes the report (see below):

frcountrymgr: Another query with the Resort & Service Line and service objects gives us different results (see below). 

Note: the SQL generated by BusinessObjects includes the following condition:

AND  Resort_Country.country='France'

User “uscountrymgr” logs in and refreshes the report, giving us the following results (see below).  Note: the SQL generated by BusinessObjects includes the following condition AND  Resort_Country.country='US'.

And the following is generated from the second query (see below):

- User “generalmgr”logs in and refreshes the report: all data displayed, US and France, (see below):

And for the second query (see below):

Note: the SQL generated by BusinessObjects does not include the following conditions:

AND  Resort_Country.country='France'

nor

AND  Resort_Country.country='US'

Go to top