Search

Custom Search

Monday, August 25, 2008

@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:





MonthBranchTransaction 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:





MonthCustomerTransaction Amount
JanCust#1 10
Jan Cust#215
Feb Cust#120
FebCust#210


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