Universe Designer Interview question Part 1

Question : What is a Universe and what is consist of ?

Answer : 

A universe is a file that contains the following:

• Connection parameters for one or more database middleware.

• SQL structures called objects that map to actual SQL structures in the database such as

columns, tables, and database functions. Objects are grouped into classes. Objects and

classes are both visible to BusinessObjects and WebIntelligence users.

• A schema of the tables and joins used in the database. Objects are built from the

database structures that you include in your schema. The schema is only available to

Designer users. It is not visible to BusinessObjects and WebIntelligence users.

BusinessObjects and WebIntelligence users connect to a universe, and run queries against

a database. They can do data analysis and create reports using the objects in a universe,

without seeing, or having to know anything about, the underlying data structures in the

database.

Question : what is class and objects ?

Answer : 

Classes :A class is a logical grouping of objects within a universe. It represents a category of

objects. The name of a class should indicate the category of the objects that it Contains. A

class can be divided hierarchically into subclasses.

Objects :

An object is a named component that maps to data or a derivation of data in the database.

The name of an object should be drawn from the business vocabulary of the targeted user

group. For example, objects used in a universe used by a product manager could be

Product, Life Cycle, or Release Date. A universe used by a financial analyst could

contain objects such as Profit Margin, and Return on Investment.

Question : Describe Universe design Methodology or Explain different phases of universe?

Answer: The universe design methodology described here consists of one planning

   stage, and three implementation phases:

• Analysis of business problem and planning the universe solution

• Designing a schema

• Building the universe

• Distributing the universe to users

Each implementation phase is based on an assumption

Development phase Description

Prepare :Identify the target data source and become familiar with its structure.

Know what data is contained within each table of each of the target

databases.

Understand the joins.

Identify the cardinality.

Know what is possible.

Analyze :Identify the user population and how it is structured; for example is the

user group structured by department or by task.

Identify what information the users need.

Identify what standard reports they require.

Familiarize yourself with their business terminology so that you can name

objects sensibly.

Plan: Identify a project strategy. For example, how many universes should be

created and which ones should have the capacity to be linked and to what

level.

Implement: Build the universe using Designer. This manual covers this part of the

universe development cycle, the actual use of the design tool.

Test frequently during the build process for validity and reliability of

inferred SQL.

Test :Form a small group of users, preferably BusinessObjects or

WebIntelligence power users who have some knowledge of what

information they expect to get from the universe. Ask the users to perform

thorough tests simulating live usage of the universe(s).

Deploy: Distribute the universe by exporting universe to the repository, where it

can be accessed by end users.

Evolve Update and maintain the universe as the data sources and user

requirements change and grow.

Evolve : Update and maintain the universe as the data sources and user

requirements change and grow.

Question : Describe the environment in which business objects can be used?

Answer: Designer can be used in two types of Business Objects environments

Enterprise:

Designer is used with a Business Objects repository. Universes are saved in the Universe

domain of the repository. Universe access, version control, and security are controlled by

Business Objects Supervisor. A universe is imported from and exported to the repository

by the universe designer. This is the most common environment in which Designer is

used.

Workgroup:

Designer is used alone with no repository connection. Access to universes, version

control, and security are controlled by the universe designer.

Question : What are the different type of connections are available in universe ?

Answer:   there are three types of connection as available in universe- 

Personal connections

Restricts access to data to the universe creator and the computer on which it was created.

Connection parameters are stored in the PDAC.LSI file located in the LSI folder in the

Business Objects folder under your user profile in Documents

Personal connections are unsecured in terms of Business Objects products security. You

do not use personal connections to distribute universes. You could use personal

connections in the following situations:

• To access personal data on a local machine

• To access specific database accounts to test an SQL sample through the Free-hand SQL

option in BusinessObjects.

Shared connections

Allows access to data for all BusinessObjects and WebIntelligence users. These

connections are unsecured in terms of Business Objects products security. 

Shared connections can be useful in a universe testing environment.

Secured connections

• Centralizes and controls access to data. It is the safest type of connection, and should

used be to protect access to sensitive data.

• You can create secured connections with Designer or Supervisor.

• Connections are stored in the security domain of the repository. These can be shared

with designers and supervisors with the appropriate privileges.

• You must use secured connections if you want to distribute universes through the

Business Objects repository.

• Secured connections can be used and updated at any time. To define a secured

connection you must be using Business Objects products in Enterprise mode. You must

be connected to a repository and using the Business Objects repository key file. The

default name for this file is BOMain, but it can be modified at any time in Supervisor.

Question : Explain what is Theta Join,Shortcut joins,Full outer joins,Outer joins?

Answer : 

Theta joins

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. The

following example and procedure show you how to create a theta join that uses the

"Between" operator.

Outer joins

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. You specify

the outer table from the Edit Join dialog box for the selected join.

Full outer joins

By default you can create either a left outer, or a right outer join depending on which side

of the join the outer table is designated. You can also create a full outer join by activating

ANSI 92 support for joins in the universe. This is achieved by setting a universe SQL

parameter ANSI 92 to YES (File>Parameters>Parameter). This allows the universe to

support ANSI 92 syntax for joins, and you can select the tables on either side of a join to

be outer tables. Refer to the section defining a full outer join on page 169 for information

on creating full outer joins.

Shortcut joins

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. A common use of shortcut joins is to link a

shared lookup table to another table further along a join path. The join path comprises

several different tables in the same context. In such a case, the shortcut join is only

effective when the value being looked up has been denormalized to lower levels in a

hierarchy of tables, so the same value exists at all the levels being joined.

Question : What is aliases and what is its use?

Answer: 

Alias Another possible use of aliases is to create an alias for each table as it is inserted into the

schema. You then build the schema using the alias tables, not the original base tables.

You place the base tables together away from the main universe structure. This allows

you to give meaningful names to tables, and prevents the need to rebuild major sections

of a universe structure should a base table need to be aliased at a later stage.

Using aliases to solve loops

The most common use of aliases in universe development is to solve potential loops in

the use of common tables. A loop is a set of joins that defines a closed path through a set

of tables in a schema. Loops occur when joins form multiple paths between lookup tables

You use an alias to break a loop by providing alternative table for an original lookup

table that is being used for multiple query paths.

Using aliases to solve fan traps

Aliases are also used to solve potential fan traps. These can occur in a serial oneto- many

join path that can return inflated results when aggregates are summed at the "many" end

of the joins. This use of aliases is discussed in the section

Question : Explain how keys can help in setting Index Awareness ?

Answer: 

The Keys tab allows you to define index awareness for an object. Index awareness is the

ability to take advantage of the indexes on key columns to speed data retrieval. The

objects that you create in Designer are based on database columns that are meaningful to

an end user. For example, a Customer object retrieves the field that contains the customer

name. In this situation the customer table typically has a primary key (for example an

integer) that is not meaningful to the end user, but which is very important for database

performance. When you set up index awareness in Designer you tell Designer which

database columns are primary and foreign keys. This can have a dramatic effect on query

performance in the

following ways:

• Designer can take advantage of the indexes on key columns to speed data

retrieval.

• Designer can generate SQL that filters in the most efficient way. This is particularly

important in a star schema database. If you build a query that involves filtering on a value

in a dimension table, Designer can apply the filter directly on the fact table by using the

dimension table foreign key. This eliminates unnecessary and costly joins to dimension

tables. Designer does not ignore duplicates with index awareness. If two customers have

the same name, Designer will retrieve one