Universe

A universe is a data abstraction layer which is built with the BusinessObjects Designer tool. The universe dose not contains any data itself, it only acts as an interface. This layer is where you define your "Business Objects" [Dimensions, Measures, Details and Filters] according to your business requirements. You will need also to define a valid database connection to the database schema that you want to use to build your universe. Then you can select tables you want or create new derived tables from the existing ones. You can also create joins between tables or let the designer tool automatically detect any reference join defined in the database schema by a reference constrain. The good point is, if the schema for the data base is changed later you will not need to go through all old reports that you build to change them. Just when you change them once in universe it will be reflects in all reports.

This is a simple way to understande the roll of the universe
Database --> Schema (Tables + Columns) --> Universe (Objects [Dimentions, measures, detailes and filters]) --> report.

Convert Balance

Introduction:

While you build your report you may face a case that you want to convert balance or money amount from currency to another. This case comes from that fact that when you want to store balances you store the amount in a decimal type column in your database and you store the currency in another column. This gives you the flexibility to use any convert balance function later to display your balance in your report currency. So we accept that you may display your balances in report with currency (report currency) that differs from the currency stored in the database (database currency).

Report Level Convert:

Business objects presents 4 functions that you can use to convert your balance. But there is two restrictions to use these functions.

Functions:

EuroConvertFrom(balance_amount ; from_currency_ISO ; Decimals _Blaces)

2) EuroConvertFrom(balance_amount; from_currency_ISO ; Decimals _Blaces)

3) EuroFromRoundError(balance_amount; from_currency_ISO ; Decimals _Blaces)

4) EuroToRoundError(balance_amount; from_currency_ISO ; Decimals _Blaces)

Restrictions:

1) You can convert from or to Euro only.

2) You have to select one of the following currencies (Reason: those European nation currencies have a fixed exchange rate with Euro)

Currencies table:

(code):country(currency)

BEF: Belgian (franc)

DEM: German (mark)

GRD: Greek (drachma)

ESP: Spanish (peseta)

FRF: French (franc)

IEP: Irish (punt)

ITL: Italian (lira)

LUF: Luxembourg (franc)

NLG: Dutch (guilder)

ATS: Austrian (schilling)

PTS: Portugese (escudo)

FIM: Finnish (mark)

Universe Level Convert:

You have to write a database procedure and use it whenever you need to convert balance. You can write a tailored procedure or a general use one. Here is a small pseudo code that you can use:

Convert_balance (p_balance, p_from_currency, p_to_currency, p_as_of_date) return converted _balance

{

Select exchange_rate

Into v_exchange_rate

From daily_exchange_rate_table a

Where a.as_of_date = p_As_of_date

And a.from_Currency = p_from_currency

And a.to_currency = p_to_currency

Return p_balance * v_exchange_rate;

}

@Aggregate_Aware

Function Name: @Aggregate_Aware
Description:

This function used to increase the performance by directing the report query to select objects form the aggregated table instead of the detailed table.

It may be also used to make some objects incompatible with some others. The best example for such case is the stand alone table that you add in your universe for a specific report. For sure you don’t want the user select other incompatible objects from other incompatible tables.

Example # 1: (performance)

[UNIVERSE TABLES]

Assume that you have universe with 2 tables:

BANK_TRANSACTION_DAILY (

AS_OF_DATE,

TRANSACTION_ID,

PARTY_ID,

ACCOUNT_NUMBER,

BRANCH,

TRANSACTION_AMOUNT

)

This table contains detailed daily information for the transaction. The second table

BANK_TRANSACTION_MONTHLY (

MONTH,

BRANCH,

NO_OF_TRANSACTION,

TRANSACTION_AMOUNT

)

This is an aggregated table that aggregates this data monthly.

[UNIVERSE OBJECTS]

Dimensions:

Date: @Aggregate_Aware (BANK_TRANSACTION_MONTHLY. AS_OF_DATE, BANK_TRANSACTION_DAILY. MONTH)

Transaction ID: BANK_TRANSACTION_DAILY. TRANSACTION_ID

Party ID: BANK_TRANSACTION_DAILY. PARTY_ID

Account Number: BANK_TRANSACTION_DAILY. ACCOUNT_NUMBER

Branch: @Aggregate_Aware (BANK_TRANSACTION_MONTHLY. BRANCH, BANK_TRANSACTION_DAILY. BRANCH)

Measures:

Number of Transactions: BANK_TRANSACTION_MONTHLY. NO_OF_TRANSACTION

Transaction Amount: @Aggregate_Aware (BANK_TRANSACTION_MONTHLY. TRANSACTION_AMOUNT

, BANK_TRANSACTION_DAILY. TRANSACTION_AMOUNT)

Note:

1 When we use the ‘@Aggregate_Aware’ function we consider putting the faster column (aggregated) first. So we put the monthly column then the daily one.

2 We can use more than one aggregated table. In our example we can add a yearly level aggregated table and so when we use the ‘@Aggregate_Aware’ the yearly column will come first cause it is the faster one among (most aggregate) all the three tables.
Ex: Transaction Amount: @Aggregate_Aware (BANK_TRANSACTION_YEARLY. TRANSACTION_AMOUNT, BANK_TRANSACTION_MONTHLY. TRANSACTION_AMOUNT, BANK_TRANSACTION_DAILY. TRANSACTION_AMOUNT)

[REPORTS]

Report # 1:

Objective: display Transaction amount per branch monthly wise (Month, Branch, and Transaction Amount)

Screen:

Month	Branch	Transaction Amount
Jan	Bran#1	10
Jan	Bran#2	15
Feb	Bran#1	20
Feb	Bran#2	10

Query Panel:

Objects: (Date, Branch and Transaction Amount)

Filter : N/A

Description: Now we have selected 3 objects. The business objects engine has two ways to calculate data in this report. The first one is to go to the daily table and to sum the transaction amount grouped by the branch and month. The second is simply to go to the aggregate table (BANK_TRANSACTION_MONTHLY) in our case and just retrieve the required data directly without need to sum. Note that number of rows in BANK_TRANSACTION_MONTHLY less than number of rows in the daily one and so there will be a great increase in query performance if it runs against the monthly (aggregated) table.

Report # 2:

Objective: display Transaction amount per Customer monthly wise (Month, Branch, and Transaction Amount)

Screen:

Month	Customer	Transaction Amount
Jan	Cust#1	10
Jan	Cust#2	15
Feb	Cust#1	20
Feb	Cust#2	10

Query Panel:

Objectsà (Date, Party ID and Transaction Amount)

Filter à N/A

Description: Now look carefully for this report. The date information can be retrieved from the two tables (Monthly and daily) this is the same also for the Transaction Amount object. But what about the Party ID object. This information is only available in the detailed table (Daily one) and there is no other place to retrieve this information from. So business object query engine will go to the bad choice because this is the only way to calculate data in this report.

[NOTES]

1 @Aggregate_Aware function tells business objects that it can calculate the column value from many tables.

2 Business objects query engine decide based on the objects chosen in the report witch table to use to retrieve data from (aggregated table or the detailed one) based on columns passed as parameters to @Aggregate_Aware function.

3 @Aggregate_Aware function is basically used to increase performance by target the report’s query to the fastest table when it is visible.

4 Use Toolsà aggregate navigation [Designer Application] to set the incompatible objects with your tables and incompatible tables with your objects.



Example # 2: (incompatible)

If you have in your universe a standalone table that used for some specific and tailored report, and you don’t want the end user to miss up with objects from the stand alone table and from the other tables in the universe. Just do as follow:

1 Define all stand alone table use the @Aggregate_Aware function Ex: Revenueà @Aggregate_Aware(STANDALONE_TABLE.REVENUE)

2 Use Toolsà aggregate navigation [Designer Application] to set the incompatible objects with your tables and incompatible tables with your objects.

3 While you build your report if you choose and object form the stand alone table in the query panel and then tried to select other incompatible objects from another table you will receive an incompatible objects error message and it will force you to use objects from the standalone table only or form other universe tables only.

END: For any information or question related for this topic just leave a comment or send me e-mail tahaqp@gmail.com