How to determine BusinessObjects service pack and fix pack

Method 1. Software Inventory Tool

The best way to determine the current version is through Software Inventory Tool. It has complete history of your BOE updates.

Method 2. CMC Settings

If you don't have access to the server with the BOE, you can check the metrics of BusinessObjects servers.

Open CMC, go to Settings. In the properties, you will find the Product Version. Lookup BOE SP/FP using the reference table below.

Method 3. BO Executable Version

If you have access to file system of the server, you may find the following method to be the quickest.

Browse to the folder with BO installation. For example:

C:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\win32_86

Right-click the busobj.exe file, click Properties, on the the Version tab. Lookup BOE SP/FP using the reference table below.

Reference

Continue reading "How to determine BusinessObjects service pack and fix pack"

Drawing tutor

There are many brain fitness exercises on the web that helps to develop mental skills. There are not that much programs that help to develop visual skills. Well I do not know any of such.

The purpose of this program is to develop visual skills. You could also improve your intellectual skills trying to figure out how to use it =).

There are four games:

  • Lines
  • Curves
  • Proportions
  • 3D

The basic principle behind most of them – you see a figure and a point on the left side, your task is to indicate location of the point on the right side. The complexity of the game is increasing with each correct answer and decreasing otherwise.

Click here to play Drawing tutor

The functionality is quite finished, but there is no fancy graphics etc. If you have ideas how to improve – please let me know.

Lines

Drawing tutor - Lines

Curves

Drawing tutor - Curves

Proportions

Drawing tutor - Proportions

3D

Drawing tutor - 3D


Dynamic Formatting in Web Intelligence

You can get the response string to a prompt using Web Intelligence function UserReponse(). However the function returns string value which is formatted according to Web Intelligence preferred locale. To make handling of date strings independent on the locale, dynamic formats can be used.

Dynamic Formats in ToDate Function

To convert the date prompt string into date, ToDate function can be used.

  date ToDate(date_string;format)

If locale is French (Canada), the format should be "yyyy-MM-dd HH:mm:ss".

For example,

= ToDate(UserResponse("Date:");"yyyy-MM-dd HH:mm:ss")

To make a report independent on the format (and correspondingly the locale), the dynamic formats can be used. The dynamic input formats are "INPUT_DATE_TIME", "INPUT_DATE".

So:

= ToDate(UserResponse("Date:");"INPUT_DATE_TIME")

Handling Date and DateTime Format

Before SP2 of BOE XI 3.1, the user could enter date in both date and datetime format, and conversion using specific format would not work correctly in all situations. The formula that accounts the both possible formats:

=If(IsError(ToDate(UserResponse("Date:");"INPUT_DATE"));
            ToDate(UserResponse("Date:");"INPUT_DATE_TIME");
            ToDate(UserResponse("Date:");"INPUT_DATE"))

Dynamic Formats with FormatDate

The dynamic formats can be used in FormatDate() function.

 =FormatDate(CurrentDate();"LONG_DATE")

BusinessObjects documentation does not list the available dynamic formats. Some of them can be found in dialog "Number Format".

ForceMerge() example

The Webi document is built on two queries. First query dimensions are Country and Resort:

There is only one dimension – Resort in the second query.

The queries are merged on Resort.

The first table in the result contains expected numbers. While in the second table, sum of Number of guests (565+446+540=1551) is duplicated for each Country. The explanation for this is that in the second table, dimension Resort is not used, so the queries are not merged:

Webi function ForceMerge can help in this situation.

ForceMerge forces Web Intelligence to account for synchronized dimensions in measure calculations when the synchronized dimensions do not appear in the calculation context of the measure

Using ForceMerge([Number of guests]) for the column will produce the following result:

Sometimes we have to use ForceMerge, sometimes it can improve performance of SQL queries. However try to avoid it if possible, this will make document logic more clear.

Getting Started with Designer SDK in C#

You will need:

  • BusinessObjects Enterprise XI 3.x: You need BusinessObjects client tools installed on your PC, and a connection to a BusinessObjects server.
  • Visual Studio C#: You can install Microsoft Visual Studio Express from http://www.microsoft.com/express/downloads/.
  • Create new project: File > New Project… > Console Application
  • Add reference to the SDK: Project > Add Reference… > COM > BusinessObjects Designer 12.0 Object Library
  • Now you can start coding!

Getting started

The Designer SDK is quite simple and easy to use. Universe Designer API Reference is all you need to solve almost any task (that can be solved via the SDK).

Class Application is the main class that represents the Designer product. When an instance of Application is created, Desiner is started on background, and you can see designer.exe in Task Manager. The first thing you usually do starting Designer is logging in. The same should be done in the program using either function LogonDialog() or Logon(UserName, password, CMS, authenticationMode). In the end the program should quit the Designer, otherwise the designer.exe will stay running, even when your program finished. For this reason, your code should handle possible exceptions and quit the application before quiting the program.

using System;
using Designer;
namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            Application application = new Application();
            try
            {
                application.LogonDialog(); 

                // ... some code here ...
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                application.Quit();
            }
        }
    }
}

Hello Universe!

The following program imports the standard universe "Island Resorts Marketing" and prints the structure of its classes.

using System;
using Designer;
namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            Application application = new Application();
            try
            {
                application.LogonDialog();
                Universe universe =
                    application.Universes.OpenFromEnterprise(
                        "webi universes",
                        "Island Resorts Marketing",
                        false);
                Console.WriteLine(universe.Name);
                PrintClasses(universe.Classes, 3);
                universe.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                application.Quit();
            }
        }

        static void PrintClasses(Classes classes, int indent)
        {
            foreach (Class theclass in classes)
            {
                Console.WriteLine(
                    new String(' ', indent)
                    + theclass.Name
                    + " [" + theclass.Objects.Count
                    + " objects, "
                    + theclass.PredefinedConditions.Count
                    + " conditions]");
                PrintClasses(theclass.Classes, indent + 3);
            }
        }

    }
}

Selectivity and Execution Plan

Cardinality is how many rows should be returned after evaluating predicates. Selectivity is cardinality divided by the total number of input rows. DBMS uses selectivity for choosing the right index.

Density is characteristic of data in a column. It is equal to 1 divided by the number of distinct values.

Let's consider few examples of queries with different selectivity of predicate. We will restrict on one field so the selectivity of the predicate will be equal to the density.

Data

Create a table with five indexed fields. All fields have different number of unique values.

DROP TABLE t;
CREATE TABLE t (id INT, 
                group1 INT, 
                group2 INT, 
                group3 INT, 
                group4 INT);

BEGIN
  FOR id IN 1..1000000 LOOP
    INSERT INTO t VALUES (id, 
                          dbms_random.Value(1,100000), 
                          dbms_random.Value(1,1000), 
                          dbms_random.Value(1,100), 
                          dbms_random.Value(1,10));
  END LOOP;
END;
/

commit;

CREATE INDEX t_id ON t (id);
CREATE INDEX t_group1 ON t (group1);
CREATE INDEX t_group2 ON t (group2);
CREATE INDEX t_group3 ON t (group3);
CREATE INDEX t_group4 ON t (group4);

Example 1

Query with a restriction on id with unique values. Selectivity=1/1M (high)

SELECT * FROM t WHERE id = 1; 

Execution plan. Index used.

TABLE ACCESS (BY INDEX ROWID) of T #1 TABLE (Cost=4 Card=1 Bytes=65)
  INDEX (RANGE SCAN) of T_ID INDEX Optimizer=ANALYZED (Cost=3 Card=1 Bytes=)

Example 2

Query with a restriction on group1. Selectivity=1/100K (still high)

SELECT * FROM t WHERE group1 = 1;

Execution plan. Index used.

TABLE ACCESS (BY INDEX ROWID) of T #1 TABLE (Cost=6 Card=3 Bytes=195)
  INDEX (RANGE SCAN) of T_GROUP1 INDEX Optimizer=ANALYZED (Cost=6 Card=3 Bytes=)

Example 3

Query with a restriction on group2. Selectivity=1/1K

SELECT * FROM t WHERE group2 = 1;

Execution plan. Index used.

TABLE ACCESS (BY INDEX ROWID) of T #1 TABLE (Cost=348 Card=468 Bytes=30,420)
  INDEX (RANGE SCAN) of T_GROUP2 INDEX Optimizer=ANALYZED (Cost=3 Card=468 Bytes=)

Example 4

Query with a restriction on group3. Selectivity=1/100 (low)

SELECT * FROM t WHERE group3 = 1;

Execution plan. Index has NOT been used.

TABLE ACCESS (FULL) of T #1 TABLE (Cost=795 Card=6,425 Bytes=417,625)

Example 5 (hint)

Let's compare with the plan of the same query but using hint forcing use of the corresponding index.

                       
SELECT /*+ INDEX(t t_group3) */ * FROM t WHERE group3 = 1;

The cost is 1,774 vs 795 i.e. 2.23 times larger.

TABLE ACCESS (BY INDEX ROWID) of T #1 TABLE (Cost=1,774 Card=6,425 Bytes=417,625)
  INDEX (RANGE SCAN) of T_GROUP3 INDEX Optimizer=ANALYZED (Cost=13 Card=6,425 Bytes=)

Example 6

Just to complete picture. Here is query without restriction:

SELECT * FROM t;

Execution plan:

TABLE ACCESS (FULL) of T #1 TABLE (Cost=792 Card=1,179,200 Bytes=76,648,000)

localhost in QaaWS for Xcelsius dashboards

To allow easy migration of QaaWS from one server to another it is recommended to use localhost instead of server name in QaaWS and in Xcelsius dashboard connection to QaaWS.

This however complicates the job of Xcelsius dashboard developer, because reference to localhost will not work for QaaWS connection if he works from his PC and not on the server.

You should either install Xcelsius on the server and develop dashboards there (probably via remote desktop). Or the dashboards should be developed with hardcoded server name and when the development finished, the connections should be rebuild with localhost as server name. This could be very tedious task.

As a workaround, you can add localhost with IP of the server to C:\Windows\System32\drivers\etc\hosts, e.g.

172.20.30.3 	localhost