Posts By: dmytro

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

Posted by & filed under 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 […]

Hierarchical (Recursive) Queries in SQL Server

Posted by & filed under 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 […]

Dimension lookup / update properties for all transformations

Posted by & filed under Kettle.

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 […]

How to add variable using BO RE Java SDK

Posted by & filed under 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 […]

Purging data provider queries using BO RE Java SDK

Posted by & filed under 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 = […]

Lock information in SQL Server

Posted by & filed under 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, […]

Universe Documenter – Some Best Practices

Posted by & filed under Tools.

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 […]

Script to kill user sessions in Oracle

Posted by & filed under 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 […]

Log file with timestamp in Windows

Posted by & filed under Programming.

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 […]

How to diff universes using Universe Documenter

Posted by & filed under Tools.

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. Now we will start the tool, open the […]