1. How would you improve the performance of the reports.

Performance of the reports starts with analyzing the problem.The problem could be with database, Universe or the report itself

Analyzing the database-Run the SQL from the report on an oracle client like SQL Navigator or toad after passing in all the parameters.-Identify if the SQL takes considerable less time than the report. If yes then the problem is with the Universe or with the report if no then-Run an explain plan on the SQL-Look to see if all the statistics are computed, indexes are built and the indexes are used-Check to see if aggregate tables can be used ( Aggregate tables are useful if the data can be concised to 1/10th of fact data)-Check to see if data has increased and usage of materialized views could help.

oCreating materialized views enable to pre -run the complex joins and store the data.oMost of the DW environment has a day old data hence they don’t have lot of overhead.oRunning a report against a single materialized table is always faster then running against multiple tables with complex joins.oIndexes can be created on this materialized view to further increase the performance.-Check to see if the performance of the SQL can be increased by using hints ,if yes then add a hint to the report SQL and freeze the SQL, this might have an additional overhead of maintaining the report

Analyzing the Universe

- Check is all the outer joins are properly created and remove unnecessary outer join

- Business Objects as such do not use Multi Pass SQL , Multi pass SQL is a technique a software use to break down a complex SQL into multiple smaller SQLs. Hence a query using one fact table and three dimension tables can be broken down into two, one between the dimension tables and the second between the first result and the fact table. This can be achieved in BO by creating Derived Tables. The derived table would be based on three dimension tables and the reports hence can use one derived table and one fact table instead of four tables.

- The Keys tab allows you to define index awareness for an object. Index awareness is the ability to take advantage of the indexes on key columns to speed data retrieval.

o In a typical data warehousing environment surrogate keys are used as primary keys instead of natural keys , this primary key may not be meaningful to the end user but Designer can take advantage of the indexes on key columns to speed data retrieval.o The only disadvantage is it would not return duplicate data unless the duplicate data has separate keys

- Check to see if the size of the universe has increased recently

oTry to create a different universe for new requirementsoUnder extreme conditions the AUTOPARSE parameter in the param file can be turned off, this could be too risky if not handled properly.

Analyzing the Report-Check to see if there are any conditions which could be pushed into universe as Filters-Check to see if a formula has multiple usage ,turn this to a variable

-Check if there are any variables which are not used, remove them.-Remove any additional filters or alerters on the report.

Read more: http://www.placementpapers.us/business_objects/309-business_objects_interview_questions_answers.html#ixzz1Bxxj2yxi Under Creative Commons License: Attribution

3. Complex ReportsTV BOM is a complex report with lots of alerters, filters where the user wants to see both filtered and unfiltered grand totals

4. Difference between filters and Condition and which is betterFilters are conditions designed at Universe level and Conditions are defined at Report level.Filters are better since it filters the data at database level making the report to run fast.

5. Difference between Formula and a variable and their advantagesWhen a formula is assigned a name it becomes a variable. Formulas should always be converted to variables if they are used more than once in a report

6. Can u use multiple database in a universeNo We cannot use multiple data providers in a universe .

7. What are shortcut joinsA 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.

8. How do u migrate/promote universe and reports from dev to test environmentsBusiness Objects as such does not have any tool for version controlling, hence a detailed document is maintained about the changes done in each release.QMF is a tool used for version controlled promotions. It has its own repository, before promoting the new universe/report it backs up the old one in the repository9. Problems from migrating from 5x to 6x

10. How do you restrict data based on user profilesRow Level security in Supervisor can be used to restrict data.

11. What are fan traps and Chasm trapsMany to one joins from two fact tables converge on a single lookup table. This type of join convergence can lead to a join path problem called a chasm trap.

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.

12. Ways to solve these trapsThese traps can be solved by applying contexts and using aliases. Aliases are better than contexts because contexts lays open the database structure to the end user

Read more: http://www.placementpapers.us/business_objects/309-business_objects_interview_questions_answers.html#ixzz1Bxyd0D2S Under Creative Commons License: Attribution

Retrieving Data

When using the "in list" feature in Deski, if you are typing items in yourself (rather than picking them from a list of values), the items in your list should be separated by commas. Do not type in any quote marks -- Desktop Intelligence will add them itself -- and do not put a space after a comma. However, if you are filling in a list of values in response to a prompt, you should separate the values with a semicolon, rather than a comma (but still no space between values).

If you notice that the objects in your universe aren't refreshed, be sure that when you first log on to Desktop Intelligence that the box labeled "Use is Offline Mode" is not checked.

If you get a "No data to fetch" message, and you're pretty sure you should be getting something, check to make sure you have configured your conditions correctly. For example: everything in the student data collection uses upper case letters. Another example: if you are entering a condition for a date in any of the financial data collections, use the full year, in other words, 06-01-2008 (instead of 06-01-08)

If you get a "Table or View does not exist" message, check to make sure the data collection is available on the warehouse status page.

To determine how many rows your query retrieved, choose "View" from the Data menu, then click on the Definition tab of the Data Manager window. The most recent execution of the query will be listed first, by date and time the query was executed, along with the number of rows retrieved, and the amount of time the database took to execute the query (please note that this time is not the time elapsed on your desktop machine).

When first testing a query you may wish to limit the number of rows retrieved to determine if your results are as expected. You can do this by clicking the Options button at the lower left corner of the Query Panel. Select "10 Rows", "20 Rows" or enter another value by clicking "Other" in the Partial Results area of the window. Remember to return the setting to "Default Value" when you are ready to retrieve all rows matching your query criteria.

You can use use wildcards in conjunction with the "Matches Pattern" operator to retrieve data that is like a value, rather than exactly equal to it. For example, using a percent sign ( % ) in the condition COA_Fund Matches Pattern 5% will retrieve all funds (to which you have access) from 500000-599999. Similarly, if you're trying to match almost the exact syntax, you can use an underscore ( _ ). For example, COA_ORG Matches Pattern '91_2' will retrieve ORG values 9132, 9142 and 9152.

If you have multiple queries in one report (one .rep file), you may want to rename your queries to better describe their use, so that when you go to edit or refresh them, you'll have a better idea of what data you'll be retrieving. To do this, go to the Data menu, and select "View Data" to display the Data Manager window. The General section of the Definition tab contains a field called "Name", which you can reuse to rename the query from the default "Query x with Universe" (i.e., "Query 1 with FINQUERY").

If you are trying to create a User Defined Object (UDO), but the universe you are in won't let you (in the Query Panel, clicking on User Objects does nothing, or Deski suddenly quits) try this: Log out, delete the .udo file for that universe, log back in and try creating the User Objects again. (Caution! doing this will remove any user objects you previously created for this universe; you will have to re-create them.)User Defined Objects all live on your local computer. They reside within one file per universe, in the following path:C:\Documents and Settings\<user_name>\Application Data\Business Objects\Business Objects 11.5\Universes\(where <user_name> is your user name on your computer) The file names will be <universe_name>.udo(where <universe_name> is the name of the universe, for example, "FINQUERY.udo" holds your local User Defined Objects for the FINQUERY universe.)

You can re-use queries between similar universes, so long as all the result objects and conditions from your query exist in the universe to which you'd like change. To do this, go to the Data menu, and select "View Data" to display the Data Manager window. The General section of the Definition tab contains a field called "Universe", which has a small button with "..." directly to the right. When you first display this window, the Universe field will display the universe against which the query is currently directed. Click the "..." button to display a list of all other universes to which you have access. Choose the one to switch your query to, and click ok. As long as all the objects in your query are available in the new target universe, that universe name will now appear in the Universe field. (This is convenient for switching from the FINQUERY to FINQUERY Template Universe, for example.)

Saving and Exporting Queries and Data

If you have taken the time to create a query, but want to be able to save your work without running it, you have two options. The most convenient is to use click the "Save and Close" button in the Query Panel to simply save the structure of your report. If you do this, be sure to then use "Save As" to save it with a distinctive name, in the location you wish. You can then open the query and simply click the Refresh button to retrieve your data. Alternatively, once you've built your query, you can click the Options button at the bottom left of the Query Panel, and click the "Do Not Retrieve Data" checkbox, click OK, and then click Run. Again, only the structure of your report will be displayed, which you can then save. Keep in mind, though, that you must uncheck the Do Not Retrieve Data checkbox before you choose to run the query in the future.

By default, Desktop Intelligence saves queries (.rep files) in \My Documents\My Business Objects Documents\userDocs. If you work with several universes, you may find it convenient to create subfolders within that directory to better organize your work.

If you want to export the data you've retrieved for use in another application, you have a couple options:

Save As: Desktop Intelligence allows you to use the "Save As" feature to save documents in Excel, Adobe Acrobat PDF or CSV formats. Once you've refreshed a report, click on the File menu and select Save As. Select the file format you'd like from the "Save as type" drop-down list. Desktop Intelligence will then save your file with the data as it appears on the screen. This means

the output will be saved and reflect section breaks, filters, special formatting, etc. The results of any variables or calculations will be saved as text, not underlying formulae. If your report has multiple tabs, and you choose to save as Excel, each tab will appear as a separate worksheet within one workbook. Similarly, multiple tabs will be accessible indidually in Adobe Acrobat (PDF) files. InfoView users can perform a similar action. If you experience undesired changes in Excel color formatting when overwritting a Excel file previoulsy saved from Deski, save it instead to different file name.(Note: This feature was not available in Business Objects 5.1.4 - the work around was to use the Business Objects Edit menu and select "Copy All", then open a blank worksheet in Excel and choose "Paste Special" from the Excel Edit menu. Choose the "Unformatted Text" option and click Ok to paste your data into the worksheet.)

Export: You can also export the raw data retrieved in your query (retaining no Business Objects post-query formatting or calculations), in a variety of file formats. From the Data menu, select "View Data" to display the Data Manager window. Click the Export button at the bottom of the Results tab, and designate file name and path, and file format (options include text formats, and .xls for Excel). If you choose the All files (*.*) format, you can also control the field delimiters used. [A word of caution about using the .xls format: some versions of Excel object to the field names used in the Business Objects classes. If you encounter an error when exporting/importing using the Excel format, try it again with either the All Files or the Text Files format, and then use the Excel Import Wizard to bring the data into your speadsheet.]

Report Formatting

If you're having a problem displaying or printing pages after the first page of a report, and you have an image of some sort as part of your report layout, check to see whether the image is part of a table with other elements. If it is, move it outside the table and see if this fixes your problem.

To minimize the processing time for your query, consider using the Desktop Intelligence toolbar icons or the Slice and Dice panel (rather than the query itself) to perform the following operations on the desktop once your query has finished:

Sort the data: You can place sorts on multiple objects using the Slice and Dice panel. You may also add breaks or report sections based on objects, and then sort those as well.

Filter the data: Once you have retrieved all the data meeting your conditions you may wish to further filter the data. Filtering via the Slice and Dice Panel enables you to include or exclude specific values retrieved. This is particularly useful when working with a large data set that you wish to manipulate many ways before deciding upon the final report format. You may set, change and remove filters as often as you wish without having to rerun the original query.

Perform calculations: You may add calculations to result objects, such as Sum, Count, Average, Minimum, Maximum and Percentage. This allows you to both view the data at the level of detail retrieved by your query and calculate at break levels you have designated. Available calculations are dependent upon the datatype of the object. More advanced calculations are also available using report Variables.

Duplicate rows - to show or not to show

Unless you intentionally go to the Options in the Query Panel and select “no duplicate rows”, Business Objects will return duplicate rows if they exist in the database.However, Desktop Intelligence will by default show the aggregate of those rows in the report. In other words, the report may not be showing you all the underlying data. You can see all the rows

if you go to the “View Data” tool, but if everything on two rows is identical in every column, then Deski will only display one row in the resulting table on the report.

If this isn’t what you want it to do, to force Deski to show all rows, do the following:

Right click on the table in your report, and select Format Table. On the General tab, check the box that says “Avoid Duplicate Rows Aggregation” and

then click OK.

 

Password Issues

Desktop Intelligence and InfoView do check your password when you log in. However, you must be sure to use the password change application to synchronize your Data Warehouse, Business Objects and Business Objects DB Credentials each time you change your Data Warehouse password. (The same applies if you are using Business Objects to query other Oracle databases, such as Penn Community.) If your passwords are not synchronized, your query attempts will return errors:

If you check only the Business Objects boxes in the PassWord Changer application, and not the Data Warehouse and other database boxes, when you submit a query you will get a message saying your access is denied for password errors.

If you check the database boxes (i.e., Data Warehouse) and only one of the Business Objects boxes, you will get this error:Connection or SQL sentence error: (DA0005) Exception: CS, Unexpected behavior

The resolution for all of the above situations is to go to the password change application and reset your password, and be sure to check ALL of the boxes.

 

Installation Issues

Symptoms are: You retrieve the installation files and unzip them, double-click the installer.bat, and get a message: "Please go to the control panel to install and configure system components." This can happen if you downloaded the installation files to a drive that is different from the one on which you are trying to install the BusinessObjects Deski client. Try downloading and unzipping to the user's desktop, and run the installer.bat from there.

Symptoms are: You either 1) get a DA0005 error, and when you click on the error message "details" it says that the DBDriver failed to load, or 2) you are installing Deski on a remote computer and you get an "openSessionLogon" error telling you it cannot establish a CMS connection.Resolution: You must log in to InfoView to activate Desktop Intelligence the first time you use it following installation. See the "Configure the Client" step in the Installation instructions for details.

You get an "Operation TimeOut" error and the message that "Your internet server is not responsive." This probably means you are trying to use an older version of Business Objects (for example, this can happen when your desktop shortcut is still pointing to the old version). When you launch, make sure you are actually using Business Objects Enterprise XI release 2 / Desktop Intelligence.

 

Other Error messages and error conditions

Reports with date prompts returning no data: If you're not getting data back when running reports with date prompts (and the same values worked in Business Objects 5), make sure you're entering the date in 4-digit year format. For example, rather than entering "7/1/07" (for July 1, 2007), enter "7/1/2007".

If you attempt to open a report and you get an error:You are not authorized to use this document. (FRM0008)try one of the following solutions:1) Is this a Corporate Document? In which case, do not try to open the local copy, but instead go to File->Import from Repository...and use the new one from the repository.2) If this is a document you created yourself on your local computer using the old version (or modified and saved on your local computer using the old version), then, if possible, use the old version -- version 5.1.4 -- to open it. Go to File->Save As... and before you save it, click the box next to "Save for all users" in the lower left corner of the screen. Then click on Save, and close it. You should now be able to open it using Deski.3) You can also get this error if you created this document yourself on your local computer, then exported it to the Repository (for example, to your "Favorites" box in InfoView), and then subsequently deleted the one on the Repository. The local one will no longer be available to you -- or anyone else! To avoid this problem, always make a backup copy of your local document, before you delete a report on the Repository.

If you've run a query, but are not seeing any results on screen other than the column header cells, you may want to check to see if any of the following features are set:

·You are viewing the Structure of the report, rather than the actual data retrieved. (Check the View menu, Strucure setting.)·There are Filters in your report. (Click on any cell that you can see, and choose Filters from the Format menu.)·The report is folded, so that only headers appear. (First check the old checkbox on the General tab of the Standard Report Styles window, accessible from the Tools menu. If Fold there is unchecked, you may also want to check the same setting in Format/Table, to see if the report is folded there.

You log in to Deski and try to refresh a report, but get: "Error During SQL Execution: (DA0003) - CS, Job already in use".One possible cause of this error message is because Internet Explorer 7 has a default time out of 30 seconds compared with the Internet Explorer 6 time out of 60 seconds. For some queries, this is not enough time to refresh the entire query, since the client is connecting using an HTTP protocol to the server.It is necessary to extend the time-out limit on Internet Explorer 7. This can be done by adding a key to the registry.(CAUTION! The following resolution involves editing the registry. Using the Registry Editor incorrectly can cause serious problems that may require you to reinstall the Microsoft Windows operating system. Use the Registry Editor at your own risk. It is strongly recommended that you make a backup copy of the registry files before you edit the registry. End users should NOT attempt this without first consulting with their Local Support Providers.)

To resolve the error message (if you are using IE 7)1. Click Start > Run. The Run dialog box appears.2. Type “regedit” in the Open: text box. Click OK. The Registry Editor appears.3. Navigate to HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings.4. Add a ReceiveTimeout DWORD value with a data value of (<number of seconds>)*1000. For

example, if the required time out duration is eight minutes, set the ReceiveTimeout data value to 480000 (<480>*1000).5. Exit the Registry Editor.6. Restart the computer.

If your Business Objects menu bar disappears, try the following steps: 1. From the Windows 'Start' menu, select 'Run'. 2. Type "regedit" in the 'Run' dialog box. 3. Locate and delete this folder in the registry:

HKEY_CURRENT_USER\Software\Business Objects\Suite 11.5\default\BusinessObjects\Application Preferences\BusinessReporter\Desktop Intelligence

4. Restart Desktop Intelligence 5. If the steps above do not resolve the issue, try deleting the Desktop Intelligence folder

under HKEY_USERS & the user's SID:

HKEY_USERS\<user's SID>\ Software\Business Objects\Suite 11.5\default\BusinessObjects\Application Preferences\BusinessReporter\Desktop Intelligence

6. Restart Desktop Intelligence If the Classes and Object pane disappears from the Query Panel, try the following steps:

1. From the Windows 'Start' menu, select 'Run'. 2. Type "regedit" in the 'Run' dialog box. 3. Locate and delete these two registry keys:

HKEY_CURRENT_USER\Software\Business Objects\Suite 11.5\default\BusinessObjects\Administrator User Prefs

HKEY_CURRENT_USER\Software\Business Objects\Suite 11.5\default\BusinessObjects\Application Preferences\BusinessDesigner

4. Restart Desktop Intelligence

 

If you're having problems accessing the Slice and Dice window, click the Slice and Dice button again, and, when the window doesn't appear, hit Alt+space, and then Maximize the window.

 

There are four Report Creation Wizards:

• Standard

• Cross-Tab

• Mailing Label

• OLAP

Standard: The Standard Report Creation Wizard is the most generic of the

wizards. It guides you through choosing a data source and linking database tables.

It also helps you add fields and specify the grouping, summarization (totals), and

sorting criteria you want to use. Finally, the Standard Report Creation Wizard

leads you through chart creation and record selection. The Templates screen

contains predefined layouts for you to apply to your report to give it more impact.

Cross-Tab: The Cross-Tab Report Creation Wizard guides you through the

creation of a report in which your data is displayed as a cross-tab object. Two

special screens (Cross-Tab and Grid Style) help you create and format the cross-

tab itself.

Mailing Label: The Mailing Labels Report Creation Wizard lets you create a

report that is formatted to print on any size mailing label. You can use the Label

screen to select a commercial label type, or you can define your own layout of rows

and columns for any multi-column style report.

OLAP: The OLAP Report Creation Wizard lets you create a report in which your

OLAP data is displayed as a grid object. Although similar to the Cross-Tab Report

Creation Wizard in several ways, the OLAP Report Creation Wizard appears to be

different due to the requirements of working with OLAP data sources. You first

specify the location of your OLAP data, and then you choose the dimensions you

want to include in the grid. Next you filter the report data and choose the style of

the grid object, which you can also customize. Finally, you can define labels for

your grid and insert a chart, if you wish.

POSTED BY BO WORLD!!! AT 11:21 PM 35 COMMENTS

T U E S D A Y , M A Y 2 6 , 2 0 0 9

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

POSTED BY BO WORLD!!! AT 7:53 PM 0 COMMENTS

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

POSTED BY BO WORLD!!! AT 5:49 PM 0 COMMENTS

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.

POSTED BY BO WORLD!!! AT 2:49 PM 1 COMMENTS

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 

POSTED BY BO WORLD!!! AT 10:19 AM 6 COMMENTS

M O N D A Y , M A Y 2 5 , 2 0 0 9

Desktop Intelligence Related Questions

1) What is Desktop Intelligence?

Desktop Intelligence is an integrated query, reporting and analysis solution for

business professionals that allows you to access the data in your corporate

databases directly from your desktop and present and analyze this information in a

Desktop Intelligence document.

When you run the query, Desktop Intelligence connects to the database and

retrieves the data mapped to the objects you selected. A query is a type of data

provider. The data provider contains the data you have chosen to retrieve from the

data source. Using this data set, you can build interactive reports. Desktop

Intelligence lets you access data from a wide range of sources: from relational and

multidimensional databases, from packaged applications, from personal data

documents, and, using Microsoft Visual Basic for Applications procedures, from

virtually any source.

2) How is information shared in business objects?

You can quickly and easily share the documents you have created with other users

in your company, either by sending them directly to selected individuals or groups,

or by Exporting them to the repository as Folders or Categories. The repository

stores the documents you send so that other users can retrieve and view them. It

also stores information about the documents it stores, such as name of sender,

date, and also which users in the company have the right to retrieve and view a

document. You can Import documents that other users have sent, using Web

Intelligence documents which you can open and view in Desktop Intelligence. You

can also use InfoView to send documents for scheduled processing.

3 ) How are documents organised in repository?

The Repository organizes documents into Folders and Categories in an orderly

system that permits easy access for you and others working with documents. You

select the documents you want to import from or export to Desktop Intelligence.

Folders: Folders are the physical place where documents are stored. Only one

document with a given name may be placed in a folder or category. It is possible to

place documents in several categories. If necessary change the name of the

document or give it a number to place it in the same folder or category. It is

possible to create or delete sub-folders.

There are 2 types of folders:

• My Folders with 2 sub-folders

• Favorites (Generally reserved for often used documents)

• Inbox (Generally reserved for documents received from other users

• Public Folders (For shared documents.)

Categories: Categories are used for classifying information regardless of its

storage location.

There are two types of Categories:

• Corporate Categories

• Personal Categories

4) What are different data sources available for Desktop Intelligence?

Desktop Intelligence lets you access data from a wide range of sources. You can

access data from:

• Universes

• Personal Data Files

•Stored Procedures: You can only use stored procedures if your supervisor or IS

department has provided them, and if the RDBMS at your site supports them. A

stored procedure is an SQL (Structured Query Language) script, saved and

executable on your database.

• Freehand SQL Server: You can use free-hand SQL if you are familiar with SQL,

which is the language used to interact with relational databases. In free-hand SQL,

you open or write a SQL script, which you then run against the database.

• XML Data Provider

• VBA Data Provider

5) Can all Desktop Intelligence users build queries using all data

providers?

Your Desktop Intelligence supervisor can restrict access to certain types of data

providers, or even certain objects within a universe. As a result, you might be able

to build queries on universes but no other type of data provider, and then be able

to use only certain objects in the universe. The way the supervisor sets up access

to data providers and other Desktop Intelligence features depends entirely upon

the query and reporting needs of your organization.

6 ) Who sets up connection to data providers in desktop intelligence?

Universe: The Universe designer sets up connection to the database.

Stored Procedures: The supervisor creates the connection to access a stored

procedure.

Free- hand SQL: In free-hand SQL, you can create your own connection to the

database. Once you have created the connection, you can make it available to

other users.

Personal data files and XML files: When you access data in a personal data file or

XML file, you select the file and in doing so, you “connect” to it. This is not a

technical task, it’s just a question of selecting the right file.

VBA procedures: A VBA procedure runs a VBA macro that retrieves data for your

Desktop Intelligence report. The person who creates the macro defines the

connection to the data source in the macro code.

7) What are restrictive connections to database in desktop intelligence

mean?

If you are working with a universe that is set up with a restrictive connection, you

need to supply the database username and password to run a query. This

username/password is not the one that you use to log onto Desktop Intelligence; it

is the username/password of the underlying database (for example an SQL Server

database) that the universe accesses. This database normally remains hidden, but

the universe designer can set up a restrictive connection to add an extra layer of

security. Depending on the type of restrictive connection, you need to supply the

database username and password in some or all of the following situations:

• When you first run a query

• When you refresh a query

• When you parse a query to test its validity

8) How Universe queries are generated using Desktop Intelligence?

When you build a query, you select objects from a universe, then run the query.

Desktop Intelligence connects to the database, and retrieves the data mapped by

the objects you selected. Desktop Intelligence retrieves this data by executing an

SQL query against the database. Desktop Intelligence generates this SQL

according to the objects you select.

9 ) What is meant by building powerful queries in Desktop Intelligence?

You build a simple query by adding objects to the Query Panel. The procedures

described in the following sections enable you to build a more powerful query by

controlling the data that your queries retrieve. You can:

• Define scope of analysis

• Conditions

• Sort data

• Retrieve a specified number of rows of data

• Eliminate duplicate rows of data from the query result

10) What is meant by Scope of Analysis?

Analysis means looking at data from different viewpoints and on different levels of

detail. “Scope of analysis” means a subset of data, returned by a query, that you

will use for analysis in your report. The data for your scope of analysis does not

appear in the report until you decide that you want to use it in analysis.

The scope of analysis you can define depends on hierarchies in the universe. 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.

11) How do you limit the query using conditions and what are they?

A condition is a way of limiting the data that a query returns. In Desktop

Intelligence, you can set three types of conditions on a query:

Predefined conditions: When universe designers build universes, they can create

predefined conditions for you to use.

Simple conditions: Enable you to limit data returned by a result object. For

example, you can find out about certain customers by applying a simple condition

on the Customer object, then selecting the customer names that appear in a dialog

box.

Complex conditions: Enable you to limit the query results by any object in the

universe.

12) What are options that can be setup before running a query?

Before running a query, you can set options that enable you to:

• Specify the number of rows of data that you want the query to return. The

Default Value option corresponds to the maximum number of rows that the

universe designer specified for queries on the current universe.

• Eliminate duplicate rows of data.

• Retrieve no data when you run the query. In this case, Desktop Intelligence

generates the query SQL but does not connect to the database. The names of the

objects included in the query appear as column headings in the report. This option

is useful if you want to save the query you have built, but refresh it at an off-peak

time.

13) What are the restrictions on Free-hand SQL?

The types of SQL script that you are allowed to run as free-hand SQL are

determined by your Desktop Intelligence administrator. If you attempt to run a

script for which you do not have permission, you will receive an error message.

Typically, you are able to run scripts that contain only one SELECT statement.

Note: BusinessObjects does not execute COMPUTE and ORDER BY clauses in free-

hand SQL statements.

14) What are restrictions on Stored Procedures?

• The Desktop Intelligence supervisor grants access to the database or account

where stored procedures are located.

• Not all RDBMSs support stored procedures.

• COMPUTE, PRINT, OUTPUT or STATUS statements contained in stored

procedures are not executed.

15) What are the benefits on using Personal Data files?

The main benefits of using personal data files are as follows:

• You can display corporate data next to personal data in the same report which

can be used for comparison.

• If you have no connection to a remote database or if there is no RDBMS at your

site, you can use personal data files as your only data source.

• You can use Desktop Intelligence reporting and analysis features to work on data

that comes from other applications.

16) How do you use Visual Basic for Applications procedures?

A VBA data provider is a powerful and flexible tool for accessing external data.

Very often you will want to access automation servers through VBA to retrieve

their proprietary data. VBA allows you to retrieve data from various sources: ADO,

DAO, RDO, Application Object Models, EDK and low-level APIs.

To create a VBA data provider, you write a VBA procedure that takes the interface

DpVBA Interface as a parameter. You can write this procedure from within the

VBA environment of Desktop Intelligence. The DpVBAInterface is the interface to

the VBA data provider Automation object. The procedure for writing a VBA data

provider is:

1. Create a connection to the data source.

2. Create a data cube.

3. Set the data cube dimensions.

4. Populate the cube with data from the data source.

Once the data cube is populated, you can generate a report based on this data in

Desktop Intelligence.

17) What is an XML?

XML is a text-based data format that structures data in elements or tags. XML files

are similar to the HTML files used to build pages on the World Wide Web. The

principal difference is that, the set of HTML elements is limited to those used to

describe the structure of a Web page, an XML file can contain any elements,

depending on its application.

Creating an XML-based report involves two steps:

• building an XML filter

• building the report

18) What are common dimensions?

Common dimensions are dimensions with the same name occurring in the same

universe.

Dimensions called Year that occur in a universe and an Excel spreadsheet are not

common. Desktop Intelligence will prompt you to link them.

19 ) What situations require you link data providers?

Desktop Intelligence automatically links data providers with a common dimension.

Two dimensions in separate data providers are common when they belong to the

same universe and have the same name. Desktop Intelligence prompts you to link

data providers if there is no common dimension between the data providers. If you

simply want to add columns of data to a report, use the Edit Data Provider

command on the Data menu instead of building a new query. This method lets you

add result objects to the initial query; Desktop Intelligence automatically inserts

the new columns of data in the report or creates a new report.

20) Which dimension should act as a link between data providers?

It is necessary that the dimension you use to link data providers be the same type

(numeric or alphanumeric) in both data providers. If not, two rows of data will

appear for the linked object when you create a table that uses the object.

Additionally you should use only dimensions that return the same type of values. It

doesn’t make sense to create a link between dimensions with totally different lists

of values (Year and Region, for example).

21) What is difference between purging and deleting of data providers?

Purging means emptying a data provider of its results, deleting means getting rid

of the data provider for good —an action that cannot be undone.

Why purge or delete a data provider? Purging reduces the size of a document, so is

useful when you want to send the document to other users, or save it on a diskette,

for example. You should only delete a data provider, however, if you are certain

that you and other users no longer need it.

22) What are different ways in which data can be analyzed using desktop

intelligence?

• Desktop Intelligence on-report analysis allows you to work directly on your data

in your report using drag and drop and or with simple mouse clicks.

• Desktop Intelligence enables you to carry out multidimensional analysis in Drill

mode.

• Desktop Intelligence Slice and Dice mode allows you to organize data for analysis

in the slice and dice panel.

23) What is On-report analysis?

You can drag and drop data on your report to get a different viewpoint for your

analysis. You can add data from the Report Manager to create tables and sections.

You can replace, swap and re-organize data on the report. Desktop Intelligence

redoes the calculations in your report immediately so that you can see at once how

different combinations of factors affect your performance. Dynamic on-report

grouping allows you to create groups for comparative analysis and you can quickly

insert common business calculations or easily create your own formulas and

variables for analysis. You can sort, filter and rank your data using a simple mouse

click to focus your analysis on a slice of data.

24) What is Drill – mode?

Drill mode is a Desktop Intelligence analysis mode that allows you to break down

data and view it from different angles and on different levels of detail to discover

what the driving factor is behind a good or bad result. Drill mode allows you to

include data for analysis behind the scenes of your report and display the top level

data only. If necessary, you can drill down to the more detailed data to understand

the higher-level data. This allows you to see how different aspects of your business

affect your revenue step by step.

25) How does drill mode work?

The person who creates the Desktop Intelligence universe organizes objects in

hierarchies, with the most general object in the class at the top and the most

detailed at the bottom. Objects are grouped in this way to make it easy for you to

find what you are looking for. They are classified inside the groups so that if you

want to make a high level report you know you need to include objects at the top of

the list in your query and if you want a more detailed report then you choose

objects from further down the list.

26) How are hierarchies used in drill mode?

When you analyze data in drill mode, you use hierarchies. The universe classes are

the default hierarchies you use for drilling but the universe designer can also set

up custom hierarchies. You can also create and edit hierarchies in your reports.

Drill hierarchies contain dimension objects only. In drill mode, you drill down on

dimensions, for example from Year to Quarter to Month. At each level Desktop

Intelligence recalculates measures such as Revenue or Profit Margin.

27) What is the difference between drill-up, drill-down, drill across and

drill through?

Drilling down: When you drill down, you display the next level of detail in a

hierarchy

Drilling up: Drilling up is the opposite of drilling down. When you drill up, you go

back up through the hierarchy to display data on less detailed levels.

Drilling across: When you drill down and up, you move through the levels of the

same hierarchy. However, if you cannot find the answer to a question by analyzing

data in its current hierarchy, you can move to another hierarchy to analyze other

data.

Drilling through to the database to bring in new data If the lowest level of detail

you need is not currently available in the report, you can drill through to the

database directly from drill mode and get the data you need. You do not have to

edit the query in the Query Panel.

28) What is Slice Dice mode?

Slice-and-dice mode enables you to switch the position of data in a report, for

example by moving columns to rows to create a crosstab. You can also use slice-

and-dice mode to:

• work with master/detail reports

• display and remove data

• rename, reset and delete blocks

• turn tables and crosstabs into charts, and vice versa

• apply, edit and delete breaks, filters, sorts, rankings and calculations

You access slice-and-dice mode through the Slice and Dice Panel, a pop-up window

that provides a graphical representation of the report you are working on. You

carry out slice-and-dice tasks by dragging and dropping icons that represent your

data.

29 ) What are master/detail reports?

Master/detail reports display data in sections. Each section contains a “master” or

parent piece of data, for example a resort, or a year. The rest of the data in the

section relates to the master.

30) How are filters used to manage filters?

A filter enables you to hide data you do not want to view behind the scenes and

display only the data you need. There are two types of filter. A global filter affects

the whole report. A block specific filter filters data for the specified chart, table or

crosstab only.

31) How can the filter be ignored using formulas?

You can force Desktop Intelligence to ignore any filters you have inserted on a

report so that it calculates on all data, not just the filtered values. To do this, you

use the NoFilter function. The syntax is: =NoFilter(formula)

In the first table, the sum includes New York and Washington revenues only.

The formula to calculate this sum is: =Sum()

In the second table, formula for calculating the sum includes the NoFilter function.

As a result, the sum includes revenues for all cities. The formula is as follows:

=NoFilter (Sum())

32) What is the use of ranking?

You might want to show just the extreme ranges of your data, for example your top

ten customers. Ranking enables you to look at the largest and smallest numbers in

a report. Like filtering, it hides the data you do not want to display. Desktop

Intelligence does not delete the data from the report; you can view it again

whenever you like by removing the ranking. Ranking also sorts the data in

descending order. Thus, the largest value of the ranking is always at the top of the

ranked column and the smallest value at the bottom. You can rank data in tables,

crosstabs or master cells in master/detail reports.

33) How is ranking performed if they are breaks inserted inside the data?

In a table or crosstab in which breaks have been inserted, data is ranked

separately for each break level.

Note: If you have created a local variable using values from different data

providers, you will not be able to rank data based on this variable. The variable will

not be displayed in the list in the Ranking dialog box.

34) How do you manage ranking with filters and sorts?

To rank data in a report, you must remove any sorts or filters currently applied to

that data. If any sorts or filters exist when you try to apply a ranking, Desktop

Intelligence displays the following message: A sort and/or a filter has already been

applied to the selection. Do you want to overwrite it?

35) What is the function of alerts?

You can highlight data in a Desktop Intelligence report using alerters. Alerters use

special formatting to make data that fits certain conditions stand out from the rest

of the data. This helps draw attention to trends and exceptions in report data.

36) Can we use alerters on charts?

No

37) What are user objects and what does it contain?

A universe consists primarily of classes and objects created by the universe

designer. If the objects in a universe do not meet your needs, you can create your

own additional user objects. User objects appear in the User Objects class in the

universe. You include them in queries in the same way that you include regular

objects. You do not need to define a connection to a database to define a user

object.

A user object has a name, a type (character, date or numeric), a qualification

(dimension, measure or detail) and a formula. The formula contains a combination

of functions, objects, user objects, operators, and text. User objects are end-user

personal objects that are not shared with other end-users. User objects are defined

for each universe and stored on a local file inside the “\Universe” folder. For

example, if you create a user object in the BEACH.UNV universe, Desktop

Intelligence stores it locally in the file BEACH.UDO in the Universe folder.

38) What are the restrictions on user objects?

· Cannot be moved from User class: You can work only with the user objects that

you create yourself, and you cannot move user objects from the User Objects class.

· Also, user objects are available only in the universe in which they were created.

· User objects are not shared.

· Reports that include user objects can only be viewed by other end-users. This is

because user objects are stored locally in a user object definition file. Other end-

users, who do not have the same user object definition file, are not able to access

the user object definitions.

· If an end-user tries to refresh or edit a query that contains another user’s user

objects, Desktop Intelligence removes the objects from the query and report.

Despite these restrictions, the universe designer can convert user objects into

regular objects that can be made available in other universes and for other users.

Note: You cannot schedule reports that contain user objects and keep the user

objects; they are removed when the report is refreshed.

39) How can the user objects be shared among the end users?

If you want to share user objects with other users, you should ask the universe

designer to include these user objects in the related universe in order to make

them available to all Desktop Intelligence end-users.

40) What are wildcard characters?

Conditions with the Match pattern and Different from pattern operators are great

for finding lists of similar values, such as customer names beginning with S.

Wildcards are special characters that can denote any single character, or any

number of characters. Desktop Intelligence supports the standard wildcard

characters, which are:

% : Replaces several characters, or in the response to a prompt. For example, N%

returns all values beginning with an N (New York, Nevada)

- : Replaces a single character in a constant. For example, GR_VE returns Grave,

Grove, Greve.

41) How does the operator in list work?

The In list operator lets you select multiple values for a document. These multiple

values can be a condition on a query that you want to build or can be the basis for

an interactive document in which Desktop Intelligence prompts other users to

select values from the list you created to view data limited to their needs.

42) Explain the difference between Different from and except operator?

Different from, Not in list and Except are all operators that exclude certain data

from your query results. Does this mean that you could use this condition to obtain

a list of customers who have not stayed at Bahamas Beach?

Resort Different from ‘Bahamas Beach’

The result of this query includes those customers who have stayed at Bahamas

Beach and elsewhere. Why? Because reservations exist for these customers for

resorts other than Bahamas Beach. These reservations alone are enough to satisfy

the condition ‘Resort differs from Bahamas Beach’. Furthermore, this condition

excludes customers who have made no reservations. Desktop Intelligence checks

these customers’ records against reservations and determines that no reservations

satisfy the condition ‘Resort differs from Bahamas Beach’ - because there are no

reservations! Nevertheless, it is clear that a report showing customers who have

not stayed at Bahamas Beach should include customers who have not stayed

anywhere.

You solve this problem by using the Except operator instead of Different from.

When you use Except, Desktop Intelligence builds two queries:

• All customers.

• Customers who have reservations for Bahamas Beach.

Desktop Intelligence then subtracts the customers given by the second query from

those given by the first. This returns the result you want.

Note also that:

• You can only specify one value with Different from, but multiple values with Not

in list.

• You can only specify one value with Except. However, you can build combined

queries using MINUS to exclude, for example, Bahamas Beach customers and 1996

customers.

43) Define level of calculation?

· If you want to obtain a single result row: click Globally, then click Next.

· If you want to obtain several result rows: click By one or more objects, select the

objects from the list.

44) What is meant by synchronization of calculation?

While making a condition in the query panel and adding a calculation to a

condition after choosing the level of calculation it prompts for synchronizing the

calculation.

This is can be done in two ways:

· If you want to make a calculation independently of your objects: click

Independently of your objects, then click Next.

· If you want to make a calculation for each value of one or more of your objects,

which allows you to limit the calculation to particular objects: click For each value

of one or more objects, select the objects from the list, then click Next.

45) What is meant by applying calculation to a query?

When you use a calculation, Desktop Intelligence builds SQL that contains a

subquery. A subquery is an inner query. The database that receives the SQL

generated by Desktop Intelligence evaluates the result of the inner query against

each row of the outer query to determine if the row should appear in the result.

In the process as you move through the wizard, you specify:

Ø The object to use in the calculation: This is the object in the outer query whose

value is compared against the result of the inner query. In the example, the object

is Invoice Date.

Ø The aggregate function to apply to the object: In the example you applied the

Maximum function because you were interested in the most recent invoice date.

Ø The level of calculation: This determines the grouping in the subquery. In the

example you chose Globally because you were interested simply in the customer’s

latest invoice date, not a latest invoice date by some other criteria.

Ø Synchronization: This determines the subquery links to the main query In the

example you chose the Customer object because you were interested in each

customer’s latest invoice date.

Ø The number of values to compare: This determines how many values in the

subquery the database compares against the values in the outer query. In the

example you can choose either option because the subquery returns one row only

for each customer.

46) What is list of values?

A list of values contains the values returned by an object. You use lists of values to

select the value(s) you need when defining conditions on a query or when selecting

the value(s) in a prompt. When you use or view a list of values for the first time,

Desktop Intelligence creates a .lov file that contains the query definition and the

values it returns. By default, .lov files are located in sub-folders inside the

UserDocs folder.

47) How are list of values created?

In Designer, the universe designer decides whether to associate a list of values

with an object. Once associated, the list of values can be viewed or edited in

Designer or Desktop Intelligence. The first time you view an object’s list of values,

Desktop Intelligence runs a query and retrieves the values from the database; the

object’s default list of values is generated by the object query.

48) Where does the function Variance doesnot work?

Variance syntax containing the Where operator will work in all cases except when

you have two cascading Wheres (a variance using a Where operator that contains a

formula that also uses a Where operator) or in certain contexts, for example the

variance of the variable "" in Report.

49) What is a crosstab?

A crosstab displays data in rows and columns, as opposed to a table which displays

data in columns only. Measures are typically placed in the body of a crosstab at the

intersection of rows and columns.

50) What are input context and output contexts?

Desktop Intelligence defines an input context and an output context to determine

the result of an aggregate calculation. A context is made up of one or more

dimensions.

-The input context consists of one or more dimensions that go into the calculation.

-The output context consists of one or more dimensions that Determine the result

of the calculation.

The syntax for input and output contexts is as follows:

=AggregateFunction( In ) In

The following example explains this in more detail.

Example: The following formula returns the minimum revenue per city per region:

=Min( In (,)) In

The input context consists of Region and City, while the output context is Region.

51) What are reset contexts?

You use a reset context in a cumulative aggregation, such as running total revenue

per quarter. The reset context consists of one or more dimensions which reset the

value of the calculation to zero each time a dimension value changes.

Example: Calculating running total revenue per country

You want to calculate running total revenue per country per year, and naturally

you want the calculation to be reset for each country. When the value of Country

changes, you want the calculation to begin at 0. To obtain this result, you display

Country, Year and Revenue in a table, and apply a break on Country. You then add

the cumulative aggregation =RunningSum(;)

in which you specify Country as the reset context.

The syntax is

=RunningAggregateFunction(;)

giving, for example =Sum(;;)

52) What are local variables?

A local variable is a named formula. Local variables appear in the list of variables

in the Report Manager Data tab; you can use them to build tables, charts and

crosstabs in the same way as you use variables returned by a data provider.

Why use local variables?

Variables have a number of advantages over formulas because there are some

things you cannot do using formulas alone:

• You cannot apply alerters, filters, sorts and breaks on columns or rows

containing formulas, but you can on those containing variables.

• You can include variables qualified as dimensions in drill hierarchies. Local

variables are also useful because:

• You can re-use them easily in the same document.

• Formulas can be complex. You can use (and reuse) variables in formulas to

simplify them. Because you can re-use variables, you don’t need to type the same

formulas over and over again. Variables make complex formulas easier to decipher

because they break the formulas up into manageable components.

53) What are functions?

Desktop Intelligence contains many built-in functions which greatly extend its

capabilities. Functions are pre-defined formulas. A function consists of the function

name followed by a pair of parentheses. The parentheses can contain arguments

and arguments supply functions with values on which to operate. Arguments can

be objects, constants or other functions.

54) Concatenation:

Combining data in a single cell:

Concatenation(character string, character string)

=Concatenation(,)

=Concatenation( ,(Concatenation(" " ,))) Name>)))

The syntax to achieve the same result as shown above using the & operator is:

=&" "&

The Concatenation() function and & operator allow you to combine charactertype

data only. If you want to combine text or character-type data with numbers you

must first convert the number into a character string. Otherwise, Desktop

Intelligence displays the error message 'Incompatible data type'. You can convert a

number to a character string using the FormatNumber() function.

In the same way, if you want to combine text with dates using the & operator or

the Concatenation() function, you must first convert the date into a character

string. Otherwise, Desktop Intelligence displays the error message 'Incompatible

data type'. You can convert a date into a character string using the FormatDate()

function