Universe Designer

Business Objects  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 Business Objects document.

It is an OLAP tool that high-level management can use as a part of a Decision Support Systems (DSS).

Business Objects makes it easy to access the data, because you work with it in business terms that are familiar to you, not technical database terms like SQL.

User Module, Designer, Supervisor, Auditor, Set Analyzer, Info View (Web Intelligence), Business Objects Software Development Kit (SDK), Broadcast Agent etc.

There are two types of access modes in which universe can be saved and accessed.

Enterprise Mode

When you are working in a design by connecting to repository means you are working in enterprise mode.

Workgroup Model

When you save universe for other users so that they can access it without connecting to repository is called as workgroup mode.

By default universe is saved in a mode in which you presently working.

Regardless of access mode you can save universe for all users so that they can access it. While saving universe for other users connection type must be “unsecured”

Steps to save universe for all users:

1. Make sure universe you want be available to all users does not have secured connection
2. Select File -> Save As
3. From Save As dialog , check on “Save for All Users”

These two modes define whether you are connected to repository or not while working with universe designer.

Online Mode: When you are connected to the repository while working with universe designer then it called as online mode

Offline Mode: When you access universe designer without connecting to repository is called as offline mode,

You can open the universe in offline mode only when that universe was previously opened in online mode.

You can access the database if you have access information and security information stored on local machine.

Offline mode comes pretty handy if you need to work when traveling, offsite etc.

Business Objects universe is semantic layer; it hides the data complexity from the business user by presenting underlying data base structure in the form of friendly business terminology by using objects and classes. It helps user query the database without knowing the complexity of database.

In order to create universe and reports you need to connect to the database where data is stored.

To connect to database you need to define the connection. Connection is a named set of parameters which contains details of how to connect to database.

Business Objects be default provides data access drivers of various popular databases also it supports popular connection type like ODBC, OLEDB. However it is recommended to use native drivers of database for data access.

Business Objects has three types of connection which can be used based on need.

1. Shared Connection
2. Secured Connection
3. Personal Connection

All the connection details are stored in LSI folder located at.

C:\Documents and Settings\<user name>\Application Data\Business Objects\Business Objects 12.0\lsi

Shared Connection

It’s an unsecured type of connection and generally used when multiple designers are working on universe.

The details of shared connection are stored in SDAC.LSI file.

Secured Connection

Secured connection plays a vital role in data access as these are the most secured data connection and used to distribute the universe.

1. Secured connection is used to centralize and control the access to data.
2. It’s the safes type of connection and must be used to prevent sensitive data.
3. Secured connections are must when you are distributing universe to end users.

Personal Connection

It’s a personal use connection and restricts the data access on the machine where universe and connection is created.

You can not use personal connection type while exporting universe to CMS.

Its details are stored in PDAC.LSI file

Business Objects Designer lets you set the universe options through universe parameters.

Following parameters options are available.

Definition:

In Tab we can provide the universe name, Description and choose the connection. If connection is not available you can create or edit the existing connection.

Summary:

This tab gives you information about the universe. It shows following information.

Created: The date universe was created

Modified: The last time universe was modified.

Revision: Number of times universe was exported

Comments: You can enter the informative text here.

Statistics: All the object level statistics

Strategies:

You can select the strategies to get the metadata of the universe. You can define the external strategies and select here to apply to current universe.

Controls:

Controls tab allows to set the various result set level parameters here.

Limit size of result set to: This allows limit the number of records to be returned by the query. It only limits the rows returned to the web intelligence. This limit does not limits the number of records returned by database

Limit execution time to: This limits the time query executed. It does not limit the actual time query executes on RDBMS. It does not stops the query if query executing on the database.

Warn if cost estimates exceeds: This parameter depends on if database statistics is updated.

SQL:

This tab allows to control the SQL generation.

Links:

This tab allows to specify the linked universes like Kernel universe and derived universe.

Parameters:

In this tab you can specify the parameters which are specific to the data access driver.

Note: Parameters set here are specific to universe. It does not affect other universe.

Following joins are available in universe designer

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. Self join is actually self restriction join.

Cardinality of self join should be set to 1:1 otherwise you might receive “Not all cardinality is set message” during integrity check.

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.

Example: Theta join

The Age_Group table below contains age range information that can be

used to analyze data on the age of customers.

You need to include this table in the universe, but there is no common column between the Customer table and the Age_Group table, so you cannot use an equi-join. You create a theta join using the operator “Between” for maximum age range and minimum age ranges. By using a theta join, you infer that a join exists where the value in a row of the Age column in the Customer table is between the values in a row for the Age_Min and Age_Max columns of the Age_Group table. The join is defined by the following expression:

Customer.age between Age_group.age_min and Age_group.age_max

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.

A shortcut join is a join that provides an alternative path between two tables. shortcut joins improve the performance of a query by not taking into account intermediate tables, and so shortening a normally longer join path. A common use of shortcut joins is to link a shared lookup table to another table further along a join path. The join path comprises several different tables in the same context. In such a case, the shortcut join is only effective when the value being looked up has been demoralized to lower levels in a hierarchy of tables, so the

Same value exists at all the levels being joined.

Example: Shortcut join

In the following example the column Article_code appears in both the tables Product_Promotion_Facts and Shop_Facts. The value of Article_code is the same for both tables. The normal path for a query using Article_code

from Product_Promotion_Facts and Shop_Facts, is to pass through the intermediary table Article_Lookup.

The shortcut join directly linking Product_Promotion_Facts and Shop_Facts allows the query to ignore the intermediary table Article_Lookup, optimizing the query.

Note: Designer does not consider shortcut joins during automatic loop and context detection. However, if you set the cardinality for a shortcut join you avoid receiving the message ‘Not all cardinality is set while detecting contexts.

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 cardinality in joins options in the Database tab of the Options dialog box, Designer detects and retrieves the cardinality of the joins. If you do not use this option, you can still retrieve the cardinality for one or all joins in the universe.

Setting up cardinality is very important as it lets you find the SQL trap problems as well as it helps in detecting the contexts.

Automatic cardinality detection tool fires three queries per join to detect the cardinality of a join. T could lead to serious performance problem if there are lots of table in universe which contains huge data.

e.g. Suppose I have a employee table and Department table. To detect its cardinality Business Objects will fire following three queries

select count(*) from calendar_year_lookup;

select count(*) from shop_facts;

select count(*) from calendar_year_lookup a, shop_facts b where a.week_id=b.week_id;

Table which contains higher number of records will be at many ends.

Also, Automatic join detection is totally depends on data in the table, if you are creation universe on empty database or dummy data , automatic join detection might not return correct result.

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.

e.g. 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.

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.

Objects can be of any type of amongst three.

· Dimension Objects

· Details Objects

· Measure Object

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

There are two levels of aggregation in the query process

1. Aggregation at SELECT level
2. Aggregation at projection level

Aggregation at SELECT level

Aggregation at SELECT level occurs first in query process

• User creates a query in Web Intelligence.
• Web Intelligence infers the SQL from the query and sends a Select statement to the target database.
• The data is returned to the micro cube. This is the first aggregation level.
• The micro cube projects the aggregated data onto the report. Data is split out in the Query pane requiring aggregation to lower levels. This is the second aggregation level.

Aggregation at projection level

When you run the query the result set of the Select statement is stored in the micro cube, and all data then held in the micro cube is projected into a block. As data is projected from the lowest level held in the micro cube no projection aggregation is taking place.

However, when you use the Query pane to project only partial data from the microcube, aggregation is required to show measure values at a higher level. For example, in the previous example, if you do not project the year data into the block, the three rows related to Year need to be reduced to one row

To show the overall Sales Revenue for that resort, so a sum aggregation is used.

When projecting all variable from the micro cube. No aggregation takes place. When projecting some variables from micro cube aggregation takes place.

You set projection aggregation on the Properties page of the Edit Properties sheet for a measure (right-click object > Object Properties > Properties). Projection aggregation is different from Select aggregation.

Setting selection and projection aggregates.

Statically only certain SELECT/projection aggregation is compatible.

For the report to present statistically correct data for a measure object both at a query level and projection level, SELECT and projection aggregates needs to be complement each other.

However, as Universe designer if you configure a measure differently, the BO end user querying tool will not stop you.

Note: For AVERAGE aggregate, projection aggregate would be set as SUM. You would need to change it to NONE manually.

Projection aggregates are those who take place when either dimension is moves on level above or below.

Following are the recommended projection functions.

You can set the projection aggregate at measure properties. Note the projection aggregate of average should be set to none.

Testing measure object is very important as it is dynamic in nature.

Following unit test can be performed to test measure object.

1. Create a report which includes at least two dimensions and a measure.
2. Test the measure object with at-least 3-4 different queries.
3. Try removing dimensions from the report to test the projection
4. Valid date output by firing actual SQL queries on database.

A delegated measure is a measure whose calculation as is done at database level means its delegated to database.

Delegated measure is useful when web intelligence does not provide correct result of measure in certain scenario like.

1. Non Additive measure like Unique users
2. Complex average like weighted average
3. Ratios

Benefits of Delegate Measures:

1. Increase web Intelligence querying efficiency
2. Makes Non-additive measures available in Universe.
3. It also helps in performance optimization as it performs calculation at database level.
4. It extends calculations beyond Web Intelligence.

How delegate measures work:

In normal scenario, web intelligence calculates measures based on dimensions objects in the query.

Delegate measures calculate measure for every subset of dimensions required in the report.

e.g. If you have report with

Country, Region, Year dimension and Sales Total and Average Sales Total , Normal measures will calculate these two measures for all the dimensions in the report however, If you have removed the any of the dimensions from grouping set , Web Intelligence does not no how to calculate correct average and might present wrong average value.

For above scenario in case of delegate measures, measure values will be calculated for all the dimensions set as

Country

Country, Region

Country, Region, Year

When report changes and user adds any new dimensions in the report web intelligence shows #TOREFRESH in place of measures indicating report to re-run to calculate measures correctly.

Best Practices

1. Use Delegate measures when web intelligence might return wrong output for measures like complex average.
2. Do not use delegate measures when standard measure works
3. Do Not use delegate measures when you have filter on measure and further aggregation on filtered measure value as web intelligence returns #UNAVAILABLE for such measures as it does not know how filter affects measure.

Loops occur when there are two different paths to accomplish one join. Such join forms a closed path of table relationship causing loops. When loops exist in universe it returns fewer records than expected.

The following structure now includes PRODUCT_PROMOTION_FACTS in the Test Fashion universe. If users want to analyze articles versus time, there are now two join paths. Business Objects does not know which path to take, the one via SHOP_FACTS or the one via PRODUCT_PROMOTION_FACTS.

The circular appearance of these four joins is a loop, which can give undesired SQL results.

One way of spotting the problem table in the loop is the table which has at one end of the one-to-many relationship.

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

Error: Incompatible combination of objects

There are two ways to solve loops: Using Context and Alias.
LOOPS
What is alias and it can be used to resolve Loops?

Alias is an alternative name given to table or any other object. Same technique can be used to break the loop in designer.

In our loop problem COUNTRY table is serving two purposes, resort country and customer country. We can break this loop by creating an alias table for country table to separate resort country and customer country as.

Country table will join to resort to become resort country

Country_Regioin alias will be joined to region to become customer country

Now if you create same report you will see

Country_region =Region.country_id

Country.country_id=Resort.country_id

Once you have created an alias, you would also need to redefine the object to use alias table. In above case origin country should use coutry_region.country column instead of country.country column.

Resolving Loops using Contexts

Context is another way to resolve loops in universe. Contexts resolves loop by defining a set of join that defines specific path through tables in a loop. It makes sure that join are not included from different path in the same query.

Generally contexts are used solve a loop caused by multipurpose look table.

A context is a group of path that defines a specific path for a query. Any objects created on a table column which belong to specific contexts is naturally compatible with all other objects from same contexts. When objects from two or more contexts are used, separate SQL is generated and results are then merged in a micro cube. This makes sure that no incorrect result is generated due loop or any other join path issue.

It breaks loop by defining set of joins that define path through table in a loop. It ensures that joins are not included from different path within the same SQL.

If you are using context make sure its testes properly. You should test using following scenarios.

1. 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.
2. Create a query which includes objects from both the contexts. BO should generate two queries and then unions it.
3. Create a query which includes object which are common two both the context. BO should prompt for contexts to be used.

Designer detects context by identifying table which has only many ends of joins attached. No joins following back from one to many are included.

Every Join except shortcut join must exist in at least one context.

Dis-advantage:

When you use a context, you expose the BO end user to the database structure. They are forced to decide which context they want to use to run their query. The role of the universe is to shield end users from the database structure, so they do not have to make such decisions.

How to identify contexts

1. Universe designer does have automatic detection tool for context. Make sure cardinality of all joins is set correctly for this.
2. Look for tables which are only at many end of relationship.

When context exist in universe. Designer can generate three types of queries.

· Ambiguous query

· Inferred query

· Incompatible Objects query.

Whenever you create a context you should check context against these three queries two test correctness of context.

Ambiguous query

If you have multiple contexts in universe, try creating a report which includes objects common to both the context. This situation does not give enough information on which context to use while generating the query and inturn it prompts all available contexts to user to choose from. Once user selects the context, query is generated accordingly.

Make sure Allow selection of multiple context option is selected from SQL tab of universe parameters as user might select multiple context in case of ambiguous query.

Incompatible Objects query.

If you have used object in a report which belong to multiple context and objects which are unique to each contexts. The tool creates multiple SELECT statements for each context and then result is merged in to present in single table form this is called as Incompatible Objects query.

Inferred query

Inferred query is query which gives enough information to tool to choose the right context without prompting to user.

When you have context in universe make sure you test the contexts by creating all three types of query and observe the behavior of tool in each query type.

Fan traps and Chasm traps are problems which are inherent in SQL that are caused by the order in which the elements of SELECT are processed.

In SQL a select statement processes SELECT, FROM and WHERE cause first and forms a result table in memory based on the tables specified in where clause and restrictions specified. However this does not cause a problem, but if aggregates are applied then it may cause a problem in particular circumstances.
These traps are difficult to identify unless you take a deeper look at the detailed data.

These traps return many rows than expected.

There are two types of trap which you might need to deal with.

• CHASM Trap
• Fan 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

1. There is many-to-one-to-many relationship between three tables
2. Reporting query have objects on tables from many end
3. 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

If I want to see number of future guest for a sports service, repot returns following result

However If I include moth the measure together in the same query.

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.

For “Number of Future Guest’ we would include additional dimension “Days Reserved” to see granular data.

Now lets combine the result

You could see, query returns every possible combination of future guests with number of guests and when result is aggregated it gives wrong umbers.

How to solve CHASM Trap?

You can solve CHASM trap using context. In above example you can create context.

1. Analyze many-to-one-many relationship to detect possible CHASM trap.
2. Use Detect Context to create contexts

1. Select the contexts and click on Add.
2. Select File->Parameters to launch universe parameter box.
3. Click SQL Tab
4. Select Multiple SQL for each contexts option.
5. Click OK

Now when you create query, two separate queries will be generated and result will be merged. 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.

Apart from CHASM trap, fan trap is another kind of trap that may occur in universe causing wrong results in reports. A fan trap can occur in universe when there is a one-to-many join between two tables which again joins to another table having one-to-many join relationship.

However having one-many-many relation in universe does not always cause fan trap. You get fan trap issue only when reporting query have

1. There is a measure object based on middle table – Table B
2. Measure object from last table. – Table C
3. Table B (Middle table) holds values which is aggregate of values from ,Table C

e.g. Consider following example.

It satisfies the first condition of Fan trap i.e 1-N-N relationship between three tables.

Now if user creates a query to see how many models were sold by customer Sham and show his sale quantity and sale total.

Query will include following object.

CustName, SaleTotal, ModelId, Sale Quanity

which would certainly give a wrong result as below due to existing fan trap issue.

If you could observe, Sham has sold total two models and its sale total is 100, however in above report it would come as 200 as for each instance of model sale total is repeated in report.

So what’s really happening in Fan Trap

When you run the query with following objects

CustName, SaleTotal and SaleQty, measures are correctly aggregated, However when also want to know modelid, you get saletotal for every model which makes result looks inflated.

There is now way can detect Fan Trap automatically, you would need to visually analyze the relationship between table and result in report.

How to solve Fan Trap

There are three ways to resolve fan trap.

1. Changing the universe parameters to generate different SQL for each measure.

This method works only for measure objects. Though it works to solve CHASM trap and Fan trap it’s not recommended.

1. Use combination of alias and context based on scenario in Fan traps.

Scenario 1

1. 1. When three tables are joined by 1-many relationship
   2. Report query contains dimension from first table and measure from rest of the two tables.

Solution

1. Create an alias for a middle table which is at many ends.

2. Create a join between new alias table and first table which is at one end.

3. Set the cardinality and set context

4. Change the SELECT of SALE_TOTAL object to refer to alias table.

`

Now if you create a create query by implementing above solution you would see two queries for each measure and in turn result gets merged at the report cube level. This should solve the fan trap.

Scenario 2

1. 1. When two tables are joined by 1-many relationship
   2. Report query contains dimension and measure from first table and measure from last table.

Solution

1. Create an alias for table A
2. Crate join from the alias Ax to table A and set cardinalities
3. Set context B and A
4. Edit object Y so that it refers to columns in the alias Ax instead of table A

How to Avoid Fan Trap

While designing a universe you can avoid the Fan trap altogether by creating measure from the table which is at the end table of 1-M-M relationship however it also depends on if you have those aggregate columns present in end table.

[/expanded]
How would you identify if there is a SQL trap in universe?

There is not automated way to detect SQL traps in universe. One needs to do very detailed analysis to identify possible traps in universe. You can use following techniques to identify traps in universe.

• Check and analyze all the one-to-many relationships
• Use “Detect Contexts” tool to detect possible contexts. This tool will analyze all N:1 join paths will show possible contexts.
• Analyze contexts. The table where contexts intersect is the source of possible trap.
• Check if you have any two tables which converge in to a single row. It could be a CHASM trap source.
• In the report if you are using any object from a tables which are converging into a single table. Check the result using database queries.

Object restriction is nothing but a condition applied on object to restrict the data. Whenever that object gets used in query, condition gets imposed automatically restricting the data.

Object restriction should be used with caution. As it might sometime confuse the user after seeing the data.

It has following drawbacks.

1. User can not override the objects restriction.
2. User might not know that there is default restriction on objects and might get confused after seeing a data.

So if user is not aware of restriction it might create confusion for user. So one should avoid putting restrictions at object level.

A condition objects or a predefined condition is restriction, created in designer which user can choose to apply or not.

When user creates a report query they can inert these restriction whenever they want to restrict the data which gives much more control to user to apply restriction than forced restriction.

Advantages:

1. User can choose the restrictions.
2. We can apply it on entire universe or a class.

So it’s good to use condition objects compared to object restrictions.

@Functions are available in Universe designer and are available in edit select box of an object.

Following @functions are available in Designer.

@Select()

@Variable()

@Where()

@Script

@Prompt

@Agreegate_Aware

Let learn them one by one.

@Prompt

This function is very useful to create an interactive object which would force user to enter some value for a condition in order to restrict the dataset to be returned by the query.

Prompt functions are generally used in where clause of an object to build interactive filter condition in the report.

The syntax of @prompt function is as below.

@Prompt(‘message’,'type’,[lov],mono/multi,free/constrained/primary_key,persistent/not_persistent, [{'default value':'default key'[,'default value':'defaultkey',...]})

Message: Is the text which would be prompted to user. It should be included in single quotes.

Type: Data type to be returned by function. It can be any one of following.

A: Alphanumeric

N: Number

D: Date

LOV: List of values which would be displayed to end user to chose from. LOV could be hardcode or you can use LOV of an exiting object.

e.g. {‘A’,’B’} or ‘Country\Name’

Mono: User can select only one value from list

Multi: user can select multiple values from list

Free: User can select or enter value.

Constrained: User ,ust select value from the list.

Primary Key: This options needs to be used with free or constrained. When used , user selected values are not used its key values is used from index awareness column.

Persistent: Last user selected value is persistent when report is refreshed.

Defaultvalue:keyvalue: Default values to be presented to user. If you have used Primary Key in function you must specify the key value here.

@prompt(‘Enter value(s) for Customer with A:’, ‘A’,'Customer\CustomerA’, Multi,primary_key,{‘A’:’1’,‘B’:’2’})

@Select

This function is used to reselect the select clause of another object. This function is pretty useful to reuse the existing select clause of another object instead of creating it again. So when original object select clause changes, all other objects would be changed if they are using original objects select clause using this function.

Syntax.

@Select(Classname\Objectname)

@Where

This function can be used to re-use the where clause of another object.

Syntax:

@Where(Classname\Objectname)

@Variable

The @Variable function is used to call the value assigned to variables. This function is generally used in security implementation. You can more information on this function on business objects user guide.

e.g. You variable (‘BOUSER’) will return the name of currently logged in business objects user

@Aggregate_Aware()

This function is used to define the aggregate awareness and will be discussed in another chapter.

@Script

This function is used to call a VBA macro and works only for desktop intelligence and designer and is not advised to use in case of web intelligence. It works only on windows platform.

@Script(‘var_name’, ‘vartype’, ‘script_name’)

Hierarchy is an ordered sequence of dimensions which is very helpful for multi-dimensional analysis of data.

e.g. time hierarchy.

Day->Week->Month->Quarter->Year

Multidimensional analysis enables user to see data from different perspective which uncovers the various pattern in data which would be very helpful in taking tactful business decision.

Hierarchies can be natural/Default or logical based on business rules.

Default Hierarchies/Natural Hierarchies:

A natural hierarchy is nothing but natural order of dimensions.

e.g. Time Hierarchy

Day->Week->Month->Quarter->Year

Geographic Location Hierarchy.

City->State->Country->Region

Logical Hierarchies:

Logical hierarchies are set of dimensions whose order is set based on business domain of analysis.

e.g. Hierarchy of designation.

CEO->CTO->Director->Manager

There are two types of hierarchies in Business Objects Universe.

Default Hierarchies

By default hierarchy is set based on order in which dimensions are placed in the class.

e.g In store class dimensions are arranged as State->City->Store Name. So by default hierarchy would be set as from top dimension to lower dimension.

Custom Hierarchies:

Business Objects allows creating logical hierarchies using custom hierarchy. In custom hierarchy the sequence of dimensions is defined by developer based on business need of analysis.

End user might want to drill down the revenue generated based on employee designation. You can define the sequence of dimensions based on business need.

If two or more hierarchies starts with same dimensions but follow different dimensions at lower end and if user performs drill-down on dimensions from such hierarchy then Web Intelligence asks user to select the drill path.

e.g. If we create two custom hierarchy which starts with Year but follow different path.

Then it asks user to select the drill path as there two drill path defined from Year.

Generally, almost every business wants to analysis the data based on time e.g. Year, Month, Quarter

In data warehouse time analysis is done by associating a date dimensions however there might be cases where date dimensions are not available. IN such cases Business Objects gives an facility to create time hierarchy based on Date.

e.g. We have revenue table which has date in it however we want to analyze the revenue based on year, month.

Advantages of Automatic Time Hierarchy:

1. It’s the fastest way to create time hierarchy
2. Business Objects uses database scalar function automatically and uses it in SQL Select statement.

Dis-advantages of Automatic Time Hierarchy:

1. Developer can not control the appearance of object.
2. Developer can not edit the object definition of automatic time hierarchy.
3. LOV can be applied to original Date object not the derived objects like Year.

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

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>
[/expanded]
Is it a good idea to associate LOV with each object and why/why not?

Business Objects first select distinct query on database to create LOV. So LOV is expensive in terms of performance also, LOV is useful only when object is used in prompts where use needs to select some values. So we should associate LOV with only those object which are used in prompts.

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 various expressions, 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.

e.g.

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 then Business Objects creates two queries one for each measure. Now some time this may result in performance issues. You can avoid this by creating a lookup table for different fact tables using derived tables.

e.g.

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 of derived table.

Since derived table is not an actual table you may face performance issues if underlying SQL query has performance issues.

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. Also Business 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.

Making universe index aware means telling universe on which columns indexes are created. This helps Business Objects to generate efficient query which uses indexes instead of actual column values which help to speed up the data retrieval.

e.g. If we have customer name in the query its useful to end user of report however we can use customer id to retrieve the data by making the use of index awareness which would help to fetch data faser.

You can define two types of index awareness in universe.

Primary Key: Using primary key index awareness universe can use index value instead of actual value of column. The query will thus use the key value. This helps database to fetch data faster.

Foreign Key: Using foreign key index awareness on object universe can filter the data without the need of join in query. Suppose you need to build a report which has filter on dimensions table. In absence of index awareness designer will use actual dimension values which requires join between fact table and dimension table. However if we had foreign key index awareness applied. Designer can apply filter directly in fact table using foreign key index. So this avoids join between dimension table and fact table. However foreign key index awareness requires dimension column values to be unique if same value is represented by different key. Then this may return unwanted result.

Suppose you are building a report on Island Universe “Service wide Sale revenue.”, After you drag drop the object, You will see following query generated.

Now if you see in the query, designer has joined Outlet_Lookup table to put restrictions on the states and using actual values from service table to filter the data.

Now Suppose we apply index awareness on service object defining primary keys and foreign keys.

The query will look as below.

If you could see in the query, Dimension values have been replaced with actual foreign keys filtering data on fact table. Which is an efficient way to filter data? So query will run faster compared to earlier way.

However remember, index awareness might return wrong result if you have dimension value which have two different keys in dimension table.

e.g.

If we have data as below in service dimension

Now if you have index awareness applied on service object and using service as a filter in report. Since ‘Activities’ have two keys, Business Objects does not know this might put any of the PK as filter returning wrong data in report

While defining the index awareness for particular column you can also define the data restriction for the object using WHARE clause in index awareness. It’s very useful to restrict the data in index awareness.

e.g. for service object I can define service price WHERE clause as below.

Index awareness when used with proper data analysis can give you significant performance gains.

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.

The core universe is a universe to which other universes are linked. It contains

components that are common to the other universes linking to it. These

universes are called derived universes.The core universe represents a

re-usable library of components.

A core universe can be a kernel or master universe depending on the way the core universe components are used in the derived universes.

A derived universe is a universe that contains a link to a core universe. The

link allows the derived universe to share common components of the core

universe:

• If the linked core universe is a kernal universe, then components can be

added to the derived universe.

• If the linked core universe is a master universe, then the derived universe

contains all the core universe components. Classes and objects are not added to the derived universe. They can be hidden in the derived universe depending on the user needs of the target audience.

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 link all 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.
[/expanded]
What is the difference between linking and including?

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.

Linking.

· 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

Including

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

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 duplicate objects/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.

Once you are done with development of universe there might be requirement to restrict the universe access to particular user or user group. You can achieve this by applying various access restrictions in universe.

Universe has various types of access restriction as explained below.

Connection: Using this access restriction you can define which connection is accessible to which user group.

SQL Controls: Using the restriction type one can define result set size and query execution time.

SQL: Using this restriction you control the SQL generation.

Objects: Using this restriction you can define which objects will be not be accessible to which users/group.

Row: Using the restriction type you can define row level access restriction. It uses the WHERE clause to restrict the data access.

Table Mapping: Using the restrictions you can define alternate tables for particular group of user.

Designer allows you to distribute universes by importing and exporting universes to the Central Management System (CMS) repository. You can use any of the following method to deploy universes.

1. Directly export the universe to target repository if you have access to it from designer.
2. Use IMPORT wizard to import and create a BIAR to deploy universe and report on another Business Objects environment.
3. Use Lifecycle Manager to promote universe and report to different environment.

Business Objects provided creating multilingual universe using Translation manager. Using translation manager you can define the names of objects and classes based on your language requirement. When these universes are access by uses. Business Objects will display the names of classes/objects based users Locale preference.