Universe Designer Great Interview Questions

Designer

1)    What are the difference between BO XI R2 and 3.0?

Ans. BO XI 3.0 supports the following features which are not supported in XI R2. These are:

Smart measure: A measure can be called as a smart measure in the universe when its data aggregated in a way not supported by WEBI. A smart measure has projection function set to “Database delegated” on the properties tab of object properties.

Optional prompts: In WEBI you can ignore optional prompts by not specifying any value and prompts does not apply a filter on the data.

ForceMerge function: With this WEBI function you can calculate measures for merged dimensions and is similar to Multicube function in DESKI.

Data tracking: WEBI has the feature to track data changes by highlighting the changed data and displays the previous value of a dimension or measure along with its current value.

It highlights the changed data according to parameters you set. In the option Auto-update, the current data becomes the reference data after each data refresh and the other option is “use the current data as referenced data” the report always show the difference between the most recent data and this fixed reference data.

Multilingual support: From the same report, you can decide in which available language you want to view the report’s metadata and prompts. Using Translation manager you can add translations to the universe on which the report is created or translate the report itself. Once the document is translated, it is published and available to users in their preferred viewing language.

Note: Data is dependent on database source cannot be translated.

Document linking: Using WEBI you can create links to other WEBI documents, to web sites, or to any resource accessible from a WEBI report using a simple user interface. From WEBI you can even pass data to linked resource.

Stored Procedures: In Designer, you can create a universe based on one or more existing stored procedures.

2)    How can you optimize a Universe?

Ans. Query time can be shortened by optimizing a universe. There are several ways to optimize a universe.

      Optimizing the Array fetch parameter in the Universe parameters

      Allocating weight to each table

      Using shortcut joins

      Creating and using aggregate tables in database

Optimizing the Array fetch parameter

               The array fetch parameter allows you to set maximum number of rows that are permitted in a fetch procedure and determines the packet size on the network. For example, if the array fetch is set as 20 and you plan to retrieve 40 rows, then two fetches will be executed to retrieve the data.

               Some data sources do NOT allow to modify the fetch size, in such cases all rows will be return in single FETCH. If you want to reteive binary long-objects (BLOB) set FETCH size as 1.

               If you have a network that allows you to send a large array fetch, then you can set a new larger value. This value will speed up the FETCH procedure and reduce query processing time.  Values can be set from 1 to 999.

Allocating table weights

               Table weight is a measure of how many rows are there in a table. By default BO sorts the table from lighter to heavier tables and this determines the table order in the FROM clause of SQL statement.

               If you are using Oracle database, you can optimize the SQL by reversing the order that BO sorts the table. To do this we need to modify the oracle .PRM file by changing parameter REVERSE_TABLE_WEIGHT from ‘Y’ to ‘N’ (Default – Y ). By doing this BO forces to sort the tables from those with most rows to those with least rows.

Path of oracle.PRM file => <INSTDIR>\dataAccess\RDBMS\connectionServer\oracle\oracle.prm

               Restart Designer to apply the changes to the file.

In Designer we can manually change the number of rows for any table.

To view, select View menu => Number of rows in tables. To modify

      Open a universe in designer, Right click the relevant table.

      Select number of rows in table from contextual menu. A dialog box appears.

      Select the Modify manually tables row count radio button. A text box appears.

      Type a number in the text box. This is the number of rows that you want to use for the     table.

      Click ok and save the universe.

Using short cut joins

               A short cut is an alternate path between two tables. Short cut join used to reduce number of tables that are used in a query.

Using Aggregate Awareness

               The ability of a universe to make use of Aggregate tables to optimize SQL query is called Aggregate Awareness. These are the tables which contain pre-calculated data.

               You can use @Aggregare_Aware function in the select statement for an object which directs SQL query to run against Aggregate tables rather than on base tables.

               Using Aggregate tables speeds up the execution of query and thus improves the performance.

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 ( Tools- Aggregate Navigation )

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.

Define any necessary contexts

1. Define one context per level of aggregation.

Test the results

1. Run several queries.

              2. Compare the results.

3)    Can you please clarify the difference between compatible and incompatible objects? When we will use these two?

Ans. When you are defining Aggregate tables in Universe design, you need to create hierarchies for aggregated objects, for that you need to make the relative objects incompatible (the objects which are of lower level of aggregation -- not of hierarchy). If you have Year, Quarter, Month, Day as a hierarchy, while aggregation, you need to define as follows -

Year - Quarter, Month, Day Compatibles

Quarter - Month, Day Compatibles, Year Non-compatible

Month - Day Compatible, Year, Quarter Non-compatible

4)    How can you set access restrictions on a universe?

Ans. Access restrictions that apply to a user group are defined in a restriction.

         Universe security is managed at two levels:

ØCMS

o   From the Central Management Console, 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

o   You can define restrictions for users allowed to use a universe. It can be

      Connection

      Query controls

      SQL generation option

      Object access

      Row access

      Alternate table access

5)    How do you set Row access restriction?

Ans.  Tools => Manage Security => Manage Access Restrictions

         Click New  => Click Rows tab => Click Add

         Click Browse button next to Table box

         Click table name and click ok

         Click Browse button next to Where clause box

         Give condition => OK => OK .

To view the restrictions applied to all users and groups, select

Tools => Preview security restrictions, click user account name in the list => Preview.

Parameters and options that appear in red are those that have been modified and apply specifically to the restriction.

6)    What are Linked universes?

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

         Core universe is a universe to which other universes are linked.

         Derived universe is a universe that contains a link to a core universe.

If the linked core universe is a Kernel universe, then components can be added to derived universe. If the linked core universe is a master universe, then the derived universe contains all the core universe components. Class and objects are not added to the derived universe but they can be hidden in the derived universe depending on the user needs of the target audience.

Advantages:

Ø  Maintenance will be easy because when you modify a component in the core universe, designer will automatically reflect the changes in all the derived universes.

Ø  No need to re-create common components each time you create a new universe as often used components can be kept in a core universe.

Requirements:

Ø  Core universe and derived universe use the same data account or database. Using the same connection for both universes makes managing the universes easier, but this can be changed at any time.

Ø  Core and derived universes must be in same repository.

Ø  Core universe was exported and re-imported at least once. Derived universe does not need to have been exported before creating a link.

Ø  Exported derived universes are located in the same universe domain as the core universe

Ø  You have the authorization to link the given universe.

Restrictions:

Ø  You can use only one level of linking. You cannot create derived universe from a universe which is itself derived.

Ø  All classes and objects are unique in both the universes else conflict will occur.

Ø  Only the table schema, classes and objects of the core universe are available in the derived universe. Context must be re-detected in the derived universe.

Ø  The two universe structures must allow joins to be created between a table in one universe to a table in the other universe else Cartesian product will occur.

Ø  List of values associated with a core universe are not saved when you export a derived universe with the core universe structure.

To save LOV’s associated with core universe:

      Create new objects using the same definition

      Assign the new objects the same LOV’s as the core objects

      Hide these new objects.

The hidden objects serve the function of holding the LOV’s so that they can be exported and imported with the derived universe.

You can log into designer as a different user without quitting your work session.

Tools => Login As. Give user name and password.

(If there are any open universes, designer closes them automatically)

Tools => Change password ( to change password).

7)    What is cardinality and what happens if Cardinalities are not resolved?

Ans. Cardinality means a relationship between two tables based on a join. Means how many rows of one table will match with rows in other tables when these tables are joined.

If Cardinalities are not specified between the tables, then

We get more results at report level than the actual result, like a Cartesian product.

We can’t detect the Loops & Traps which are mainly detected by the CARDINALITIES option.

8)    What is Context?

Ans. Context is a list of joins that defines a specific path for query and is used for solving loops and traps.

Main disadvantage is that it exposes the end users to the database structure and they are forced to decide which Context to use to run their query.

9)    How to test the Context?

ü Create a query which includes objects which are only in one context : BO should be able to get the correct result by determining the context.

ü Create a query which includes objects from both the contexts: BO should generate two queries and then unions it.

ü Create a query which includes object which are common two both the context: BO should prompt for contexts to be used.

10) What is Alias?

Ans. Alias used for solving loops and traps by using same table with different name in the query.

11) What is loop and how do you resolve them?

Ans. Loop is a closed circular path among the joined tables.

If the loop is formed between 1 fact table and 2 (or more) dimension tables, then create an Alias for existing dimension table.

If the loop formed contains more than 1 fact table, then go for Context.

If loops are not resolved and report is run following error might come.

Error: Incompatible combination of objects

12) What is Chasm trap?

Ans. It is a type of join path between three tables when there are two “many to one” joins converging on a single table, and there is no context in place that separates the converging join paths. Because of Chasm trap we get incorrect results i.e., Cartesian product will occur.

Resolving a Chasm trap:

·         When there are dimension objects in one or both fact tables, use context.

·         When there are measure objects defined for both fact tables, use the universe parameter option SQL “Multiple SQL statements for each measure”.

13) What is Fan trap?

Ans. It is a type of join path between three tables when a “one to many” join links a table which in turn kinked by another “one to many” join. Because of Fan trap we get incorrect results i.e., Cartesian product will occur.

Resolving a Fan trap:

·         Create an alias for the table that is producing the multiplied aggregation.

·         Create a join between the original table and Alias table

·         Create the context

OR

·         When there are only measure objects defined for both the tables , use the universe parameter option SQL “Multiple SQL statements for each measure”.

14) What are the Derived Tables and its use?

 Ans. It is defined by an SQL query at the universe level that can be used as a logical table in Designer.

Sometimes it not possible to create dimension/measure directly in universe in that case we use derived tables. E.g. First time users, union queries etc.

Derived tables have the following advantages: 

• Reduced amount of data returned to the document for analysis.

You can include complex calculations and functions in a derived table. These operations are performed before the result set is returned to a document, which saves time and reduces the need for complex analysis of large amounts of data at the report level.

15) What is the difference between Conditions and Filters?

Ans. The major difference between query conditions and document filters is that a condition is applied to the query and limits the data retrieved from the database, whereas the filter is applied to the data in the document to hide information and display only the information that you want to appear.

16) What is a Join? What are different types of Join?

Ans. As we retrieve data from more than 1 table, we apply join between the tables.

Different types of Joins are:

Inner join         Left outer join        Right outer join     Full outer join        Theta join            Equi join    Self join

17) What are different types of Objects?

Ans. Dimension object - Dimension is a main analysis object in a query which maps to one or more key columns in a database. Dimension objects can be organized hierarchically within a class to make default hierarchies for drill-down operations. For example, time can be a dimension with year-month-day hierarchy.

Detail Object - Detail object provides descriptive detail data about a dimension (maps to one or more columns or functions). For example, a month name in time dimension can be a detail of month index.

Measure - Object provides metrics (aggregated numbers) by which dimensions are compared.

18) What are concatenated Objects in a Universe?

Ans. A concatenated object is an object you create by combining two existing objects.

For example, let’s say that you wish to create an object called Full Name, which is a concatenation of the objects Last Name and First Name in the Customer class.

Example: Full Name = [First Name] + [Last Name]

If Month value is 1 and Year value is 2011 then MonthYear = 12011

(Both variables are number datatype)

=FormatNumber([Month];”0”) + FormatNumber([Year];”0”)

19) What is Class?

Ans. Class is a logical grouping of objects within a universe and can be divided hierarchically into subclasses and represents as folders on a tree hierarchy in the universe pane.

20) How do you group measure objects?

Ans. Create a new variable and use If..Then..Else in the formula.

Example: =If (<Usage Billing> < 5000) Then "Low" Else If (<Usage Billing> > 5000 and <Usage Billing> < 10000) Then "Medium" Else "High"

21) Is it possible to join more than one universe in Business Objects? If it is so how is that possible?

Ans. Yes it is possible. We can link the two universes by using add link option which is available in parameters (option seen in tool bar file menu).

22) What is category?

Ans.  Category is logical way of structuring or organizing the content (BO document).

23) What are the different data providers can be used to create report?

Ans. Universe, Personal data files such as Text files(.asc, .prn,.txt,.csv),Excel spreadsheet (.xls),Database files(.dbf), xml files(.xml), Free-hand SQL, Stored Procedures, VB, etc.

24) What is the delimiter can be used for text files?

Ans. Tabulation, Space, or Character.

25) How do you combine data from different data providers?

Ans. Dimension objects from one data provider are linked to dimension objects that contain the same values in a different data provider.

26) What are the limitations for Combined queries?

Queries must contain the same number of objects

Objects must be of the same type( i.e. character, date, number)

there can be upto eight queries in a combined query

when using minus, the second query is subtracted from the first

27) How can you check the integrity of universe?

Ans. By making use of Check integrity button.

28) Is it possible to create reports from different universes in one document?

Ans. Yes it is possible to use different universe to generate a single report....multiple data providers. OR you can link other universes to existing universe and then develop the report.

29) What are Universe parameters?

Ans. Definition, Summary, Strategies, Controls, SQL, Links and Parameters .

30) Why do we need metrics and sets?

Ans. Metrics are used for Analysis and Sets are used for grouping.

31) What issues you faced while creating universe?

Ans. Loops, traps, connections to the data base.

32) What are Pre-defined conditions? How do you compare with report conditions?

Ans. These are the conditions defined by Designer in the Universe. Often used Query conditions are qualify for pre-defined condition.

33) What is object and what do you mean by object qualification?

Ans. Object is an instance of class and object qualification represents what kind of object it is like: Dimension or Detail or Measure.

Object is a component named with business terminology and maps to data in the database or uses other objects within the same universe.

34) What is LOV? Where it is store?

Ans. It displays the list of data values associated with an object. A list of value can contain data from a Database file or an External file.

35) Explain in Detail about Measure Objects? What is the use of it? How to create it?

Ans. Measure Object conveys numeric information which is used to quantify a dimension object. A measure object returns numeric information. You can create a measure object by using aggregate functions such as: Sum Count Average Minimum and Maximum.

36) In universe parameter if you don’t give the option multiple contexts, what is the impact in report level?

Ans. By having multiple contexts you can utilize the Multipass SQL feature.

Multipass: Breaking one large SQL into multiple SQL. If you are using the star schema with two or more fact tables, and you enable this feature, BO will automatically generate two or more SQLs (i.e. one SQL for each fact table object used in the report). Then the results will be synchronized in the report.

37) What is isolated join in check integrity

Ans. A join which is not associated to any one of the contexts.

38) Can I link two Universes having with different connection?

Ans. Yes as long as they point to same database.

39) I have 2 universes, U1 and U2. From U1, I created one report that is R1. Now i want to give the connection R1 to U2 and at the same time delete the connection from U1 to R1? How is it possible?

Ans. We can change the connection for the report R1. For webI reports in query panel on left side we have Query properties there we can change the connection of the universe to U2. Then it will map to the U2.

40) How to set Cascading LOV’s?

Ans. We are going to create Prompt based on:

Country > Resort > Service Line > Service (Hierarchy diagram shown below)

Go to the Edit Properties window of Resort

Select Automatic refresh before use and click Edit

Bring the Country object to conditions panel and build prompt:

“What Country are you interested in?” – Save and Close – Apply

Go to the Edit properties window of Service Line

Select Automatic refresh before use and click Edit

Bring the Resort object to conditions panel and build prompt:

“What Resort are you interested in?” – Save and Close – Apply

Go to the Edit properties window of Service

Select Automatic refresh before use and click Edit

Bring the Service Line object to conditions panel and build prompt:

“What Service Lines are you interested in?” – Save and Close – OK

In WEBI, Select Country, Service Line, Revenue in result objects and Service Line into query conditions panel and build prompt “What service lines are we tracking?”

Click Run query

Now you will be prompted for Service Line Hierarchy

Click values and this is where the fun begins. You will be then prompted for a Resort

41) What is the test methodology for testing BO Universes?

Ans. Universe is tested keeping in view of the reporting requirements. A Universe should support creation of all the reports that needs be created off it plus any other additional requirements.

Using check integrity option we can test the universe.

Check whether any loops are there in universe.

Check any chasm trap and fan trap are there in universe.

Check parse of all the objects created in universe.

Check the joins. Weather any condition is not properly specified.

42) What is hierarchy?

Ans. Hierarchy is an ordered tree structure of relevant dimension objects. Hierarchies are used for drilling information across various levels.

43) What is the difference between the default hierarchy and custom hierarchy?

Ans. Default hierarchies are created as per the default classes and dimension structure in the Universe while Custom hierarchies are those explicitly defined by in Universe by using dimension objects.

44) What is difference between local filter and global filter?

Ans. Applying filter to a specific table (block) is called as local filter while applying filter to a report is called as global filter and it is applicable for all the blocks in a report.

45) What is Drill down, Drill up, Drill Through, and Drill across?

Ans. Drill Down: When you drill down, you display the next level of detail in a hierarchy.

Drill Up: When you drill up, you display the next highest level of detail in a hierarchy.

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

Drill Through: 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.

46) What is the syntax of prompt?

Ans. @Prompt('message','type',[lov],mono/multi,free/constrained/primary_key,per

sistent/not_persistent, [{'default value':'default key'[,'default value':'default

key',...]})

‘message’ - Test of the prompt message enclosed in single quotes.

‘type’ - Data type returned by the function.

‘A’ for alphanumeric, ‘N’ for number & ‘D’ for date.

Lov - You can specify two types of list of values.

Hard coded list: {‘AUSTRALIA’,’FRANCE’,’USA’} for example.

Pointer to a list of values from an existing object: ‘CLIENT\COUNTRY’.

Mono - user can select only 1 value from LOV.

Multi - user can select multiple values from LOV

Free - user can enter a value or select from LOV

Constrained - user must select a value from the LOV

Primary key - Use the primary key parameter with "free" or "constrained". The user enters a value or selects from the list of values. If the primary key parameter is present, the entered or displayed value is not used to generate the query. The associated key value from the Index Awareness column is used.

Persistent - when refreshing a document, the last values used in the prompt is displayed by default.

Not persistent - when refreshing a document, no last values will be displayed in prompt.

'default value':'defaultkey' - The default values parameter is used to define default values presented to the user.

You can define multiple default values. The syntax for each default value is: ‘value’:’key’.

The colon (:) is the separator between the value and the key.

When refreshing a document these values are displayed by default but if

the persistent option is set, then the last values used in the prompt are used instead of the default values.

If you specify the primary key parameter, then you must provide the key value(s).

47) What is Index Awareness in Universe?

Index awareness is the ability to take advantage of the indexes on key column to retrieve the data fast.

The objects that we create in the universe are based on database columns that are meaningful to an end user. When you set up index awareness in designer, you tell designer which database columns are primary and foreign keys which will increase query performance.

To setup Index Awareness, open the properties of objects, open “Keys” tab. Click Insert and Add respective primary key and foreign key.

48) How to implement Index awareness?

Ans. This is a feature of the universe to speed up performance of queries in several ways:

Ø It can remove joins from the SQL

Ø It can remove tables from the SQL

Ø It can search indexed, rather than non-indexed, columns

Here ClientName is a transformation (concatenation of FirstName and LastName) and searching a transformation is never going to be fast, as it cannot be indexed in the database.

Now with the help of Index awareness, instead of searching a ClientName, the SQL will search the ClientID which is a indexed column.

The Key values are then stored in the LOV for the object. When a user selects a value from the LOV, the SQL will switch the value to the indexed column. Of course, if the user does not select from the LOV, but types in the value instead, Index Awareness is not used. So you will need to train your users to select from the LOV.

Index awareness works best when used on a transactional database, or a snowflake schema. Star schemas are usually already optimized for queries, so you may not get as much improvement with a star schema.

49) You are a universe designer and report developer in BO, what type of information you gather from client?

Ans. Universe level:

What new objects to be made available in Universe

What new pre-conditions to be added

What User restrictions apply

Report Level:

Attributes required in report

Report input criteria (Prompts)

Report filters to be added

Alerter - Any information to be highlighted on specific criteria

Any calculated attributes required and exact calculation in functional terms

Is User to be informed in case of duplicate records and what should be the action

Report layout

50) What is BIAR file?

Ans. It stands for Business Intelligence Archive Resource which is similar to a zip file which is used to collect objects together to facilitate easier backup, restore or transporting objects from one system to another.

You can create, export, import and promote a BIAR file with Life Cycle Manager (LCM).

51) How to make Back-up of a universe?

Ans. Using Business Objects Designer import the universe. Then making no changes, close the universe. This will create a fresh, unaltered copy of the universe on your local computer.

Locate the universe file (*.unv) and the folder (has the same name as the universe file) in the following folder path on the computer on which you are running Designer:
C:\Documents and Settings\Application Data\Business Objects\Business Objects 12.0\Universes@DevelopmentWorking Area

Select both the universe file and the folder (hold CTRL and use mouse). Then right-click either the selected file and chose “WinZip -> Add to Zip File…” Note: Any compression tool will do.

Enter a name that is meaningful to you; it may indicate the version of the universe, the date/time, or editor. Click “OK” and notice the new Zip file you created. This is your backup.

52) How to Restore a back-up of a universe

Ans. Many logical work flows for handling universes can result in changing CUIDs and lost report bindings. Nevertheless, if properly back-up a universe can be restored to a prior state by following this work flow.

Open Designer, but do not open any universe

Locate the desired corresponding Universe folder and zip file.

They should be located in:
C:\Documents and Settings\Application Data\Business Objects\Business Objects 12.0\Universes@DevelopmentWorking Area

Back-up the existing universe file and folder to a new Zip file

Delete the existing universe file and folder

Unzip the backed-up universe file and folder to this same location. Note: They must be in the same local folder as the universe file and folder they are replacing.

Open the universe file that you just unzipped and export it to the folder in which you are working:

You will receive a prompt similar to the following. Click “Yes”.

Prompt reads, “A newer version of this universe exists in the repository. If you continue with the export you may overwrite existing changes. Do you want to continue?

A newer version of this universe exists in the repository. If you continue with the export you may overwrite existing changes. Do you want to continue?

WARNING: if you receive any messages asking you to Move, Copy, or Overwrite a universe then you may not be restoring to the exact location that the universe previously resided. Prompts asking to overwrite the universe will be received if the universe you are attempting to restore does not have the same CUID as the backup universe. In this case, double-check your directories and analyze everything in Query Builder.

At the end of the export close the universe and import the universe you just exported.
Verify that this is the correct universe.
You have successfully restored the universe

: Designer

1)    What are the difference between BO XI R2 and 3.0?

Ans. BO XI 3.0 supports the following features which are not supported in XI R2. These are:

Optional prompts: In WEBI you can ignore optional prompts by not specifying any value and prompts does not apply a filter on the data.

ForceMerge function: With this WEBI function you can calculate measures for merged dimensions and is similar to Multicube function in DESKI.

Data tracking: WEBI has the feature to track data changes by highlighting the changed data and displays the previous value of a dimension or measure along with its current value.

Note: Data is dependent on database source cannot be translated.

Stored Procedures: In Designer, you can create a universe based on one or more existing stored procedures.

2)    How can you optimize a Universe?

Ans. Query time can be shortened by optimizing a universe. There are several ways to optimize a universe.

      Optimizing the Array fetch parameter in the Universe parameters

      Allocating weight to each table

      Using shortcut joins

      Creating and using aggregate tables in database

Optimizing the Array fetch parameter

               The array fetch parameter allows you to set maximum number of rows that are permitted in a fetch procedure and determines the packet size on the network. For example, if the array fetch is set as 20 and you plan to retrieve 40 rows, then two fetches will be executed to retrieve the data.

               Some data sources do NOT allow to modify the fetch size, in such cases all rows will be return in single FETCH. If you want to reteive binary long-objects (BLOB) set FETCH size as 1.

               If you have a network that allows you to send a large array fetch, then you can set a new larger value. This value will speed up the FETCH procedure and reduce query processing time.  Values can be set from 1 to 999.

Allocating table weights

               Table weight is a measure of how many rows are there in a table. By default BO sorts the table from lighter to heavier tables and this determines the table order in the FROM clause of SQL statement.

               If you are using Oracle database, you can optimize the SQL by reversing the order that BO sorts the table. To do this we need to modify the oracle .PRM file by changing parameter REVERSE_TABLE_WEIGHT from ‘Y’ to ‘N’ (Default – Y ). By doing this BO forces to sort the tables from those with most rows to those with least rows.

Path of oracle.PRM file => <INSTDIR>\dataAccess\RDBMS\connectionServer\oracle\oracle.prm

               Restart Designer to apply the changes to the file.

In Designer we can manually change the number of rows for any table.

To view, select View menu => Number of rows in tables. To modify

      Open a universe in designer, Right click the relevant table.

      Select number of rows in table from contextual menu. A dialog box appears.

      Select the Modify manually tables row count radio button. A text box appears.

      Type a number in the text box. This is the number of rows that you want to use for the     table.

      Click ok and save the universe.

Using short cut joins

               A short cut is an alternate path between two tables. Short cut join used to reduce number of tables that are used in a query.

Using Aggregate Awareness

               The ability of a universe to make use of Aggregate tables to optimize SQL query is called Aggregate Awareness. These are the tables which contain pre-calculated data.

               You can use @Aggregare_Aware function in the select statement for an object which directs SQL query to run against Aggregate tables rather than on base tables.

               Using Aggregate tables speeds up the execution of query and thus improves the performance.

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 ( Tools- Aggregate Navigation )

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.

Define any necessary contexts

1. Define one context per level of aggregation.

Test the results

1. Run several queries.

              2. Compare the results.

3)    Can you please clarify the difference between compatible and incompatible objects? When we will use these two?

Year - Quarter, Month, Day Compatibles

Quarter - Month, Day Compatibles, Year Non-compatible

Month - Day Compatible, Year, Quarter Non-compatible

4)    How can you set access restrictions on a universe?

Ans. Access restrictions that apply to a user group are defined in a restriction.

         Universe security is managed at two levels:

ØCMS

o   From the Central Management Console, 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

o   You can define restrictions for users allowed to use a universe. It can be

      Connection

      Query controls

      SQL generation option

      Object access

      Row access

      Alternate table access

5)    How do you set Row access restriction?

Ans.  Tools => Manage Security => Manage Access Restrictions

         Click New  => Click Rows tab => Click Add

         Click Browse button next to Table box

         Click table name and click ok

         Click Browse button next to Where clause box

         Give condition => OK => OK .

To view the restrictions applied to all users and groups, select

Tools => Preview security restrictions, click user account name in the list => Preview.

Parameters and options that appear in red are those that have been modified and apply specifically to the restriction.

6)    What are Linked universes?

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

         Core universe is a universe to which other universes are linked.

         Derived universe is a universe that contains a link to a core universe.

If the linked core universe is a Kernel universe, then components can be added to derived universe. If the linked core universe is a master universe, then the derived universe contains all the core universe components. Class and objects are not added to the derived universe but they can be hidden in the derived universe depending on the user needs of the target audience.

Advantages:

Ø  Maintenance will be easy because when you modify a component in the core universe, designer will automatically reflect the changes in all the derived universes.

Ø  No need to re-create common components each time you create a new universe as often used components can be kept in a core universe.

Requirements:

Ø  Core universe and derived universe use the same data account or database. Using the same connection for both universes makes managing the universes easier, but this can be changed at any time.

Ø  Core and derived universes must be in same repository.

Ø  Core universe was exported and re-imported at least once. Derived universe does not need to have been exported before creating a link.

Ø  Exported derived universes are located in the same universe domain as the core universe

Ø  You have the authorization to link the given universe.

Restrictions:

Ø  You can use only one level of linking. You cannot create derived universe from a universe which is itself derived.

Ø  All classes and objects are unique in both the universes else conflict will occur.

Ø  Only the table schema, classes and objects of the core universe are available in the derived universe. Context must be re-detected in the derived universe.

Ø  The two universe structures must allow joins to be created between a table in one universe to a table in the other universe else Cartesian product will occur.

Ø  List of values associated with a core universe are not saved when you export a derived universe with the core universe structure.

To save LOV’s associated with core universe:

      Create new objects using the same definition

      Assign the new objects the same LOV’s as the core objects

      Hide these new objects.

The hidden objects serve the function of holding the LOV’s so that they can be exported and imported with the derived universe.

You can log into designer as a different user without quitting your work session.

Tools => Login As. Give user name and password.

(If there are any open universes, designer closes them automatically)

Tools => Change password ( to change password).

7)    What is cardinality and what happens if Cardinalities are not resolved?

Ans. Cardinality means a relationship between two tables based on a join. Means how many rows of one table will match with rows in other tables when these tables are joined.

If Cardinalities are not specified between the tables, then

We get more results at report level than the actual result, like a Cartesian product.

We can’t detect the Loops & Traps which are mainly detected by the CARDINALITIES option.

8)    What is Context?

Ans. Context is a list of joins that defines a specific path for query and is used for solving loops and traps.

Main disadvantage is that it exposes the end users to the database structure and they are forced to decide which Context to use to run their query.

9)    How to test the Context?

ü Create a query which includes objects which are only in one context : BO should be able to get the correct result by determining the context.

ü Create a query which includes objects from both the contexts: BO should generate two queries and then unions it.

ü Create a query which includes object which are common two both the context: BO should prompt for contexts to be used.

10) What is Alias?

Ans. Alias used for solving loops and traps by using same table with different name in the query.

11) What is loop and how do you resolve them?

Ans. Loop is a closed circular path among the joined tables.

If the loop is formed between 1 fact table and 2 (or more) dimension tables, then create an Alias for existing dimension table.

If the loop formed contains more than 1 fact table, then go for Context.

If loops are not resolved and report is run following error might come.

Error: Incompatible combination of objects

12) What is Chasm trap?

Ans. It is a type of join path between three tables when there are two “many to one” joins converging on a single table, and there is no context in place that separates the converging join paths. Because of Chasm trap we get incorrect results i.e., Cartesian product will occur.

Resolving a Chasm trap:

·         When there are dimension objects in one or both fact tables, use context.

·         When there are measure objects defined for both fact tables, use the universe parameter option SQL “Multiple SQL statements for each measure”.

13) What is Fan trap?

Ans. It is a type of join path between three tables when a “one to many” join links a table which in turn kinked by another “one to many” join. Because of Fan trap we get incorrect results i.e., Cartesian product will occur.

Resolving a Fan trap:

·         Create an alias for the table that is producing the multiplied aggregation.

·         Create a join between the original table and Alias table

·         Create the context

OR

·         When there are only measure objects defined for both the tables , use the universe parameter option SQL “Multiple SQL statements for each measure”.

14) What are the Derived Tables and its use?

 Ans. It is defined by an SQL query at the universe level that can be used as a logical table in Designer.

Sometimes it not possible to create dimension/measure directly in universe in that case we use derived tables. E.g. First time users, union queries etc.

Derived tables have the following advantages: 

• Reduced amount of data returned to the document for analysis.

You can include complex calculations and functions in a derived table. These operations are performed before the result set is returned to a document, which saves time and reduces the need for complex analysis of large amounts of data at the report level.

15) What is the difference between Conditions and Filters?

Ans. The major difference between query conditions and document filters is that a condition is applied to the query and limits the data retrieved from the database, whereas the filter is applied to the data in the document to hide information and display only the information that you want to appear.

16) What is a Join? What are different types of Join?

Ans. As we retrieve data from more than 1 table, we apply join between the tables.

Different types of Joins are:

Inner join         Left outer join        Right outer join     Full outer join        Theta join            Equi join    Self join

17) What are different types of Objects?

Measure - Object provides metrics (aggregated numbers) by which dimensions are compared.

18) What are concatenated Objects in a Universe?

Ans. A concatenated object is an object you create by combining two existing objects.

For example, let’s say that you wish to create an object called Full Name, which is a concatenation of the objects Last Name and First Name in the Customer class.

Example: Full Name = [First Name] + [Last Name]

If Month value is 1 and Year value is 2011 then MonthYear = 12011

(Both variables are number datatype)

=FormatNumber([Month];”0”) + FormatNumber([Year];”0”)

19) What is Class?

Ans. Class is a logical grouping of objects within a universe and can be divided hierarchically into subclasses and represents as folders on a tree hierarchy in the universe pane.

20) How do you group measure objects?

Ans. Create a new variable and use If..Then..Else in the formula.

Example: =If (<Usage Billing> < 5000) Then "Low" Else If (<Usage Billing> > 5000 and <Usage Billing> < 10000) Then "Medium" Else "High"

21) Is it possible to join more than one universe in Business Objects? If it is so how is that possible?

Ans. Yes it is possible. We can link the two universes by using add link option which is available in parameters (option seen in tool bar file menu).

22) What is category?

Ans.  Category is logical way of structuring or organizing the content (BO document).

23) What are the different data providers can be used to create report?

Ans. Universe, Personal data files such as Text files(.asc, .prn,.txt,.csv),Excel spreadsheet (.xls),Database files(.dbf), xml files(.xml), Free-hand SQL, Stored Procedures, VB, etc.

24) What is the delimiter can be used for text files?

Ans. Tabulation, Space, or Character.

25) How do you combine data from different data providers?

Ans. Dimension objects from one data provider are linked to dimension objects that contain the same values in a different data provider.

26) What are the limitations for Combined queries?

Queries must contain the same number of objects

Objects must be of the same type( i.e. character, date, number)

there can be upto eight queries in a combined query

when using minus, the second query is subtracted from the first

27) How can you check the integrity of universe?

Ans. By making use of Check integrity button.

28) Is it possible to create reports from different universes in one document?

Ans. Yes it is possible to use different universe to generate a single report....multiple data providers. OR you can link other universes to existing universe and then develop the report.

29) What are Universe parameters?

Ans. Definition, Summary, Strategies, Controls, SQL, Links and Parameters .

30) Why do we need metrics and sets?

Ans. Metrics are used for Analysis and Sets are used for grouping.

31) What issues you faced while creating universe?

Ans. Loops, traps, connections to the data base.

32) What are Pre-defined conditions? How do you compare with report conditions?

Ans. These are the conditions defined by Designer in the Universe. Often used Query conditions are qualify for pre-defined condition.

33) What is object and what do you mean by object qualification?

Ans. Object is an instance of class and object qualification represents what kind of object it is like: Dimension or Detail or Measure.

Object is a component named with business terminology and maps to data in the database or uses other objects within the same universe.

34) What is LOV? Where it is store?

Ans. It displays the list of data values associated with an object. A list of value can contain data from a Database file or an External file.

35) Explain in Detail about Measure Objects? What is the use of it? How to create it?

36) In universe parameter if you don’t give the option multiple contexts, what is the impact in report level?

Ans. By having multiple contexts you can utilize the Multipass SQL feature.

37) What is isolated join in check integrity

Ans. A join which is not associated to any one of the contexts.

38) Can I link two Universes having with different connection?

Ans. Yes as long as they point to same database.

39) I have 2 universes, U1 and U2. From U1, I created one report that is R1. Now i want to give the connection R1 to U2 and at the same time delete the connection from U1 to R1? How is it possible?

40) How to set Cascading LOV’s?

Ans. We are going to create Prompt based on:

Country > Resort > Service Line > Service (Hierarchy diagram shown below)

Go to the Edit Properties window of Resort

Select Automatic refresh before use and click Edit

Bring the Country object to conditions panel and build prompt:

“What Country are you interested in?” – Save and Close – Apply

Go to the Edit properties window of Service Line

Select Automatic refresh before use and click Edit

Bring the Resort object to conditions panel and build prompt:

“What Resort are you interested in?” – Save and Close – Apply

Go to the Edit properties window of Service

Select Automatic refresh before use and click Edit

Bring the Service Line object to conditions panel and build prompt:

“What Service Lines are you interested in?” – Save and Close – OK

In WEBI, Select Country, Service Line, Revenue in result objects and Service Line into query conditions panel and build prompt “What service lines are we tracking?”

Click Run query

Now you will be prompted for Service Line Hierarchy

Click values and this is where the fun begins. You will be then prompted for a Resort

41) What is the test methodology for testing BO Universes?

Ans. Universe is tested keeping in view of the reporting requirements. A Universe should support creation of all the reports that needs be created off it plus any other additional requirements.

Using check integrity option we can test the universe.

Check whether any loops are there in universe.

Check any chasm trap and fan trap are there in universe.

Check parse of all the objects created in universe.

Check the joins. Weather any condition is not properly specified.

42) What is hierarchy?

Ans. Hierarchy is an ordered tree structure of relevant dimension objects. Hierarchies are used for drilling information across various levels.

43) What is the difference between the default hierarchy and custom hierarchy?

44) What is difference between local filter and global filter?

Ans. Applying filter to a specific table (block) is called as local filter while applying filter to a report is called as global filter and it is applicable for all the blocks in a report.

45) What is Drill down, Drill up, Drill Through, and Drill across?

Ans. Drill Down: When you drill down, you display the next level of detail in a hierarchy.

Drill Up: When you drill up, you display the next highest level of detail in a hierarchy.

46) What is the syntax of prompt?