I have created Interview questions 2016 for Helping the job seekers for different technologies.
This Section Contains bo interview questions and answers for experienced , Real time bo interview scenarios.
This questions and answers section will help job seeker to crack the interviews will provide bo admin interview questions answers in Upcoming posts. I will try to Post bo interview questions and answers pdf also for the job seekers , they can easily free download it from my website . Please feel free to comment on the interview questions and answers for improvement.
I will try to improve my website so job seekers can take most out of it
.
Explain Universe design methodology
- Analysis of business problem and planning the universe solution.
- Designing a schema
- Building the universe
- Distributing the universe to users
************************************************************************
Indicating resource controls
- Limit size of result set to a specified value default 1000 rows
- Limit execution time to a specified value default 10 mins
- Limit size of long text objects to a specified value default 1000 Characters
************************************************************************
YOU MAY ALSO LIKE UNIVERSE DESIGNER INTERVIEW QUESTIONS
************************************************************************
Indicating SQL restrictions
Query controls
Allow use of union,intersect and minus operators
Multiple SQL statements controls
Multiple SQL statements for each context
Multiple SQL statements for each measure
Allow selection of multiple contexts
MAX_INLIST_VALUES
The max number of values you may enter in a condition when
you use the IN LIST operator is 256.
Default is 99
************************************************************************
************************************************************************
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
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.
************************************************************************
************************************************************************
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
• chasm traps
• fan traps
************************************************************************
Loops are Joins which form multiple paths between
lookup tables It would return an
intersection of the results for each path, so fewer rows are returned
than expected
Can be detected by Detect Aliases,Detect Contexts,Detect
Loops,Check Integrity,Visual analysis of schema and can be resolved by creating
aliases and contexts to break loops.
What is the loop doing to the query?
The purpose of the joins is to restrict the data that is
returned by the query. In a loop, the joins apply more restrictions than what
we expect, and the data returned is incorrect or few rows.
We use an alias to break a loop?
Alias breaks a loop by using the same table twice in the
same query for a different purpose. but the
different name “tricks” SQL into accepting that you are using two
different tables.
You can see the change in the generated SQL in applying the
restrictions in where clause. Only a one time restriction is applied in the
joins
There is now one join applying a
restriction on the Country table and another join applying a restriction on the
Resort_Country table. The loop has been broken.
************************************************************************
************************************************************************
What is a Chasm Trap? and Resolving Chasm Traps
A chasm trap is a type of join path between three tables
when two “many-to one” joins converge on a single table, and there is no
context in place that separates the converging join paths or we can define
the chasm trap as. If you have two fact tables with many to one joins
converging to a single lookup table, then we have a potential chasm trap.
We will get incorrect results only when:
1. A “many to one to many relationship”exists among three
tables in the universe structure.
2. The query includes objects based on two tables both at
the “many” end of their respective joins.
3 There are multiple rows returned for a single dimension.
And chasm trap inflates results without any warning For Ex
The number of guests that have used,
and future guests who have reserved to use the Sports service has increased
considerably. A Cartesian product will been returned and the results are
incorrect. This can be a serious problem if undetected.
How does a chasm trap inflate results?
The chasm trap causes a query to return every possible
combination of rows for one measure with every possible combination of rows for
the other measure.
Unlike loops, chasm traps are not detected automatically
by Designer, however, you can use Detect Contexts (Tools > Detect Contexts)
to automatically detect and propose candidate contexts in your schema.
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.
Resolving a Chasm Trap
To resolve a chasm trap we 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,
To resolve a chasm trap we can go for a
• Creating a context for each fact table. This solution
works in all cases.
• or Modify the SQL parameters for the universe so that it
generates separate SQL queries for each measure. This solution only works for
measure objects. It does not generate separate queries for dimension or detail
objects.
When do you use contexts?
Creating contexts will always solve a chasm trap in a
universe. When you have dimension objects in one or both fact tables, you
should always use a context.
Using Multiple SQL Statements for Each Measure
If you have only measure objects defined for both fact
tables, then you can use the Universe Parameters option Multiple SQL statements
for each measure. This forces the generation of separate SQL queries for each
measure that appears in the Query pane.
This solution does not work for dimension and detail
objects.
************************************************************************
************************************************************************
What is a Fan Trap?
A fan trap is a type of join path between three tables when
a “one-to-many”
join links a table which is in turn linked by another
“one-to-many” join. The
fanning out effect of “one-to-many” joins can cause
incorrect results to be
returned when a query includes objects based on both tables.
A fan trap is a less common problem than chasm traps. It has the same effect of
returning more data as in the chasm trap
The Sale Value aggregate appears twice. Once for each
instance of Model_ID. When these results are aggregated in a report, the sum is
incorrect. The fan trap has returned a Cartesian product. Wendy bought two cars
for a total of $57,092.00, and not 114,184.00 as summed in the report.
The inclusion of Model_ID in the query, caused the SaleValue
to be aggregated for as many rows as Model_ID.
The fan trap using dimension objects in the query is solved
by using an alias and contexts. The following schema is the solution to the fan
trap schema:
The fan trap using dimension objects in the query is solved
by using an alias and contexts. The following schema is the solution to the fan
trap schema:
How Do You Detect a Fan Trap?
We cannot automatically detect fan traps. We need to
visually examine the direction of the cardinalities displayed in the table
schema.
If we 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.
How Do You Resolve a Fan Trap?
There are two ways to solve a fan trap problem.one is to •
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.
************************************************************************
************************************************************************
How are Aliases Used in a Schema?
We use aliases for two main reasons:
• To use the table more than once in a query. This is the
main reason for using aliases, and includes using aliases to solve loops and
fan traps. The example Beach universe contains 2
aliases; Resort_Country for Country, and Sponsor for Customer.
• To abbreviate the table name to save typing when writing freehand
SQL.
You need to create an alias manually to solve a fan trap.
You also create
aliases manually if you are creating a schema using only
aliases and not the
base tables
************************************************************************
************************************************************************
How are Contexts Used in a Schema?
Contexts are a collection of joins which provide a valid
query path for Web Intelligence to generate SQL. We can use contexts in a
universe schema for
• Solving loops.
The most common use of contexts is to separate two query
paths, so that one query returns data for one fact table, and the other query
returns data for another fact table.
• Solving chasm traps.
• Assisting in some solutions for fan traps.
• Assisting in detecting incompatibility for objects using
aggregate awareness.
Contexts are not updated automatically when the universe
structure is changed. If you add or remove any tables to the structure, or if
you add or remove any joins, you must update all the contexts.
A one-to one-cardinality positioned at the end of a join
path can prevent
Context Detection in Designer from detecting a context. You
resolve this
problem by changing the cardinality of the table at the end
of the join path to
one-to-many.
How do Contexts Affect Queries?
contexts can lead to three types of queries like:
• Ambiguous queries : When a query is ambiguous, While
running a report displays a dialog box that prompts the user to select one of
two contexts. When the user selects a context, the corresponding tables and
joins are inserted into the SQL query.
• Inferred queries : When a query is run without prompting an end user to choose a
context. It contains enough information for the correct context to be inferred
• Incompatible queries : When we pull Objects from two
different contexts. It generates two Select statements which are synchronized
to display returned data in separate tables.
When we query is run such kind of queries, no prompt appears
as Business Objects make use of of both
the contexts and Select statements for
both contexts are synchronized. To allow incompatible queries to be run
We can also use the option , select the Multiple SQL
statements in Designer for each context option.
************************************************************************
************************************************************************
Defining hierarchies
You create object hierarchies to allow users to perform
multidimensional analysis.
Multidimensional analysis is the analysis of dimension
objects organized in meaningful hierarchies.
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.
************************************************************************
Drill
A user can use drill to navigate through hierarchical levels
of detail. Users can “drill up” or “drill down” on a hierarchy.
For example, a manager wants to track reservation data over
time. As the universe designer, you could set up a Reservation Time hierarchy
to include the dimensions Reservation Year, Reservation Quarter, Reservation
Month, and Reservation Date. From a higher level of aggregation for example
Reservation Quarter, the manager can drill down to a more detailed level such
as Reservation Month or Reservation Date. He or she could also drill up from
Reservation Quarter to Reservation Year to see a more
summarized view of the data.
************************************************************************
How to identify a hierarchy
Hierarchies can take different forms. Examples of classic
hierarchies include:
• Geography: Continent -> Country -> Region -> City
• Products: Category
-> Brand -> Product
• Time: Year Quarter
-> Month -> Week ->
Day
While there are no precise rules for determining where the
hierarchies in the data lie, the one-to-many (1-N) relationships inherent in
the database structure can indicate the existence of hierarchies. In the schema
below, the one-to-many relationships between the tables imply a geographical
hierarchy.
Setting up hierarchies
By default, Designer provides a set of default hierarchies
for multidimensional analysis. These are the classes and the objects arranged
in the order that they appear in the Universe pane. When you create objects,
you should organize them hierarchically, to ensure that default hierarchies
have a sense to users.
You often need to create customized hierarchies that include
objects from different classes. In these cases you need to create a new
hierarchy.
Designer represents hierarchies with a folder symbol, and
dimensions with a cube symbol. The left pane lists all the classes that contain
dimension objects in the active universe. The right pane shows all the customized
hierarchies that you create.
Create a new hierarchy
by creating a new folder in the Custom Hierarchies pane, then adding the
appropriate dimensions in a hierarchical order.
************************************************************************
Using cascading lists of values for hierarchies
You can associate a default or custom hierarchy with lists
of values, called Cascading lists of values.
A cascading list of values is a sequence of lists of values
associated with a hierarchy in a universe. Prompts are defined for each
hierarchy level to return a list of values for the level.
When a report containing a hierarchy associated with a
cascading list of values is refreshed, the hierarchy is displayed, and you are
prompted to choose a level, and select one or more values from the list of
values, before the query is run.
For example; Reservation quarter is associated with a Year
hierarchy. When Reservation quarter month is used in a query, the Year
hierarchy is displayed, and the user is prompted to select a year for the
quarter before running the query.
Creating a hierarchy for a list of values
Drag the objects that you want to place in the hierarchy
into the Result Objects box to the right of the existing object, as shown below
:
************************************************************************
************************************************************************
Optimizing universes
We can optimize universes by using the following techniques:
• Using aggregate tables
• Using @Functions
• Using external strategies to customize universe creation
• Using analytic functions
Using aggregate tables
We can use features in Designer to allow us to define the
Select statement for an object to run a query against aggregate tables in the
database instead of the base tables. You can set conditions so that a query
will be run against aggregate tables when it optimizes the query, and if not,
then the query will be run against the base tables. This kind of ability for
the object to use aggregate tables to optimize a query is called aggregate
awareness
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 which contain
pre-calculated data. We can use @Aggregate_Aware function 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.
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.
Setting up aggregate awareness
Build the Objects
1. Identify all the possible definitions (table/column
combinations) of the objects.
2. Arrange the objects by level of aggregation.
3. Build the objects using the @Aggregate_Awareness
function.
Specify the incompatible objects
1. Build an objects/aggregate tables matrix.
2. For the first aggregate table, decide whether each object
is either:
- at the same level of aggregation or higher (compatible)
- at a lower level of aggregation (incompatible)
3. Check only the boxes of objects that are incompatible for
that table.
4. Repeat the steps for the remaining aggregate tables.
@Aggregate_Aware
Define any necessary contexts
Define one context per level of aggregation.
Test the results
1. Run several queries.
2. Compare the results.
************************************************************************
Setting access restrictions on a universe
Universe security is managed at two levels:
CMS : From the Central Management Console you can set
restrictions that apply to universes stored in a CMS. You can set what universes
users can access, and depending on the rights defined for a user group, you can
restrict viewing, editing, deleting, and other actions in a universe.
Universe : You can define restrictions for users allowed to
use a universe. A restriction can include object access, row access, query and
SQL generation controls, and connection controls.
A restriction can define the following types of restrictions
to apply to a selected user or group for a universe:
Connection : Universe connection to the data source. 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.
Object access You can select objects that are not available
to the universe.
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 the database.
YOU MAY ALSO LIKE UNIVERSE DESIGNER INTERVIEW QUESTIONS
************************************************************************
