Business Objects

 

What is business objects?

Business objects is one of the leading companies in BI & Reporting. The company was created on 1990 and finally accrued by SAP on 2007. There were many products offered by business objects but the most famous one is Business Objects XI. The first official SAP release was SAP Business Objects XI3. The last available version of SAP business objects is BO R4 and the frame work was completely changed in that version to comply with Microsoft ribbon technology. The integration between Business objects and other SAP products was dramatically enhanced in this version as well.

Business Objects version history:

Release Name	No of Service Pack	Release Date
BusinessObjects 3.x	NA	1995
BusinessObjects 5	11	1999
BusinessObjects Enterprise 6.x	9	2003
BusinessObjects Enterprise XIR2	5	2005
SAP BusinessObjects Enterprise XI3	5	2008
SAP BusinessObjects BI4	??	2011

 

List of Business Objects Tools:

• Universe Designer
• Rich Client.
• Desktop Intelligence.
• Translation Manager.
• Import manager

What is Micro Cube in SAP Business Objects

 

When you create data query in SAP business Objects the data retrieved and stored in a Micro cube, known also as Webi micro cube. This Micro Cube is stored along with the report and the report size will vary based on the saved micro cube size. BO reporting engine will use this microcube to display data on the report based on the selected objects and based on the aggregation function associated with each measure. to reduce the micro cube size you need to assign database aggregation function on each measure. This will submit a grouped by query to the data base and it will reduce the micro cube size and it will enhance the overall report performance.

You can set 2 aggregation level on universe measure

Database level: used as an aggregation function applied on the measure when submitting the query to the database. if you query is against a detailed table the grouped by query will retrieve less number of rows rather than the normal query. This will lead also to a smaller micro cube size.

Report level : we can set this property from object properties tab. if we set the object qualification to be measure we will be able to set the aggregation method from the properties tab. this function will be used only in the report level on top of micro cube data to display data on the report.

Coming Back !!?

Sorry for that long period of absence. I will come back very soon with many useful posts related to BO and i will expand my posting area to include information about sap and other technologies in our fast growing world. So i need your help and feedback to find what areas that i need to focus more on it. I went through your old comments and yes i find many useful things to be done. Finally thanks for all visitors.
Taha M. Mahmoud,  The Author

What is Scope of Analysis?

Scope of Analysis is one of the most powerful features in business objects. Now you can easily build your custom hierarchies and use them in your report to navigate through data. To make it easy I will consider a small example then go step by step starting from building the universe and end with how to use the drill feature to navigate through data.

Example:

We have one fact table that contains daily sales and number of customers against branches.

Fact Table:

As of Date	Branch CD	Number of Customer	Total Sale
30-Nov-2008	101	10	4245
30-Nov-2008	102	30	543
30-Nov-2008	103	20	6434
30-Nov-2008	104	40	6543
30-Nov-2008	105	28	3264
30-Nov-2008	106	36	657
30-Nov-2008	107	48	677
30-Nov-2008	108	48	675
31-Dec-2008	101	45	546
31-Dec-2008	102	54	765
31-Dec-2008	103	63	658
31-Dec-2008	104	23	768
31-Dec-2008	105	98	876
31-Dec-2008	106	67	786
31-Dec-2008	107	34	453
31-Dec-2008	108	21	564
31-Jan-2009	101	23	546
31-Jan-2009	102	43	545
31-Jan-2009	103	67	654
31-Jan-2009	104	32	654
31-Jan-2009	105	45	675
31-Jan-2009	106	21	765
31-Jan-2009	107	45	675
31-Jan-2009	108	67	546
28-Feb-2009	101	64	657
28-Feb-2009	102	21	865
28-Feb-2009	103	36	324
28-Feb-2009	104	73	143
28-Feb-2009	105	15	432
28-Feb-2009	106	24	324
28-Feb-2009	107	53	324
28-Feb-2009	108	21	532
31-Mar-2009	101	64	321
31-Mar-2009	102	54	432
31-Mar-2009	103	23	543
31-Mar-2009	104	25	543
31-Mar-2009	105	53	546
31-Mar-2009	106	21	564
31-Mar-2009	107	68	544
31-Mar-2009	108	54	654
30-Apr-2009	101	34	546
30-Apr-2009	102	43	654
30-Apr-2009	103	21	567
30-Apr-2009	104	54	764
30-Apr-2009	105	21	675
30-Apr-2009	106	65	765
30-Apr-2009	107	32	765
30-Apr-2009	108	11	745
31-May-2009	101	33	546
31-May-2009	102	45	547
31-May-2009	103	56	564
31-May-2009	104	78	544
31-May-2009	105	98	654
31-May-2009	106	76	546
31-May-2009	107	65	654
31-May-2009	108	54	567

 

Branch hierarchy Table:

Region		Area		Branch CD	Branch
Central		First Area Riyadh		101	Branch 101
Central		First Area Riyadh		102	Branch 102
Central		Second Area Riyadh		103	Branch 103
Central		Second Area Riyadh		104	Branch 104
West		Jeddah		105	Branch 105
West		Jeddah		106	Branch 106
West		Mekkah		107	Branch 107
West		Mekkah		108	Branch 108

 

Time hierarchy Table:

As of Date		Month		Quarter	Year
30-Nov-2008		Nov		Q4	2008
31-Dec-2008		Dec		Q4	2008
31-Jan-2009		Jan		Q1	2009
28-Feb-2009		Feb		Q1	2009
31-Mar-2009		Mar		Q1	2009
30-Apr-2009		Apr		Q2	2009
31-May-2009		May		Q2	2009

 

 

Build The Universe:

To build the universe just adds the Fact Table and the 2 hierarchy tables. Make the proper joins and create 3 main classes:

 

Tables:

• Fact Table.
  
• Branch Hierarchy table.
  
• Time hierarchy table.
  

Classes (Universe window should be like this):

Fact Table (class)

----------------------

| -----> As of Date (Dimension)

| -----> Branch Code (Dimension)

| -----> Number of Customers (Measure) [Sum]

| -----> Total Sales (Measure) [Sum]

 

Time Hierarchy (Class)

----------------------

| -----> As of Date (Dimension)

| -----> Month (Dimension)

| -----> Quarter (Dimension)

| -----> Year (Dimension)

 

Branch Hierarchy:

-----------------------

| ----->Region (dimension)

| ----->Area (dimension)

| ----->Branch CD (dimension)

| ----->Branch (dimension)

Joins:

First Table		Second Table	Join criteria
Fact Table		Branch Hierarchy table	Branch CD = Branch CD
Fact Table		Time hierarchy table	As of Date = As of Date

 

Hierarchies:

By default in business objects it consider every dimension created as a standalone hierarchy. By for some cases as our example you want to define a custom hierarchy like time, geographical information and customer segmentation…etc. in our example we need to build 2 custom hierarchies.

Click on hierarchies button from the editing tool bar or go Tools --> Hierarchies

Select the custom hierarchy option and then start building your hierarchy.

You can just add the Time Hierarchy class and Branch Hierarchy class from the left ban to the right ban. Or you can click new button from the right ban. Then type you hierarchy name. after that start adding dimensions from the left ban to your hierarchy tree. When you finish you can start order your hierarchy level by clicking move up and down buttons. Your hierarchy tree should be like this at the end:

Branch Hierarchy:

-----------------------

| --------- > Region

| --------- >Area

| --------- >Branch

Time Hierarchy:

----------------------

| --------- > As of Date

| --------- > Month

| --------- >Quarter

| --------- >Year

Note That if you even store all your data as detailed in one big table you will be able to build your hierarchy based on dimension that you will select to construct your customer hierarchies. For example assume if the fact table looks like this.

As of date	Month	Quarter	Year	Branch	Area	Region	No of Cust	Total Sales

 

This table is no more fact table and we call it a detail table.

 

Scope of analysis:

Now after we finish bulding our univers let go and build our report. Assume that we just need to build a very simple report like this

Year	2008		2009
Region	No of Cust	Total Sales	No of Cust	Total Sales
center	342	23432	234	23432
west	234	23424	424	324

 

Create a new report based on our universe that we build earlier. Select region, year, number of customer and total sales then run the report. After that click on the drill icon (Analysis --> Drill) and then right click on any of the selected dimension (region, year) you will find that there is no option to drill by.

Now edit data provider (Data --> Edit data provider) and click view SQL button for the tool bar. You will find that the data selected are just the four columns that we have selected. Now to find what Scope of Analysis do click on the Scope of Analysis button and select both time and branch hierarchies. Please note that you can expand the hierarchies build before and select a specific dimensions. Let's assume that you want to analysis your figures by quarter, and year only and you are not interested in monthly figures or daily figures. Then all what you need is to put a check mark in the check box beside year and quarter in the time hierarchy tree when you open the scope of analysis window.

Now click again on view SQL button and note that all dimension with a check make beside in scope of analysis added to your SQL statement even if you didn't select them in the query builder window (remember that we have selected 2 dimensions [region and year])

Run the report after that.

 

Drill:

After running the report click on drill icon (analysis à drill) and then right click on region you will find that you can do the following:

• Drill down: will go to the second level of the hierarchy. In our example it will display the area information and note that there is a drill down filter (combo box) displayed in the drill down filter tool bar. If you right click again on are and select drill down you will go to the second level (branch) and so on. If you filter on a specific region from the drill down menu it will just display the selected region date.
  
• Drill up: will go a level up. If you were in area and click on drill up it will return back to region.
  
• Drill by: a menu will displayed contains all hierarchy that you have selected in scope of analysis. The menu should be like this if you right click on region dimension
  
• Drill by:
  Area
  Branch
  ----------
  Quarter
  

 

• Drill through: to select another dimension that you didn't select in scope of analysis. In our example it will be month because we select year and quarter only from the time hierarchy tree.
  

 

• Scope of analysis: if you want to update hierarchies that you want to analysis your data according to.
  
• Set as drill filter: make this column a drill filter that you can filter your report data from it.
  

 

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;

}