Category Archives: Tools

Purge or Refresh Universe Lists of Values from Command Line

Leave a reply

The command line tool imports a universe from CMS, refreshes or purges lists of values (LoVs) and exports the universe back to CMS.

Downloads

Executable for BOE XI 3.1

Source – C# VS 2010 Express solution

This software is distributed AS IS with no warranty of any kind, use it on your own risk.

Options

/server: – BO server. If not specified, standalone mode will be used.
/user: – User name. Default – “Administrator”
/password: – password. Default – blank
/auth: – Authentication. Default – “secEnterprise”
/cmsfolder: – CMS folder with universe
/cmsuniverse: – Universe name in CMS
/file: – Local file name
/lovs: – File with list of values. Only specified LoVs will be purged/refreshed.
/purge – Purge all lists of values
/refresh – Refresh all lists of values
/save – Save all lists of values to a file

The tool can purge or refresh LoVs.

server, user, password and auth are the connection parameters. The tool can also run in standalone mode, that is when server is not specified.

When cmsfolder and cmsuniverse are specified, the tool will import the universe, handle lists of values and export it back to CMS.

The tool can work with locally saved universe using option file. In this case the tool will open the universe, handle LoVs and save it to the same location.

The tool can proceed only subset of lists of values using lovs option which is the name of the file. To build the file with all LoVs, the option save should be used.

Example

Build a list of all lists of values and save to a file lovs.txt

LovUtil.exe /server:localhost /cmsfolder:"test examples" /cmsuniverse:Univers1  lovs:lovs.txt /save

Refresh lists of values specified in the file lovs.txt

LovUtil.exe /server:localhost /cmsfolder:"test examples" /cmsuniverse:Univers1 /lovs:lovs.txt /refresh

Refresh all lists of values for a locally saved file in standalone mode

LovUtil.exe /file:"Univers1.unv" /refresh

Command line InfoStore Query Builder with export to Excel

A free command line tool that runs query against CMS database and saves the result in Excel or CSV.

Options

-cms: BO server. Default is localhost
-username: BO user name. Default is Administrator
-password: User password. Default is blank
-auth: Authentification. Default is secEnterprise

-query: CMS query (the same as you run in Query Builder). See example.
-file: CMS query will be read from the file.
You should either specify -query: or -file: but not both.

-excel: Output excel file name. It will contain two spreadsheets: one for simple properties
and another for containers. See containers.
-csv: Output cvs file for simple properties. See containers.
-csv2: Output cvs file for containers properties. See containers.
If neither -excel: nor -csv: are specified, the simple properties will be printed to the standard output in CSV format.

Example

java -jar querybuilder.jar -query:"select si_name from ci_infoobjects"

Containers

There are simple properties of CMS objects e.g. SI_ID, SI_NAME, and containers – properties that have subproperties. For instance, the property SI_PROMPTS has subproperties SI_NUM_PROMPTS, SI_PROMPT1, SI_PROMPT2 etc. In turn SI_PROMPT1 has subproperties: SI_PROMPT, SI_PROMPT_TYPE, SI_OPTIONS etc. The containers are exported in the form: infoobject id, infoobject title, property path, property value.

Download

The tool has been desupported. Check Biclever CMS Query Builder.

InfoStore Query Builder to Check Relations

23 Replies

There is a nice tool in CMC that allows to check relations between objects. Something similar can be done in the query builder with some non documented features.

For instance, to find all reports that use a universe Finance, you can write:

SELECT *
FROM ci_infoobjects, ci_appobjects, ci_systemobjects
WHERE PARENTS("si_name='Webi-Universe'","si_name='Finance'")

PARENTS has two arguments. First is the relation type, second is the reference object.

If you want to find opposite – all universes used by a report, you can write

SELECT *
FROM ci_infoobjects, ci_appobjects, ci_systemobjects
WHERE CHILDREN("si_name='Webi-Universe'","si_name='Balance Sheet'")

What are the possible types of relations (first argument)? You can find the list querying CMS:

SELECT si_name FROM ci_systemobjects WHERE si_relation_table_name='RELATIONS'

Now you can also build such queries and export result to to Excel in InfoStore Query Builder:

How to Replace a Table with a Derived Table

6 Replies

This post describes the detailed steps to replace a database table with a derived table. Also it describes how Universe Documenter can help to achieve the perfect quality even for such complex modification of a universe.

You may need to replace a table with a derived table if your universe is affected by the self-outer join issue.

We will consider the following schema:

The task is to replace BO_CUSTOMER with a derived table to avoid self join in the universe database structure.

0) Make a backup of the universe and the reports that use the universe

Also export a copy of the universe to CMS. This will be required on the step 9 for verification.

1) Create the derived table that contains SQL with the self join SQL in WHERE

Create table BO_CUSTOMER_ with the following SQL:

SELECT * FROM BO_CUSTOMER WHERE ACCESSUSERNAME=@VARIABLE('BOUSER')

2) Switch all joins from the original table to the derived table.

Edit the join expression. Do not change tables in the combo-box, this may screw up the select.

The self join BOEMPLOYEE.ACCESSUSERNAME=@VARIABLE(‘BOUSER’) should be removed. You will be prompted that it is used in contexts. Ignore this message.

The contexts do not need to be updated.

3) Update objects expression and the expression of the predefined conditions

  • Select first class in the universe.
  • Hit Ctrl+F.
  • Enter BO_CUSTOMER in the field “Find what”
  • Enter BO_CUSTOMER_ in the field “Replace”
  • Select “SQL” in the Look Also In
Sometimes Replace All works fine, but it would be more preferable to change objects one by one.

4) If the table is a fact table and aggregate awareness is used in the universe, check if you need to update aggregate navigation.

  • Open aggregate navigation
  • Select the fact table
  • Select option “Incompatibles only”
  • Check objects and predefined conditions.
  • If the list “Associated Incompatible Objects” is empty, you do not need to do anything.
  • If the list is not empty, the same incompatibilities must be defined for the derived table.
(This is not needed in our example)

3) Check if you are lucky

Try deleting the original table. If the table was removed without prompts, you finished with the table. If you see the prompt that the table is used in object definitions, do not remove the table.

  • The table can be used in index awareness.
  • The table can also be used to force join with another table.

6) Modify the objects that use join forcing

  • Click right button on BO_CUSTOMER
  • In pop-up menu select View associated objects
  • If no objects highlighted in class structure, go to the next step
  • Go through all highlighted objects
  • The select of such objects do not contain reference to the table BO_CUSTOMER because we replaced all BO_CUSTOMER to BO_CUSTOMER_ on the step 3.
  • Click Tables and change BO_CUSTOMER to BO_CUSTOMER_ holding Ctrl button.

7) Update objects for which index awareness is defined.

There is no easy way to find the objects that has index awareness in Universe Designer. You need to go through all objects and check Keys in properties:

The easy way is to build the list of index awareness using Universe Documenter

8) Save, export

9) Verify

The modification described here is very complex. It is easy to make a mistake. Luckily it is possible to use the tool Universe Documenter to identify the mistakes.

To reduce the number of differences, import the copy of the original universe and change the name of the replaced table to BO_CUSTOMER_ and export the universe back to CMS.

Import the modified copy and the corrected universes in Universe Documenter, perform diff (How to diff universes using Universe Documenter) and check all highlighted differences. The two universes must be almost identical with an exception that the BO_CUSTOMER_ in the corrected universe is a derived table.

Universe Documenter – Some Best Practices

2 Replies

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

How to diff universes using Universe Documenter

13 Replies

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.

Universe Documenter

The tool exports BusinessObjects universe metadata into Excel file including information about objects, predefined conditions, tables, joins, contexts, index awareness and incompatibilities.

Requirements

  • Microsoft .Net Framework 3.5
  • BusinessObjects XI 3.1 SP3
  • Microsoft Office 2007 or OpenOffice 3.0

Installation

The tool is provided and supported by biclever.com.

The tool is build using Express version of Visual Studio, therefore it does not have a normal installer. Maybe I will do something about this later.

How to use

  • Start the tool  UnvDoc.exe as Administrator
  • Login to BOE system. If you cancel login, the tool will run in standalone mode.
  • Open universes from the disk or import from CMS.
  • Select output options. Do not check “List of values details” unless you need it – extraction of this information is very slow.
  • Save result to a specific file or click Quick View to get the result in Excel. ( Example of the result)

More Information

How to diff universes using Universe Documenter

Best practices of using Universe Documenter

List of output Excel columns

Objects
Universe Name
Universe Full Name
Class Path
Class Id
Class Name
Object Id
Object Name
Object Type
Object Format
Object Description
Object Show
Object Select
Object Where
Tables
Object Qualification
Measure Projection
Associated Object
Object Has LoV
LoV Allow Edit
LoV Automatic Refresh
LoV Hierarchical
LoV Export With Universe
LoV Delegate Search
LoV Name
LoV Is Custom SQL
LoV SQL
Can Be Used In Condition
Can Be Used Result
Can Be Used Sort
Security Access Level
Database Format

Classes
Universe Name
Universe Full Name
Class Path
Class Id
Class Name
Class Description
Class Show
Parent Class

Conditions
Universe Name
Universe Full Name
Class Path
Class Id
Class Name
Condition Id
Condition Name
Condition Description
Condition Show
Condition Where
Tables
Mandatory Condition
Apply On LoV

Hierarchies
Universe Name
Universe Full Name
Hierarcy Id
Hierarchy Name
Object Id
Object Name

Tables
Universe Name
Universe Full Name
Table Id
Table Name
Alias
Is Derived
Derived SQL

Table Columns
Universe Name
Universe Full Name
Table Id
Table Name
Column Name

Joins
Universe Name
Universe Full Name
Join Id
Left Table
Right Table
Outer Join
Short Cut
Expression

Contexts
Universe Name
Universe Full Name
Context Name
Context Description
Join Id
Left Table
Right Table

Index Awareness
Universe Name
Universe Full Name
Class Path
Class Name
Object Name
Type
Select
Where
Enable

Incompatibilities
Universe Name
Universe Full Name
Table
Type
Object Id
Object Name