Several years ago i fall my interview, after this i took all important definitions and learn them by heart. It was very helpful!
What is the
universe?
The business objects universe is the semantic layer
that isolates business users from the technical complexities of the databases
where their corporate information is stored. For the ease of end user,
universes are made up of objects and classes that map to data in the database,
using everyday terms that describe their business environment.
Size 1 Mb, max 500 objects.
Does universe
store data?
You associate data to universes by mapping to a data
source. Data is not stored in the .unv file.
What are the
main advantages of a universe?
·
Only the universe designer needs to know how to write SQL and understand
the structure of the target database.
·
The interface allows you to create a universe in an easy-to-use graphical environment.
·
Data is secure. Users can see only the data exposed by the universe.
Users can only read data, not edit it.
·
The results are reliable and the universe is relatively easy to
maintain.
·
Users can use a simple interface to create reports.
·
All users work with consistent business terminology.
·
Users can analyze data locally.
Describe the
connection types and their purposes.
·
Secured: Use this connection to enable your universe to be exported to
the Business Objects Enterprise repository.
·
Shared: Use this connection to share your universe across the network
with the other designers.
·
Personal: Use this connection if universe is solely for a single user
and there is no need to neither share nor publish this universe to the
Enterprise system.
What are the
types of objects that can be created in a universe? Provide a brief description
of each type.
·
Dimensions: These objects relate to the key information being retrieved
from the database. The can be date, character, or number type, and may contain
a calculation. They are used in queries and condition/filters and can also be
used to drill on hierarchies and link between queries.
·
Detail: These objects relate to supportive information and are
associated with dimensions. They can be date, character, or number type, and
may contain a calculation. They are used in queries and condition/filters but
cannot be used to dril on or link on between queries.
·
Measures: These objects are dynamic aggregates and are always a number
type, containing functions like Sum, Count, Average, Max, Min. Depending on the
other objects in the query, they dynamically aggregate themselves when
displayed in the report block.
·
Predefined Condition/ Filter: These objects contain a name, a
description and a restriction. They are designed to give the user a choice as
to whether to apply a condition filter or not. They can be quite basic, or can
contain a complex where clause.
What are the
different join types allowed in the universe structure. Provide a short
description of each type.
·
Equi: This join works on the principle of the data in the fields that
are a joined maych, for example Customer_ID=Customer_ID, therefore returning
data from both tables where the data matches.
·
Outer: This join works in the same way as an equi-join, however there
may be data in one table but not the other, and the requirements is for the
data to be returned regardless. For example, a join between Customer and Sales
would be based on Customer_ID, however a customer may still be prospective and
not have any records in the sales table, but all customers must be reported on
regardless of sales.
·
Complex/Theta: This join works on the Between principle. For example, a
sales date may be joined to a begin and also an end date in a table which is
date based, for example sales date between begin date and end date.
·
Self-restricting: This join works as a restriction on the table. If
there is a field that can be used as a flag on a table, then this join would
allow that flag to be referenced every time the table is used in the SQL
statement.
·
Self-referencing: A self-referencing join is a join from one column of a
table to another column of the same table. This join should not be used as it
creates a loop. Ideally, if this join
exists, then the table should be aliased and the appropriate join and
cardinality should be assigned.
·
Shortcut: A shortcut join is a join that provides an alternate path
between two tables. It improves the performance of a query by not taking into
account intermediate tables, and shortening a normally longer join path.
What are the
different types of SQL traps that can be found in a schema? How can each of
these types be identified? How can each type be resolved in the universe
schema?
·
Chasm trap:
Identify – A chasm trap is essentially a many-to-one-to-many
relationship and may be seen as a parent table with two child tables, the child
tables having the many end of the relationship. If a query is run with a chasm
trap, depending on the objects used, either too many or not enough rows (but
aggregation may be affected) are produced.
Resolve – Best resolve by using contexts or modify SQL
parameters (select for each measure).
·
Fan trap:
Identify – A fan trap is identified as a one-to-many or a
one-to-many-to-many relationship. In itself, this isn’t an issue is if there
are two fields being used as aggregates and they are at different levels in the
path. This can give the same result as a chasm trap.
Resolve – If they can be avoided by using the lowest level of
granularity then this is the best practice, however that isn’t always possible.
In which case, a combination of aliases and contexts resolves the fan trap and
modify SQL parameters (select for each measure).
What are loops
and how you would test a measure to make sure the aggregation levels are
projected correctly?
Loops are joins between tables that (eventually) come
back to the starting point, forming a circle, or loop. Usually the error
Incompatible combination of objects alerts you to the fact you may have a loop.
Loops can be resolved by creating aliases and contexts
manually. However, depending on the complexity of a universe, this can be a
cumbersome task.
Describe how
you would test a measure to make sure the aggregation levels are projected
correctly.
To test a measure correctly, a minimum of three
queries should be created.
1. The measure on its own.
2. The measure with two different
dimensions, in different queries. Once the table has been projected, remove the
dimensions and then add them again using the drag and drop method. This checks
the aggregation in the report is correct.
3. Applying a total to the tables
checks (if they are all the same total) the aggregation level from the
database.
Explain two
drawbacks of using restrictions at the objects level.
1. Conflicting restrictions in the
objects may be confusing if those objects are placed in the query. For example:
UK customers, US customers. Effectively you are saying they must be UK and US
at the same time, this is a conflict so no data is returned.
2. Creates a flat level at the end
of the class making drilling through the hierarchy difficult.
Describe the
use of the @selection function.
The @select function has no benefit to the end user.
However, it does have benefit to the designer, as this is how they can reuse
code between objects.
@prompt: You can use @prompt to
create an interactive object. Are
usefull when you want to force
restriction in the inhered SQL but do not want to present value of the
condition. It forces an end user to enter value for a restriction when that
object is used in a query.
@where: they can reuse code between objects.
What is a
hierarchy?
A hierarchy is the order of dimensions in a “class” in
one-to-many order.
Hierarchies can be default (or natural) and custom and
time hierarchis.
(Automatic/Using DB functions/Table based)
Describe what
a derived table is and how it is generally used in a universe schema.
A derived table is and how it is generally used:
·
When it is not possible to create a table at the database level,
especially if there is a need to link two otherwise un-likeable tables
together.
·
To create a table based on a restriction – instead of aliasing tables
based on their flag and using self-restricting joints
Describe what
index awareness is, and how it is used in a universe schema.
Index awareness is a way of utilizing the indexing in
the database to enable queries to be more efficient in the SQL that they
generate. It is set up via the object properties.
What are the
pros and cons of delegated functionality in a universe-based reporting
structure?
Delegated or “smart” measure objects were discussed in
the core Universe Design class. The objective is to shift the responsibility
for the aggregation to the data source, instead of the universe. This allows
for complex and weighted calculations of dimensional subsets, but requires a
refresh to the data source in order to display the results in Web Int report.
@Aggregate
awareness.
@Aggregate awareness directs query to run against
aggregate tables or summary tables, whenever possible.
Using summary table data speed up response time:
·
There are fewer rows to process.
·
Value calculation is not required.
·
Fewer, if any, joints are required.
Aggregate Navigation – tool you use in Univ Designer
to set incompatibilities objects and tables in the structure of the universe.
Security.
In BO, universe
security can be managed at these levels:
·
Connection credentials and data store.
·
Central Management Server.
·
Universe (restrictions – objects, rows access, query, SQL generation
controls and connection controls).
Restriction sets:
·
Connection – you can select an alternative connection for the universe.
·
Query controls – options to limit the size of the result set and query
execution time.
·
SQL generation options – options to control the SQL generated for
queries.
·
Objects access – you can apply column-level security.
·
Row access – you can define a WHERE clause that restricts access to row
and limits the result set returned by a query.
·
Alternative table access – you can replace a table referenced in the
universe by another table in database.
Forced restrictions – clause where
Optional restriction – predefine condition
Objects
restrictions problem: Multiple Objects, Hierarchy
Problems, Name confusions, Restr conflicts.
CMS
The CMS is responsible for authenticating users and
groups, and keeping track of the availability of the other BO Enterprise
services. It also maintains the BOE system database, which includes information
about users, groups, security levels, BOE content, and services. The CMS also
maintains a separate audit database of information about user actions and
managers the BO repository.
CUID - Component Unique ID
BIAR – Business Intelligence
Archive Resource file
