Guidelines & Best Practices - Universe & Report Design

 

Universe Design: Guidelines & Best Practices

Introduction
Gives the basic guidelines/practices that could be followed in any Universe Design

Connection
--> When using a repository, always define a SECURED Connection to the Database.
--> Use the Universe Property panel to define the Universe Use and Version (last update).
--> Define the Connection Name that helps for Easy Database Identification.
--> Parameters - SQL Tab - Multiple SQL statements for each measure to be unchecked.
--> Parameters - SQL Tab - Cartesian Products - Prevent is checked.

Class
--> Define Universe Classes / Subclasses as per the business logic & Naming Convetion.
--> Involve the business users in defining the classes hierarchy and business names for the classes and objects.
--> AVOID Auto Class generation in the Designer.
--> Give description for the use of each Class/SubClass.
--> Avoid deep level of subclasses as it reduces the navigability and usability.

Objects
--> Object to be used in calculation HAS to be Measure Objects.
--> Object to be used in Analysis HAS to be Dimension Objects.
--> Give description for the use of each Object.
--> Include an Eg. In the description for Objects used in LOV.
--> Do not set LOV Option for each Dimension. Use it only for required Objects, esp. those to be used in Report Prompts.
--> Keep "Automatic Refresh before Use" option clicked for LOV Objects:
--> If LOV is editable by the user, provide a significant name to List Name under object properties.
--> All the measure objects should use aggregate functions. This will ensure that the aggregation happens at the database for the selected dimensions.
--> Avoid having dupicate Object names (in different classes).
--> Format for objects of type Numeric, Currency & Date should be defined.

Predefined Conditions
--> Give description for the use of each pre-defined condition.
--> If Condition is resulting in a Prompt, make sure associated Dimension Object has LOV.
--> Time dimension related predefined conditions such as Current year, Current month,Previous year,Last (x) weeks, etc can be defined to make it easy for scheduling daily/weekly/period based reports.

Tables
--> Alias Tables should be named with proper functional use.
--> Arrange the tables in the Structure as per Business/Functional logic. This helps other Universe users in understanding.
--> It is always best to bring the tables without joins and build them manually. It helps the designer to understand the intricacies of the model.

Joins & Context
--> AVOID keeping hanging (not joined) tables in the structure.
--> AVOID having joins that are not part of any context.
--> Give proper functional naming to the context for easy identification.
--> AVOID having 1:1 joins.

Import/Export
--> Make sure of the path for Import, which usually is always in the Business Objects' Universe folder.
--> LOCK the universe if Administrator/Designer does not want any user to Import/Export.
--> DO "Integrity Check" before Exporting the Universe.
--> Good to have correct folder structure , so that you can have a secured environment.
--> Once exported, never delete any objects from the universe without doing an impact analysis on the object usage

Migration
--> Better take a backup of the repository and then proceed with the migration in BO5.X and BO6.X Version

Report Design: Guidelines & Best Practices

General
--> Give meaningful names for the report tabs --> For complex reports, keep an overview report tab explaining the report --> Use the Report properties to give more information about the report

Dataproviders
--> Each Dataprovider should be given a name that reflects the usage of the data its going to fetch.
--> Select Objects in such a fashion that the resulting SQL gives a hierarchial order of Tables. This helps to achieve SQL Optimisation.
--> Avoid bringing lot of data into the report which will unnecessarily slow down the report performance.

Report Variables
--> Follow the naming convention of "var_" as prefix to each report level variable. This helps to identify Report Variables different from Universe Objects.
--> Each variable that carries a calculation involving division should have IF <Denominator> <> 0 THEN <Object>. This avoids display of #DIV/0 errors in the report.
--> Avoid having deep nested calculations which will slow down the performance of the report.

Report Structure
--> Make use of Report Templates when having most of the report with similar structures. This makes the work to move faster and consistant across.

Report Formats
--> All the reports should have page layout set in a printable manner. (Landscape/Portrait, Fit in 1 page wide or/and 1 page tall are different options).
--> All the reports should have page numbers in the footer.
--> All the reports should have Last Refreshed Timestamp in the header or footer.
--> All the above can be standardized by using templates

Report CELL Formats
--> All Numeric should be given Number format as per the language Eg. For German #.##00 for English #,##00.
--> Number cells should have a Right Alignment while Text cells should have Left Alignment.
--> Cell showing Percentage should carry the % text (either Column Header or in each cell).
--> Indenting should ALWAYS be done using the Indenting Tool and NOT by using " ".

 

 

Business Objects Tips and Tricks - Desktop Intelligence

Business Objects Tips and Tricks - Desktop Intelligence

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

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.

 

 

Collection of Business Objects Interview Questions - Universe Related Questions

 

1) What is a Universe?
Universe is a semantic layer that isolates you from the complexities of the database. A universe maps to data in the database in everyday terms that describe your business situation. Universes are made up of classes and objects. For example, the objects in a human resources universe would be Names, Addresses, Salaries. Classes are logical groupings of objects. Each class has a meaningful name, such as Vacation (for objects pertaining to employee vacations). Each object maps to data in the database and enables you to retrieve data for your reports.

 

2) How do u export universe to repository?You make a universe available to Web Intelligence users and other designers by exporting a universe to the repository. When you export a universe the universe is: Moved to the selected universe folder on the repository file system and Created in the Central Management System (CMS). Each time the universe is exported to the repository, the universe version in the CMS is updated. Exporting a universe to the repository involves following steps:
1. Select File > Export. The Export Universe dialog box appears.
2. Select a universe folder from the folder drop down list box. (or) Click the Browse button and select a universe folder in the folder browser.
3. If you want to lock the universe, double-click the universe name.
4. Click a group in the Groups list box. This is the user group that uses the exported universe.
5. Click a universe in the Universes list box. The Universes list box shows the names of the active universes.
6. If you want to export other universes that are not open, click the Add Universe button, and then use the browser to select the other universes.
7. Click

3) What is Strategies?
A strategy is a script that automatically extracts structural information from a database or flat file. Strategies have two principle roles:
• Automatic join and cardinality detection (Join strategies)
• Automatic class, object, and join creation (Objects and Joins strategies)
Strategies can be useful if you want to automate the detection and creation of structures in your universe based on the SQL structures in the database. There exits two types of strategies:
-Built in strategy is the Default strategy shipped with Designer. Built in strategies cannot be customized.
-External strategy: User defined script that contains the same type of information as a Built in strategy, but customized to optimize information retrieval from a database.

4) What is a derived table?
Derived tables are tables that you define in the universe schema. You create objects on them as you do with any other table. A derived table is defined by an SQL query at the universe level that can be used as a logical table in Designer. Derived tables have the following advantages:
• Reduced amount of data returned to the document for analysis.
• Reduced maintenance of database summary tables. Derived tables can, in some cases, replace statistical tables that hold results for complex calculations that are incorporated into the universe using aggregate awareness.
Derived tables are similar to database views, with the advantage that the SQL for a derived table can include prompts.

5) What is Join Path Problems?
A join path is a series of joins that a query can use to access data in the tables linked by the joins. Join path problems can arise from the limited way that lookup and fact tables are related in a relational database. The three major join path problems that you encounter when designing a schema are the following:
• Loops: Returns too few rows. Joins form multiple paths between lookup tables.
• Chasm traps: Returns too many rows. Many 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.
• Fan traps: Returns too many rows. 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.

6) What is a Lookup Table?
A lookup (or dimension) table contains information associated with a particular entity or subject. For example, a lookup table can hold geographical information on customers such as their names, telephone numbers as well as the cities and countries in which they reside. In Designer, dimension and detail objects are typically derived from lookup tables.

7) What is a Fact Table?
A fact table contains statistical information about transactions. For example, it may contain figures such as Sales Revenue or Profit. In a universe, most but not all, measures are defined from fact tables.

8) What are Contexts?
Contexts are a collection of joins which provide a valid query path for Web Intelligence to generate SQL. Contexts are used in:
• Solving loops:
• Solving chasm traps.
• Assisting in some solutions for fan traps.
• Assisting in detecting incompatibility for objects using aggregate awareness.

9) How can we detect chasm trap?
Detect Contexts examines the many to one joins in the schema. It picks up the table that receives converging many to one joins and proposes contexts to separate the queries run on the table. This is the most effective way to ensure that your schema does not have a chasm trap.

10) How Do You Detect a Fan Trap?You cannot automatically detect fan traps. You need to visually examine the direction of the cardinalities displayed in the table schema. If you have two tables that are referenced by measure objects and are joined in a series of many to one joins, then you may have a potential fan trap.

11) How does a context resolve a loop?

A context resolves a loop by defining a set of joins that specify one specific path through tables in a loop. It ensures that joins are not included from different paths within the same SQL query.

12) How Do You Resolve a chasm Trap?
To resolve a chasm trap you need to make two separate queries and then combine the results. Depending on the type of objects defined for the fact tables, and the type of end user environment, you can use the following methods to resolve a chasm trap:
• Create a context for each fact table. This solution works in all cases.
• Modify the SQL parameters for the universe so you can generate separate SQL queries for each measure. This solution only works for measure objects. It does not generate separate queries for dimension or detail objects.

13) How Do You Resolve a Fan Trap?
There are two ways to solve a fan trap problem.
• Create an alias for the table containing the initial aggregation, then use Detect Contexts (Tools > Detect Contexts) to detect and propose a context for the alias table and a context for the original table. This is the most effective way to solve the fan trap problem.
• Altering the SQL parameters for the universe. This only works for measure objects.

14) What is a microcube?
When you create a measure you must specify the way the aggregate function will be projected onto a report. Returned values for a measure object are aggregated at two levels of the query process:
• Query level. Data is aggregated using the inferred Select statement.
•Microcube Level: A microcube is a conceptual way to present the data returned by a query before it is projected onto a report. It represents the returned values held in memory by a Business Objects reporting product. The block level is the 2 dimensional report that a user creates with the returned data. A user can choose to use all, or only some of the data held in the microcube to create a report. A user can also do aggregate functions on the returned values in the microcube (local aggregation) to create new values on a report.

15) What are @Functions?
@Functions are special functions that provide more flexible methods for specifying the SQL for an object. @Functions are available in the Functions pane of the Edit Select box for an object. @Functions are very flexible. Depending on what you want to achieve, you can use any @function in either a Select statement, or a Where clause.

@Aggregate_Aware: The @Aggregate_Aware function allows an object to take advantage of tables containing summary data in the database. If your database contains summary tables and you are running queries that return aggregate data, it is quicker to run a Select statement on the columns that contain summary data rather than on the columns that contain fact or event data.

@Aggregate_Aware(sum(agg_table_1), ... sum(agg_table_n))

 @Prompt: You can use the @Prompt function to create an interactive object. You use a @Prompt function in the Where clause for an object. It forces a user to enter a value for a restriction when that object is used in a query. When the user runs the query, a prompt box appears asking for a value to be entered. @Prompts are useful when you want to force a restriction in the inferred SQL but do not want to preset the value of the condition.
@Prompt(‘message’,‘type’,[lov],[MONOMULTI],[FREECONSTRAINED])

@Select: You can use the @Select function to re-use the Select statement of another object. When the @Select function is used in the Select statement of an object, it specifies the path of another object in the universe as a parameter of the @Select function, in the form Class_Name\Object_Name. This then acts as a pointer to the Select statement of the referenced object.
@Select(Classname\Objectname)

Note:
When you use @Select and @Where functions, one object now depends on another in the universe. You have created a new object dependency. When one object is deleted, the other object using the @Select or @Where function needs to be manually updated.

@Where: You can use the @Where function to re-use the Where clause of another object. When the @Where function is used in the Where clause of an object, it specifies the path of another object in the universe as a parameter of the @Where function, in the form Class_Name\Object_Name. This then acts as a pointer to the Where clause of the referenced object. Using the Where clause creates a dynamic link between two objects. When the Where clause of the original object is modified, the Where clause of the referencing object is automatically updated.

16) What is multidimensional analysis?
Multidimensional analysis is the analysis of dimension objects organized in meaningful hierarchies. Multidimensional analysis allows users to observe data from various viewpoints. This enables them to spot trends or exceptions in the data. A hierarchy is an ordered series of related dimensions. An example of a hierarchy is Geography, which may group dimensions such as Country, Region, and City.
In Web Intelligence you can use drill up or down to perform multi dimensional analysis.

 

17) What are external strategies?
An external strategy is an SQL script stored externally to the .UNV file, and structured so that it can be used by Designer to automate object or join creation, and table detection tasks in a universe. External strategies are stored in an external strategy file with the extension STG. External strategy files are in XML format. There is one for each supported RDBMS.

18) What is Aggregate Awareness? Aggregate awareness is a term that describes the ability of a universe to make use of aggregate tables in a database. These are tables that contain pre-calculated data. You can use a function called @Aggregate_Aware in the Select statement for an object that directs a query to be run against aggregate tables rather than a table containing non aggregated data. Using aggregate tables speeds up the execution of queries, improving the performance of SQL transactions.

The reliability and usefulness of aggregate awareness in a universe depends on the accuracy of the aggregate tables. They must be refreshed at the same time as all fact tables. A universe that has one or more objects with alternative definitions based on aggregate tables is said to be “aggregate aware”. These definitions correspond to levels of aggregation. For example, an object called Profit can be aggregated by month, by quarter, or by year. These objects are called aggregate objects. Queries built from a universe using aggregate objects return information aggregated to the appropriate level at optimal speed.
@Aggregate_Aware(sum(agg_table_1), ... sum(agg_table_n))

 

19) What are incompatible objects?
You must now specify the incompatible objects for each aggregate table in the universe. The set of incompatible objects you specify determines which aggregate tables are disregarded during the generation of SQL. With respect to an aggregate table, an object is either compatible or incompatible. The rules for compatibility are as follows:
• When an object is at the same or higher level of aggregation as the table, it is compatible with the table.
• When an object is at a lower level of aggregation than the table (or if it is not at all related to the table), it is incompatible with the table

.20) What is a restriction set?
A restriction set is a named group of restrictions that apply to a universe. You can apply a restriction set to a selected group or user account for a universe. When users connect to a universe, the objects, rows, query types, and connection that they use in the universe are determined by their applied restriction set. You can create, edit, and delete a restriction set at any time once the universe has been exported to the CMS. You can create multiple restriction sets depending on the query needs of a target user group

.

21) What are linked universes?
Linked universes are universes that share common components such as parameters, classes, objects, or joins. When you link two universes, one universe has the role of a core universe, the other a derived universe. When changes are made in the core universe, they are automatically propagated to the derived universes.

22) What is a core universe?
The core universe is a universe to which other universes are linked. It contains components that are common to the other universes linking to it. These universes are called derived universes. The core universe represents a re-usable library of components. A core universe can be a kernel or master universe depending on the way the core universe components are used in the derived universes.

23) What are derived universes?
A derived universe is a universe that contains a link to a core universe. The link allows the derived universe to share common components of the core universe:
• If the linked core universe is a kernel universe, then components can be added to the derived universe.
• If the linked core universe is a master universe, then the derived universe contains all the core universe components. Classes and objects are not added to the derived universe. They can be hidden in the derived universe depending on the user needs of the target audience.

24) How are universes optimized?
Query time can often be shortened by optimizing a universe. There are several ways you can optimize a universe:
• Optimizing the Array Fetch parameter in the Universe Parameters.
• Allocating a weight to each table.
• Using shortcut joins.
• Creating and using aggregate tables in your database.

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.

25) Different ways to link universes?
The following approaches when linking universes:
• Kernel approach
• Master approach
• Component approach

Kernel approach: With the kernel approach, one universe contains the core components. These are the components common in all universes. The derived universes that you create from this kernel universe contain these core components as well as their own specific components.

Master approach: The master approach is another way of organizing the common components of linked universes. The master universe holds all possible components. In the universes derived from the master, certain components are hidden depending on their relevance to the target users of the derived universe. The components visible in the derived universes are always a subset of the master universe. There are no new components added specific to the derived universe.

Component approach: The component approach involves merging two or more universes into one universe. The Sales universe below was created by merging two universes: Part 1 and Part

26) Explain about detail objects?
Dimensions are focus of analysis in a query. A dimension maps to one or more columns, functions in the database that are key to a query. Detail Objects provides descriptive data about a dimension. A detail is always attached to a dimension. It maps to one or more columns or functions in the database that provide detailed information related to a dimension. One cannot drill on details nor link on details when linking multiple data providers. While Customer ID would be a dimension, customer name, address, phone and soon should be details.

27) What is business objects repository?
Business Objects Repository:
Ø It is a semantic layer which stores the BO Users and their privileges.
Ø Repository means set of database tables, Business object store security information e.g user, group, access permission, user type etc. , universe information e.g. objects, classes, table name, column name, relationship etc and document information.
Ø Repository contains data accounts with three domains in each one type. You can create the repository anywhere. The security domain has the user security info and other domains address.

28) What are domains?
Domain is nothing but logical grouping of system tables. There are three domains usually in a basic setup they are Secure, Universe, Document. In 6.5 Universe are situated in one domain, Reports in one domain and Supervisor.

29) When is the Repository created?
In 5i/6i versions repository is created after installing the software, whereas in XI version a repository is created at the time of installation
 
Join Information

Equi Join: An equi-join links two tables on common values in a column in table 1 with a column in table 2. The restriction conforms to the following syntax:
Table1.column_a = Table2.column_a
In a normalized database the columns used in an equi-join are usually the primary key from one table and the foreign key in the other

Theta Join: A theta join is a join that links tables based on a relationship other than equality between two columns. A theta join could use any operator other than the “equal” operator.

Outer Join: An outer join is a join that links two tables, one of which has rows that do not match those in the common column of the other table. You define an outer join by specifying which table is the outer table in the original equi-join. The outer table contains the column for which you want to return all values, even if they are unmatched.

Shortcut join:A shortcut join is a join that provides an alternative path between two tables.
Shortcut joins improve the performance of a query by not taking into account intermediate tables, and so shortening a normally longer join path.

Self restricting joins: A self restricting join is not really a join at all, but a self restriction on a single table. You can use a self restricting join to restrict the results returned by a table values using a fixed value.