Web Intelligence Related Questions

1) How does Web Intelligence work?
Web Intelligence provides business users an easy to use interactive and flexible user interface for building and analyzing reports on corporate data over the web, on secured intranets and extranets. The Web Intelligence software is installed by your administrator on a web server on your corporate network. To use Web Intelligence from you local computer, you log into the business intelligence portal InfoView via your Internet browser. Then, depending on your security profile, you can interact with the reports in corporate documents or edit or build your own documents using a Web Intelligence report panel or query panel.

2) What are different tools used to create or edit Web Intelligence reports?
You can create or edit Web Intelligence documents using one of several tools:
• Java Report Panel
• Query – HTML
• HTML Report Panel

3) How is Web Intelligence Java Report Panel designed?
The Java Report Panel is designed for users who need more flexibility with designing report layout and defining formulas and variables. A graphical Formula Editor enables you to build formulas rapidly using drag-and-drop.

Note: The Web Intelligence Java Report Panel is available if your administrator has deployed Web Intelligence in ASP mode and if your administrator has deployed Web Intelligence in JSP mode.

4) How is Web Intelligence Query- HTML designed?
Designed for users requiring a pure HTML environment to build data providers, Web Intelligence Query – HTML offers the ability to define the data content of documents on multiple data sources. You can use Query – HTML to create new documents from scratch or edit the data providers in documents created using any of the other Web Intelligence tools.

Used together with On-Report Analysis, Query – HTML provides a complete solution for building data providers and designing powerful reports in a pure HTML environment. Once you have run the data providers to generate a standard report, you can leverage Web Intelligence On-Report Analysis features to format multiple reports, add formulas, and create variables.
Note: Web Intelligence Query – HTML and On-Report Analysis in Interactive view format are only available, if your administrator has deployed Web Intelligence in JSP mode.

5) How is Web Intelligence HTML Report Panel?

Designed for users who need to build basic reports, the HTML Report Panel provides query and report features in a simple wizard-like interface. Each document is based on a single data source and can contain multiple reports, displaying different subsets of information. In addition, the HTML Report Panel is 508 compliant and can be customized for specialized deployments.

Note: The Web Intelligence HTML Report Panel is only available, if your administrator has deployed Web Intelligence in JSP mode.

6) What information do you need before logging into Infoview?
Before you can use InfoView and Web Intelligence you need the following information:
• a URL to the InfoView server
• the InfoView server name and port number
• your login and password
• your authentication, which controls the InfoView resources available to you

7) What are the two ways in which data in the Web Intelligence document is edited?
There are two ways to open a Web Intelligence document in edit mode:
• view the document first and then switch to Edit mode
• switch to edit mode directly without viewing the document contents first

8) What are data providers?
A data provider contains one or more queries that return data from a database. A query requests data from the database. If the data is available, then the requested data is returned by default in the form of a table which contains rows and columns. When you build a query, you are creating a request for information from a database. A request can be very simple, for example; give me total sales in California for the first quarter of last year, or more complicated, for example; give me an average age of customers who bought sweaters during the spring television promotion in Paris.
Queries are sent to the databases in a language called SQL (Structured Query Language). However, when you use Web Intelligence you do not have to know any SQL. The Web Intelligence report panel presents the information available in the database as objects that have names and meanings familiar to you. These objects are organized in a structure called a universe.
You build data providers by combining objects in a universe. The universe translates the objects presented in your business language to SQL, and then sends the request for information to the database. Web Intelligence can generate SQL data providers of unlimited length. When the data is returned to the Web Intelligence report panel, it is presented in a table form, with columns that have the same names as the objects that you used in the query. The data is arranged in rows.

9) What is scope of analysis?
The scope of analysis for a query is extra data that you can retrieve from the database to give more details on the data returned by each of the objects in a query. This extra data does not appear in the initial result document, but it remains available in the data cube, so you can pull this data in to the report to allow you to access more detail at any time. This process of refining the data to lower levels of detail is called drilling down on an object.
In the universe, the scope of analysis corresponds to the hierarchical levels below the object selected for a query. For example, a scope of analysis of one level down for the object Year, would include the object Quarter, which appears immediately under Year.
You can set this level when you build a query. It allows objects lower down the hierarchy to be included in the query, without them appearing in the Results Objects pane. The hierarchies in a universe allow you to choose your scope of analysis, and correspondingly the level of drill available.
You can also create a custom scope of analysis by selecting specific dimensions to the Scope of Analysis pane.
Note: You cannot define a scope of analysis when working in Query Drill mode. Query Drill defines the scope automatically in response to drill actions.

10) What is the functionality of cube?
The scope of analysis for a query is extra data that you can retrieve from the database to give more details on the data returned by each of the objects in a query. This extra data does not appear in the initial result document, but it remains available in the data cube, so you can pull this data in to the report to allow you to access more detail at any time. This process of refining the data to lower levels of detail is called drilling down on an object.
When you run the query, the dimensions included in the scope of analysis are returned to the cube for that document, but are not projected onto the reports that the document contains. They can be added to the reports at any time, without having to run the query again. Values for the dimensions in the scope of analysis can also be viewed by switching a report to Drill mode, and then drilling down to them from the values displayed on the report.

11) What is an ambiguous query?
An ambiguous query is a query that contains one or more objects that can potentially return two different types of information. In a universe, certain dimensions may have values that are used for two different purposes in the database. For example, the [Country] dimension in the query below can return two types of information:
• Customers and the country in which they spent their vacation.
• Customers and the country for which they have made their reservation.
The role that Country plays in this query is ambiguous. A country value can be either the country where a vacation was sold, or a country where a vacation is reserved. One is existing information (sales), and the other is future information (reservations). To avoid ambiguities in a query, the universe designer identifies the different ways that objects can be used in the universe, and implements restrictions on how these objects can be combined. These restrictions are called contexts.

12) What is a context?
A context is a defined group of objects that share a common business purpose. This business purpose is usually the type of information that these related objects represent. For example, a sales context is a grouping of all the objects that can be used to create sales data providers. A reservations context is a grouping of all the objects that can be used in reservation data providers. Contexts are defined in a universe by the universe designer.
You can combine any object within the same context to create a query. You can also combine objects in different contexts. If you use an object that is common to both contexts, Web Intelligence will try to determine the context that best fits the other objects in the query.

13) What are incompatible objects?
Sometimes it is not possible to use certain combinations of objects in data providers. This situation arises when objects bear no relationship to one another. These objects are called incompatible objects.
For example, the Island Resorts Marketing universe contains the [Reservation Year] and [Revenue] objects, which are incompatible. This is because there is no revenue associated with a reservation. Revenue is generated only when the customer is invoiced. The underlying database structure reflects this; you cannot build a query that aggregates revenue by reservation year because there is no such thing as revenue by reservation year. In other words, the aggregation context that you specified for the
[Revenue] object does not exist.

When you build a query, Web Intelligence generates SQL behind the scenes. This SQL is run against the database to produce a result that Web Intelligence displays in a report. For a query to be free of incompatible objects, Web Intelligence must be able to generate a single SQL query to retrieve the data. If this is not possible, the query contains incompatible objects.

14) How can queries be combined and what is the use?
You can combine queries in three relationships:
• union
• intersection
• minus
In a union combination, Web Intelligence takes the all the data from both queries, eliminates duplicate rows, and builds a combined data set.
In an intersection combination, Web Intelligence returns the data that is common to both queries.
In a minus combination, Web Intelligence returns the data in the first query that does not appear in the second.

15) What is purging?
When you purge data from a document you remove all data from the document, while leaving the document structure intact. If the document contains multiple data providers, you can purge specific data providers within the document.

16) What are the different table templates used in Web Intelligence?
The different table templates you can use to display information on Web Intelligence reports:
• Vertical tables: Vertical tables display header cells at the top of the table and the
corresponding data in columns.
• Vorizontal tables: Horizontal tables display header cells at the left of the table and the corresponding data in rows.
• Crosstabs: Crosstabs are useful for presenting results that correspond to the intersection of two or more dimensions.
• Forms: Forms are useful in your report if you want to display detailed information per customer, product, or partner. For example, a form is a useful way of
displaying individual customer records with information such as the customer account, name, address, and so on

Restriction Sets

Universe can be secured in two ways:

1) Restrict access to entire universe by setting universe rights in the Central Management Console(CMC)

2) Create various forced and optional restrictions within designer
-> Forced
- Object restrictions
- Self-restricting joins
- Inferring multiple tables
-> Optional
- Condition Objects

Purpose of Restriction sets in Business

Business requirement to secure business critical data based on a user's role in the organization.

Restriction Set:
-> A restriction set is a named group fo restrictions that apply to a universe
-> Restriction sets can be applied to Business Objects users and groups to force behavior changes in a universe
-> Restriction sets are managed using the Business Objects Universe Designer application, using a feature named access restrictions

Manage Access Restrictions in Designer
1) Create new restriction
2) Add appropriate groups and users (add priority)
3) Map restrictions to group and users

Tips:

-> Export the universe before you apply restriction sets
-> Although most changes become effective when a universe is exported, remember that restrictions take effect as soon as they are applied

List of Values (LOVs)

                                                             Using a List of Values 

A list of values is a list that contains the data values associated with an object. A list of values can contain data from two types of data source:

1) Database file: When you create an object, Designer automatically associates a list of values with the object. The list of values is not created until a user, or you the designer, choose to display

a list of values for the object in the Query pane. A SELECT DISTINCT query is then run against the column or columns inferred by the object. The returned data is stored in a file with a.LOV extension in the universe sub folder created under the same folder that stores the universe file.The.LOV file is then used as the source for values for the list.

2) External file: Personal data, for example a text file, or an Excel file can be associated with a list of values. A list of values that is based on an external file is fixed. You cannot have a dynamic link with an external file. You must refresh the.LOV file if your external file has changed.

                              How is a list of values used in Web Intelligence? 

In Web Intelligence, a user can create a query in the Query pane using the operand “Show list of values” to apply to an object when applying a condition.

Note:  A.LOV file is also created whenever any condition is applied to an object in the Query pane that requires a restriction on the column values inferred by the object.

A.LOV file is also created whenever any condition is applied to an object in the Query pane that requires a restriction on the column values inferred by the object.

The List of Values for an object appears showing values available for the object, allowing the user to choose the terms for the condition. The first time a list of values is used, it is saved as a.LOV file in the universe sub folder on the file system. This allows the SELECT DISTINCT query to be run only once for an object. This folder also stores the.LOV files created in Designer which are used to restrict the list of values returned for objects for which the designer wants to control access to the data.

                     How List of values are used with an object 

When you create a dimension or detail object in Designer, it is automatically assigned an associated list of values. This list does not physically exist when you create an object, but by default, the object has the ability to query the database to return a list of its values when used in the Query pane.

Note: No default list of values is assigned to measure objects. When a condition is first placed on an object in the Query pane that requires a list of values to be displayed in Designer, a SELECT DISTINCT statement is run against the appropriate columns inferred by the object, and the list of values is returned.

A.LOV file is automatically created in the universe subfolder to hold the list values. The next time that the list of values is required for the object in Designer, the values are returned from the.LOV file and not from the database.

The designer’s role in controlling lists of values 

As the universe designer, you can define how the data is presented in the list, and define restrictions on the amount and type of data returned to the list. You can set the properties for an object to determine the following actions for a list of values:

• If a list of values is associated with an object.

• When the list is refreshed.

• Define a query that sets conditions on the SELECT DISTINCT query that an object uses to return a list of values. You save this query in the properties of an object.

• Display list values either as a simple list, or as an object hierarchy.

• If the list is based on column values, or values from an external file, for example an Excel spreadsheet.

You can also create a permanent list for values for an object and export this list to the repository. This.LOV file is then always used as the list of values for that object. It is not updated.

List of values properties and options 

Associate a List of Values:  

• When selected, allows a list of values to be associated with the object. It is selected by default.

• When cleared, no list of values is associated with the object.

• Selected by default for dimensions and details. Not selected for measures.

List name: Name of the.LOV file that stores the returned list data. Limited to 8 characters.

Allow users to edit this List of Values: 

• When selected, users can edit the list of values file in Web Intelligence.

• When cleared, the user cannot edit the list.

Note: This does not apply to personal data files such as Excel spreadsheets. These are not exported to the repository. They remain on a local machine. A user can edit a local file, or change the target list of values for another local data file.

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, the list data is refreshed each time the list of values for an object is displayed in the Query pane. This can have an effect on performance each time the .LOV is refreshed. This option does not apply to Web Intelligence reports.

• When cleared, the list is refreshed only once at the start of a user logon session.

      -> If the list contains values that regularly change, then you can select this option, but you                  should take into account the effect on performance.

      -> If the list contents are stable, then you should clear this option.

Export with universe:  

• When selected, the.LOV file associated with the object is exported with the universe to the repository. The universe domain and document domain must exist on the same data account. A list of values is stored in the document domain. The document domain does not have to be visible to the a user’s profile in Supervisor.

• You must create the list of values that is associated with the object for it to be exported. This list is saved as a.LOV file.

• When cleared, a.LOV file for the object is not exported to the repository. Select this option if you customize this list regularly. This allows your modifications to be exported and imported with the universe.

Exporting List of Values 

You can export a list of values with the universe to the CMS. On the file system, the associated .LOV file is copied to a universe sub directory in the same folder that stores the universe file.

When a user runs a query in Web Intelligence using an object that is associated with a .LOV file exported from Designer, the list of values that is returned for the object is determined by one of the following:

• The data contained in the .LOV file.

• The SQL for the SELECT DISTINCT query defined in the .LOV file. 

If you have created a condition in Designer to restrict the data values returned for an object, the restricted list appears, and not the default list of all the data values. The list retains all conditions and formatting implemented in Designer.

If you had not exported the .LOV file with the universe, then the object would simply return the default list with no conditions and formatting. A default .LOV file would then be created to hold the data.

Administering lists of values in the universe 

 You can manage all the lists of values in the active universe from the Lists of Values dialog box (Tools > Lists of Values). All the classes and objects are presented in a tree view. You can select any object, and access its list of values. You can perform the following actions from the Lists of Values dialog box:

-> Edit: Displays the Query pane used to define a query for the selected

object. You can define and edit existing queries for a list of

values.

-> Display: Displays the current list of values for the selected object.

-> Purge: Clears the contents of the list of values currently assigned to the selected object.

-> Refresh: Refreshes the display of the list of values.

Performance Tuning

First, Let us look into the question what are Performance Issues in a report?

 1) Reports are running extremely slow and getting timed out

2) BO Report has significant slow response time

3) Performance of the BO Report displaying aggregated or summarized data is extremely sloe

4) BO report is taking more processing time and still displaying partial data

5) A list of values request is taking more than fifteen minutes to return

Second, Let us look into the options of how to we tune the performance of the reports

BO reports can be optimized at 4 levels:

a) Universe level

b) Report level

c) Database level

d) Server level

                                Level 1 optimization - Universe level

-> Modify Array Fetch parameter

-> Allocate weight to each table

-> Use shortcut joins

-> Use aggreagte functions

-> Use aggregate tables

-> Minimize usage of the derived tables

Modify Array Fetch parameter:  The Array fetch parameter sets the maximum number of rows that are permitted in a FETCH proedure. For example, of the Array Fetch size is 20, and total rows are 100, then five fetches will be executed to retrieve the data, which will consume more time in comparison with one fetch.

Resolution: If network allows sending large arrays, then set Array fetch parameter to new larger value. This speed up the FETCH procedure, and reduce query processing time.

Allocating table weights: Table weight is a measure of how many rows there are in a table. Lighter tables have less rows than heavier tables. By default BusinessObjects sorts the tables from the lighter to the heavier tables. The order in which tables are sorted at the database level depends on your database. For example, Sybase uses the same order as BusinessObjects, but Oracle uses the opposite order. The SQL will be optimized for most databases, but not for Oracle where the smallest table is put first in the sort order. So, if you are using an Oracle database, you can optimize the SQL by reversing the order that BusinessObjects sorts the tables. To do this you must change a parameter in the relevant PRM file of the database.

Resolution: Business Objects settings, the ORACLE PRM file must be modified as below:

• Browse to directory Business Objects\BusinessObjects Enterprise

                  6\dataAccess\RDBMS\connectionServer\oracle.

• Open ORACLE.PRM file, change the REVERSE_TABLE_WEIGHT

                 value to N from Y.

Using Shortcut joins: Numbers of tables in join are more, even when selected objects are less. Even when no object of related table is selected, then also that table is appearing in the join condition. For e.g., If A_id object from A table of C table is selected with B table in between, then BO generated SQL shows that intermediate table ‘B’ table was present in ‘From’ clause.

Resolution: Shortcut joins allow users to skip intermediate tables and allow alternative paths between tables. Use of shortcut join reduces the number of tables used in query to improve SQL performance. Results in query performance improving from 1.5 minute to 30 seconds!!

Use aggregate functions:   Data is aggregated on the subject of analysis (user selected criteria) at report level. This takes more processing time, as data from database is loaded in temporary

memory and then aggregated or processed to display.

Resolution: Use aggregate functions (e.g., sum, count, min, max) in measure objects at universe

level. Aggregate functions will aggregate the data at database level rather than at report level which will save on processing time at report level and also reduce the number of rows returned back to report. 

Creating and using aggregate tables:  Aggregate data are obtained by scanning and summarizing all of the records in the fact table at real-time which consumes more time.

Resolution: Aggregate tables contain pre-calculated aggregated data. Using aggregate tables

instead of detail tables enhances the performance of SQL transactions and speeds up query execution. Aggregate_Awareness function has ability to dynamically re-write SQL to the level of granularity needed to answer a business question. Aggregate tables allow for faster querying speed and increases query performance manifolds!!

Minimize usage of derived tables:  Since derived tables are evaluated and executed at runtime, SQL tuning is not possible. 

Resolution:  Minimize the usage of derived tabled and replace them with tables or materialized

view. SQL tuning techniques such as creating index can be applied on tables or materialized views which will improve performance of BO reports.

Level 2 optimization - Report level

-> Opt for Refresh At- Will over Refresh-On-Open

-> List of Values (LOV's)

-> Conditional Objects

-> Complex Calcualtion in ETL

-> Minimize usage of Report variables/formulas

Opt for Refresh At- Will over Refresh-On-Open: Refresh-on-open reports refresh new data each time it is opened. Connection with database is established each time report is refreshed which in turn slows the report performance.

Resolution:  If report is based on snapshot data and static, it is better to publish report without

refresh-on-open property. Users will thus view the same instance of report without establishing database connection, which will reduce the response time of BO report.

List of Values (LOV's): When we create LOV object, distinct values are selected into it. DISTINCT forces an internal sort/compare on the table. Selecting a distinct list on large table is not optimal e.g., selecting a distinct list of custom_store against t_curr_tran_daily table is not

optimal.

Resolution: 

a. Re-map the object list of values to smaller look up tables.

b. If there are no smaller lookup tables, then create external file as a source to LOV. This file needs to be exported along with universe and be available to all users, which is additional overhead. Usage of external file replaces the need of lookup table and delivers high performance and weighs down the overhead cost

c. Avoid creating LOV on dates and measures. Disassociate LOV from all such objects which are not display as prompts.

Universe Condition Objects: The entire data from database is fetched (<=maximum rows setting) and the filters are applied at the report level. As data is not restricted at the database or universe level, the reports takes more time to execute.

Resolution:

When handling huge data, one of the following steps can be taken to limit data:

1. Use prompts to restrict data selection at universe level. Preferably use time period prompts in reports.

2. Replace report filters with Universe condition objects, if possible. Usage of conditional objects will limit rows returned at database level.

Complex Calculations: The data from database is fetched and then calculations are applied to that data. As calculations are performed at universe or report level on huge data, reports takes more time to execute.

Resolution: When dealing with huge data warehouses perform complex calculations at ETL level.

Thus Business Objects saves time on calculations and deliver high performance.

Minimize usage of Report variables/formulas: If the report is pulling tons of data, doing loads of joins, making lot of clever calculations, using lot of report variables and formulas, report may run very slow. Report variables and formulas are loaded and calculated in memory at real time. As variables are created at real time and calculations are performed at report level, reports takes more time to execute.

Resolution:  When dealing with big reports, minimize usage of report variables/formulas and try to place them at universe to deliver high performance reports.

Level 3 optimization - Database level

Examine the execution plan of SQL: Determine the execution plan of BO generated SQL in target database. EXPLAIN PLAN is a handy tool for estimating resource requirements in advance. It displays execution plans chosen by Oracle optimizer without executing it and gives an insight on how to make improvements at database level. 

Level 4 optimization - Server level

If the performance of system deteriorates when reports are accessed by larger number

of users over web, then fix the problem at fourth level i.e., server level (Level 4).

-> Scalable System

-> Event Based Scheduling

-> Report Server/Job Server closer to database server

-> Maximum Allowed Size of Cache

-> Minutes Before an Idle Connection is closed

-> File Polling Interval in Seconds

-> Maximum Number of Dwonloaded Documents to Cache

-> Oldest On-demand Data given to a Client