Universe Parameters

---

The universe parameters provide information about your universe as well as allow you to change universe behavior such as how long a query can run and the type of SQL BusinessObjects generates. To modify the universe parameters, bring up the Universe Parameters dialog box by selecting File | Parameters.

Definition

From the Definition tab, you see the name of the universe as well as the connection you specified when building the universe through the wizard. The Description is an underutilized feature of BusinessObjects. Users see the description as a Help box whenever they create a new report and select a particular universe. Therefore, the Description box is an excellent place to provide users with information about the purpose of the universe and the target business user group, as well as timely information such as when data was last updated or if there are any data integrity issues.

Connections

Universe connections can be a great source of confusion for new universe administrators as well as DBAs. The connection in the universe parameters can be the same as or different from the connection used to validate your BusinessObjects logon (usually it is different). The parameters you specify will depend on how you decide to deploy BusinessObjects, where you store the repositories, and where your data sources are located. When you click the Edit button, BusinessObjects presents you with a dialog box that lets you customize the logon, advanced, and custom settings.

BusinessObjects Architecture

To understand how the universe connections work, you must first understand how BusinessObjects is deployed and how security in the various data sources, whether OLTPs or data marts, interact.

Scenario 1: Let's assume that you are deploying BusinessObjects for a relatively small group of users who wish to access information in a source system-there is no data mart. As Figure 6-1 shows, the BusinessObjects repository resides in a PC database such as MS Access. The source system data resides in an enterprise-wide RDBMS such as Oracle, DB2, or Teradata. When BusinessObjects was first installed, the supervisor created security and universe domains but did not specify a user ID and a password for the user. The only information stored in locdata\BOMain.key is the location of the PC database. The supervisor also granted each individual user a BusinessObjects user ID and password combination, Cindi/ASK, for example. The source system database has its own set of user IDs and passwords (Chowson/Secret). This leaves us with the potential of having three different sets of user IDs and passwords. Users need to know only two of them: the BusinessObjects user ID/password and the source system user ID/password.

Figure 6-1: In this scenario, there are two sets of user IDs and passwords involved in BusinessObjects: the BusinessObjects login and the source system database login.

Scenario 2: As shown in Figure 6-2, many companies use a data warehouse environment rather than accessing data directly in an OLTP. The BusinessObjects repository tables may be physically installed on the same machine as the data warehouse tables. Each set of tables may reside in its own database instance and may require a unique user ID and password combination. In Figure 6-2, the BOMain.key contains a generic logon user ID and password (BOREPOS/BOSECURE) as well as the name of the database instance.

Figure 6-2: In the second scenario, there are three sets of user IDs and passwords involved: the repository connection, the BusinessObjects logon, and the data warehouse access.

Users never see the userid/password stored in BOMain.key; this user ID and password combination never appears to users (or universe designers) and is used only to log a user onto the BusinessObjects repository to determine which products, universe, and documents each user can access and modify. The BOMain.key file lets BusinessObjects connect to the physical repository.

In this respect, the BOMain.key file uses a shared logon to the repository. DBAs and enterprise security administrators get nervous about shared logons. However, if they understand the purpose of this particular user ID and password, they see that there is no reason ever to assign a unique logon to the repository. In fact, using anything other than a shared logon in BOMain.key is sure only to increase the number of error messages a user receives.

Which Connection to Use in a Universe

When you specify the user ID and password in the universe connection parameters, the administrator must understand how these IDs and passwords interact and carefully consider ease of use, security, usage tracking, and cost. Table 6-2 summarizes the alternatives and benefits/issues of each approach-the best alternative depends on ease of use, security, tracking requirements, and cost.

Table 6-2: BusinessObjects Allows for Different User IDs and Passwords Between the Repository and the Data Source(s)

Alternative	Business Objects ID	Source System Database	Issues
1	Cindi/ASK	Chowson/Secret	This approach appears secure but is not feasible. This situation may occur when the BusinessObjects administrator and source system DBAs do not cooperate. Passwords also can become out of synch when users change their BusinessObjects password and not the data warehouse or OLTP password.
2	Cindi/no password	Cindi/Secret	Easy to administer, and users do not need to remember different passwords. BusinessObjects usage tracking is accurate only if users do not share PCs or logon IDs. Security can be done only at the source system level, which is acceptable only if documents in the BusinessObjects repository do not contain confidential data.
3	Cindi/ASK	BOshared/ BOsecret	This is fairly easy to maintain and allows BusinessObjects administrators to track usage, but source system DBAs cannot track usage by individual user. Row- and column-level security must be done within BusinessObjects Supervisor.
4	Chowson/Secret	Chowson/Secret	This provides the most secure setup with the fullest usage tracking. However, it also increases maintenance, as user IDs and passwords must be manually maintained between the two systems. The BusinessObjects Supervisor should not allow users to change their passwords, or they may become out of synch with the source system. In defining the connection parameters, select the check box Use BusinessObjects User Name And Password.

• Ease of usage If users cannot remember any assigned password or if they put in the wrong password for either the BusinessObjects logon or the data access logon (OLTP or data warehouse) and their access becomes disabled, they will blame BusinessObjects for being too difficult to use or not working.

• Security Much of what is in the BusinessObjects repository is not company confidential; it's not data. The exception to this is the document domain, which your company may or may not use. For many full client installations, the document domain essentially contains templates without data. Duplicating the data with the report templates can cause the document domain to become huge, so it is advisable only when you are trying to minimize access to the source system. In a WebIntelligence (WebI) implementation, reports in the document domain often do contain data and should be protected with a password. Clearly, data in the source system needs to be protected from unauthorized access.

• Usage tracking Beyond these security aspects, the primary reason for requiring a BusinessObjects user ID and password is to track usage of BusinessObjects components: reports, universes, users, and so on. This cannot be strictly accomplished at the data source level. Tracking usage at the data source level will tell you how often a user accessed a particular table, but it will not tell you that the user had a report open for two hours and that the user actually combined data from two different source systems. Only BusinessObjects can do this. Conversely, if you decide to use a shared login from BusinessObjects to the source system database (alternative 3 in Table 6-2), it makes it harder to control runaway queries, particularly in a full client implementation. In a full client implementation, a source system DBA would have to kill the query and would not be able to identify the individual user who launched the query. In a WebI implementation, the WebI administrator would be able to see whose query has been running so long, as the rogue query would be consuming both WebI resources and source system database resources.

For this reason, many companies assign user IDs and passwords at both the BusinessObjects and data source levels. This also allows the source system RDBMS to handle row-level and column-level security.

• Cost Finally, another consideration in choosing between unique source system IDs or a shared one is your RDBMS licensing. If your license is by named user, then it is more cost effective to use a shared database logon for data access. You will still need to pay for the appropriate number of concurrent licenses, a price that, if your data warehouse is well tuned, can be relatively low with BusinessObjects. Many BI tools will keep the database connection active for longer period of times, thus increasing the number of RDBMS licenses required. Designer enables you to modify these settings to keep concurrency against the data source low.

In evaluating the ease of use, security, usage tracking issues, and cost issues, I prefer alternative 3 or 4 described in Table 6-2. Alternative 1 may happen inadvertently and will produce errors when users attempt to refresh a query. Alternative 2 is feasible when different source databases require different passwords. The BusinessObjects supervisor disables password checking; however, the password specified during BusinessObjects login still gets passed through to the RDBMS. Prior to refreshing a query for a second data source that requires a different password, the user must log out of BusinessObjects and login again to enter the password for the second data source.

Caution 

Always, always test your connection when changing the user ID, passwords, or database name. Just when you think you've typed it perfectly is when you make a mistake, rendering everyone's universe unusable!

The Advanced tab of the Connection dialog box provides additional options that affect users during query execution. These options and their defaults will vary depending on which database you are using. Under Connection Properties, if you keep the connection active during the whole session, user queries are never slowed down for the source system/data warehouse logon and logoff process. This is great for users but can become expensive for RDBMS licensing and WebI overhead. Conversely, disconnecting after each transaction can make repeated querying for users appear unnecessarily slow as the logon process is added to each data refresh. A good balance is to allow the connection to be active for ten minutes.

In the vendor-provided database guides, Business Objects recommends avoiding the Use Asynchronous Mode option. Asynchronous mode allows a user to press ESC to cancel a query while the database is still analyzing the query (otherwise, users can cancel the query only during the fetch stage, when data is being sent across the network). Business Objects recommends disabling this, because while a user may cancel a query on his or her PC, the query will still be running on the database. However, if a user has been waiting an unexpectedly long time for the query to complete, the user may simply reboot his or her PC and start over; this essentially has the same effect on the source system database as if the user had simply pressed ESC to begin with. Therefore, I recommend using the asynchronous mode in conjunction with performing the cost estimate. In WebI, asynchronous mode is not supported. Connection Mode options are not available in ODBC.

The cost estimate feature is available in some databases and is database specific (at the time of this writing, Sybase and MS SQL do not have it). To enable cost estimates in Oracle, the DBA must first create a PLAN_TABLE using the Oracle-supplied utlxplan.sql file. Secondly, the DBA must analyze the database schema on a regular basis or whenever major changes are made to the database. Finally, the DBA must allow each individual user (or the shared BusinessObjects user ID as described in Table 6-2, Alternative 3) write access to the PLAN_TABLE. The BusinessObjects administrator also must enable two parameters:

• Cost Estimate Feature Under Parameters | Connection | Advanced

• Warn If Cost Estimate Exceeds Under Parameters | Controls

The last Advanced option you may want to change is the array fetch size. This setting determines how many rows of data can be shipped back to the client in one fetch. Increasing the fetch size may get the results to appear faster on the user's PC. However, higher settings consume more memory, and the actual response times also depend on network load and server load. If you are experiencing slow fetches, try increasing the number by increments of 10.

The Custom tab enables you to make additional changes that are not available in the Advanced tab. They are database specific and rarely used. Your DBA may suggest changing certain custom parameters.

The Summary Tab

The Summary tab of the Universe Parameters dialog box provides information about the universe, such as the number of classes, objects, and joins. Additionally, it provides a last modification date, the name of the modifier, and a version number. Some companies will create one BusinessObjects administrator ID that people share (against the advice of security personnel). In order to take advantage of the revision information provided in the Universe parameters, it's important that administrators not share logon IDs.

Tip 

The Comments section in the Summary tab allows an administrator to include additional technical notes on the universe. BusinessObjects users do not see these notes. This makes the Comments section an excellent place to store detailed revision notes for change control purposes.

The Strategies Tab

The Strategies tab of the Universe Parameters dialog box defines how the universe uses information from the data dictionary to help you build the universe. Next to the Connection information, the Strategies can prove to be the greatest challenge in first developing universes. Although BusinessObjects provides some very powerful strategies to get you started, if you have a data warehouse or source system architecture that uses public synonyms (most do), you will need to modify them or Designer will never see those tables.

The strategy information is used during a number of Designer activities:

• During the wizard's universe build

• If you use the Insert button or menu to insert tables, joins, or candidate objects

• In automatically creating joins and objects, if your database options use this setting (discussed later in the section 'Database Options that Relate to Strategies')

Although your source system DBA will generally help you customize these strategies, as a BusinessObjects administrator, the more you understand how they work, the better you can advise the DBA on the best approach to customization.

Strategies are SQL statements that read the data dictionary tables in your source system. The queries then use that dictionary information to present you with a list of initial classes, objects, joins, and tables. So if all your data warehouse tables start with DW, for example, then your initial proposed classes will also start with DW (such as Dw Product). If you do not want the user to see this DW (not recommended), then you can modify your strategy file to drop the DW from the proposed classes.

There are three parts of a universe that strategies interact with: objects, joins, and tables.

• Objects The object strategy determines how Designer reads the table and column information from the data dictionary to come up with some initial classes and objects (either when using the wizard or when inserting tables). By default, the table names become the proposed class names and the individual columns within each table become the objects in your universe. The default object strategy also converts all names to initial caps and removes the underscore (_) from any table and field names.

• Joins Designer has three built-in approaches to automatically create joins, listed next.

• Smart Matching Column Names (no key info) If the names of two columns match exactly, then Designer will propose a join between the two. Recall that the WEEK_KEY columns in our initial universe did not match exactly. Key was initial caps in one instance and lowercase in the second table. The columns must also be a primary key in each of their tables.

• All Matching Column Names This strategy appears to work the same as the preceding one.

• All Matching Numeric Column Names This strategy works well if your tables contain a lot of foreign keys. Designer will look for numeric columns that are named exactly the same and propose joins between the two.

• Tables This part of the strategy is probably your most important part. If Designer cannot find your necessary tables, it can never move to the next step of creating objects and proposing joins. Information collected from the table part of the strategy is displayed in the structure window, such as the column name, data type (Numeric, Character, Date), and key.

In earlier versions of BusinessObjects, the SQL used to find the tables in the RDBMS was quite seamless. With version 4, BusinessObjects switched to this approach of built-in strategies and external strategies. Thus, the exact SQL of the built-in strategies is no longer viewable. However, Designer basically selects all physical tables owned by the individual user specified in the connection parameters. For example, if the owner of the tables in your Central Data Warehouse is CDW, then you must log on with the user ID CDW for Designer to find these tables.

You can change the OWNER=N parameter in your database.prm file. This will drop the owner name from your table prefix and will cause the built-in strategies to look for public synonyms and views (for example, the username specified in the connection parameters does not necessarily own the physical tables). However, setting OWNER=N without customizing an external strategy can also pick up a lot of clutter such as system tables. Therefore, you will still need to use or create an external strategy.

External Strategies

External strategies appear under each of the drop-down options for objects, joins, and tables. However, you create them outside of BusinessObjects with any text editor such as Notepad.

The parameter for your particular database contains a setting on the STG line that tells BusinessObjects where to find the SQL for these strategies. As an example, in an Oracle database, the file ora7EN.prm contains the line STG=STORA7EN. STORA7EN is the default strategy file. Parameter files are database specific and located in c:\Program Files\BusinessObjects\Data Access 5x\specific database. The files use the following naming convention: StnnnnEN.txt where St means Strategy, nnnn is the database abbreviated name, and EN is the English language version.

The strategy file requires a particular format as shown in the example that follows. The TYPE must be OBJECT, JOIN, or STRUCT. STRUCT refers to strategy files that generate the table information. The NAME section is the name that will appear in your strategy drop-down menu-for example, "Smart Matching Columns." The SQL section is the core of the strategy. This is where, if you don't like DW appearing in each class name, you use LTRIM to cut it.

If your source system uses L33 for every column involving a customer, use REPLACE to replace L33 with CUSTOMER. The HELP section is optional and provides a more detailed explanation of how this strategy works. The Help information appears in the drop-down box of the strategy selection.

[STRATEGY] TYPE=JOIN NAME= External Strategy: Same Column Name [SQL] Enter your customized SQL statements here, ensuring the correct number of columns are generated as shown in Figures 6-4 through 6-6. [HELP] HELP= This join strategy reads the database constraints and provides a list of joins.

Figures 6-3 through 6-6 show how Designer reads information from the RDBMS' data dictionary tables to create objects and joins and to display information about tables in your universe. Figure 6-3 shows the raw data dictionary information. The example uses information about the demo SALES table in an Oracle database. The dictionary tables that provide the information are USER_TABLES, USER_TAB_COLUMNS, and USER_COL_COMMENTS. Figures 6-4 through 6-6 show the required output layouts for objects, joins, and tables. For your external strategies to work, you must use the exact output layout. If you forget a column or mix up the order, you will get unusual results or an error.

Figure 6-3: Strategies uses SQL to read the data dictionary tables.

Figure 6-4: Object strategies propose the initial classes and objects and require nine columns of information. Consider creating an object strategy using spreadsheet data from a JAD session.

Figure 6-5: Join strategies propose joins between tables and use five columns of information. Three are required, two are optional.

Figure 6-6: Table or structure strategies require six columns of information and are used to provide information in the structure window of the universe.

Note 

Accessing USER dictionary tables can be a problem in strategies if the username in the connection parameters is not the owner of the tables, views, or synonyms. Often synonyms are set up as PUBLIC. In such cases, use ALL_ dictionary tables to find synonyms and use WHERE clauses to find the appropriate database objects.

Figure 6-4 shows the exact object layout that your SQL strategy must generate for Designer to propose initial classes and objects. As mentioned previously, it's rare to derive business-oriented classes and object names from data mart tables (and certainly not from OLTP tables). However, this section of an external strategy file is very helpful in ensuring your SQL statements are correct. Also, none of the built-in or sample strategies leverage the comments column in your RDBMS. If your DBA or ETL tool has used the comments column in your RDBMS, the comments column can be a good starting point to provide meaningful descriptions/help text for individual objects. Finally, the object strategy file is your best bet for recycling in a spreadsheet file that users can easily modify.

As an example, let's assume that following a JAD session, you have a documented list of initial universe requirements. Following the universe design guidelines discussed in Chapter 5, you carefully review this list to remove unwanted items and propose new ones. All these changes are documented in a spreadsheet. At this point, you, as the universe designer, can insert columns into the spreadsheet to get the physical table and column names and a formula (example: +A1&'.'&B1) to generate the SQL SELECT statement. You can then create an external objects strategy that creates objects based on a text output of your JAD spreadsheet. This process can more quickly give you a working universe prototype than using the built-in strategy provided by BusinessObjects, simply because your new strategy uses business terms rather than generic OLTP or data warehouse terms.

The following table or STRUCT strategy was created by an oil and gas company that uses Public synonyms in its data warehouse. One of the risks of reading dictionary information from the ALL_TABLES, etc., dictionary tables is that the SQL will generate information about system tables, clearly information you do not need in a business universe. This strategy file does a good job of limiting the information generated, thus making structure refreshes faster.

[STRATEGY] TYPE=STRUCT NAME= DBLINK - To PRD5 [SQL] SQL=SELECT       'REPT', '|',       S.TABLE_OWNER, '|',       S.SYNONYM_NAME,'|',       U1.column_name,'|',       decode(SUBSTR(U1.DATA_TYPE,1,1),'N','N','F','N','D','D','C'),'|',       '','|' FROM ALL_TAB_COLUMNS U1, ALL_COL_COMMENTS U2, ALL_OBJECTS O, ALL_SYNONYMS S WHERE    S.table_owner=O.owner AND  S.table_name=O.object_name AND  (O.OBJECT_TYPE='TABLE' OR O.OBJECT_TYPE='VIEW') AND  O.owner=U1.owner AND  O.object_name=U1.table_name AND  U1.owner=U2.owner AND  U1.table_name=U2.table_name AND  U1.column_name=U2.column_name AND  S.table_owner NOT IN ('SYSTEM', 'MDSYS', 'ORDSYS', 'SYS') AND  S.DB_LINK IS NULL UNION SELECT       'PRD5','|',       S.TABLE_OWNER, '|',       S.SYNONYM_NAME,'|',       U1.column_name,'|',       decode(SUBSTR(U1.DATA_TYPE,1,1),'N','N','F','N','D','D','C'),'|',       '','|' FROM [e-mail] U1, [e-mail] U2, [e-mail] O, ALL_SYNONYMS S WHERE    S.table_owner=O.owner AND  S.table_name=O.object_name AND  (O.OBJECT_TYPE='TABLE' OR O.OBJECT_TYPE='VIEW') AND  O.owner=U1.owner AND  O.object_name=U1.table_name AND  U1.owner=U2.owner AND  U1.table_name=U2.table_name AND  U1.column_name=U2.column_name AND  S.table_owner NOT IN ('SYSTEM', 'MDSYS', 'ORDSYS', 'SYS') AND  S.DB_LINK = 'PRD5.WORLD' ; [HELP] HELP= DBLINK - To PRD5 

Database Options that Relate to Strategies

The settings on the Strategies tab also relate to the Designer's Database Options under Tools | Options | Database. In the Strategies tab, even if you tell BusinessObjects to use Smart Matching based on the column names, BusinessObjects will automatically create the joins in the universe only if you check the box Extract joins with tables in Options. (Yes, it would be more intuitive if this were in the same place as the Strategy settings!) The main difference between Parameters and Database Options is that Parameters are specific to the individual universe, whereas the Options apply to Designer, and, therefore, all universes created on the PC. But I confess that I still struggle to remember where each setting is because they are so interdependent!

When Designer detects cardinalities, it analyzes the rows in each table to determine where the one-to-one, one-to-many, and many-to-many relationships are. Leaving the Detect Cardinalities In Joins box checked on large databases can cause a very slow structure creation. I'm fairly impatient, so I will leave the cardinalities unchecked during an initial build and will detect the cardinalities at a later point.

The last default creation option, Create default classes and objects from tables, should probably never be checked unless you have a very small star schema and you have modified your strategy to read only that schema. Additionally, your column names in your data mart must be based on business terminology, and even then, you will still need to delete a lot of extraneous objects and move them around to more meaningful classes. Otherwise, you will very quickly have a lot of garbage in your universe.

Table and column values is a useful feature to show you a sample of the data in your tables as you build objects in the universe. As a default, Designer will present you with the first 100 values in your database.

Controls

The Controls tab of the Universe Parameters dialog box enables you to specify additional limits that will affect individual users' queries. Limit size of result set to: prevents the user PC and the wide area network from becoming saturated with too many rows of data. It does not reduce the load on the source system database. Therefore, leave this box unchecked or increase it to a larger number such as 300,000 rows.

Limit execution time to: is another check box to use carefully. This option limits the time the PC is tied up, but it will not limit the time the database is affected. So if an administrator sets the limit to 60 minutes, the database could actually try to run the query for 70 minutes and will give the user an error message only after 70 minutes.

Note 

If your queries are running this long, you need to do some serious index optimization or encourage users to use the Broadcast Agent (refer to Chapter 20).

As described previously, Warn if cost estimate exceeds: is an excellent option for giving users a warning for long-running queries. As of this writing, BusinessObjects is the only BI tool that allows users to take advantage of this RDBMS capability. Users get to cancel the query before it hits the source system database and ties up resources unnecessarily. Set the time limit for this option high enough that users do not receive the following error message every time they refresh a query, yet low enough to allow them to receive a useful warning for particularly slow queries.

Limit size of long text objects to: is useful for blob fields or for very long variable- length fields. Users can adjust their column widths if they do not want to see the full text. However, if you as the administrator set this number too low, users cannot override it; they never can get to the data at the end of a long field.

SQL

When deploying BusinessObjects directly against an OLTP, you may want to limit complex SQL queries so that they do not affect response time for inputting data. Otherwise, I prefer to leave complex SQL enabled in pure reporting databases. Casual users will not know what to do with them and can ignore the options. However, if you disable advanced SQL, then power users may get frustrated. The following three options in the SQL tab of the Universe Parameters dialog box allow you to limit complex queries:

• Allow Use Of Subqueries Subqueries are a powerful type of query that allows users to nest one query within a main query (see Chapter 22). As these queries are complex and use additional RDBMS resources, administrators can remove this capability. By default, leave it enabled.

• Allow Use Of Union, Intersect, And Minus Operators These operators allow advanced users to combine multiple SQL statements into one report (see Chapter 22). By default, leave it enabled.

• Allow Complex Operands In Query Panel This option is similar to the preceding one but allows users to select the conditions from the query panel: Complex operands are Both and Except. Both generate an INTERSECT query, and Except generates a MINUS query.

The Multiple Paths options determine the kind of SQL generated behind the scenes. The users may not see the SQL, but they do affect the performance and accuracy of any given query.

• Multiple SQL Statements For Each Context Contexts were discussed briefly in Chapter 5 and are explained in detail in Chapter 7. This option should be checked so that a separate SQL statement is generated for each context or star schema. If your universe has multiple contexts and you do not enable this box, users will receive an error message when trying to create a query that contains measures from two different contexts. In the EFASHION universe, for example, one could not create a query that analyzes promotion costs and sales revenue for a given product.

• Multiple SQL Statements For Each Measure This box should always be enabled or queries from multiple fact tables may produce incorrect results. In the EFASHION universe, unit sales price and extended sales price are from two different tables. It's also useful to check this option even if you think you have one central fact table. For example, you may later create measures such as number of days or number of products that go against dimension tables. BusinessObjects will create a Cartesian product and give incorrect information if your measures come from more than one table (discussed further in Chapter 12).

• Allow Selection Of Multiple Contexts Enables users to create queries on objects from multiple contexts. This box should be enabled. In the EFASHION universe, it allows users to analyze sales and promotions in one report; sales and promotions are two different contexts.

  

The Multiple SQL statements for each measure option has some nuances that are important to understand. First, even if this box is enabled, if the measures come from the same fact table, BusinessObjects will issue only one SQL statement. This is a good thing as it avoids tying up the database unnecessarily. Second, BusinessObjects will only issue two SQL statements if the object is a measure (discussed further in Chapter 8) and it contains an aggregate function (sum, count, and so on). The EFASHION universe is somewhat misleading, as a unit price should never contain a SUM. However, it still illustrates the risk of not splitting the measures when multiple fact tables are involved.

When SQL joins two tables together, it will repeat each row for each combination in the GROUP BY section. Figure 6-7 illustrates how this happens. The ARTICLE_LOOKUP table has only one row and shows a price of 114.55. The SHOP_FACTS table has six rows showing that this style blazer sold six times during week 8 of year 2000. When SQL joins ARTICLE_LOOKUP with SHOP_FACTS, the 114.55 unit price will get repeated six times and summed to 687.30; an incorrect result. This is not a problem of BusinessObjects; it is an issue of understanding how SQL works and ensuring that you have developed your underlying data mart and universe to give users correct results. The following report shows the results depending on which option is set in the SQL universe parameters:

Figure 6-7: SQL may give incorrect results if you try to use a GROUP BY function such as SUM across two tables that have different numbers of rows. To prevent this inaccuracy, check the box Multiple Select Statements For Each Measure in the universe parameters.

Links

The last tab of the Universe Parameters dialog box, Links, allows an administrator to create a master universe that is then linked to other universes. As this is an architecture and maintenance issue, links are described more fully in Chapter 14, 'Linked Universes.' By default, leave this tab blank.

---