Archive for June, 2011

Report Design: Guidelines & Best Practices

Introduction – Gives the basic guidelines/practices that could be followed in any Report Design.


  • Give meaningful names for the report tabs
  •  For complex reports, keep an overview report tab explaining the report
  • Use the Report properties to give more information about the report


  • Each Dataprovider should be given a name that reflects the usage of the data its going to fetch.
  • Select Objects in such a fashion that the resulting SQL gives a hierarchial order of Tables. This helps to achieve SQL Optimisation.
  • Avoid bringing lot of data into the report which will unnecessarily slow down the report performance.

Report Variables

  • Follow the naming convention of “var_” as prefix to each report level variable. This helps to identify Report Variables different from Universe Objects.
  •  Each variable that carries a calculation involving division should have IF <Denominator> <> 0 THEN <Object>. This avoids display of #DIV/0 errors in the report.
  •  Avoid having deep nested calculations which will slow down the performance of the report.

Report Structure

  • Make use of Report Templates when having most of the report with similar structures. This makes the work to move faster and consistant across.

Report Formats

  • All the reports should have page layout set in a printable manner. (Landscape/Portrait, Fit in 1 page wideor/and 1 page tall are different options).
  • All the reports should have page numbers in the footer.
  • All the reports should have Last Refreshed Timestamp in the header or footer.
  • All the above can be standardized by using templates

Report CELL Formats

  • All Numeric should be given Number format as per the language Eg. For German #.##00 for English #,##00.
  • Number cells should have a Right Alignment while Text cells should have Left Alignment.
  •  Cell showing Percentage should carry the % text (either Column Header or in each cell).
  • Indenting should ALWAYS be done using the Indenting Tool and NOT by using ” “.

With lots of Reports to be made, Universes to be designed and in parallel, Processes to followed for QA Analysys, there would be little things to remember that can help to design which in long terms helps for ease of maintenance, readability and helps to avoid rework for simple mistakes.

The document is a compilation of learnings that can be used as Guideline and Best Practices for Report & Universe Design.

Universe Design: Guidelines & Best Practices

Introduction – Gives the basic guidelines/practices that could be followed in any Universe Design


  •  When using a repository, always define a SECURED Connection to the Database.
  •  Use the Universe Property panel to define the Universe Use and Version (last update).
  •  Define the Connection Name that helps for Easy Database Identification.
  •  Parameters – SQL Tab – Multiple SQL statements for each measure to be unchecked.
  •  Parameters – SQL Tab – Cartesian Products – Prevent is checked.


  •  Define Universe Classes / Subclasses as per the business logic & Naming Convetion.
  •  Involve the business users in defining the classes hierarchy and business names for the classes and objects.
  •  AVOID Auto Class generation in the Designer.
  •  Give description for the use of each Class/SubClass.
  •  Avoid deep level of subclasses as it reduces the navigability and usability.


  •  Object to be used in calculation HAS to be Measure Objects.
  •  Object to be used in Analysis HAS to be Dimension Objects.
  •  Give description for the use of each Object.
  •  Include an Eg. In the description for Objects used in LOV.
  •  Do not set LOV Option for each Dimension. Use it only for required Objects, esp. those to be used in

Report Prompts.

  •  Keep “Automatic Refresh before Use” option clicked for LOV Objects:
  •  If LOV is editable by the user, provide a significant name to List Name under object properties.
  •  All the measure objects should use aggregate functions. This will ensure that the aggregation happens at the database for the selected dimensions.
  •  Avoid having dupicate Object names (in different classes).
  •  Format for objects of type Numeric, Currency & Date should be defined.

Predefined Conditions

  •  Give description for the use of each pre-defined condition.
  •  If Condition is resulting in a Prompt, make sure associated Dimension Object has LOV.
  •  Time dimension related predefined conditions such as Current year, Current month,Previous year,Last(x) weeks, etc can be defined to make it easy for scheduling daily/weekly/period based reports.


  • Alias Tables should be named with proper functional use.
  • Arrange the tables in the Structure as per Business/Functional logic. This helps other Universe users in understanding.
  • It is always best to bring the tables without joins and build them manually. It helps the designer to understand the intricacies of the model.

Joins & Context

  • AVOID keeping hanging (not joined) tables in the structure.
  • AVOID having joins that are not part of any context.
  • Give proper functional naming to the context for easy identification.
  •  AVOID having 1:1 joins.


  • Make sure of the path for Import, which usually is always in the Business Objects’ Universe folder.
  •  LOCK the universe if Administrator/Designer does not want any user to Import/Export.
  •  DO “Integrity Check” before Exporting the Universe.
  •  Good to have correct folder structure , so that you can have a secured environment.
  •  Once exported, never delete any objects from the universe without doing an impact analysis on the object usage

Free Sample Questions for SAP Business Objects Web Intelligence(BOWI-30) Exam

Which two statements are true of an ambiguous query? (Choose two.)

A. It can be resolved with a loop.

B. It can be resolved with a context.

C. It contains all possible combinations of rows from the tables inferred by the objects.

D. It contains one or more objects that can potentially return two different types of information.



Answer- BC

Free Sample Questions for SAP Business Objects Web Intelligence(BOWI-30) Exam

You are reviewing a report with the following statement in a variable: =If([Sales revenue] > 750000) Then “Top Performer” Else If([Sales revenue] Between (200000;650000)) Then “Average Performer” Else If([Sales revenue] < 200000) Then “Low Performer” Which statement is true of the results produced by the statement for sales representatives with sales between 650,000 and 750,000?

A. They will be Top Performers

B. They will be Average Performers

C. They will not have a performance value assigned.

D. They will not be included in the report.




Answer – C



Click here to download

Lookout for more information in future blogs






Free Sample Questions for SAP Business Objects Web Intelligence(BOWI-30) Exam

You are using calculation contexts within an If() function. Which two are valid contexts for use in an If() function? (Choose two.)

A. In Cell

B. In Row

C. In Report

D. In Block




Answer: CD


Free Sample Questions for SAP Business Objects Web Intelligence(BOWI-30) Exam

What is the default behavior when creating a report from two queries from the same universe?

A. Result produces two table blocks.

B. Dimension objects are automatically merged.

C. Dimension objects are not automatically merged.

D. Results are shown on two different report tabs.



Answer: B


1.  Upgrade management tool

The Upgrade management tool is a new software component that lets you upgrade the content of your BI repository from a previous version of SAP BusinessObjects Enterprise.

You can perform a complete upgrade or an incremental upgrade. A complete upgrade replaces the functionality that had previously existed in the Central Configuration Manager (CCM). The incremental upgrade replaces the functionality that had previously existed in the Import Wizard.

To migrate the content of a BI repository to another repository with the same version, use the Lifecycle Management Console.

2. Monitoring

Monitoring is a new tool in SAP BusinessObjects Enterprise XI 4.0. This application provides the ability to capture the runtime and historical metrics of SAP BusinessObjects Enterprise XI 4.0 servers and applications, for reporting and notification. System administrators can use the tool to identify if a reporting application is functioning normally and if the response times are as expected.

The following features are also available in Monitoring:

  •   Check the performance of each server: A feature named Watches, represents the state of each server as traffic lights. The system administrator can set thresholds for these watches and receive alerts when the thresholds are breached. This assists in taking proactive steps for potential failures or outages.
  •  View critical system KPIs: For monitoring activities and resources. The KPIs are displayed in the dashboard page of the monitoring application.
  • Test system availability and response time with Probes: Probes simulate workflows to check if the servers and services in the Enterprise deployment are functioning as expected. By analyzing the roundtrip time of these probes in intervals, the system administrator can assess system usage patterns.
  • Analyze peak load and peak period for the Central Management Server (CMS): This helps the system administrator to determine whether more licenses or system resources are required.
  • Integrate with other enterprise applications: The SAP BusinessObjects Enterprise XI4.0 monitoring  application can be integrated with other enterprise applications like SAP Solution Manager and IBM Tivoli.

3. The information design tool( Universe Designer)

The information design tool is the SAP BusinessObjects design environment for creating and publishing new SAP BusinessObjects universes and connections. The information design tool lets you take advantage of these major new universe design features:

  • Dimensional universes that support OLAP dimensions and hierarchies
  • Multisource universes that federate multiple relational data sources
  • A design environment that facilitates designer teamwork and universe resource sharing
  • A security editor for universe data and metadata
  • Extended connection management
  • Easier management of repository resources

These features will be described in more detail in the subsequent posts –

You can convert your existing universes created with Universe Designer XI R2/XI 3 and start taking advantage of the new universe features. Universes created with the universe design tool in SAP BusinessObjects Business Intelligence platform 4.0 are can also be converted and edited in the information design tool.

Universes created using the information design tool can be used by the following SAP BusinessObjects data analysis and reporting applications:

  • SAP BusinessObjects Web Intelligence BI 4.0
  • SAP Crystal Reports for Enterprise BI 4.0
  • SAP BusinessObjects Explorer BI 4.0
  • SAP BusinessObjects Dashboard Design BI 4.0

Dimensional universes

The information design tool automatically generates OLAP universes for Microsoft Analysis Services OLAP cubes. The resulting universes are dimensional, including objects such as:

  • Dimensions and analysis dimensions (groups of dimensions sharing the same axis of analysis)
  • Hierarchies (parent-child as well as level-based)
  • Dimension attributes
  • Calculated members and measures
  • Named sets of members
  • Filters based on dimensional business objects
  • Hierarchical and cascading list of values

The query panel is enhanced to allow you to create queries based on hierarchies or members of hierarchies. The major new features of the query panel are:

The Member Selector that lets you do the following:

  • Use OLAP-style selection on, for example, children, descendants, or parents
  • Select hierarchy levels
  • Select calculated members and named sets
  • Create named sets of members during universe definition
  • Select members that will be excluded from queries
  • A preview pane that lets you preview result sets
  • The ability to choose values at multiple levels in a hierarchical lists of values

Multisource universes

Using the information design tool, you can create a data foundation on multiple relational data sources to create a multisource universe. Using this universe, your SAP BusinessObjects data analysis and reporting applications can query multiple relational sources at once. To provide this functionality, the query engine technology from SAP BusinessObjects Data Federator has been enhanced and integrated into SAP BusinessObjects Business IntelIigence platform 4.0.

The integrated data federation query engine provides many features including the following:

  • The ability to federate data from multiple relational databases.
  • Relational connections to SAP NetWeaver Business Warehouse. These connections let you generate data foundations as star schemas based on the relational tables in InfoCubes, MultiProviders, Virtual Providers, and DataStore objects. Objects can be defined and manipulated in the data foundation as for any relational connection.
  • Relational connections to SAS.
  •  Use of standard SQL 92 syntax to define joins, derived tables, and business objects. The query engine translates the queries for all sources, and distributes them.
  • Use of database-specific syntax to define expressions for calculated columns and derived tables in the data foundation. These native expressions take advantage of database-specific functions of the data sources.
  • Improved performance and detection for narrow queries (data coming from large tables that can be filtered using data from small tables). This semi-join technology can select the optimal strategy for retrieving data from data sources during execution, depending on the volume of data.

Design environment

To build a universe, you use editors in the information design tool to define the following resources:

  • Connections to OLAP or relational data sources
  • Data foundations based on connections to define the schema for a relational universe
  • Business layers to define the business objects based on a data foundation or an OLAP connection

You then publish the business layer which exports the business layer and the resources it references to create the universe.

Resources are stored in projects. You can share projects in a repository to make resources available to other designers. Several designers can work on the same project at the same time and can share and synchronize their universe resources.

You can also reuse universe resources. For example, you can base several business layers on a single data foundation. The data modeling in the data foundation can then be shared by multiple universes.

The following new universe features are available when designing the data foundation:

  • Multisource universes based on multiple relational connections.
  • Calculated columns. A calculated column is a new column in a table that is the result of a calculation based on one or more columns of the same table.
  • Data foundation views. A view is a set of the tables and joins in the data foundation used to focus the work of the designer on a specific part of the schema.
  • Profiling of data stored in a column. Graphs and tables show the repartition of the distinct values of the column.
  • Enhanced contexts to solve join path loops.
  • Table families. A family is a set of display parameters that can be used to visually group tables of the same type when working in the editor.
  •  A powerful search panel to visually filter the data foundation tables based on specific characteristics,for example connections, table types, contexts, or families.
  • Prompted parameters and lists of values (LOVs) that are inherited by any business layer built on the data foundation.

The following new universe features are available when designing the business layer:

  • Dimensional objects as described in the section on dimensional universes.
  • The ability to create and run test queries and store them in the business layer.
  • Business layer views. A business layer view allows you to define a subset of a universe as a starting point for building a query. With views, it is possible to create larger universes and provide smaller views that can be business-oriented, for example, a Human Resources view or a Finance view.
  • Prompted parameters and LOVs as independent objects in the universe that are shareable:
  • An LOV can be shared by multiple business layer objects.
  • An LOV can be shared by multiple parameters (prompts) or filters.
  • For multi-column LOVs, you can choose which columns to display and which column will be used for the query.

Securing universe data and metadata

Using the Security Editor in the information design tool, you can define universe security for users and groups. You first define profiles for the universe:

  • Data Security Profiles control access to data. Data Security Profiles can be seen as the equivalent to Access Restrictions defined for universes using the universe design tool.
  • Business Security Profiles control access to data by using business layer views and objects, or by defining filters on these objects.

You then assign profiles to users and groups.

In the Security Editor, you can easily browse the defined security by user or by universe. You can also preview the net security profile for a user or group, and display the security inherited by a user.

The Access Restrictions defined for universes in the universe design tool and their assignments are converted into the equivalent Data and Business Security Profiles when a secured universe is converted.

From the Security Editor, you can run a query on a universe in a repository. The query is then secured by the Data Security Profiles and Business Security Profiles that apply to the user used to log into the Security Editor.

Connection management

The information design tool lets you to create local connections on the file system or secured connections in the repository. Once you have validated a local connection, you can publish it in the repository to create an equivalent secured connection. In the repository, you store connections under the Connections folder and its sub-folders.

The same relational connections can be used by universes created with the information design tool and the universe design tool.

The same OLAP connections can be used in the information design tool and with SAP BusinessObjects Advanced Analysis.

Repository management

The Repository Resources View in the information design tool lets you navigate several repositories at the same time. You can create and edit secured connections, retrieve and convert universes, and organize the repository resources in the Connections and Universes folders and their sub-folders.

The actions you are allowed to perform in the Repository Resources View are controlled by Central Management Console security rights.