Universe Designer Interview Questions
- What is ‘business objects’?
- What are the various Business Objects products?
- What are the advantages of Business Objects over other DSS?
- How many modes are there in BO & Designer
- What are Enterprise and Workgroup modes?
- How do you save a Business Objects document which can be accessed by all users in workgroup mode?
- What is online and offline mode?
- What is a universe?
- Can a Universe connect to multiple databases?
- How do you define universe parameters?
- What is a database connection?
- What are the types of connections we use when connecting to the database?
- What are different types of joins available in Universe design? Explain each
- How do you design a universe?
- What are the components of the Designer interface?
- What are classes/objects?
- What are classes?
- What are objects?
- What are Dimension-Measure-Detail Objects?
- What is hierarchy
- How to create hierarchies in BO?
- What are Contexts?
- What are aggregated tables and how would you use it in BO Universe
- What is Incompatibility?
- What is the typical strategy employed in developing/maintaining/distributing Universes?
- I have Customer dimension table and a fact table with cust_to_ship_key and cust_to_bill_key. How do I get the corresponding customer names?
- What are strategies?
- What are the different types of Strategies?
- How do you specify external strategies?
- What are the visualization options available?
- What is Join Path Problem?
- How to add aggregate table in universe in real life scenario?
- If we have a user group and we want to give the access to the report from 1990 to 2000. and from that group we want one user to restrict to see the report from 1990 to 1995. Then what to do in BO designer, so that this can be possible (Not in report level).
- What is shortcut join? What is its use explain with an example
- What is isolated join, explain with an example
- What is cardinality and its significance in BO universe
- How will you know the version of BO designer you are using
- What is loop in Universe? Explain its problem and different methodologies to resolve it.
- What is CHASM trap and how to resolve it
- When any new universe changes are deployed how does the end user get the view of the new classes/objects added (apart from specs doc)?
- I have 2 universes that is 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 explain?
- What is meant by ZABO and FC(full-client)?
- What happens if cardinalities are not resolved?
- What is aggregate navigation.
- What is Index Awareness in Universe.
- What are @functions
- What is core Universe
- What is derived universe.
- What are linked universes? Explain with advantages and disadvantages.
- What is Object Qualification
- How to create filter in Universe and what is advantages and disadvantages.
- Why do we need to create derived table in Universe.
- Explain security level in BO Universe
- How to implement row level security in Universe.
- How do you determine when to use alias and when to use context.
- What are different ways to link universes.
- How do you distribute Universe?
- What is the list mode?
- What is Parse checking? .
- What are the disadvantages of Alias?
- What are linked Universes
- Explain Universe Design Methodology.
- Explain Universe Development Lifecycle.
- What is failover and fault over?
- What is role of CMS?
- Working with FRS Pruning and Tracing
- What is PRM file?
- Universe Parameters/Data source Connection
- What is Sql Editor?
- What is File Repositary server?
1.What is ‘business objects’?
BUSINESSOBJECTS is an integrated query, reporting and analysis solution for business professionals that allow them to access the data in their corporate databases directly from their desktop and present and analyze this information in a BUSINESSOBJECTS document.
It is an OLAP tool that high-level management can use as a part of a Decision Support Systems (DSS).
BUSINESSOBJECTS makes it easy to access the data, because you work with it inbusiness terms that are familiar to you, not technical database terms like SQL.
2.What are the various Business Objects products?
User Module, Designer, Supervisor, Auditor, Set Analyzer, Info View (Web Intelligence),Business Objects Software Development Kit (SDK), Broadcast Agent etc.
3.What are the advantages of Business Objects over other DSS?
Familiar Business Terms.
Drag and Drop.
Powerful reports in less time.
Enterprise wide Deployment of documents using WebI
Customized dashboards using Application foundation and Business Objects SDK.
4.How many modes are there in BO & Designer
There are 2 types: Enterprise Mode, Workgroup
5.What areEnterpriseand Workgroup modes?
Designer lets you save universes in either enterprise or workgroup mode.Enterprisemode means that you are working in an environment with a repository. Workgroupmode means that you are working without a repository. The mode in which you save your universe determines whether other designers are able to access them. By default, a universe is saved in the mode in which you are already working. For example, if you launched a session in enterprise mode, any universe you save is automatically in that mode.
6.How do you save a Business Objects document which can be accessed by all users inworkgroup mode?
If we want to make a universe accessible to another designer working without a repository, then click the “Save For All Users” check box in the Save as universe dialog box.
7.What is online and offline mode?
If you want a universe to be accessible in offline mode, you must firstly ensure that the
universe has been opened at least once in online mode, and that it has been saved with the Save for All Users check box selected in the Save Universe As box. To make Offline mode available:
Online Default mode of operation for Designer when you are working in an
environment with a repository.
Offline Mode of operation for Designer when you are not connected to a repository.
• Only available if you have previously connected in online mode.
• In offline mode you can open universes that are stored on your local computer only if those universes have been opened previously in online mode.
• You can access databases where the connection and security information are stored on your local machine (personnel and shared connections.)
• You can use offline mode when you do not have access to the repository, for example when working with a laptop off site, or when the network is not available.
8.What is a universe?
Universe provides a semantic layer between you and the database. It consists of classes and objects named in business terms. It is basically a mapping of table and the columns in the database to classes and objects respectively in the query panel.
Alternatively, It is logical mapping of data in business terms.
In the BusinessObjects User module, universes enable end users to build queries from which they can generate and perform analysis. Universes isolate end users from the complexities of the database structure as well as the intricacies of SQL syntax.
9.Can a Universe connect to multiple databases?
10.How do you define universe parameters?
The first step in creating a universe is to specify its parameters. These parameters include the definition of a universe, which is comprised of: the universe name, a description of the universe, a connection to an RDBMS. You enter universe parameters from the Universe Parameters dialog box. This dialog box also lets you set up database options, external strategies, graphic options, and print settings.
11.What is a database connection?
A connection is a set of parameters that provides access to an RDBMS. These parameters include system information such as the data account, user identification, and the path to the database. Designer provides three types of connections: secured, shared, and personal.
12.What are the types of connections we use when connecting to the database?
There are three types of connections namely: – Secured, Shared and Personal.
A secured connection is used to centralize and control access to sensitive or critical data. It is the safest type of connection for protecting access to data.
A shared connection is used to access common resources such as universes or documents. Several users can thus use it.
A personal connection is specific to one user and can be used only from the computer on which it was created.
13.What are different types of joins available in Universe design? Explain each
Equi or Inner or Natural or Simple join: is based on the equality between the values in the column of one table and the values in the column of another. Because the same column is present in both tables, the join synchronizes the two tables.
Self-Join: join a table to itself i.e create a self-join to find rows in a table that have values in common with other rows in the same table.
Theta or Non-Equi join: links tables based on a relationship other than equality between two columns. A join is a relational operation that causes two or more tables with a common domain to be combined into a single table. The purpose of joins is to restrict the result set of a query run against multiple tables.
Outer join: links two tables, one of which has rows that do not match those in the common column of the other table.
Left Outer Join: All records from first table with matching rows from second.
Right Outer Join: All records from second-named table with matching rows from left.
Full outer join: All rows in all joined tables are included, whether they are matched or not.
Shortcut join: can be used in schemas containing redundant join paths leading to the same result, regardless of direction. Improves SQL performance.
14.How do you design a universe?
The design method consists of two major phases.
During the first phase, you create the underlying database structure of your universe. This structure includes the tables and columns of a database and the joins by which they are linked. You may need to resolve loops which occur in the joins using aliases or contexts. You can conclude this phase by testing the integrity of the overall structure.
During the second phase, you can proceed to enhance the components of your universe. You can also prepare certain objects for multidimensional analysis. As with the first phase, you should test the integrity of your universe structure. Finally, you can distribute your universes to users by exporting them to the repository or via your file system.
15.What are the components of the Designer interface?
In Designer, you create a universe using three areas: the Universe pane, the Structure pane, the Table Browser. The Universe pane displays the components of the universe from the point of view of BusinessObjects; that is the classes, objects, and conditions. The Structure pane reflects the underlying database structure of the universe including the tables, columns, and joins. The Table Browser is the component that lets you create the classes and objects of the universe from the tables and columns of a database.
16.What are classes/objects?
An object maps to data or a derivation of data in the database. For the purposes of multidimensional analysis, an object can be qualified as one of three types: a dimension, detail, or measure.
A class is a collection of objects based on business categories.
A universe is a set of classes and objects intended for a specific application or group of users.
17.What are classes?
A class is a logical grouping of objects within a universe. In general, the name of a class reflects a business concept that conveys the category or type of objects. For example, in a universe pertaining to human resources, one class might be Employees. A class can be further divided into subclasses. In the human resources universe, a subclass of the Employees class could be Personal Information. As designer, you are free to define hierarchies of classes and subclasses in a model that best reflects the business concepts of your organization.
18.What are objects?
An object is the most refined component in a universe. It maps to data or a derivation of data in the database. Using objects, end users can build queries to generate reports. The name of an object suggests a concept drawn from the terminology of a business or discipline. For a human resources manager, objects might be Employee Name, Address, Salary, or Bonus, while for a financial analyst, objects might be Profit Margin, Return on Investment, etc. For the purposes of multidimensional analysis, objects are qualified as one of three types: dimension, detail, or measure.
19.What are Dimension-Measure-Detail Objects?
When creating universes, universe designers define and qualify objects. The qualification of an object reveals how it can be used in analysis in reports. An object can be qualified as a dimension, a detail, or a measure.
A dimension object is the object being tracked; in other words, it can be considered the focus of the analysis. A dimension can be an object such as Service, Price, or Customer.
Dimension objects retrieve the data that will provide the basis for analysis in a report. Dimension objects typically retrieve character-type data (customer names, resort names, etc.), or dates (years, quarters, reservation dates, etc.)
A detail object provides descriptive data about a dimension object (or attribute of a dimension). It is always associated with a specific dimension object. However, a detail object cannot be used in drill down analysis. E.g. Address & phone number can be attributes about the customer dimension.
A measure object is derived from one of the following aggregate functions:
Count, Sum, Minimum, Maximum or average or is a numeric data item on which you can apply, at least locally, one of those functions. This type of object provides statistical information. Examples of measure objects include the following:
Revenue, unit price etc
20.What is hierarchy
Groups of related dimension objects are referred to as dimension hierarchies. An example of a dimension hierarchy is Geography, which can consist of City, Region, and Country.(Hierarchy is an ordered series of related dimensions, which can be used, in multidimensional analysis) Good examples of hierarchy are geography and time
21.How to create hierarchies in BO?
A hierarchy, which the designer sets up when creating the universe, consists of dimension objects ranked from “less detailed” to “more detailed”. The objects that belong to hierarchies are the ones you can use to define scope of analysis.
22.Can a Universe have more than 1 fact Table?
Yes. Typically a universe can have more than 1 fact table and numerous aggregated tables.
23.What are Contexts?
A context is a rule that determines which of two paths can be chosen when more than one path is possible in the database from one table to another. It helps in resolving the loops created by various joins in the universe tables.
With certain database structures, you may need to use contexts rather than aliases to resolve loops. A situation where this commonly occurs is a transactional database with multiple fact tables (“multiple stars”) that share lookup tables.
24.What are aggregated tables and how would you use it in BO Universe
Aggregate tables are table which contains summarized data at different level depending on the need of reports. Imagine a fact table which contains granular data up to minute’s transaction. Now if you are developing a reports which has hour, day, week, month, quarter, year level summaries. Queries to get these summary values will scan millions of records which would in turn result in poor performance of reports. One can address this issue by creating aggregate summary table.
Possible problems of using Aggregate table
Aggregate table are good to achieve performance for high level summery queries. However if there are multiple aggregate tables which contains summary values then using aggregate table might be an issue.
Consider following example
e.g. you have two aggregate table
AggregateID Year Quarter Month Sales Revenue
AggregateID Year Quarter Month ServiceType Sales Revenue
Now if you have a requirement wherein one of your report is displays year wise sales revenue whereas other report is displays Year wise ServiceType “sales revenue”. How would you tell report to use table1 for “year wise” “sales revenue” and use table 2 for another report.
Using Aggregate Table in Business Objects
Business Objects provides a wonderful function to use aggregated tables.
Aggregate_aware(). This function determines which aggregate table to use based on the attributes used in the query.
Expression = field or valid SQL expression or calculation
e.g. so formula for sales revenue might be.
Notice the arrangement of column used in function. It is highest summarized to least summarized.
Now if while creating report if you use only year attribute and sales revenue. Aggregate_aware will use table1 to get the sales revenue, if used service type in report It will use table2, in all other cases it will use sum(sometable.column) which could be a fact table.
Rules to use aggregate_aware
1.If the object is at the same level of aggregation or higher, it is OMPATIBLE with the summary table.
e.g. In above example none of the table contains aggregated value for “Article” so article object will be incompatible.
You can not use aggregate_aware with “Article object”
2.If the object is at a lower level of aggregation, it is INCOMPATIBLE.
3.If the object has nothing to do with the summary table, it is INCOMPATIBLE.
25.What is Incompatibility?
The set of incompatible objects you specify determines which aggregate tables are disregarded during the generation of SQL. With respect to an aggregate table, an object is either compatible or incompatible. The rules for compatibility are as follows:
When an object is at the same or higher level of aggregation as the table, it is compatible with the table.
When an object is at a lower level of aggregation than the table (or if it is not at all related to the table), it is incompatible with the table.
26.What is the typical strategy employed in developing/maintaining/distributing Universes?
Phase 1: Break down the informational system into functional areas.
Phase 2: Analyze the information needs of users.
Phase 3: Design a conceptual schema
Design the specification of the user.
Phase 4: Create a Universe with designer.
Test the Universe with the Business Objects module.
Distribute the Universe.
Repeat the other steps for other Universes.
Phase 5: Update and maintain the Universe.
Notify end users of changes.
27.I have Customer dimension table and a fact table with cust_to_ship_key and cust_to_bill_key. How do I get the corresponding customer names?
Create an Alias table for Customer dimension table. Join the cust_to_ship_key with a customer key of Actual customer table and join the cust_to_bill_key with customer key of the Alias.
28.What are strategies?
A strategy is a script that automatically extracts structural information from a database or flat file.
29.What are the different types of Strategies?
In Designer we can specify two types of strategies:
1) Built-in strategies and
2) External strategies
Built-in Strategies: Designer provides a number of default strategies which we can use. These are strategies for extracting joins, detecting cardinalities, and creating default classes and objects. Options for indicating default strategies are located in the Database tab of the Options dialog box.
External Strategies: We can also create our own strategies. Such strategies are referred to as external strategies. With an external strategy, we can specify the exact way that objects and joins are to be extracted from the database structure. The strategy we use, for example, can be a script generated from a CASE Access tool. An external strategy is specific to one RDBMS.
30.How do you specify external strategies?
With an external strategy, you can specify the exact way that objects and joins are to be extracted from the database structure. All external strategies are contained within the same text file. The name of this text file is indicated in the .prm file specific to your RDBMS. In the .prm file, the strategy file is declared as follows: STG=[StrategyFileName] where StrategyFileName is the name of the strategy file.
An external strategy, whether for objects or for joins, is made up of the following sections:
a name and description (These are visible in the Strategies tab of the Universe Parameters dialog box.)
a type parameter: object or join
an SQL parameter or file parameter
an optional parameter that points to a connection other than the universe connection.
An external strategy can be based on SQL or a file.
31.What are the visualization options available?
Designer contains a variety of features for organizing and viewing the tables and columns in the Structure pane. Among these features are: List Mode, which adds three panes to the Structure pane. These panes are for viewing the names of tables, joins, and contexts. When you click a component in a pane, its corresponding graphical representation in the schema is highlighted. Graphic options, which let you customize the shape or appearance of the tables, columns, joins, and cardinalities in the Structure pane.
Arrange tables, a feature that reorganizes the tables in the Structure pane so as to produce an orderly display. Gridlines, a command that displays a grid, which you can use to align tables in the Structure pane.
Table (Column) Values, commands that display the data values associated with a particular table or column.
32.What is Join Path Problem?
A one to many join links a table, which is in turn linked by a one to many join. This type of fanning out of one to many joins can lead to a join path problem called a fan trap.
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.
33.How to add aggregate table in universe in real life scenario?
Decide the reports which are using high aggregates
Create Aggregate table in DB
Insert them in Universe
Join them with dimension table
34.If we have a user group and we want to give the access to the report from 1990 to 2000. and from that group we want one user to restrict to see the report from 1990 to 1995. Then what to do in BO designer, so that this can be possible( Not in report level).
This can be done using row level security in universe parameter.
35.What is shortcut join. What is its use explain with an example
A shortcut join is a join which links two table by bypassing intervening tables that exists in the universe. This is used when it is possible in certain circumstances to make SQL more efficient.
e.g. in Above example if you want get the client list and there countries you can simple join country ID from client table to country id from country table. However this would introduce a loop. So instead of using normal join you make it as shortcut join. So if query contains object from client table and country table it would you shortcut join. This would result in efficient query avoiding extra join of region.
36.What is isolated join, explain with an example.
Isolated joins means the joins which are not included in
any context. suppose you have 15 joins in ur universe. in
context A you included 7 joins and in context B 7 joins.
The rest means one join isur isolated join.
37.What is cardinality and its significance in BO universe
Cardinality expresses the minimum and maximum number of instances of an entity B that can be associated with an instance of an entity A. The minimum and the maximum number of instances can be equal to 0, 1, or N.
Because a join represents a bi-directional relationship, it must always have two cardinalities.
There are two main methods for detecting or editing cardinalities:
• Detect Cardinalities command
• Edit Join dialog box
If you selected the Detect cardinalities in joins options in the Database tab of the Options dialog box, Designer detects and retrieves the cardinalities of the joins. If you do not use this option, you can still retrieve the cardinalities for one or all joins in the universe.
SQL Traps in Business Objects Universe : How to Solve CHASM Trap
A Chasm trap is a join path type problem between three tables where two many-to-one join path converge on a single table and there is no context to separate the converging path.
However even if we have above type of joins in universe we experience chasm trap problem only when
- There is many-to-one-to-many relationship between three tables
- Reporting query have objects on tables from many end
- There is more the one value for a single dimensional value.
Let’s see it in detail. Consider below diagram.
Now in above case when a query includes object from table B and Table C and objects from table A, the CHASM trap causes a query to return every possible combination of one measure with other. This result gets multiplied by number of rows in result set and output is similar like a Cartesian product.
This CHASM trap can be resolved by executing separate query for each measure and then merging the results.
How to detect CHASM trap in a universe
CHASM trap can be detected automatically; you would need to use multiple ways to identify a possible CHASM trap issue.
- Make sure you arrange one-to-many table from left to right in universe and analyze one-to-many relationship to detect possible CHASM trap issue.
- Use detect context automatic tool to detect possible context in universe and use them in order to avoid CHASM traps.
- Test many-to-one tables by creating reports using object from table at many end. Try adding additional dimension object in report. If there is a CHASM trap aggregated values will be double which might help you to detect possible CHASM trap.
Lets see a practical CHASM trap example in a universe
Let consider a following joins in universe. In following diagram three tables are joined by many-to-one-to-many join relationship.
If I want to see number of guest for a sports service, report returns following result
|Service||Number of Guests|
If I want to see number of future guest for a sports service, repot returns following result
|Service||Number of Future Guests|
However If I include moth the measure together in the same query.
|Service||Number of Guests||Number of Future Guests|
Result seems to be inflated due to CHASM trap issue.
How does CHASM trap inflate the result of a query?
CHASM trap causes query to return every possible combination of a measure with other measure, which makes query to return Cartesian product result and since result is grouped against single dimension value its gets aggregated.
In above example
- Number of guests transactions *Number of future guest transactions
- Number of future guest transactions*Number of guests transactions
Lets go deeper to understand what has happened.
In order to examine what all rows are included in aggregation we would need to split the aggregated data into granular level.
For “Number of Guest’ we would include additional dimension “Days Billed” to see granular data.
|Service||Number of Guests||Days Billed|
For “Number of Future Guest’ we would include additional dimension “Days Reserved” to see granular data.
|Service||Number of Future Guests||Days Billed|
Now lets combine the result (Disable generate separate query for measure from universe parameters)
|Service||Days Billed||Number of Guests||Days Reserved||No. of Future Guests|
You could see, query returns every possible combination of future guests with number of guests and when result is aggregated it gives wrong numbers.
How to solve CHASM Trap?
You can solve CHASM trap using context. In above example you can create context.
- Analyze many-to-one-many relationship to detect possible CHASM trap.
- Use Detect Context to create contexts
- Select the contexts and click on Add.
- Select File->Parameters to launch universe parameter box.
- Click SQL Tab
- Select Multiple SQL for each contexts option.
- Click OK
Now when you create query, two separate queries will be generated and result will be merged on common dimension. This is how CHASM trap gets resolved using contexts.
Using “Multiple Statement for Measure” to solve CHASM trap.
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 force 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.
How to Define Cardinalities in Business Objects Universe
by BIDW TEAM
In previous post we have learned how to set up join in BO universe. in this post we will lean what is cardinality and how to define cardinalities in SAP Business Objects Universe.
What is cardinality?
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.
Setting up cardinality is very important to resolve loops BO universe.
Let’s take a practical example of cardinality.
A manager can have many employees reporting to him, so the relationship between manager and employee table is 1-N.
The cardinality can be any of one type.
- One-to-One (1-1)
- Many-to-many (N-N)
Setting up cardinality manually or using automatic detection tool
Cardinality in universe design is based on a logical algorithm, which uses physical count of record from the table.
The automatic detection tool only gives correct cardinality if the database is populated with realistic data.
Also, the automatic detection tool fires three queries for every join to set the cardinality. So if you have lots of table in schema, automated cardinality detection tool is not a good idea as it might overload the database with queries.
Let’s take an example of how cardinality detection tool works.
Manager table has multiple employees reporting to each manager so cardinality of manager and employee table is 1-N. Let understand how automated cardinality detection tools determines the cardinality for this join.
- One query to find number of rows from manager table
- One query to find number of rows from employee table
- One query to find number of rows when these two tables are joined
If manager table has 10 rows, Employee table has 20 rows.
1st query will return 10, second query will return 20, and third query will return 20 which would tell that employee table is at many sides and manager table is at 1 side.
The output of queries is very important for automated tool and that’s why database should contain realistic data.
Detect cardinality using automation tool.
To detect cardinality of all joins
- From Tools->Automation Detection->Detect Cardinality
- If no joins is selected, it asks for if you want to detect cardinality for all joins.
- Click OK.
To detect cardinality for specific join
- Right click on specific join
- Click on detect Cardinality
To set cardinality manually
- Double click on join for which you want to set cardinality
- Edit join dialog appears with join expression
- Check on cardinality check box
- Select appropriate 1,N radio box
- Click ok.
After reading this article you should be comfortable with cardinality concept and its usage in SAP Business Objects Universe Design.
List of Values (LOV) in Business Objects Universe
by BIDW TEAM
List of values or LOV is a distinct list of data values associated with an object. When any dimension of details object is created LOV is assigned to an object automatically.
Use of List of values.
When user needs to filter data in a query based on specific object values, User can simply view the LOV of that objects and choose the value on which they want to filter the data.
e.g. if COUNTRY dimension has following distinct values
A,B,C and if user wants to filter the data of country B, user can put a filter on Country dimension and choose the B as filter while executing the query.
How to create a LOV for an object.
- Double click on object in designer to view its properties.
- Click on Properties Tab
- Click on “Associate a List of Values” checkbox.
- Select other LOV options based on requirement.
When first LOV is created it is stored in .LOV file name at universe subfolder on the system file system.
The default location is
C:\Documents and Settings\<UserName>\Application Data\Business Objects\Business Objects 12.0\Universes\@<ServerName>\<UniverseName>
It’s the name of LOV file by which it will stored on local file system. User can override the default name and can enter his own LOV name. Maximum character limit is 8.
Allow Users to Edit List of Values
When checked this option allows report users to edit the list of values of an objects. The purpose of a list of values is usually to limit the set of available values to a user. If they can edit a list, you no longer have control over the values they choose. Normally, if you are not using a personal data file as a list of values source, you clear this option to ensure that users do not edit lists of values.
Automatic Refresh before Use
When selected this option LOV will be refreshed each times it is referred and used in report. You should choose this option only if contents of underlying column are frequently changing. This options should be use very carefully after evaluation. If this option is not selected LOV is refreshed first when the objects is used in a user session.
Select the Hierarchical Display property to display the cascading list of values as a hierarchy in Web Intelligence.
Export with Universe
When this option is selected LOV file associated with object is exported to universe CMS and gets stored as XML on CMS
Viewing the LOV of an object
To view the LOV of an objects click on display button on properties tab of an object
Modifying the LOV of an object
You can remove the values from LOV of an object by applying a filter or add values to LOV by adding a column.
Apply condition on LOV
To apply condition on LOV
- Click on Edit button on objects edit properties tab
- The designer query panel will appear showing default object of a LOV
- Drag drop the condition object in condition pane and specify the appropriate condition.
- You can also view the SQL of the LOV query by click on SQL icon on toolbar.
- Run the query to test the values after applying condition on LOV
View and Edit LOV of complete universe
You can also view all the object which has LOV associated with them and edit them.
- Click on Tools->List of Values->Edit
- List of values dialog will appear
- Select the LOV objects and click on Edit if you want to edit a LOV.
- In addition to query you can also define LOV for an object using personal data filelike CSV and values from this file can also be used as LOV for an object. To do so.
- Click on Personal Data and provide the details on Personal data LOV dialog box.
Cascading LOV is a LOV associated with hierarchy of an object in the universe. Cascading LOV is created, and if any of the object is used as prompt filter in report query, user has to answer series of values from cascading LOV.
How to create Cascading LOV
- Click on Tools->List of Values->Create Cascading LOV.
- Add the object and re-arrange them as per your hierarchy
- Click on generate LOVs
- Click OK.
Now if you use any of the objects as prompt in query. It will prompt the hierarchical LOV to user.
Using Derived Table in SAP Business Objects Universe
What are Derived table and its use?
Derived table is not a physical in database however its logical table created in Business Objects Universe using SQL. Derived table can be considered like views in database where its structure is defined using SELECT statement.
Advantages of Derived Table:
- Derived table in Universe lets you create a SQL statement to fetch data using variousexpressions, joins which is not possible using universe structure.
- Its lets you put inline views (select statement in FROM clause) which are not possible in Universe normally.
select agg1_id as id from
(select * from Agg_yr_qt_mt_mn_wk_rg_cy_sn_sr_qt_ma)
- Derived table can be treated as normal tables and can be joined with actual table in Universe.
- Its lets you merge data from different table which is not possible using normal in universe using underlying data sources.
- One can embed prompts in derived table definition.
- You can use derived table as a lookup when you have multiple fact table separated by contexts. Normally if you want to use measured from different fact table thenBusiness Objects creates two queries one for each measure. Now some time this mayresult in performance issues. You can avoid this by creating a lookup table for different fact tables using derived tables.
Suppose you have measure1 in fact1 and measure2 in fact2 and dimension is dim1. Now if you create a query with dim1, measure1, measure2 you will get two different queries. Now instead of this you can create separate derived table which includes dim1, measure1, measure2.
Disadvantages if derived table.
Since derived table is not an actual table you may face performance issues if underlyingSQL query has performance issues.
How to Create Derived Tables
- · From menu mar select Insert->Derived Tables
- · Now write the SELECT statements which to define the structure of derived table. Remember to give Alias to column if you are using any expression in column list.
- · Click on “Check Syntax” to confirm the definition of derived table
- · Click OK
· Join the newly created derived table to existing table.
- · If you have context. Include the join in relevant context.
- · Save the Universe.
- · Now you can create object using derived table just like normal tables.
Nested Derived Tables
Nested derived table is nothing but a derived table using another derived in definition. It behaves similar to normal derived tables. Nested derived tables are generally using when underlying derived table is complex to build. In that case you can create different small derived tables and then use this derived table in main derived table.
Advantage of using nested derived table is the simplicity in derived table creating. AlsoBusiness Objects combines the definition in single SQL and it’s treated as single SQL.
Business Objects does not limit number of derived tables but nesting is limited to 20 levels.
You can create a nested derived table by using existing derived table in the from clause.
Linking Universe in Universe Designer
There are many times when universe development task is big and it’s not possible to dingle person to develop the universe in stipulated time. To cater this need Business Objects has provided the facility to divide the universe design task amongst severaldesigners and then integrate the work in single universe using universe linking method.
What is Universe Linking?
Linked universe are universes which share common component such as objects, classes and joins. When two universes are linked the one universe is called as core universe. It’s the main universe which contains common components where other universe is called as derived universe. Changes made to core universe are automatically propagated to derived universe.
Uses and advantages of universe linking.
- When you have to develop multiple universes but there are some common components across these universes in that you can create a core universe of common components and link it to other universes.
- Linking of Universe enables to distribute the universe designing task amongst other developers.
- It’s also helps to follow the code-reusability practices.
- If there are any changes to common objects. It needs to be done only in core universe. It gets propagated to all derived universes.
- Linking of universes help in universe maintenance.
Universe Linking Strategies.
Core Strategy: This strategy is used when you need to develop a universe for different functions. In such case you can create multiple universes for each function and the linkall universes in single universe. This strategy allows us to create a common object only once and also help to split the universe design task amongst developers.
Master Strategy: Suppose you have a requirement to create clone of existing universe. You can achieve this by creating another copy of existing universe however this will increase maintenance as you would need to maintain two universes now. To avoid that you can use master linking strategy. In which existing universe is linked to new blank universe so it creates a copy of existing universe with different CUID and we need to maintain only one universe as core universe is linked.
Multiple Core Strategy: Now if you want divide the development task then you can follow this approach in which developers can develop their universe and at the link all universe in one universe.
Limitations and Restrictions of Linking Universes:
- Both the universes (core and derived) must use same connection and should connect to same database.
- Both the universe must be present in same repository in order to link.
- Only one level of linking is allowed you can create derived universe from another derived universe.
- Both universes should have unique object and classes. If there are duplicateobjects/classes it will be renamed in core universe.
- Tables from two universes must be joined after linking in order to avoid Cartesian product.
- When core universe is linked in derived universe only classes, objects and tables are made available in derived universe. Context and LOV needs to be recreated in derived universe.
How to Link Universes?
To link universe make sure
- · Core universe is exported to repository
- · It’s open in designer.
- · Now open universe parameters from File->Parameters
- · Click on “Link” tab
- · Click on “Add Link”
- · Select the core universe to link.
- · Click OK
- · After this components from core universe will be available in derived universe and it will be grayed.
- · Now analyze the derived universe and create joins between tables added from core universe.
- · Create context/Alias wherever required.
- · Save and Export the derived universe.
Using Include to import one universe into another
In linking universe contents are not copied to derived universe and one cannot edit the core universe components in derived. However sometimes one need to merge two universe into one. For this purpose one can use “Include” universe approach instead of linking. When universe are included components of one universe are copied into another.
Difference between Linking and Including Universes
Including and Linking decision needs to be taken based on your own need following are the points which may help.
- · Core universe structure is created once and used in many derived universe.
- · Only one copy of components from core universe exists in repository.
- · Changes needs to done only to core universe and it gets propagated to all derived universe.
- · Linking universe needs both the universe to be present in repository.
- · Only one level of linking is allowed.
- · Context and LOV needs to recreate in derived universe.
- · Both the universe must use same connection and connect to same database
- · It’s the easiest and fastest way to copy universe into another.
- · Context needs to redefine after including.
- · Changes are not propagated from core to derived universe.
- · Both the universe must exist in repository like in linking.
- · One can easily maintain one universe rather than multiple universe o maintenance becomes bit easy.
Business Objects Universe Optimization
Every Business Objects Universe designer faces performance problems in his/her career at least once and in most of the cases he will use “push down strategy” ask DBA to optimize the warehouse
However it works very well but there are some thing which can also help to optimize the performance of Universe.
Analyze Report SQL for unnecessary Joins
1. First get the list of reports which are performing low and get their SQL.
2. Analyze the report SQL for joins. There could be unnecessary join which is causing query to perform low. make a change in your universe accordingly to generate query with optimized joins.
Analyze report Query for Indexes. ( work with DBA for this )
1. Get the report SQL , check the where clause
2. Check if indexes are user properly in SQL and also they exist in database
3. Also, check if statistics in database is updated, as its its not then DB may not generate optimized query.
Use Aggregates for measures
1. Use Aggregate aware for your measure objects to use summary tables form Database\
2. You can also think of Automatic Query re-write instead of using Aggregate Aware in Universe designer, However this requires careful planning and high involvement from DBA
Use Partitions for high volume fact tables
1. Partitioning the fact table can boost your query performance.
2. Work with DBA to get it done
Array Fetch Size
1. Play with Universe options for setting up optimal value of Array fetch Size parameter.
Universe Analysis for Shortcut joins
1. Analyze your reports and universe for possible use of shortcut joins as they play little but important role in performance.
1. Try using Index Awareness from Universe side to generate optimized query.
Note: This requires thorough testing of report data and detail understating of data warehouse data
1. Make sure LOV is disabled measure objects
2. Make sure LOV is disable for un-necessary dimension objects
1. Try evaluating JOIN_BY_SQL universe parameter
I will try posting above Universe Tuning/Optimization practical examples one by one in coming time. Need to do few experiments . Business Objects Universe Optimization is not a one night task it requires careful planning and effort.
Difference between CMC,CMS and CCM
CMS = Central Management Service, a process running as part of your Business Objects Enterprise servers, including the CMS database, authenticating users, storing access rights, etc. The CMS is the heart of a Business Objects Enterprise system.
CMS maintains the CMS database (system database) and Audit database. It acts like an Auditor not “auditing database”.
The CMS (Central Management Server) is a service/daemon that manages the entire BOXI server deployment, authentication, object repository, services; scheduling etc.
We can say CMS keep the track of Security details, objects hierarchy, managing servers, user activity
CMC = Central Management Console, web based administration interface for your Business Objects Enterprise system, where one can add new users/groups, create folders, set access rights, configure SSO, configure your Business Objects Enterprise server services, etc.
The CMC is a web application interface for administrating the BOXI server.
Most server management tasks are now handled through the CMC, not in the CCM.
We can say CMS is web-based tool for handling day-to-day administrative tasks i.e. data (content) management ,server management (stopping the process ),user management
The CMC is used by SAP Business Objects Administrators to access and configure the SAP Business Objects BI system.
The CMC provides management of and configuration for the following system elements:
User and group creation and management
Folder and Category management
Server groups (clusters)
Universes and data connections
User interface settings and preferences
CCM = Central Configuration Manager, an application which allows you to configure/add/remove/stop Business Objects server services.
The CCM is a server troubleshooting and node configuration tool.
The CCM allows you to view and to modify server settings only while Business Objects server processes are offline. The CMC is used to stop server processes, then the CCM is used to modify performance settings or change server port numbers.
Let’s say that your CMS is down, what would you do? Can you login to CMC to start it? Of course you can’t. You need to use CCM.
2.We can open XIR2 universes using XIR3 Designer but XIR3 universe cannot be opened using earlier versions of designer.
Central Management Server (CMS)
The aptly named Central Management Server (CMS) is the main server in the BO XI collection.
The CMS maintains a database of information about your BusinessObjects Enterprise system. This is known as the CMS database. All the platform services are managed and controlled by the CMS.
The CMS handles communication with the RDBMS tables that store the metadata about the BO XI objects. Any commands issued by the SDK to the servers are communicated via the CMS.
CMS is also known as :
- In Application Foundation 6.x versions and earlier, BusinessObjects repository
- Before the rebranding effort, the Crystal Management Server,
- and before that as the Automated Process Scheduler (APS).
There are still a few active properties that are named for the old APS designation. One of these APS references is found in the ServerKind propertyof the Server class. The CMS ServerKind designation of the Central Management Server is still “aps”.
The CMS also manages :
- the auditing database
- all schedule and custom events.
The CMS can also maintain an optional auditing database of information about user actions, and files with the File Repository Servers.
File events alone are handled by the Event Server.
The CMS manages :
- security and controls authentication of all users
- as well as license management.
Because the Central Management Server is the principal server, it cannot be stopped from within the SAP BOBJ – Central Management Console (CMC). You must use the Central Configuration Manager.
In a production environment, it’s a good idea to disable all servers first so they can finish any pending requests before shutting them down, with the CMS being the last to close. If you’re working with a cluster, shutting down one CMS will shift the workload to the other active ones—a feature that allows maintenance without causing downtime.
The CMS also manages access to the system file store where the physical documents are managed.
CMS data includes information about :
- users and groups,
- security levels,
This data allows the CMS to perform four main tasks:
- Maintaining security
The CMS enforces the application of rights at both the folder and object level, and supports inheritance at the user and group level. It also supports aggregation through a flexible, group-user membership model. An integrated security system is available for customers who do not currently use an entitlement database, although BusinessObjects Enterprise is designed for integration with multiple concurrent third-party security systems, such as LDAP, Siteminder, or Microsoft Active Directory.
When a change is made to a user in the entitlement database, the change is then propogated to BusinessObjects Enterprise.
- Managing objects
The CMS keeps track of the object location and maintains the folder hierarchy. InfoObjects are system metadata objects that contain contain index information, and the actual documents or objects are stored in a file store. The separation of the object definition (metadata) from the document allows for fast object processing as only the required information is retrieved from the system’s repository. The CMS also runs scheduled report jobs.
- Managing servers
Server process are monitored by the CMS and allocates work to less busy processes. It will also add or remove service instances as work loads change or services become unavailable. The CMS handles load balancing and automated clustering to avoid bottlenecks and maximize hardware efficiency. In some multi-server environments, BusinessObjects Enterprise may not require a separate third-party load balancing system.
- Managing auditing
User actions can be monitored and written to a central audit database. This information allows system administrators to better track and manage their BusinessObjects Enterprise deployment. The auditing functionality allows administrators to better understand which users accessed the enterprise system, which documents they interacted with, and the overall system metrics for system optimization. Usage data is collected from the system interactions recorded in the auditing database. A sample universe and sample auditing reports are also available to provide fast access to information such as the most accessed reports, peak system use times, and average user session times.
It is strongly recommended that you back up and audit the CMS system database frequently.
The CMS database should not be accessed directly. System information should only be retrieved using the calls that are provided in the BusinessObjects Enterprise software development kit (SDK).
The Central Management Server (CMS) Repository
The content of the Business Objects Enterprise (BOE) system consists of the physical files and the metadata information about the physical files.
For a Crystal Report, the physical file as well as the metadata about the file should exist in the BOE system. The Crystal report is stored as a file on File Repository Server (FRS) with an extension of .rpt . The Metadata information such as report name, type, report ID, path, etc is stored as an InfoObject in the CMS Repository.
The CMS metadata is physically stored on a database as InfoObjects. There are six tables, the purpose of which is given below.
|Sno||Physical Table Name||Purpose|
|1||CMS_VersionInfo||Contains the current version of BOE.|
|2||CMS_InfoObjects6||Each row in this table stores a single InfoObject. This is the main table in the repository.|
|3||CMS_Aliases6||Maps the user alias(es) to the corresponding user ID. For example, a user may have both a Win NT alias and an LDAP alias. Regardless of the number of aliases a user may have, in the BI Platform each user has only one user ID. The map is stored in a separate table to enable fast logins.|
|4||CMS_IdNumbers6||The CMS uses this table to generate unique Object IDs and Type IDs. It has only two rows: an Object ID row and a Type ID row. The CMSs in a cluster use this table when generating unique ID numbers.|
|5||CMS_Relationships6||Relationship tables are used to store the relations between InfoObjects. Each row in the table stores one edge in the relation. For example, the relation between a Web Intelligence document and a Universe would be stored in a row in the WebI – Universe Relation table. Each relationship table has these columns: Parent Object ID, Child Object ID, Relationship InfoObject ID, member, version, ordinal, data.|
|6||CMS_LOCKS6||This is an auxiliary table of CMS_RELATIONS6.|
The CMS repository tables cannot be queried directly. Query builder is the tool to be used to retrieve Business Objects metadata information using the virtual tables such asci_systemobjects, ci_infoobjects and ci_appobjects.
Working with FRS Pruning and Tracing
Have you ever got the chance to see how a Crystal or WebI document or Instance stored internally in the File System? Here it is.
The document will be saved internally in the file system with in one or more folders named based on random name generation.
What will happen if the report or the Instance deleted?
The report or the Instance alone will be deleted and leaving the temporary folders as it is. As a result of this over the period of time, there will be thousands of folders in the FRS and will be a intricacy for the Administrator when he goes for the FRS Backup. The Backup process will be very time consuming as well as occupy more space and finally the FRS will be inefficient.
How to get rid of this?
The “-Prune” command, added at the end of the command line of the File repository servers will be handy at this moment.
Working with FRS Pruning and Tracing
-Prune command, added at the end of the command line of servers triggers the server to go through the ‘Input’ or ‘Output’ folders in the internal ‘Filestore’ folder of Business Objects Enterprise to clean up all the empty directories.
-Trace command, added at the end of the command lines of the servers logs the activity of that specific server in the ‘Logging’ folder of the BOE installation directory.
We need to periodically delete the empty FRS directories to cleanup the disk but not to be manually. Instead the FRS server should be started with the -Prune command line switch. When this switch is used, the FRS servers’ status will remain ‘Starting’ till the deletion is done. Once deletion is done, the servers will stop. The -Prune switch will have to be removed manually to allow the servers to start normally.
Working with FRS Pruning and Tracing
Add -Trace and -Prune
1. Stop File Servers (both IFRS and OFRS) in CCM (XIR2) or in CMC (XI 3.x).
2. Add -Prune command at the end of the line to FRS (Input and Output) and also –TraceCommand at the end of the line to check it is cleaning up files and folders that are empty.
3. Start the servers and Monitor the pruning process, you should gain more hard disk space.
Remove -Trace and -Prune
1. Stop the Servers and remove -trace and -prune commands from the command line Parameters of FRS.
2. Start the Servers again normally.
Viewing Log files
You can find the log files in the below location (for XI 3.x)
C:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\Logging
Points to remember
- After the Pruning process if any empty folders still exists, they may be used by BO to keep for its housekeeping process.
- Don’t leave the prune option enabled even after the prune completed. Once pruning completed successfully the FRS will be stopped. We have to re-modify the command line by removing –Prune and -Trace and Server has to be started manually.
- Pruning process does not clean-up any CMS object that lost the FRS files that they need to point to.
Please Note -Prune is an undocumented feature in Business Objects.
What is PRM File?
The PRM file is a text file that lists parameters used to configure universes creation and SQL query generation in Web Intelligence. There is a PRM file for each supported RDBMS.
PRM files are located in the database folders under
Verifying which PRM file is used by a connection
To verify which PRM file is used by a universe connection:
- Select File > Parameters.
The Parameters dialog box appears.
- Click the Test button.
The Test Connection message box appears.
- Click the Details button.
The details of your connection appear in a drop down message box.
- Scroll down the message box to the line that starts with PRM.
This line indicates the file path and name of the PRM file currently used by the active universe.
Universe Parameters / Data Source Connection
A connection is a named set of parameters that defines how a Business Objects application accesses data in a database file and is defined in an universes via universe designer.
Create a new connection
From the parameters dialog
You can create a new connection from the Definition page of the Universe Parameters dialog box (File > Parameters > Definition).
You can view all connections available to a universe from the Connections list (Tools > Connections).
A sstrategy is a script that automatically extracts structural information from a database or flat file. Strategies have two principle roles:
- Automatic join and cardinality detection (Join strategies)
- Automatic class, object, and join creation (Objects and Joins strategies)
Strategies can be useful if you want to automate the detection and creation of structures in your universe based on the SQL structures in the database.
In Designer you can specify two types of strategies:
|Built in strategy||Built in strategies can not be customized. Default strategy shipped with Designer.|
|External strategy||User defined script that contains the same type of information as a Built in strategy, but customized to optimize information retrieval from a database.|
Many of the parameters common to most supported RDBMS middleware are available for editing in the Parameters tab in the universe parameters dialog box (File > Parameters > Parameter).
These parameters apply only to the active universe, and are saved in the UNV file. When you modify an SQL parameter for a universe in Designer, the value defined in Designer is used, and not the value defined in the PRM file (parameters file) associated with the data access driver for the connection.
To know the list of available parameters, see the product guide xi3_designer.pdf page 88 section Universe SQL parameters reference.
The PRM file is a text file that lists parameters used to configure universe creation and SQL query generation in Web Intelligence. There is a PRM file for each supported RDBMS.
Connections through ODBC to Excel and text files
You can create connections through ODBC to Excel files, and to text files in .csv format. In order that Web Intelligence can use a universe based on a text file or an Excel file accessed through ODBC, you must edit the msjet.prm file for the connection.
This file is located in the folder: $INSTALLDIR$/BusinessObjects Enterprise 12.0/win32_x86/dataAccess/connectionserver/odbc where
where $INSTALLDIR$ is the directory in which your Business Objects applications are installed.
In the msjet.prm file, change the DB_TYPE parameter as follows:
- From: <Parameter Name=’DB_TYPE’>MS Jet Engine</Parameter>
- To: <Parameter Name=’DB_TYPE’>MS Jet</Parameter>
You must stop and restart the Business Objects Enterprise server after making this change.
If you are running Designer on the same machine as your Web Intelligence server and you want to create additional universes based on text or Excel files after changing this value, you must reset the value to <Parameter Name=’DB_TYPE’>MS Jet Engine</Parameter>
You can use an SQL editor to help you define the Select statement or a Where clause for an object. The SQL Editor is a graphical editor that lists tables, columns, objects, operators, and functions in tree views. You can double click any listed structure to insert it into the Select or Where boxes.
- number, character, and date functions.
- @Functions specific to Business Objects products.
Available functions are listed under the Functions entry in the parameters BOBJ – PRM File for the target database.
Show object SQL
When selected, the SQL syntax is displayed for the objects that appear in the Select, or Where boxes.
When clicked, parses the syntax. If the syntax is not valid, a message box appears describing the problem.
File Repository Servers (FRS)
nput and Output File Repository Server (FRS) processes run on each BusinessObjects Enterprise server machine.
The Input FRS manages report and program objects that have been published to the system by administrators or end users using :
- the SAP BOBJ – Publishing Wizard,
- the SAP BOBJ – Central Management Console (CMC),
- the Import Wizard,
- or a Business Objects designer component such as Crystal Reports, or SAP BO – Web Intelligence report panels).
Objects with associated files, such as text files, Microsoft Word files, or PDFs, are stored on the Input File Repository Server.
The Output FRS manages all of the report instances generated by the Report Job Server or the Web Intelligence Processing Server, and the program instances generated by the Program Job Server.
If you use the BusinessObjects Enterprise SDK, you can also publish reports from within your own code.
The FRSes are responsible for listing files on the server, querying for the size of a file, querying for the size of the entire file repository, adding files to the repository, and removing files from the repository.
To avoid conflicts between input and output objects, the Input and Output FRSes cannot share the same file system directory. In larger deployments, there may be multiple Input and Output FRSes. However, only one set is active at any given time. In this case, all Input File Repository Servers share the same directory. Likewise, all Output File Repository Servers share the same directory.