Performance Tuning kanagarajeee@gmail.

com

First, Let us look into the question what are Performance Issues in a report?

1) Reports are running extremely slow and getting timed out

2) BO Report has significant slow response time
3) Performance of the BO Report displaying aggregated or summarized data is extremely sloe
4) BO report is taking more processing time and still displaying partial data
5) A list of values request is taking more than fifteen minutes to return

Second, Let us look into the options of how to we tune the performance of the reports

BO reports can be optimized at 4 levels:

a) Universe level
b) Report level
c) Database level
d) Server level
Level 1 optimization - Universe level

-> Modify Array Fetch parameter

-> Allocate weight to each table
-> Use shortcut joins
-> Use aggreagte functions
-> Use aggregate tables
-> Minimize usage of the derived tables

Modify Array Fetch parameter: The Array fetch parameter sets the maximum number of rows that
are permitted in a FETCH proedure. For example, of the Array Fetch size is 20, and total rows are
100, then five fetches will be executed to retrieve the data, which will consume more time in
comparison with one fetch.
Resolution: If network allows sending large arrays, then set Array fetch parameter to new larger
value. This speed up the FETCH procedure, and reduce query processing time.

Allocating table weights: Table weight is a measure of how many rows there are in a table. Lighter
tables have less rows than heavier tables. By default BusinessObjects sorts the tables from the
lighter to the heavier tables. The order in which tables are sorted at the database level depends on
your database. For example, Sybase uses the same order as BusinessObjects, but Oracle uses the
opposite order. The SQL will be optimized for most databases, but not for Oracle where the smallest
table is put first in the sort order. So, if you are using an Oracle database, you can optimize the SQL
by reversing the order that BusinessObjects sorts the tables. To do this you must change a
parameter in the relevant PRM file of the database.
Resolution: Business Objects settings, the ORACLE PRM file must be modified as below:
• Browse to directory Business Objects\BusinessObjects Enterprise
6\dataAccess\RDBMS\connectionServer\oracle.
• Open ORACLE.PRM file, change the REVERSE_TABLE_WEIGHT
 value to N from Y.

Using Shortcut joins: Numbers of tables in join are more, even when selected objects are less. Even
when no object of related table is selected, then also that table is appearing in the join condition.
For e.g., If A_id object from A table of C table is selected with B table in between, then BO generated
SQL shows that intermediate table ‘B’ table was present in ‘From’ clause.
Resolution: Shortcut joins allow users to skip intermediate tables and allow alternative
paths between tables. Use of shortcut join reduces the number of tables used in query to improve
SQL performance. Results in query performance improving from 1.5 minute to 30 seconds!!

Use aggregate functions: Data is aggregated on the subject of analysis (user selected criteria) at
report level. This takes more processing time, as data from database is loaded in temporary
memory and then aggregated or processed to display.
Resolution: Use aggregate functions (e.g., sum, count, min, max) in measure objects at universe
level. Aggregate functions will aggregate the data at database level rather than at report level which
will save on processing time at report level and also reduce the number of rows returned back to
report.

Creating and using aggregate tables: Aggregate data are obtained by scanning and summarizing all
of the records in the fact table at real-time which consumes more time.
Resolution: Aggregate tables contain pre-calculated aggregated data. Using aggregate tables
instead of detail tables enhances the performance of SQL transactions and speeds up query
execution. Aggregate_Awareness function has ability to dynamically re-write SQL to the level of
granularity needed to answer a business question. Aggregate tables allow for faster querying speed
and increases query performance manifolds!!

Minimize usage of derived tables: Since derived tables are evaluated and executed at runtime, SQL
tuning is not possible.
Resolution: Minimize the usage of derived tabled and replace them with tables or materialized
view. SQL tuning techniques such as creating index can be applied on tables or materialized views
which will improve performance of BO reports.

Level 2 optimization - Report level

-> Opt for Refresh At- Will over Refresh-On-Open

-> List of Values (LOV's)
-> Conditional Objects
-> Complex Calcualtion in ETL
-> Minimize usage of Report variables/formulas

Opt for Refresh At- Will over Refresh-On-Open: Refresh-on-open reports refresh new data each time
it is opened. Connection with database is established each time report is refreshed which in turn
slows the report performance.
Resolution: If report is based on snapshot data and static, it is better to publish report without
refresh-on-open property. Users will thus view the same instance of report without establishing
database connection, which will reduce the response time of BO report.

List of Values (LOV's): When we create LOV object, distinct values are selected into it. DISTINCT
forces an internal sort/compare on the table. Selecting a distinct list on large table is not
optimal e.g., selecting a distinct list of custom_store against t_curr_tran_daily table is not
optimal.
Resolution:
a. Re-map the object list of values to smaller look up tables.
b. If there are no smaller lookup tables, then create external file as a source to LOV. This file needs to
be exported along with universe and be available to all users, which is additional overhead. Usage of
external file replaces the need of lookup table and delivers high performance and weighs down the
overhead cost
c. Avoid creating LOV on dates and measures. Disassociate LOV from all such objects which are not
display as prompts.

Universe Condition Objects: The entire data from database is fetched (<=maximum rows setting) and
the filters are applied at the report level. As data is not restricted at the database or universe
level, the reports takes more time to execute.
Resolution:
When handling huge data, one of the following steps can be taken to limit data:
1. Use prompts to restrict data selection at universe level. Preferably use time period prompts in
reports.
2. Replace report filters with Universe condition objects, if possible. Usage of conditional objects will
limit rows returned at database level.

Complex Calculations: The data from database is fetched and then calculations are applied to that
data. As calculations are performed at universe or report level on huge data, reports takes
more time to execute.
Resolution: When dealing with huge data warehouses perform complex calculations at ETL level.
Thus Business Objects saves time on calculations and deliver high performance.

Minimize usage of Report variables/formulas: If the report is pulling tons of data, doing loads of
joins, making lot of clever calculations, using lot of report variables and formulas, report may run
very slow. Report variables and formulas are loaded and calculated in memory at real time.
As variables are created at real time and calculations are performed at report level, reports takes
more time to execute.
Resolution: When dealing with big reports, minimize usage of report variables/formulas and try
to place them at universe to deliver high performance reports.

Level 3 optimization - Database level

Examine the execution plan of SQL: Determine the execution plan of BO generated SQL in target
database. EXPLAIN PLAN is a handy tool for estimating resource requirements in advance. It
displays execution plans chosen by Oracle optimizer without executing it and gives an insight on how
to make improvements at database level.
 Level 4 optimization - Server level

If the performance of system deteriorates when reports are accessed by larger number
of users over web, then fix the problem at fourth level i.e., server level (Level 4).

-> Scalable System

-> Event Based Scheduling
-> Report Server/Job Server closer to database server
-> Maximum Allowed Size of Cache
-> Minutes Before an Idle Connection is closed
-> File Polling Interval in Seconds
-> Maximum Number of Dwonloaded Documents to Cache
-> Oldest On-demand Data given to a Client

Performance issue finish here.

Web Intelligence Related Questions

1) How does Web Intelligence work?
Web Intelligence provides business users an easy to use interactive and flexible user interface for
building and analyzing reports on corporate data over the web, on secured intranets and extranets.
The Web Intelligence software is installed by your administrator on a web server on your corporate
network. To use Web Intelligence from you local computer, you log into the business intelligence
portal InfoView via your Internet browser. Then, depending on your security profile, you can interact
with the reports in corporate documents or edit or build your own documents using a Web
Intelligence report panel or query panel.

2) What are different tools used to create or edit Web Intelligence reports?
You can create or edit Web Intelligence documents using one of several tools:
• Java Report Panel
• Query – HTML
• HTML Report Panel

3) How is Web Intelligence Java Report Panel designed?

The Java Report Panel is designed for users who need more flexibility with designing report layout
and defining formulas and variables. A graphical Formula Editor enables you to build formulas
rapidly using drag-and-drop.
Note: The Web Intelligence Java Report Panel is available if your administrator has deployed Web
Intelligence in ASP mode and if your administrator has deployed Web Intelligence in JSP mode.

4) How is Web Intelligence Query- HTML designed?

Designed for users requiring a pure HTML environment to build data providers, Web Intelligence
Query – HTML offers the ability to define the data content of documents on multiple data sources.
You can use Query – HTML to create new documents from scratch or edit the data providers in
documents created using any of the other Web Intelligence tools.

Used together with On-Report Analysis, Query – HTML provides a complete solution for building
data providers and designing powerful reports in a pure HTML environment. Once you have run the
data providers to generate a standard report, you can leverage Web Intelligence On-Report Analysis
features to format multiple reports, add formulas, and create variables.
Note: Web Intelligence Query – HTML and On-Report Analysis in Interactive view format are only
available, if your administrator has deployed Web Intelligence in JSP mode.

5) How is Web Intelligence HTML Report Panel?

Designed for users who need to build basic reports, the HTML Report Panel provides query and
report features in a simple wizard-like interface. Each document is based on a single data source and
can contain multiple reports, displaying different subsets of information. In addition, the HTML
Report Panel is 508 compliant and can be customized for specialized deployments.

Note: The Web Intelligence HTML Report Panel is only available, if your administrator has deployed
Web Intelligence in JSP mode.

6) What information do you need before logging into Infoview?

Before you can use InfoView and Web Intelligence you need the following information:
• a URL to the InfoView server
• the InfoView server name and port number
• your login and password
• your authentication, which controls the InfoView resources available to you

7) What are the two ways in which data in the Web Intelligence document is edited?
There are two ways to open a Web Intelligence document in edit mode:
• view the document first and then switch to Edit mode
• switch to edit mode directly without viewing the document contents first

8) What are data providers?

A data provider contains one or more queries that return data from a database. A query requests
data from the database. If the data is available, then the requested data is returned by default in the
form of a table which contains rows and columns. When you build a query, you are creating a
request for information from a database. A request can be very simple, for example; give me total
sales in California for the first quarter of last year, or more complicated, for example; give me an
average age of customers who bought sweaters during the spring television promotion in Paris.
Queries are sent to the databases in a language called SQL (Structured Query Language). However,
when you use Web Intelligence you do not have to know any SQL. The Web Intelligence report panel
presents the information available in the database as objects that have names and meanings familiar
to you. These objects are organized in a structure called a universe.
You build data providers by combining objects in a universe. The universe translates the objects
presented in your business language to SQL, and then sends the request for information to the
database. Web Intelligence can generate SQL data providers of unlimited length. When the data is
returned to the Web Intelligence report panel, it is presented in a table form, with columns that
have the same names as the objects that you used in the query. The data is arranged in rows.

9) What is scope of analysis?

The scope of analysis for a query is extra data that you can retrieve from the database to give more
details on the data returned by each of the objects in a query. This extra data does not appear in the
initial result document, but it remains available in the data cube, so you can pull this data in to the
report to allow you to access more detail at any time. This process of refining the data to lower
levels of detail is called drilling down on an object.
In the universe, the scope of analysis corresponds to the hierarchical levels below the object
selected for a query. For example, a scope of analysis of one level down for the object Year, would
include the object Quarter, which appears immediately under Year.
You can set this level when you build a query. It allows objects lower down the hierarchy to be
included in the query, without them appearing in the Results Objects pane. The hierarchies in a
universe allow you to choose your scope of analysis, and correspondingly the level of drill available.
You can also create a custom scope of analysis by selecting specific dimensions to the Scope of
Analysis pane.
Note: You cannot define a scope of analysis when working in Query Drill mode. Query Drill defines
the scope automatically in response to drill actions.
10) What is the functionality of cube?
The scope of analysis for a query is extra data that you can retrieve from the database to give more
details on the data returned by each of the objects in a query. This extra data does not appear in the
initial result document, but it remains available in the data cube, so you can pull this data in to the
report to allow you to access more detail at any time. This process of refining the data to lower
levels of detail is called drilling down on an object.
When you run the query, the dimensions included in the scope of analysis are returned to the cube
for that document, but are not projected onto the reports that the document contains. They can be
added to the reports at any time, without having to run the query again. Values for the dimensions
in the scope of analysis can also be viewed by switching a report to Drill mode, and then drilling
down to them from the values displayed on the report.

11) What is an ambiguous query?

An ambiguous query is a query that contains one or more objects that can potentially return two
different types of information. In a universe, certain dimensions may have values that are used for
two different purposes in the database. For example, the [Country] dimension in the query below
can return two types of information:
• Customers and the country in which they spent their vacation.
• Customers and the country for which they have made their reservation.
The role that Country plays in this query is ambiguous. A country value can be either the country
where a vacation was sold, or a country where a vacation is reserved. One is existing information
(sales), and the other is future information (reservations). To avoid ambiguities in a query, the
universe designer identifies the different ways that objects can be used in the universe, and
implements restrictions on how these objects can be combined. These restrictions are called
contexts.

12) What is a context?

A context is a defined group of objects that share a common business purpose. This business
purpose is usually the type of information that these related objects represent. For example, a sales
context is a grouping of all the objects that can be used to create sales data providers. A reservations
context is a grouping of all the objects that can be used in reservation data providers. Contexts are
defined in a universe by the universe designer.
You can combine any object within the same context to create a query. You can also combine
objects in different contexts. If you use an object that is common to both contexts, Web Intelligence
will try to determine the context that best fits the other objects in the query.
13) What are incompatible objects?
Sometimes it is not possible to use certain combinations of objects in data providers. This situation
arises when objects bear no relationship to one another. These objects are called incompatible
objects.
For example, the Island Resorts Marketing universe contains the [Reservation Year] and [Revenue]
objects, which are incompatible. This is because there is no revenue associated with a reservation.
Revenue is generated only when the customer is invoiced. The underlying database structure
reflects this; you cannot build a query that aggregates revenue by reservation year because there is
no such thing as revenue by reservation year. In other words, the aggregation context that you
specified for the
[Revenue] object does not exist.

When you build a query, Web Intelligence generates SQL behind the scenes. This SQL is run against
the database to produce a result that Web Intelligence displays in a report. For a query to be free of
incompatible objects, Web Intelligence must be able to generate a single SQL query to retrieve the
data. If this is not possible, the query contains incompatible objects.

14) How can queries be combined and what is the use?

You can combine queries in three relationships:
• union
• intersection
• minus
In a union combination, Web Intelligence takes the all the data from both queries, eliminates
duplicate rows, and builds a combined data set.
In an intersection combination, Web Intelligence returns the data that is common to both queries.
In a minus combination, Web Intelligence returns the data in the first query that does not appear in
the second.

15) What is purging?

When you purge data from a document you remove all data from the document, while leaving the
document structure intact. If the document contains multiple data providers, you can purge specific
data providers within the document.

16) What are the different table templates used in Web Intelligence?
The different table templates you can use to display information on Web Intelligence reports:
• Vertical tables: Vertical tables display header cells at the top of the table and the
corresponding data in columns.
• Vorizontal tables: Horizontal tables display header cells at the left of the table and the
corresponding data in rows.
• Crosstabs: Crosstabs are useful for presenting results that correspond to the intersection of two or
more dimensions.
• Forms: Forms are useful in your report if you want to display detailed information per customer,
product, or partner. For example, a form is a useful way of
displaying individual customer records with information such as the customer account, name,
address, and so on

P O S T ED BY B O W OR L D ! ! ! AT 7 : 5 3 P M

0 COMMENTS:

Restriction Sets

Universe can be secured in two ways:

1) Restrict access to entire universe by setting universe rights in the Central Management
Console(CMC)

2) Create various forced and optional restrictions within designer

-> Forced
- Object restrictions
- Self-restricting joins
- Inferring multiple tables
-> Optional
- Condition Objects

Purpose of Restriction sets in Business

Business requirement to secure business critical data based on a user's role in the organization.

Restriction Set:
-> A restriction set is a named group for restrictions that apply to a universe
-> Restriction sets can be applied to Business Objects users and groups to force behavior changes in
a universe
-> Restriction sets are managed using the Business Objects Universe Designer application, using a
feature named access restrictions

Manage Access Restrictions in Designer

1) Create new restriction
2) Add appropriate groups and users (add priority)
3) Map restrictions to group and users

Tips:

-> Export the universe before you apply restriction sets

-> Although most changes become effective when a universe is exported, remember that restrictions
take effect as soon as they are applied

P O S T ED BY B O W OR L D ! ! ! AT 5 : 4 9 P M

List of Values (LOVs)

Using a List of Values

A list of values is a list that contains the data values associated with an object. A list of values can
contain data from two types of data source:

1) Database file: When you create an object, Designer automatically associates a list of values with
the object. The list of values is not created until a user, or you the designer, choose to display

a list of values for the object in the Query pane. A SELECT DISTINCT query is then run against the
column or columns inferred by the object. The returned data is stored in a file with a.LOV extension
in the universe sub folder created under the same folder that stores the universe file.The.LOV file is
then used as the source for values for the list.
2) External file: Personal data, for example a text file, or an Excel file can be associated with a list of
values. A list of values that is based on an external file is fixed. You cannot have a dynamic link with
an external file. You must refresh the.LOV file if your external file has changed.

How is a list of values used in Web Intelligence?

In Web Intelligence, a user can create a query in the Query pane using the operand “Show list of
values” to apply to an object when applying a condition.

Note: A.LOV file is also created whenever any condition is applied to an object in the Query pane
that requires a restriction on the column values inferred by the object.

A.LOV file is also created whenever any condition is applied to an object in the Query pane that
requires a restriction on the column values inferred by the object.

The List of Values for an object appears showing values available for the object, allowing the user to
choose the terms for the condition. The first time a list of values is used, it is saved as a.LOV file in
the universe sub folder on the file system. This allows the SELECT DISTINCT query to be run only
once for an object. This folder also stores the.LOV files created in Designer which are used to restrict
the list of values returned for objects for which the designer wants to control access to the data.

How List of values are used with an object

When you create a dimension or detail object in Designer, it is automatically assigned an associated
list of values. This list does not physically exist when you create an object, but by default, the object
has the ability to query the database to return a list of its values when used in the Query pane.
Note: No default list of values is assigned to measure objects. When a condition is first placed on an
object in the Query pane that requires a list of values to be displayed in Designer, a SELECT DISTINCT
statement is run against the appropriate columns inferred by the object, and the list of values is
returned.

A.LOV file is automatically created in the universe subfolder to hold the list values. The next time
that the list of values is required for the object in Designer, the values are returned from the.LOV file
and not from the database.

The designer’s role in controlling lists of values

As the universe designer, you can define how the data is presented in the list, and define restrictions
on the amount and type of data returned to the list. You can set the properties for an object to
determine the following actions for a list of values:

• If a list of values is associated with an object.

• When the list is refreshed.

• Define a query that sets conditions on the SELECT DISTINCT query that an object uses to return a
list of values. You save this query in the properties of an object.

• Display list values either as a simple list, or as an object hierarchy.

• If the list is based on column values, or values from an external file, for example an Excel
spreadsheet.

You can also create a permanent list for values for an object and export this list to the repository.
This.LOV file is then always used as the list of values for that object. It is not updated.

List of values properties and options

Associate a List of Values:

• When selected, allows a list of values to be associated with the object. It is selected by default.

• When cleared, no list of values is associated with the object.

• Selected by default for dimensions and details. Not selected for measures.

List name: Name of the.LOV file that stores the returned list data. Limited to 8 characters.

Allow users to edit this List of Values:

• When selected, users can edit the list of values file in Web Intelligence.

• When cleared, the user cannot edit the list.

Note: This does not apply to personal data files such as Excel spreadsheets. These are not exported
to the repository. They remain on a local machine. A user can edit a local file, or change the target
list of values for another local data file.

The purpose of a list of values is usually to limit the set of available values to a user. If they can edit a
list, you no longer have control over the values they choose. Normally, if you are not using a
personal data file as a list of values source, you clear this option to ensure that users do not

edit lists of values.

Automatic refresh before use:

• When selected, the list data is refreshed each time the list of values for an object is displayed in
the Query pane. This can have an effect on performance each time the .LOV is refreshed. This option
does not apply to Web Intelligence reports.

• When cleared, the list is refreshed only once at the start of a user logon session.

-> If the list contains values that regularly change, then you can select this option, but
you should take into account the effect on performance.
 -> If the list contents are stable, then you should clear this option.

Export with universe:

• When selected, the.LOV file associated with the object is exported with the universe to the
repository. The universe domain and document domain must exist on the same data account. A list
of values is stored in the document domain. The document domain does not have to be visible to the
a user’s profile in Supervisor.

• You must create the list of values that is associated with the object for it to be exported. This list is
saved as a.LOV file.

• When cleared, a.LOV file for the object is not exported to the repository. Select this option if you
customize this list regularly. This allows your modifications to be exported and imported with the
universe.

Exporting List of Values

You can export a list of values with the universe to the CMS. On the file system, the associated .LOV
file is copied to a universe sub directory in the same folder that stores the universe file.

When a user runs a query in Web Intelligence using an object that is associated with a .LOV file
exported from Designer, the list of values that is returned for the object is determined by one of the
following:

• The data contained in the .LOV file.

• The SQL for the SELECT DISTINCT query defined in the .LOV file.

If you have created a condition in Designer to restrict the data values returned for an object, the
restricted list appears, and not the default list of all the data values. The list retains all conditions and
formatting implemented in Designer.
If you had not exported the .LOV file with the universe, then the object would simply return the
default list with no conditions and formatting. A default .LOV file would then be created to hold the
data.

Administering lists of values in the universe

You can manage all the lists of values in the active universe from the Lists of Values dialog box
(Tools > Lists of Values). All the classes and objects are presented in a tree view. You can select any
object, and access its list of values. You can perform the following actions from the Lists of Values
dialog box:

-> Edit: Displays the Query pane used to define a query for the selected

object. You can define and edit existing queries for a list of

values.

-> Display: Displays the current list of values for the selected object.

-> Purge: Clears the contents of the list of values currently assigned to the selected object.

-> Refresh: Refreshes the display of the list of values.

P O S T ED BY B O W OR L D ! ! ! AT 2 : 4 9 P M

1 COMMENTS:

1.

Pavan Kumar T LJune 24, 2010 5:13 AM

Hi,

I am new to BO.
I have created a LOV object and assigned it to a conditional object. This conditional objects is
defined with @Prompt function.

The values of LOV are as follows.

Tech
 Non-Tech
Both

When I run my report , I am prompted for these values. When a user selects any of the
above values what I want to happen to pass a different value in the back-ground to the
query than the string of LOV itself.

i.e

When User submits "Tech" , I want value '1' to be passed and if user selects "Non-Tech" , I
want value '0' to be passed and if user selects "Both" I want '1;0' to be passed to the query.

Can you help me.

MONDAY, MAY 25, 2009

Desktop Intelligence Related Questions

1) What is Desktop Intelligence?
Desktop Intelligence is an integrated query, reporting and analysis solution for business
professionals that allows you to access the data in your corporate databases directly from your
desktop and present and analyze this information in a Desktop Intelligence document.
When you run the query, Desktop Intelligence connects to the database and retrieves the data
mapped to the objects you selected. A query is a type of data provider. The data provider contains
the data you have chosen to retrieve from the data source. Using this data set, you can build
interactive reports. Desktop Intelligence lets you access data from a wide range of sources: from
relational and multidimensional databases, from packaged applications, from personal data
documents, and, using Microsoft Visual Basic for Applications procedures, from virtually any source.

2) How is information shared in business objects?

You can quickly and easily share the documents you have created with other users in your company,
either by sending them directly to selected individuals or groups, or by Exporting them to the
repository as Folders or Categories. The repository stores the documents you send so that other
users can retrieve and view them. It also stores information about the documents it stores, such as
name of sender, date, and also which users in the company have the right to retrieve and view a
document. You can Import documents that other users have sent, using Web Intelligence documents
which you can open and view in Desktop Intelligence. You can also use InfoView to send documents
for scheduled processing.

3) How are documents organised in repository?

The Repository organizes documents into Folders and Categories in an orderly system that permits
easy access for you and others working with documents. You select the documents you want to
import from or export to Desktop Intelligence.

Folders: Folders are the physical place where documents are stored. Only one document with a
given name may be placed in a folder or category. It is possible to place documents in several
categories. If necessary change the name of the document or give it a number to place it in the same
folder or category. It is possible to create or delete sub-folders.
There are 2 types of folders:
• My Folders with 2 sub-folders
• Favorites (Generally reserved for often used documents)
• Inbox (Generally reserved for documents received from other users
• Public Folders (For shared documents.)

Categories: Categories are used for classifying information regardless of its storage location.
There are two types of Categories:
• Corporate Categories
• Personal Categories

4) What are different data sources available for Desktop Intelligence?

Desktop Intelligence lets you access data from a wide range of sources. You can access data from:
• Universes
• Personal Data Files
•Stored Procedures: You can only use stored procedures if your supervisor or IS department has
provided them, and if the RDBMS at your site supports them. A stored procedure is an SQL
(Structured Query Language) script, saved and executable on your database.
• Freehand SQL Server: You can use free-hand SQL if you are familiar with SQL, which is the language
used to interact with relational databases. In free-hand SQL, you open or write a SQL script, which
you then run against the database.
• XML Data Provider
• VBA Data Provider
5) Can all Desktop Intelligence users build queries using all data providers?
Your Desktop Intelligence supervisor can restrict access to certain types of data providers, or even
certain objects within a universe. As a result, you might be able to build queries on universes but no
other type of data provider, and then be able to use only certain objects in the universe. The way the
supervisor sets up access to data providers and other Desktop Intelligence features depends entirely
upon the query and reporting needs of your organization.

6) Who sets up connection to data providers in desktop intelligence?

Universe: The Universe designer sets up connection to the database.
Stored Procedures: The supervisor creates the connection to access a stored procedure.
Free- hand SQL: In free-hand SQL, you can create your own connection to the database. Once you
have created the connection, you can make it available to other users.
Personal data files and XML files: When you access data in a personal data file or XML file, you select
the file and in doing so, you “connect” to it. This is not a technical task, it’s just a question of
selecting the right file.
VBA procedures: A VBA procedure runs a VBA macro that retrieves data for your Desktop
Intelligence report. The person who creates the macro defines the connection to the data source in
the macro code.

7) What are restrictive connections to database in desktop intelligence mean?

If you are working with a universe that is set up with a restrictive connection, you need to supply the
database username and password to run a query. This username/password is not the one that you
use to log onto Desktop Intelligence; it is the username/password of the underlying database (for
example an SQL Server database) that the universe accesses. This database normally remains
hidden, but the universe designer can set up a restrictive connection to add an extra layer of
security. Depending on the type of restrictive connection, you need to supply the database
username and password in some or all of the following situations:
• When you first run a query
• When you refresh a query
• When you parse a query to test its validity

8) How Universe queries are generated using Desktop Intelligence?

When you build a query, you select objects from a universe, then run the query. Desktop Intelligence
connects to the database, and retrieves the data mapped by the objects you selected. Desktop
Intelligence retrieves this data by executing an SQL query against the database. Desktop Intelligence
generates this SQL according to the objects you select.

9) What is meant by building powerful queries in Desktop Intelligence?

You build a simple query by adding objects to the Query Panel. The procedures described in the
following sections enable you to build a more powerful query by controlling the data that your
queries retrieve. You can:
• Define scope of analysis
• Conditions
• Sort data
• Retrieve a specified number of rows of data
• Eliminate duplicate rows of data from the query result

10) What is meant by Scope of Analysis?

Analysis means looking at data from different viewpoints and on different levels of detail. “Scope of
analysis” means a subset of data, returned by a query, that you will use for analysis in your report.
The data for your scope of analysis does not appear in the report until you decide that you want to
use it in analysis.

The scope of analysis you can define depends on hierarchies in the universe. A hierarchy, which the
designer sets up when creating the universe, consists of dimension objects ranked from “less
detailed” to “more detailed”. The objects that belong to hierarchies are the ones you can use to
define scope of analysis.

11) How do you limit the query using conditions and what are they?
A condition is a way of limiting the data that a query returns. In Desktop Intelligence, you can set
three types of conditions on a query:
Predefined conditions: When universe designers build universes, they can create predefined
conditions for you to use.

Simple conditions: Enable you to limit data returned by a result object. For example, you can find out
about certain customers by applying a simple condition on the Customer object, then selecting the
customer names that appear in a dialog box.

Complex conditions: Enable you to limit the query results by any object in the universe.

12) What are options that can be setup before running a query?
Before running a query, you can set options that enable you to:
• Specify the number of rows of data that you want the query to return. The Default Value option
corresponds to the maximum number of rows that the universe designer specified for queries on the
current universe.
• Eliminate duplicate rows of data.
• Retrieve no data when you run the query. In this case, Desktop Intelligence generates the query
SQL but does not connect to the database. The names of the objects included in the query appear as
column headings in the report. This option is useful if you want to save the query you have built, but
refresh it at an off-peak time.

13) What are the restrictions on Free-hand SQL?

The types of SQL script that you are allowed to run as free-hand SQL are determined by your
Desktop Intelligence administrator. If you attempt to run a script for which you do not have
permission, you will receive an error message. Typically, you are able to run scripts that contain only
one SELECT statement.

Note: BusinessObjects does not execute COMPUTE and ORDER BY clauses in free-hand SQL
statements.

14) What are restrictions on Stored Procedures?

• The Desktop Intelligence supervisor grants access to the database or account where stored
procedures are located.
• Not all RDBMSs support stored procedures.

• COMPUTE, PRINT, OUTPUT or STATUS statements contained in stored procedures are not
executed.

15) What are the benefits on using Personal Data files?

The main benefits of using personal data files are as follows:
• You can display corporate data next to personal data in the same report which can be used for
comparison.
• If you have no connection to a remote database or if there is no RDBMS at your site, you can use
personal data files as your only data source.
• You can use Desktop Intelligence reporting and analysis features to work on data that comes from
other applications.
16) How do you use Visual Basic for Applications procedures?
A VBA data provider is a powerful and flexible tool for accessing external data. Very often you will
want to access automation servers through VBA to retrieve their proprietary data. VBA allows you to
retrieve data from various sources: ADO, DAO, RDO, Application Object Models, EDK and low-level
APIs.

To create a VBA data provider, you write a VBA procedure that takes the interface DpVBA Interface
as a parameter. You can write this procedure from within the VBA environment of Desktop
Intelligence. The DpVBAInterface is the interface to the VBA data provider Automation object. The
procedure for writing a VBA data provider is:

1. Create a connection to the data source.

2. Create a data cube.
3. Set the data cube dimensions.
4. Populate the cube with data from the data source.

Once the data cube is populated, you can generate a report based on this data in Desktop
Intelligence.

17) What is an XML?

XML is a text-based data format that structures data in elements or tags. XML files are similar to the
HTML files used to build pages on the World Wide Web. The principal difference is that, the set of
HTML elements is limited to those used to describe the structure of a Web page, an XML file can
contain any elements, depending on its application.

Creating an XML-based report involves two steps:

• building an XML filter
• building the report

18) What are common dimensions?

Common dimensions are dimensions with the same name occurring in the same universe.
Dimensions called Year that occur in a universe and an Excel spreadsheet are not common. Desktop
Intelligence will prompt you to link them.

19) What situations require you link data providers?

Desktop Intelligence automatically links data providers with a common dimension. Two dimensions
in separate data providers are common when they belong to the same universe and have the same
name. Desktop Intelligence prompts you to link data providers if there is no common dimension
between the data providers. If you simply want to add columns of data to a report, use the Edit Data
Provider command on the Data menu instead of building a new query. This method lets you add
result objects to the initial query; Desktop Intelligence automatically inserts the new columns of data
in the report or creates a new report.

20) Which dimension should act as a link between data providers?

It is necessary that the dimension you use to link data providers be the same type (numeric or
alphanumeric) in both data providers. If not, two rows of data will appear for the linked object when
you create a table that uses the object.
Additionally you should use only dimensions that return the same type of values. It doesn’t make
sense to create a link between dimensions with totally different lists of values (Year and Region, for
example).

21) What is difference between purging and deleting of data providers?

Purging means emptying a data provider of its results, deleting means getting rid of the data
provider for good —an action that cannot be undone.
Why purge or delete a data provider? Purging reduces the size of a document, so is useful when you
want to send the document to other users, or save it on a diskette, for example. You should only
delete a data provider, however, if you are certain that you and other users no longer need it.

22) What are different ways in which data can be analyzed using desktop intelligence?
• Desktop Intelligence on-report analysis allows you to work directly on your data in your report
using drag and drop and or with simple mouse clicks.
• Desktop Intelligence enables you to carry out multidimensional analysis in Drill mode.
• Desktop Intelligence Slice and Dice mode allows you to organize data for analysis in the slice and
dice panel.

23) What is On-report analysis?

You can drag and drop data on your report to get a different viewpoint for your analysis. You can
add data from the Report Manager to create tables and sections. You can replace, swap and re-
organize data on the report. Desktop Intelligence redoes the calculations in your report immediately
so that you can see at once how different combinations of factors affect your performance. Dynamic
on-report grouping allows you to create groups for comparative analysis and you can quickly insert
common business calculations or easily create your own formulas and variables for analysis. You can
sort, filter and rank your data using a simple mouse click to focus your analysis on a slice of data.

24) What is Drill – mode?

Drill mode is a Desktop Intelligence analysis mode that allows you to break down data and view it
from different angles and on different levels of detail to discover what the driving factor is behind a
good or bad result. Drill mode allows you to include data for analysis behind the scenes of your
report and display the top level data only. If necessary, you can drill down to the more detailed data
to understand the higher-level data. This allows you to see how different aspects of your business
affect your revenue step by step.

25) How does drill mode work?

The person who creates the Desktop Intelligence universe organizes objects in hierarchies, with the
most general object in the class at the top and the most detailed at the bottom. Objects are grouped
in this way to make it easy for you to find what you are looking for. They are classified inside the
groups so that if you want to make a high level report you know you need to include objects at the
top of the list in your query and if you want a more detailed report then you choose objects from
further down the list.

26) How are hierarchies used in drill mode?

When you analyze data in drill mode, you use hierarchies. The universe classes are the default
hierarchies you use for drilling but the universe designer can also set up custom hierarchies. You can
also create and edit hierarchies in your reports. Drill hierarchies contain dimension objects only. In
drill mode, you drill down on dimensions, for example from Year to Quarter to Month. At each level
Desktop Intelligence recalculates measures such as Revenue or Profit Margin.

27) What is the difference between drill-up, drill-down, drill across and drill through?
Drilling down: When you drill down, you display the next level of detail in a hierarchy
Drilling up: Drilling up is the opposite of drilling down. When you drill up, you go back up through the
hierarchy to display data on less detailed levels.
Drilling across: When you drill down and up, you move through the levels of the same hierarchy.
However, if you cannot find the answer to a question by analyzing data in its current hierarchy, you
can move to another hierarchy to analyze other data.
Drilling through to the database to bring in new data If the lowest level of detail you need is not
currently available in the report, you can drill through to the database directly from drill mode and
get the data you need. You do not have to edit the query in the Query Panel.
28) What is Slice Dice mode?
Slice-and-dice mode enables you to switch the position of data in a report, for example by moving
columns to rows to create a crosstab. You can also use slice-and-dice mode to:
• work with master/detail reports
• display and remove data
• rename, reset and delete blocks
• turn tables and crosstabs into charts, and vice versa
• apply, edit and delete breaks, filters, sorts, rankings and calculations

You access slice-and-dice mode through the Slice and Dice Panel, a pop-up window that provides a
graphical representation of the report you are working on. You carry out slice-and-dice tasks by
dragging and dropping icons that represent your data.

29) What are master/detail reports?

Master/detail reports display data in sections. Each section contains a “master” or parent piece of
data, for example a resort, or a year. The rest of the data in the section relates to the master.

30) How are filters used to manage filters?

A filter enables you to hide data you do not want to view behind the scenes and display only the
data you need. There are two types of filter. A global filter affects the whole report. A block specific
filter filters data for the specified chart, table or crosstab only.

31) How can the filter be ignored using formulas?

You can force Desktop Intelligence to ignore any filters you have inserted on a report so that it
calculates on all data, not just the filtered values. To do this, you use the NoFilter function. The
syntax is: =NoFilter(formula)

In the first table, the sum includes New York and Washington revenues only.
The formula to calculate this sum is: =Sum()

In the second table, formula for calculating the sum includes the NoFilter function. As a result, the
sum includes revenues for all cities. The formula is as follows:
=NoFilter (Sum())

32) What is the use of ranking?

You might want to show just the extreme ranges of your data, for example your top ten customers.
Ranking enables you to look at the largest and smallest numbers in a report. Like filtering, it hides
the data you do not want to display. Desktop Intelligence does not delete the data from the report;
you can view it again whenever you like by removing the ranking. Ranking also sorts the data in
descending order. Thus, the largest value of the ranking is always at the top of the ranked column
and the smallest value at the bottom. You can rank data in tables, crosstabs or master cells in
master/detail reports.

33) How is ranking performed if they are breaks inserted inside the data?
In a table or crosstab in which breaks have been inserted, data is ranked separately for each break
level.

Note: If you have created a local variable using values from different data providers, you will not be
able to rank data based on this variable. The variable will not be displayed in the list in the Ranking
dialog box.

34) How do you manage ranking with filters and sorts?

To rank data in a report, you must remove any sorts or filters currently applied to that data. If any
sorts or filters exist when you try to apply a ranking, Desktop Intelligence displays the following
message: A sort and/or a filter has already been applied to the selection. Do you want to overwrite
it?

35) What is the function of alerts?

You can highlight data in a Desktop Intelligence report using alerters. Alerters use special formatting
to make data that fits certain conditions stand out from the rest of the data. This helps draw
attention to trends and exceptions in report data.

36) Can we use alerters on charts?

No

37) What are user objects and what does it contain?

A universe consists primarily of classes and objects created by the universe designer. If the objects in
a universe do not meet your needs, you can create your own additional user objects. User objects
appear in the User Objects class in the universe. You include them in queries in the same way that
you include regular objects. You do not need to define a connection to a database to define a user
object.
A user object has a name, a type (character, date or numeric), a qualification (dimension, measure or
detail) and a formula. The formula contains a combination of functions, objects, user objects,
operators, and text. User objects are end-user personal objects that are not shared with other end-
users. User objects are defined for each universe and stored on a local file inside the “\Universe”
folder. For example, if you create a user object in the BEACH.UNV universe, Desktop Intelligence
stores it locally in the file BEACH.UDO in the Universe folder.

38) What are the restrictions on user objects?

· Cannot be moved from User class: You can work only with the user objects that you create yourself,
and you cannot move user objects from the User Objects class.
· Also, user objects are available only in the universe in which they were created.
· User objects are not shared.
· Reports that include user objects can only be viewed by other end-users. This is because user
objects are stored locally in a user object definition file. Other end-users, who do not have the same
user object definition file, are not able to access the user object definitions.
· If an end-user tries to refresh or edit a query that contains another user’s user objects, Desktop
Intelligence removes the objects from the query and report. Despite these restrictions, the universe
designer can convert user objects into regular objects that can be made available in other universes
and for other users.
Note: You cannot schedule reports that contain user objects and keep the user objects; they are
removed when the report is refreshed.

39) How can the user objects be shared among the end users?
If you want to share user objects with other users, you should ask the universe designer to include
these user objects in the related universe in order to make them available to all Desktop Intelligence
end-users.

40) What are wildcard characters?

Conditions with the Match pattern and Different from pattern operators are great for finding lists of
similar values, such as customer names beginning with S.

Wildcards are special characters that can denote any single character, or any number of characters.
Desktop Intelligence supports the standard wildcard characters, which are:
% : Replaces several characters, or in the response to a prompt. For example, N% returns all values
beginning with an N (New York, Nevada)
- : Replaces a single character in a constant. For example, GR_VE returns Grave, Grove, Greve.
41) How does the operator in list work?
The In list operator lets you select multiple values for a document. These multiple values can be a
condition on a query that you want to build or can be the basis for an interactive document in which
Desktop Intelligence prompts other users to select values from the list you created to view data
limited to their needs.

42) Explain the difference between Different from and except operator?
Different from, Not in list and Except are all operators that exclude certain data from your query
results. Does this mean that you could use this condition to obtain a list of customers who have not
stayed at Bahamas Beach?

Resort Different from ‘Bahamas Beach’

The result of this query includes those customers who have stayed at Bahamas Beach and
elsewhere. Why? Because reservations exist for these customers for resorts other than Bahamas
Beach. These reservations alone are enough to satisfy the condition ‘Resort differs from Bahamas
Beach’. Furthermore, this condition excludes customers who have made no reservations. Desktop
Intelligence checks these customers’ records against reservations and determines that no
reservations satisfy the condition ‘Resort differs from Bahamas Beach’ - because there are no
reservations! Nevertheless, it is clear that a report showing customers who have not stayed at
Bahamas Beach should include customers who have not stayed anywhere.

You solve this problem by using the Except operator instead of Different from. When you use Except,
Desktop Intelligence builds two queries:
• All customers.
• Customers who have reservations for Bahamas Beach.
Desktop Intelligence then subtracts the customers given by the second query from those given by
the first. This returns the result you want.

Note also that:

• You can only specify one value with Different from, but multiple values with Not in list.
• You can only specify one value with Except. However, you can build combined queries using
MINUS to exclude, for example, Bahamas Beach customers and 1996 customers.

43) Define level of calculation?

· If you want to obtain a single result row: click Globally, then click Next.
· If you want to obtain several result rows: click By one or more objects, select the objects from the
list.

44) What is meant by synchronization of calculation?

While making a condition in the query panel and adding a calculation to a condition after choosing
the level of calculation it prompts for synchronizing the calculation.
This is can be done in two ways:
· If you want to make a calculation independently of your objects: click Independently of your
objects, then click Next.
· If you want to make a calculation for each value of one or more of your objects, which allows you
to limit the calculation to particular objects: click For each value of one or more objects, select the
objects from the list, then click Next.

45) What is meant by applying calculation to a query?

When you use a calculation, Desktop Intelligence builds SQL that contains a subquery. A subquery is
an inner query. The database that receives the SQL generated by Desktop Intelligence evaluates the
result of the inner query against each row of the outer query to determine if the row should appear
in the result.

In the process as you move through the wizard, you specify:

Ø The object to use in the calculation: This is the object in the outer query whose value is compared
against the result of the inner query. In the example, the object is Invoice Date.
Ø The aggregate function to apply to the object: In the example you applied the Maximum function
because you were interested in the most recent invoice date.
Ø The level of calculation: This determines the grouping in the subquery. In the example you chose
Globally because you were interested simply in the customer’s latest invoice date, not a latest
invoice date by some other criteria.
Ø Synchronization: This determines the subquery links to the main query In the example you chose
the Customer object because you were interested in each customer’s latest invoice date.
Ø The number of values to compare: This determines how many values in the subquery the database
compares against the values in the outer query. In the example you can choose either option
because the subquery returns one row only for each customer.

46) What is list of values?

A list of values contains the values returned by an object. You use lists of values to select the value(s)
you need when defining conditions on a query or when selecting the value(s) in a prompt. When you
use or view a list of values for the first time, Desktop Intelligence creates a .lov file that contains the
query definition and the values it returns. By default, .lov files are located in sub-folders inside the
UserDocs folder.

47) How are list of values created?

In Designer, the universe designer decides whether to associate a list of values with an object. Once
associated, the list of values can be viewed or edited in Designer or Desktop Intelligence. The first
time you view an object’s list of values, Desktop Intelligence runs a query and retrieves the values
from the database; the object’s default list of values is generated by the object query.

48) Where does the function Variance doesnot work?

Variance syntax containing the Where operator will work in all cases except when you have two
cascading Wheres (a variance using a Where operator that contains a formula that also uses a
Where operator) or in certain contexts, for example the variance of the variable "" in Report.

49) What is a crosstab?

A crosstab displays data in rows and columns, as opposed to a table which displays data in columns
only. Measures are typically placed in the body of a crosstab at the intersection of rows and
columns.

50) What are input context and output contexts?

Desktop Intelligence defines an input context and an output context to determine the result of an
aggregate calculation. A context is made up of one or more dimensions.
-The input context consists of one or more dimensions that go into the calculation.
-The output context consists of one or more dimensions that Determine the result of the calculation.
The syntax for input and output contexts is as follows:
=AggregateFunction( In ) In

The following example explains this in more detail.

Example: The following formula returns the minimum revenue per city per region:
=Min( In (,)) In
The input context consists of Region and City, while the output context is Region.

51) What are reset contexts?

You use a reset context in a cumulative aggregation, such as running total revenue per quarter. The
reset context consists of one or more dimensions which reset the value of the calculation to zero
each time a dimension value changes.

Example: Calculating running total revenue per country

You want to calculate running total revenue per country per year, and naturally you want the
calculation to be reset for each country. When the value of Country changes, you want the
calculation to begin at 0. To obtain this result, you display Country, Year and Revenue in a table, and
apply a break on Country. You then add the cumulative aggregation =RunningSum(;)
in which you specify Country as the reset context.

The syntax is
=RunningAggregateFunction(;)
giving, for example =Sum(;;)

52) What are local variables?

A local variable is a named formula. Local variables appear in the list of variables in the Report
Manager Data tab; you can use them to build tables, charts and crosstabs in the same way as you
use variables returned by a data provider.

Why use local variables?

Variables have a number of advantages over formulas because there are some things you cannot do
using formulas alone:
• You cannot apply alerters, filters, sorts and breaks on columns or rows containing formulas, but
you can on those containing variables.
• You can include variables qualified as dimensions in drill hierarchies. Local variables are also useful
because:
• You can re-use them easily in the same document.
• Formulas can be complex. You can use (and reuse) variables in formulas to simplify them. Because
you can re-use variables, you don’t need to type the same formulas over and over again. Variables
make complex formulas easier to decipher because they break the formulas up into manageable
components.

53) What are functions?

Desktop Intelligence contains many built-in functions which greatly extend its capabilities. Functions
are pre-defined formulas. A function consists of the function name followed by a pair of
parentheses. The parentheses can contain arguments and arguments supply functions with values
on which to operate. Arguments can be objects, constants or other functions.

54) Concatenation:

Combining data in a single cell:

Concatenation(character string, character string)

=Concatenation(,)

=Concatenation( ,(Concatenation(" " ,))) Name>)))

The syntax to achieve the same result as shown above using the & operator is:

=&" "&

The Concatenation() function and & operator allow you to combine charactertype data only. If you
want to combine text or character-type data with numbers you must first convert the number into a
character string. Otherwise, Desktop Intelligence displays the error message 'Incompatible data
type'. You can convert a number to a character string using the FormatNumber() function.

In the same way, if you want to combine text with dates using the & operator or the Concatenation()
function, you must first convert the date into a character string. Otherwise, Desktop Intelligence
displays the error message 'Incompatible data type'. You can convert a date into a character string
using the FormatDate() function.

P O S T ED BY B O W OR L D ! ! ! AT 7 : 3 6 P M 1 C OM M E NT S

Joins
Equi Join: An equi-join links two tables on common values in a column in table 1 with a column in
table 2. The restriction conforms to the following syntax:
Table1.column_a = Table2.column_a
In a normalized database the columns used in an equi-join are usually the primary key from one
table and the foreign key in the other

Theta Join: A theta join is a join that links tables based on a relationship other than equality between
two columns. A theta join could use any operator other than the “equal” operator.
Outer Join: An outer join is a join that links two tables, one of which has rows that do not match
those in the common column of the other table. You define an outer join by specifying which table is
the outer table in the original equi-join. The outer table contains the column for which you want to
return all values, even if they are unmatched.

Shortcut join:A shortcut join is a join that provides an alternative path between two tables.
Shortcut joins improve the performance of a query by not taking into account intermediate tables,
and so shortening a normally longer join path.

Self restricting joins: A self restricting join is not really a join at all, but a self restriction on a single
table. You can use a self restricting join to restrict the results returned by a table values using a fixed
value.

P O S T ED BY B O W OR L D ! ! ! AT 7 : 0 5 P M 0 C OM M E NT S

Universe Related Questions

1) What is a Universe?
Universe is a semantic layer that isolates you from the complexities of the database. A universe
maps to data in the database in everyday terms that describe your business situation. Universes are
made up of classes and objects. For example, the objects in a human resources universe would be
Names, Addresses, Salaries. Classes are logical groupings of objects. Each class has a meaningful
name, such as Vacation (for objects pertaining to employee vacations). Each object maps to data in
the database and enables you to retrieve data for your reports.

2) How do u export universe to repository?

You make a universe available to Web Intelligence users and other designers by exporting a universe
to the repository. When you export a universe the universe is: Moved to the selected universe folder
on the repository file system and Created in the Central Management System (CMS). Each time the
universe is exported to the repository, the universe version in the CMS is updated. Exporting a
universe to the repository involves following steps:
1. Select File > Export. The Export Universe dialog box appears.
2. Select a universe folder from the folder drop down list box. (or) Click the Browse button and select
a universe folder in the folder browser.
3. If you want to lock the universe, double-click the universe name.
4. Click a group in the Groups list box. This is the user group that uses the exported universe.
5. Click a universe in the Universes list box. The Universes list box shows the names of the active
universes.
6. If you want to export other universes that are not open, click the Add Universe button, and then
use the browser to select the other universes.
7. Click

3) What is Strategies?
A strategy is a script that automatically extracts structural information from a database or flat file.
Strategies have two principle roles:
• Automatic join and cardinality detection (Join strategies)
• Automatic class, object, and join creation (Objects and Joins strategies)
Strategies can be useful if you want to automate the detection and creation of structures in your
universe based on the SQL structures in the database. There exits two types of strategies:
-Built in strategy is the Default strategy shipped with Designer. Built in strategies cannot be
customized.
-External strategy: User defined script that contains the same type of information as a Built in
strategy, but customized to optimize information retrieval from a database.

4) What is a derived table?

Derived tables are tables that you define in the universe schema. You create objects on them as you
do with any other table. A derived table is defined by an SQL query at the universe level that can be
used as a logical table in Designer. Derived tables have the following advantages:
• Reduced amount of data returned to the document for analysis.
• Reduced maintenance of database summary tables. Derived tables can, in some cases, replace
statistical tables that hold results for complex calculations that are incorporated into the universe
using aggregate awareness.
Derived tables are similar to database views, with the advantage that the SQL for a derived table can
include prompts.

5) What is Join Path Problems?

A join path is a series of joins that a query can use to access data in the tables linked by the joins.
Join path problems can arise from the limited way that lookup and fact tables are related in a
relational database. The three major join path problems that you encounter when designing a
schema are the following:
• Loops: Returns too few rows. Joins form multiple paths between lookup tables.
• Chasm traps: Returns too many rows. Many to one joins from two fact tables converge on a single
lookup table. This type of join convergence can lead to a join path problem called a chasm trap.
• Fan traps: Returns too many rows. A one to many join links a table which is in turn linked by a one
to many join. This type of fanning out of one to many joins can lead to a join path problem called a
fan trap.

6) What is a Lookup Table?

A lookup (or dimension) table contains information associated with a particular entity or subject. For
example, a lookup table can hold geographical information on customers such as their names,
telephone numbers as well as the cities and countries in which they reside. In Designer, dimension
and detail objects are typically derived from lookup tables.

7) What is a Fact Table?

A fact table contains statistical information about transactions. For example, it may contain figures
such as Sales Revenue or Profit. In a universe, most but not all, measures are defined from fact
tables.

8) What are Contexts?

Contexts are a collection of joins which provide a valid query path for Web Intelligence to generate
SQL. Contexts are used in:
• Solving loops:

• Solving chasm traps.

• Assisting in some solutions for fan traps.
• Assisting in detecting incompatibility for objects using aggregate awareness.

9) How can we detect chasm trap?

Detect Contexts examines the many to one joins in the schema. It picks up the table that receives
converging many to one joins and proposes contexts to separate the queries run on the table. This is
the most effective way to ensure that your schema does not have a chasm trap.

10) How Do You Detect a Fan Trap?

You cannot automatically detect fan traps. You need to visually examine the direction of the
cardinalities displayed in the table schema. If you have two tables that are referenced by measure
objects and are joined in a series of many to one joins, then you may have a potential fan trap.

11) How does a context resolve a loop?

A context resolves a loop by defining a set of joins that specify one specific path through tables in a
loop. It ensures that joins are not included from different paths within the same SQL query.
12) How Do You Resolve a chasm Trap?
To resolve a chasm trap you need to make two separate queries and then combine the results.
Depending on the type of objects defined for the fact tables, and the type of end user environment,
you can use the following methods to resolve a chasm trap:
• Create a context for each fact table. This solution works in all cases.
• Modify the SQL parameters for the universe so you can generate separate SQL queries for each
measure. This solution only works for measure objects. It does not generate separate queries for
dimension or detail objects.

13) How Do You Resolve a Fan Trap?

There are two ways to solve a fan trap problem.
• Create an alias for the table containing the initial aggregation, then use Detect Contexts (Tools >
Detect Contexts) to detect and propose a context for the alias table and a context for the original
table. This is the most effective way to solve the fan trap problem.
• Altering the SQL parameters for the universe. This only works for measure objects.

14) What is a microcube?

When you create a measure you must specify the way the aggregate function will be projected onto
a report. Returned values for a measure object are aggregated at two levels of the query process:
• Query level. Data is aggregated using the inferred Select statement.
•Microcube Level: A microcube is a conceptual way to present the data returned by a query before it
is projected onto a report. It represents the returned values held in memory by a Business Objects
reporting product. The block level is the 2 dimensional report that a user creates with the returned
data. A user can choose to use all, or only some of the data held in the microcube to create a report.
A user can also do aggregate functions on the returned values in the microcube (local aggregation)
to create new values on a report.

15) What are @Functions?

@Functions are special functions that provide more flexible methods for specifying the SQL for an
object. @Functions are available in the Functions pane of the Edit Select box for an object.
@Functions are very flexible. Depending on what you want to achieve, you can use any @function in
either a Select statement, or a Where clause.

@Aggregate_Aware: The @Aggregate_Aware function allows an object to take advantage of tables

containing summary data in the database. If your database contains summary tables and you are
running queries that return aggregate data, it is quicker to run a Select statement on the columns
that contain summary data rather than on the columns that contain fact or event data.

@Aggregate_Aware(sum(agg_table_1), ... sum(agg_table_n))

@Prompt: You can use the @Prompt function to create an interactive object. You use a @Prompt
function in the Where clause for an object. It forces a user to enter a value for a restriction when
that object is used in a query. When the user runs the query, a prompt box appears asking for a
value to be entered. @Prompts are useful when you want to force a restriction in the inferred SQL
but do not want to preset the value of the condition.

@Prompt(‘message’,‘type’,[lov],[MONOMULTI],[FREECONSTRAINED])

@Select: You can use the @Select function to re-use the Select statement of another object. When
the @Select function is used in the Select statement of an object, it specifies the path of another
object in the universe as a parameter of the @Select function, in the form
Class_Name\Object_Name. This then acts as a pointer to the Select statement of the referenced
object.

@Select(Classname\Objectname)

Note:
When you use @Select and @Where functions, one object now depends on another in the universe.
You have created a new object dependency. When one object is deleted, the other object using the
@Select or @Where function needs to be manually updated.

@Where: You can use the @Where function to re-use the Where clause of another object. When
the @Where function is used in the Where clause of an object, it specifies the path of another object
in the universe as a parameter of the @Where function, in the form Class_Name\Object_Name. This
then acts as a pointer to the Where clause of the referenced object. Using the Where clause creates
a dynamic link between two objects. When the Where clause of the original object is modified, the
Where clause of the referencing object is automatically updated.

16) What is multidimensional analysis?

Multidimensional analysis is the analysis of dimension objects organized in meaningful hierarchies.
Multidimensional analysis allows users to observe data from various viewpoints. This enables them
to spot trends or exceptions in the data. A hierarchy is an ordered series of related dimensions. An
example of a hierarchy is Geography, which may group dimensions such as Country, Region, and
City.
In Web Intelligence you can use drill up or down to perform multi dimensional analysis.

17) What are external strategies?

An external strategy is an SQL script stored externally to the .UNV file, and structured so that it can
be used by Designer to automate object or join creation, and table detection tasks in a universe.
External strategies are stored in an external strategy file with the extension STG. External strategy
files are in XML format. There is one for each supported RDBMS.

18) What is Aggregate Awareness?

Aggregate awareness is a term that describes the ability of a universe to make use of aggregate
tables in a database. These are tables that contain pre-calculated data. You can use a function called
@Aggregate_Aware in the Select statement for an object that directs a query to be run against
aggregate tables rather than a table containing non aggregated data. Using aggregate tables speeds
up the execution of queries, improving the performance of SQL transactions.

The reliability and usefulness of aggregate awareness in a universe depends on the accuracy of the
aggregate tables. They must be refreshed at the same time as all fact tables. A universe that has one
or more objects with alternative definitions based on aggregate tables is said to be “aggregate
aware”. These definitions correspond to levels of aggregation. For example, an object called Profit
can be aggregated by month, by quarter, or by year. These objects are called aggregate objects.
Queries built from a universe using aggregate objects return information aggregated to the
appropriate level at optimal speed.

@Aggregate_Aware(sum(agg_table_1), ... sum(agg_table_n))

19) What are incompatible objects?

You must now specify the incompatible objects for each aggregate table in the universe. The set of
incompatible objects you specify determines which aggregate tables are disregarded during the
generation of SQL. With respect to an aggregate table, an object is either compatible or
incompatible. The rules for compatibility are as follows:
• When an object is at the same or higher level of aggregation as the table, it is compatible with the
table.
• When an object is at a lower level of aggregation than the table (or if it is not at all related to the
table), it is incompatible with the table.

20) What is a restriction set?

A restriction set is a named group of restrictions that apply to a universe. You can apply a restriction
set to a selected group or user account for a universe. When users connect to a universe, the
objects, rows, query types, and connection that they use in the universe are determined by their
applied restriction set. You can create, edit, and delete a restriction set at any time once the
universe has been exported to the CMS. You can create multiple restriction sets depending on the
query needs of a target user group.

21) What are linked universes?

Linked universes are universes that share common components such as parameters, classes, objects,
or joins. When you link two universes, one universe has the role of a core universe, the other a
derived universe. When changes are made in the core universe, they are automatically propagated
to the derived universes.

22) What is a core universe?

The core universe is a universe to which other universes are linked. It contains components that are
common to the other universes linking to it. These universes are called derived universes. The core
universe represents a re-usable library of components. A core universe can be a kernel or master
universe depending on the way the core universe components are used in the derived universes.

23) What are derived universes?

A derived universe is a universe that contains a link to a core universe. The link allows the derived
universe to share common components of the core universe:
• If the linked core universe is a kernel universe, then components can be added to the derived
universe.
• If the linked core universe is a master universe, then the derived universe contains all the core
universe components. Classes and objects are not added to the derived universe. They can be
hidden in the derived universe depending on the user needs of the target audience.

24) How are universes optimized?

Query time can often be shortened by optimizing a universe. There are several ways you can
optimize a universe:
• Optimizing the Array Fetch parameter in the Universe Parameters.
• Allocating a weight to each table.
• Using shortcut joins.
• Creating and using aggregate tables in your database.

Table weight is a measure of how many rows there are in a table. Lighter tables have less rows than
heavier tables. By default BusinessObjects sorts the tables from the lighter to the heavier tables. The
order in which tables are sorted at the database level depends on your database. For example,
Sybase uses the same order as BusinessObjects, but Oracle uses the opposite order. The SQL will be
optimized for most databases, but not for Oracle where the smallest table is put first in the sort
order. So, if you are using an Oracle database, you can optimize the SQL by reversing the order that
BusinessObjects sorts the tables. To do this you must change a parameter in the relevant PRM file of
the database.

25) Different ways to link universes?

The following approaches when linking universes:
• Kernel approach
• Master approach
• Component approach

Kernel approach: With the kernel approach, one universe contains the core components. These are
the components common in all universes. The derived universes that you create from this kernel
universe contain these core components as well as their own specific components.

Master approach: The master approach is another way of organizing the common components of
linked universes. The master universe holds all possible components. In the universes derived from
the master, certain components are hidden depending on their relevance to the target users of the
derived universe. The components visible in the derived universes are always a subset of the master
universe. There are no new components added specific to the derived universe.

Component approach: The component approach involves merging two or more universes into one
universe. The Sales universe below was created by merging two universes: Part 1 and Part

26) Explain about detail objects?

Dimensions are focus of analysis in a query. A dimension maps to one or more columns, functions in
the database that are key to a query. Detail Objects provides descriptive data about a dimension. A
detail is always attached to a dimension. It maps to one or more columns or functions in the
database that provide detailed information related to a dimension. One cannot drill on details nor
link on details when linking multiple data providers. While Customer ID would be a dimension,
customer name, address, phone and soon should be details.

27) What is business objects repository?

Business Objects Repository:
Ø It is a semantic layer which stores the BO Users and their privileges.
Ø Repository means set of database tables, Business object store security information e.g user,
group, access permission, user type etc. , universe information e.g. objects, classes, table name,
column name, relationship etc and document information.
Ø Repository contains data accounts with three domains in each one type. You can create the
repository anywhere. The security domain has the user security info and other domains address.

28) What are domains?

Domain is nothing but logical grouping of system tables. There are three domains usually in a basic
setup they are Secure, Universe, Document. In 6.5 Universe are situated in one domain, Reports in
one domain and Supervisor.

29) When is the Repository created?

In 5i/6i versions repository is created after installing the software, whereas in XI version a repository
is created at the time of installation.

P O S T ED BY B O W OR L D ! ! ! AT 5 : 3 0 P M 0 C OM M E NT S

Specification for Universe Design document

The TRS document contains
-> Introduction ( overview, assumptions, acronyms, stakeholders)
-> Business Requirements (report requirements)
-> Data Sources
-> Universe Design (tables names and column names, classes, Naming conventions of objects, joins &
relations, conditions to create at universe level)
->Security Design (security levels)

P O S T ED BY B O W OR L D ! ! ! AT 4 : 5 0 P M 2 C OM M E NT S

Newer PostsHome
Subscribe to: Posts (Atom)

Universe Related Questions

1) What is a Universe?
Universe is a semantic layer that isolates you from the complexities of the database. A universe
maps to data in the database in everyday terms that describe your business situation. Universes are
made up of classes and objects. For example, the objects in a human resources universe would be
Names, Addresses, Salaries. Classes are logical groupings of objects. Each class has a meaningful
name, such as Vacation (for objects pertaining to employee vacations). Each object maps to data in
the database and enables you to retrieve data for your reports.

2) How do u export universe to repository?

You make a universe available to Web Intelligence users and other designers by exporting a universe
to the repository. When you export a universe the universe is: Moved to the selected universe folder
on the repository file system and Created in the Central Management System (CMS). Each time the
universe is exported to the repository, the universe version in the CMS is updated. Exporting a
universe to the repository involves following steps:
1. Select File > Export. The Export Universe dialog box appears.
2. Select a universe folder from the folder drop down list box. (or) Click the Browse button and select
a universe folder in the folder browser.
3. If you want to lock the universe, double-click the universe name.
4. Click a group in the Groups list box. This is the user group that uses the exported universe.
5. Click a universe in the Universes list box. The Universes list box shows the names of the active
universes.
6. If you want to export other universes that are not open, click the Add Universe button, and then
use the browser to select the other universes.
7. Click

3) What is Strategies?
A strategy is a script that automatically extracts structural information from a database or flat file.
Strategies have two principle roles:
• Automatic join and cardinality detection (Join strategies)
• Automatic class, object, and join creation (Objects and Joins strategies)
Strategies can be useful if you want to automate the detection and creation of structures in your
universe based on the SQL structures in the database. There exits two types of strategies:
-Built in strategy is the Default strategy shipped with Designer. Built in strategies cannot be
customized.
-External strategy: User defined script that contains the same type of information as a Built in
strategy, but customized to optimize information retrieval from a database.

4) What is a derived table?

Derived tables are tables that you define in the universe schema. You create objects on them as you
do with any other table. A derived table is defined by an SQL query at the universe level that can be
used as a logical table in Designer. Derived tables have the following advantages:
• Reduced amount of data returned to the document for analysis.
• Reduced maintenance of database summary tables. Derived tables can, in some cases, replace
statistical tables that hold results for complex calculations that are incorporated into the universe
using aggregate awareness.
Derived tables are similar to database views, with the advantage that the SQL for a derived table can
include prompts.

5) What is Join Path Problems?

A join path is a series of joins that a query can use to access data in the tables linked by the joins.
Join path problems can arise from the limited way that lookup and fact tables are related in a
relational database. The three major join path problems that you encounter when designing a
schema are the following:
• Loops: Returns too few rows. Joins form multiple paths between lookup tables.
• Chasm traps: Returns too many rows. Many to one joins from two fact tables converge on a single
lookup table. This type of join convergence can lead to a join path problem called a chasm trap.
• Fan traps: Returns too many rows. A one to many join links a table which is in turn linked by a one
to many join. This type of fanning out of one to many joins can lead to a join path problem called a
fan trap.

6) What is a Lookup Table?

A lookup (or dimension) table contains information associated with a particular entity or subject. For
example, a lookup table can hold geographical information on customers such as their names,
telephone numbers as well as the cities and countries in which they reside. In Designer, dimension
and detail objects are typically derived from lookup tables.

7) What is a Fact Table?

A fact table contains statistical information about transactions. For example, it may contain figures
such as Sales Revenue or Profit. In a universe, most but not all, measures are defined from fact
tables.

8) What are Contexts?

Contexts are a collection of joins which provide a valid query path for Web Intelligence to generate
SQL. Contexts are used in:
• Solving loops:

• Solving chasm traps.

• Assisting in some solutions for fan traps.
• Assisting in detecting incompatibility for objects using aggregate awareness.

9) How can we detect chasm trap?

Detect Contexts examines the many to one joins in the schema. It picks up the table that receives
converging many to one joins and proposes contexts to separate the queries run on the table. This is
the most effective way to ensure that your schema does not have a chasm trap.

10) How Do You Detect a Fan Trap?

You cannot automatically detect fan traps. You need to visually examine the direction of the
cardinalities displayed in the table schema. If you have two tables that are referenced by measure
objects and are joined in a series of many to one joins, then you may have a potential fan trap.

11) How does a context resolve a loop?

A context resolves a loop by defining a set of joins that specify one specific path through tables in a
loop. It ensures that joins are not included from different paths within the same SQL query.

12) How Do You Resolve a chasm Trap?

To resolve a chasm trap you need to make two separate queries and then combine the results.
Depending on the type of objects defined for the fact tables, and the type of end user environment,
you can use the following methods to resolve a chasm trap:
• Create a context for each fact table. This solution works in all cases.
• Modify the SQL parameters for the universe so you can generate separate SQL queries for each
measure. This solution only works for measure objects. It does not generate separate queries for
dimension or detail objects.

13) How Do You Resolve a Fan Trap?

There are two ways to solve a fan trap problem.
• Create an alias for the table containing the initial aggregation, then use Detect Contexts (Tools >
Detect Contexts) to detect and propose a context for the alias table and a context for the original
table. This is the most effective way to solve the fan trap problem.
• Altering the SQL parameters for the universe. This only works for measure objects.

14) What is a microcube?

When you create a measure you must specify the way the aggregate function will be projected onto
a report. Returned values for a measure object are aggregated at two levels of the query process:
• Query level. Data is aggregated using the inferred Select statement.
•Microcube Level: A microcube is a conceptual way to present the data returned by a query before it
is projected onto a report. It represents the returned values held in memory by a Business Objects
reporting product. The block level is the 2 dimensional report that a user creates with the returned
data. A user can choose to use all, or only some of the data held in the microcube to create a report.
A user can also do aggregate functions on the returned values in the microcube (local aggregation)
to create new values on a report.

15) What are @Functions?

@Functions are special functions that provide more flexible methods for specifying the SQL for an
object. @Functions are available in the Functions pane of the Edit Select box for an object.
@Functions are very flexible. Depending on what you want to achieve, you can use any @function in
either a Select statement, or a Where clause.

@Aggregate_Aware: The @Aggregate_Aware function allows an object to take advantage of tables

containing summary data in the database. If your database contains summary tables and you are
running queries that return aggregate data, it is quicker to run a Select statement on the columns
that contain summary data rather than on the columns that contain fact or event data.

@Aggregate_Aware(sum(agg_table_1), ... sum(agg_table_n))

@Prompt: You can use the @Prompt function to create an interactive object. You use a @Prompt
function in the Where clause for an object. It forces a user to enter a value for a restriction when
that object is used in a query. When the user runs the query, a prompt box appears asking for a
value to be entered. @Prompts are useful when you want to force a restriction in the inferred SQL
but do not want to preset the value of the condition.
@Prompt(‘message’,‘type’,[lov],[MONOMULTI],[FREECONSTRAINED])

@Select: You can use the @Select function to re-use the Select statement of another object. When
the @Select function is used in the Select statement of an object, it specifies the path of another
object in the universe as a parameter of the @Select function, in the form
Class_Name\Object_Name. This then acts as a pointer to the Select statement of the referenced
object.

@Select(Classname\Objectname)

Note:
When you use @Select and @Where functions, one object now depends on another in the universe.
You have created a new object dependency. When one object is deleted, the other object using the
@Select or @Where function needs to be manually updated.

@Where: You can use the @Where function to re-use the Where clause of another object. When
the @Where function is used in the Where clause of an object, it specifies the path of another object
in the universe as a parameter of the @Where function, in the form Class_Name\Object_Name. This
then acts as a pointer to the Where clause of the referenced object. Using the Where clause creates
a dynamic link between two objects. When the Where clause of the original object is modified, the
Where clause of the referencing object is automatically updated.

16) What is multidimensional analysis?

Multidimensional analysis is the analysis of dimension objects organized in meaningful hierarchies.
Multidimensional analysis allows users to observe data from various viewpoints. This enables them
to spot trends or exceptions in the data. A hierarchy is an ordered series of related dimensions. An
example of a hierarchy is Geography, which may group dimensions such as Country, Region, and
City.
In Web Intelligence you can use drill up or down to perform multi dimensional analysis.

17) What are external strategies?

An external strategy is an SQL script stored externally to the .UNV file, and structured so that it can
be used by Designer to automate object or join creation, and table detection tasks in a universe.
External strategies are stored in an external strategy file with the extension STG. External strategy
files are in XML format. There is one for each supported RDBMS.
18) What is Aggregate Awareness?
Aggregate awareness is a term that describes the ability of a universe to make use of aggregate
tables in a database. These are tables that contain pre-calculated data. You can use a function called
@Aggregate_Aware in the Select statement for an object that directs a query to be run against
aggregate tables rather than a table containing non aggregated data. Using aggregate tables speeds
up the execution of queries, improving the performance of SQL transactions.

The reliability and usefulness of aggregate awareness in a universe depends on the accuracy of the
aggregate tables. They must be refreshed at the same time as all fact tables. A universe that has one
or more objects with alternative definitions based on aggregate tables is said to be “aggregate
aware”. These definitions correspond to levels of aggregation. For example, an object called Profit
can be aggregated by month, by quarter, or by year. These objects are called aggregate objects.
Queries built from a universe using aggregate objects return information aggregated to the
appropriate level at optimal speed.

@Aggregate_Aware(sum(agg_table_1), ... sum(agg_table_n))

19) What are incompatible objects?

You must now specify the incompatible objects for each aggregate table in the universe. The set of
incompatible objects you specify determines which aggregate tables are disregarded during the
generation of SQL. With respect to an aggregate table, an object is either compatible or
incompatible. The rules for compatibility are as follows:
• When an object is at the same or higher level of aggregation as the table, it is compatible with the
table.
• When an object is at a lower level of aggregation than the table (or if it is not at all related to the
table), it is incompatible with the table.

20) What is a restriction set?

A restriction set is a named group of restrictions that apply to a universe. You can apply a restriction
set to a selected group or user account for a universe. When users connect to a universe, the
objects, rows, query types, and connection that they use in the universe are determined by their
applied restriction set. You can create, edit, and delete a restriction set at any time once the
universe has been exported to the CMS. You can create multiple restriction sets depending on the
query needs of a target user group.

21) What are linked universes?

Linked universes are universes that share common components such as parameters, classes, objects,
or joins. When you link two universes, one universe has the role of a core universe, the other a
derived universe. When changes are made in the core universe, they are automatically propagated
to the derived universes.

22) What is a core universe?

The core universe is a universe to which other universes are linked. It contains components that are
common to the other universes linking to it. These universes are called derived universes. The core
universe represents a re-usable library of components. A core universe can be a kernel or master
universe depending on the way the core universe components are used in the derived universes.

23) What are derived universes?

A derived universe is a universe that contains a link to a core universe. The link allows the derived
universe to share common components of the core universe:
• If the linked core universe is a kernel universe, then components can be added to the derived
universe.
• If the linked core universe is a master universe, then the derived universe contains all the core
universe components. Classes and objects are not added to the derived universe. They can be
hidden in the derived universe depending on the user needs of the target audience.

24) How are universes optimized?

Query time can often be shortened by optimizing a universe. There are several ways you can
optimize a universe:
• Optimizing the Array Fetch parameter in the Universe Parameters.
• Allocating a weight to each table.
• Using shortcut joins.
• Creating and using aggregate tables in your database.

Table weight is a measure of how many rows there are in a table. Lighter tables have less rows than
heavier tables. By default BusinessObjects sorts the tables from the lighter to the heavier tables. The
order in which tables are sorted at the database level depends on your database. For example,
Sybase uses the same order as BusinessObjects, but Oracle uses the opposite order. The SQL will be
optimized for most databases, but not for Oracle where the smallest table is put first in the sort
order. So, if you are using an Oracle database, you can optimize the SQL by reversing the order that
BusinessObjects sorts the tables. To do this you must change a parameter in the relevant PRM file of
the database.
25) Different ways to link universes?
The following approaches when linking universes:
• Kernel approach
• Master approach
• Component approach

Kernel approach: With the kernel approach, one universe contains the core components. These are
the components common in all universes. The derived universes that you create from this kernel
universe contain these core components as well as their own specific components.

Master approach: The master approach is another way of organizing the common components of
linked universes. The master universe holds all possible components. In the universes derived from
the master, certain components are hidden depending on their relevance to the target users of the
derived universe. The components visible in the derived universes are always a subset of the master
universe. There are no new components added specific to the derived universe.

Component approach: The component approach involves merging two or more universes into one
universe. The Sales universe below was created by merging two universes: Part 1 and Part

26) Explain about detail objects?

Dimensions are focus of analysis in a query. A dimension maps to one or more columns, functions in
the database that are key to a query. Detail Objects provides descriptive data about a dimension. A
detail is always attached to a dimension. It maps to one or more columns or functions in the
database that provide detailed information related to a dimension. One cannot drill on details nor
link on details when linking multiple data providers. While Customer ID would be a dimension,
customer name, address, phone and soon should be details.

27) What is business objects repository?

Business Objects Repository:
Ø It is a semantic layer which stores the BO Users and their privileges.
Ø Repository means set of database tables, Business object store security information e.g user,
group, access permission, user type etc. , universe information e.g. objects, classes, table name,
column name, relationship etc and document information.
Ø Repository contains data accounts with three domains in each one type. You can create the
repository anywhere. The security domain has the user security info and other domains address.
28) What are domains?
Domain is nothing but logical grouping of system tables. There are three domains usually in a basic
setup they are Secure, Universe, Document. In 6.5 Universe are situated in one domain, Reports in
one domain and Supervisor.

29) When is the Repository created?

In 5i/6i versions repository is created after installing the software, whereas in XI version a repository
is created at the time of installation.

P O S T ED BY B O W OR L D ! ! ! AT 5 : 3 0 P M

0 COMMENTS:

POST A COMMENT

Specification for Universe Design document

The TRS document contains
-> Introduction ( overview, assumptions, acronyms, stakeholders)
-> Business Requirements (report requirements)
-> Data Sources
-> Universe Design (tables names and column names, classes, Naming conventions of objects, joins &
relations, conditions to create at universe level)
->Security Design (security levels)

Joins
Equi Join: An equi-join links two tables on common values in a column in table 1 with a column in
table 2. The restriction conforms to the following syntax:
Table1.column_a = Table2.column_a
In a normalized database the columns used in an equi-join are usually the primary key from one
table and the foreign key in the other
Theta Join: A theta join is a join that links tables based on a relationship other than equality between
two columns. A theta join could use any operator other than the “equal” operator.
Outer Join: An outer join is a join that links two tables, one of which has rows that do not match
those in the common column of the other table. You define an outer join by specifying which table is
the outer table in the original equi-join. The outer table contains the column for which you want to
return all values, even if they are unmatched.
Shortcut join:A shortcut join is a join that provides an alternative path between two tables.
Shortcut joins improve the performance of a query by not taking into account intermediate tables,
and so shortening a normally longer join path.
Self restricting joins: A self restricting join is not really a join at all, but a self restriction on a single
table. You can use a self restricting join to restrict the results returned by a table values using a fixed
value.

SATURDAY, JUNE 6, 2009

Types of Reports creation in Crystal Reports

There are four Report Creation Wizards:

• Standard
• Cross-Tab
• Mailing Label
• OLAP

Standard: The Standard Report Creation Wizard is the most generic of the wizards. It guides you
through choosing a data source and linking database tables. It also helps you add fields and specify
the grouping, summarization (totals), and sorting criteria you want to use. Finally, the Standard
Report Creation Wizard leads you through chart creation and record selection. The Templates screen
contains predefined layouts for you to apply to your report to give it more impact.

Cross-Tab: The Cross-Tab Report Creation Wizard guides you through the creation of a report in
which your data is displayed as a cross-tab object. Two special screens (Cross-Tab and Grid Style)
help you create and format the cross-tab itself.

Mailing Label: The Mailing Labels Report Creation Wizard lets you create a report that is formatted
to print on any size mailing label. You can use the Label screen to select a commercial label type, or
you can define your own layout of rows and columns for any multi-column style report.
OLAP: The OLAP Report Creation Wizard lets you create a report in which your OLAP data is
displayed as a grid object. Although similar to the Cross-Tab Report Creation Wizard in several ways,
the OLAP Report Creation Wizard appears to be different due to the requirements of working with
OLAP data sources. You first specify the location of your OLAP data, and then you choose the
dimensions you want to include in the grid. Next you filter the report data and choose the style of
the grid object, which you can also customize. Finally, you can define labels for your grid and insert a
chart, if you wish.