How to change universe of a webi document using BO RE Java SDK

Web Intelligence documents use one or many universes. Sometimes it is necessary to change universes for a bunch of reports. Changing universe might be very difficult if you need to modify dozens of documents. Luckily it is possible to utilize BusinessObjects Report Engine Java SDK.

There is a number of Webi documents in a folder which use a number of universes. The task is to make a copy of these on the same BOE server.

It is easy to copy documents (in CMC) and universes (using Universe Designer. The complex problem is to change a copied document to use the copied universes.

The manual procedure of changing universe for a document is the following:

  • Open Webi document in Web Intelligence in edit mode.
  • Click Edit query.
  • For each query, change universe in the query properties.
  • Changing universe you have to confirm mapping. If you change a universe to its copy, you do not need to do anything in this mapping. But just confirming it takes few seconds.

The automated procedure for all documents will do the following:

  • Get list of universe IDs that used by the copied documents.
  • Get list of all available universes.
  • Create a mapping of IDs.
  • Map all reports from the original universes to their copies using the mapping.

If you have to modify 5 documents, the writing code might take even more time then do it manually, but anyway coding is more fun then clicking :)

If you have to modify 50  documents, think twice before starting manual work.

Java Function

public static boolean changeUniverses(DocumentInstance widoc,
                                      HashMap<String, String> mapping)
{
   boolean failed = false;
   DataProviders dps = widoc.getDataProviders();
   HashSet<DataSource> dataSources = new HashSet<DataSource>();
   for (int i = 0; i < dps.getCount(); ++i) {
      DataProvider dp = (DataProvider) dps.getItem(i);
      dataSources.add(dp.getDataSource());
   }
   for (DataSource ds: dataSources) {
      String universeId = ds.getUniverseID();
      String oldID = universeId;
      String newID = mapping.get(oldID);
      System.out.println(oldID + "->" + newID);
      dps.changeUniverse(oldID, newID, true);
      if (dps.mustFillChangeUniverseMapping()) {
         ChangeUniverseMapping unvMapping = dps.getChangeUniverseMapping();
         ChangeDataSourceMapping[] dsMappings = unvMapping.getDataSourceMappings();
         for (ChangeDataSourceMapping dsMapping : dsMappings) {
            ChangeDataSourceObjectMapping[] objMappings = dsMapping.getAllMappings();
            for (ChangeDataSourceObjectMapping objMapping : objMappings) {
               if (objMapping.getToObject() == null) {
                  failed = true;
               }
            }
         }
         dps.setChangeUniverseMapping();
         if (widoc.getMustFillContexts()) {
            failed = true;
         }
         widoc.applyFormat();
         widoc.refresh();
      }
      if (failed) {
         return false;
      }
   }
   return true;
}

BTW, the universe id is something like:

UnivCUID=ASiM_T4jxmJIj0aKWpbeXro;UnivID=41709;ShortName=Finance;UnivName=Finance

Complete code

http://bukhantsov.org/tools/WidRemapping.zip

Hierarchical (Recursive) Queries in SQL Server

This code sample demonstrates recursive queries in SQL Server.

The query returns paths of Kettle transformations.

with Parent as
(
   select
      ID_DIRECTORY
   ,  ID_DIRECTORY_PARENT
   ,  cast(DIRECTORY_NAME
         as varchar(255)) as DIRECTORY_PATH
   from
      R_DIRECTORY
   where
      ID_DIRECTORY_PARENT = 0
   union all
   select
      TH.ID_DIRECTORY
   ,  TH.ID_DIRECTORY_PARENT
   ,  cast(Parent.DIRECTORY_PATH
         + '/' + DIRECTORY_NAME
         as varchar(255)) as DIRECTORY_PATH
   from
      R_DIRECTORY TH
   inner join
      Parent
   on
      Parent.ID_DIRECTORY = TH.ID_DIRECTORY_PARENT
)
select
   coalesce(dir.DIRECTORY_PATH, '/') DIRECTORY_PATH
,  trans.NAME as TRANSFORMATION_NAME
from
   Parent dir
right join
   R_TRANSFORMATION trans
on
   dir.ID_DIRECTORY = trans.ID_DIRECTORY

Dimension lookup / update properties for all transformations

Sometimes it might be useful to get different information about kettle transformations in a list format. Here is an example selecting update type of step Dimension lookup/update:

select
   t.NAME as TRANSFORMATION_NAME
,  s.NAME as STEP_NAME
,  fn.FIELD_NAME
,  tab.TABLE_NAME
,  fu.FIELD_UPDATE
from
(
   select
      ID_STEP
   ,  NR
   ,  CAST(VALUE_STR as varchar) as FIELD_NAME
   from
      R_STEP_ATTRIBUTE
   where
      CODE = 'field_lookup'
) fn
inner join
(
   select
      ID_STEP,
      NR,
      CAST(VALUE_STR as varchar) as FIELD_UPDATE
   from
      R_STEP_ATTRIBUTE
   where
      CODE = 'field_update'
) fu
on
   fn.ID_STEP = fu.ID_STEP
and
   fn.NR = fu.NR
inner join
   R_STEP s
on
   s.ID_STEP = fn.ID_STEP
inner join
(
   select
      ID_STEP
   from
      R_STEP_ATTRIBUTE
   where
      CODE = 'update'
   and
      CAST(VALUE_STR as varchar)='Y'
) u
on u.ID_STEP = s.ID_STEP
inner join
(
   select
      ID_STEP
   ,  CAST(VALUE_STR as varchar) as TABLE_NAME
   from
      R_STEP_ATTRIBUTE
   where
      CODE = 'table'
) tab
on
   tab.ID_STEP = s.ID_STEP
inner join
   R_TRANSFORMATION t
on
   t.ID_TRANSFORMATION = s.ID_TRANSFORMATION
order by
   TRANSFORMATION_NAME
,  STEP_NAME
,  FIELD_NAME

How to add variable using BO RE Java SDK

I got an interesting task – add variable VERSION specifying the service pack to about 50 webi documents. I had to invent something more clever then going through all them manually.

The following function can be used to add a dimension variable with name name and value value to webi document widoc.

public static boolean addReplaceVariable(DocumentInstance widoc,
                                              String name,
                                              String value)
{
   ReportDictionary dic = widoc.getDictionary();
   VariableExpression[] variables = dic.getVariables();
   boolean found = false;
   for (VariableExpression e : variables) {
      if (e.getName().equalsIgnoreCase(name)) {
         System.out.println("variable " + name
             + " expression " + e.getFormula().getValue()
             + " was replaced with " + value);
         e.setValue(value);
         found = true;
         break;
      }
   }
   if (!found) {
      try  {
         dic.createVariable(name, ObjectQualification.DIMENSION, value);
         System.out.println("variable " + name
                          + " with value " +  value
                          + " has been created");
      } catch (Exception e) {
         System.out.println("ERROR: the variable " + name + " cannot be created");
         return false;
      }
   }
   return true;
}

It can be executed

if (addReplaceVariable(widoc, "VERSION", "=\"12.00.18.00\"")) {
   widoc.save();
}

More information

Getting started with BO RE Java SDK

Purging data provider queries using BO RE Java SDK

Before copying webi documents from test to production environment it might be useful to clean up document queries.

The following function

  • purges data providers queries,
  • removes saved prompt values, and
  • regenerates queries.
public static void purgeQueries(DocumentInstance widoc) {
   DataProviders dps = widoc.getDataProviders();
   for (int i = 0; i < dps.getCount(); ++i) {
      DataProvider dp = (DataProvider)dps.getItem(i);
      if (dp instanceof SQLDataProvider) {
         System.out.println("Data provider: " + dp.getName());
         SQLDataProvider sdp = (SQLDataProvider)dp;
         sdp.purge(true); // true means purge prompt values
         sdp.generateQuery();
         sdp.resetSQL();
      }
   }
}

The code is very simple but I use this functionality most often.

More Information

Getting started with BO RE Java SDK
Package com.businessobjects.rebean.wi.*

Lock information in SQL Server

The script gets miscellaneous information about locks including SQL.

SELECT 
  L.request_session_id              AS SPID, 
  DB_NAME(L.resource_database_id)   AS DatabaseName,
  L.request_mode                    AS LockType,
  L.request_status                  AS RequestStatus,
  L.resource_type                   AS ResourceType,
  L.resource_description            AS ResourceDescription,
  L.resource_associated_entity_id   AS ResourceAssociatedEntryId,
  -- Object
  O.name                            AS ObjectName, 
  O.object_id                       AS ObjectId,
  -- SQL
  ST.text                           AS SqlStatementText,
  CN.auth_scheme                    AS AuthenticationMethod,
  -- Session
  ES.login_name                     AS LoginName,
  ES.host_name                      AS HostName,
  -- Transaction
  TST.is_user_transaction           AS IsUserTransaction,
  AT.name                           AS TransactionName,
  AT.transaction_begin_time         AS TransactionBeginTime
FROM    
  sys.dm_tran_locks L
  -- Object
  LEFT JOIN sys.objects O ON O.object_id = (
    CASE 
      WHEN resource_type IN ('PAGE', 'KEY', 'RID', 'HOBT') 
        THEN (
          SELECT P.object_id 
          FROM sys.partitions P 
          WHERE P.hobt_id = L.resource_associated_entity_id
        ) 
      WHEN resource_type = 'OBJECT' 
        THEN L.resource_associated_entity_id
      WHEN resource_type = 'ALLOCATION_UNIT'     
        THEN (
          SELECT P.object_id FROM sys.partitions P 
          INNER JOIN sys.allocation_units AU
          ON AU.container_id = (
            CASE 
              WHEN AU.type IN (1,3) THEN P.hobt_id
              WHEN AU.type = 2 THEN P.partition_id
            END
          )
          WHERE AU.allocation_unit_id = L.resource_associated_entity_id
        ) 
    END
  )
  -- SQL
  LEFT JOIN sys.dm_exec_connections CN 
  ON CN.session_id = L.request_session_id
  CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
  -- Session
  LEFT JOIN sys.dm_exec_sessions ES 
  ON ES.session_id = L.request_session_id
  -- Transaction
  LEFT JOIN sys.dm_tran_session_transactions TST 
  ON TST.session_id = L.request_session_id
  LEFT JOIN sys.dm_tran_active_transactions AT 
  ON AT.transaction_id = TST.transaction_id 
ORDER BY 
  L.request_session_id

Universe Documenter – Some Best Practices

Beside the straightforward use of the Universe Documenter for documenting list of objects, tables etc, it can help developer to speedup some verification tasks.

1. Some guys do not believe in automatic context detection and create contexts manually. In my experience if the database schema is designed properly and join cardinalities are set properly, automatic context detection works perfect. However when you are creating the first draft of the universe, cardinalities of some joins might be not set. In this case automatic context detection will prompt a warning "All Cardinalities are not set. The detection can be wrong or incomplete because of the unknown cardinalities ".

If the schema is complex, it might be difficult to see the join, and a solution is to go through all joins in List Mode and check that all join cardinalities are set. The other option is to run Universe Documenter, and check the column "Outer Join" on the worksheet "Joins" for value "Unknown". This will indicate the wrong join.

2. You can define a custom tables for an object to force a join. This might be useful for example for performance optimization. The problem with this feature is that the tables are reset if you change anything in the object expression (even if this is just a space). If you have a lot of such objects it might be really difficult task to verify all them. Verification of each object requires several mouse clicks. It is too easy to miss an object. An option is to build a list of objects and check column "Tables" on the worksheet "Objects".

3. TBW

Script to kill user sessions in Oracle

Script to kill all sessions of a user in Oracle.

BEGIN
  FOR r IN (select sid,serial# from v$session where username = 'USER')
  LOOP
    EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid 
      || ',' || r.serial# || '''';
  END LOOP;
END;

Manual

Retrieve session identifiers and session serial number (which uniquely identifies a session's objects):

select sid, serial# from v$session where username = 'USER'

The syntax to kill session is

alter system kill session 'sid,serial#'

Disconnecting session:

alter system disconnect session 'sid,serial#' post_transaction;
alter system disconnect session 'sid,serial#' immediate;

More information

Killing Oracle Sessions on www.oracle-base.com

Log file with timestamp in Windows

How to build the log file name with timestamp in the format Log_yyyy_MM_dd_HHmm.log.

Batch file

@echo off

REM get date in format yyyy-MM-dd
FOR /f "tokens=1-3 delims=- " %%a IN ("%DATE%") DO (SET filedate=%%a_%%b_%%c)

REM get time in format HHmm
FOR /f "tokens=1-2 delims=:" %%a IN ("%TIME%") DO (SET filetime=%%a%%b)

REM if time in format Hmm - add leading zero
IF "%filetime:~0,1%"==" " SET filetime=0%filetime:~1,3%

REM prepare log file name in format Log_yyyy_MM_dd_HHmm.log
SET logfilename=C:\Logging\Log_%filedate%_%filetime%.log

REM do something redirecting result to %logfilename%

ECHO See the logfile: %logfilename%

Adjusting date pattern to correspond server settings

The above script assumes the server yyyy-MM-dd as the date format and Hmm as the time format (H stands for 24 hours time format). It uses command line tool DATE to get the current date and system environment variable %TIME% to get the current time. So the easiest way to make it work is to adjust server date and time short format to yyyy-MM-dd and Hmm in Region and Language settings. But this might be not the best solution, the other option is to adjust the script.

Let's consider the line:

FOR /F "tokens=1-3 delims=- " %%a IN ('date /t') DO (SET filedate=%%a_%%b_%%c)

This line gets the date, parses it assuming format yyyy-MM-dd and builds the variable filedate in format yyyy_MM_dd.

FOR /F ["options"] %%variable IN (file-set) DO command [command-parameters]

This command reads and processes line by line all files from the files-set. You can read description of the command running FOR /?. In our case we get one line result of the command date /t and parse it using the specified options tokens and delims.

When option tokens is specified, additional variables are allocated. In our case we use first to third tokens, first token is assigned to variable a, second token is assigned to variable b, and third is assigned to variable c. We use these variables to set the variable filedate.

The option delims specifies the set of delimiters. In our case: space and minus signs.

So if your date format is ddd MM/dd/yyyy (e.g. Sat 09/10/2011), the command should be changed to

FOR /F "tokens=2-3 delims=/ " %%a IN ('date /t') DO (SET filedate=%%c_%%a_%%b)

The following line adds leading zero if necessary.

IF "%filetime:~0,1%"==" " SET filetime=0%filetime:~1,3%

Some other notes about batch files

DO part can be split over lines:

for /f "tokens=1-3 delims=- " %%a in ('date /t') do (
set year=%%a
set month=%%b
set day=%%c
)
set filedate =%year%_%month%_%day%

Or it can be written on one line with ampersand separator:

... do (set year=%%a&set month=%%b&set day=%%c)

To get last 3 characters you can use %var:-3%. To replace substring str1 with str2, you can use %var:str1=str2%.

Note that trailing space can be a part of variable. For example,

@echo off
set var =some text
echo "%var%" "%var %"
pause

This batch will print. %var% is not defined and thus empty.

"" "some text"

Further information

You can read more information about the batch commands in the help: FOR /? and SET /?.

How to diff universes using Universe Documenter

I have added a new feature to the tool Universe Documenter which allows to find differences between universes. I will describe on an example.

Let's open Island Resorts Marketing universe and make some changes: remove table Age_group and object Age group, and save the universe with a different name.
Universe Documenter - Diff Example - Universe

Now we will start the tool, open the modified and original universes, select the option Diff in Post processing, and click Quick View.
Universe Documenter - Diff Example - Dialog

In the Excel spreadsheet, you will see the information from both universes with the differences marked in yellow.

If you built the Excel for two universes one of which is the copy of another, there should be two rows for each object (condition, join etc) with the same values. If universes are different, e.g. there is additional object in one of them, there will be a corresponding row for this object in the Excel which does not have corresponding row from the other universe and such this row will be marked.

We removed table and therefore we removed corresponding join. This is shown in the Excel spreadsheet:

Universe Documenter - Diff Example - Result

If for example an object was changed in the copy, e.g. the name or type were changed then both corresponding rows are marked. You can further sort rows in Excel to find what exactly changed.