Universe Components

---

BusinessObjects administrators build universes using Designer. The key components of a universe are classes, objects, tables, joins, and contexts. As shown in Figure 5-2, classes and objects are the main items a business user sees when building a query. Objects become individual columns in a report; classes never appear in a report.

Figure 5-2: Business users see classes and objects from the Query panel. The Price objects have been placed under the Product class but could also have been placed under Measures.

Classes

Classes are a way of grouping individual objects. In Figure 5-2, these appear with a folder icon. Sometimes these relate closely to the tables in a database but should be regrouped into business topics. In the sample EFASHION universe, the class Product is a more meaningful business term than Article and includes items from multiple tables ARTICLE_LOOKUP and ARTICLE_COLOR_LOOKUP.

Objects

Objects refer to columns of data. There are different types of objects (as explained further in Chapter 8) denoted with a square, sphere, or triangle icon in Figure 5-2. Objects can include a significant amount of intelligence and may not relate directly to one column in the database. For example, the object Sold At (Unit Price) includes a calculation of revenue/quantity. However, to avoid divide by 0 errors, it also includes an if-then-else statement to check for 0 quantities. This is one example of why Business Objects universes are so powerful and a much better alternative to providing users with direct access to tables; if-then-else statements in SQL are implemented differently for each RDBMS and are not something most users would know how to write.

Tables, Joins, and Contexts

Report authors never directly see several core elements of a universe: tables, joins, and contexts (see Figure 5-3). Universe designers use tables to map data from fields to objects in the universe. Joins allow the use of more than one table in a report, and contexts resolve which join path to take when more than one path is possible. All three of these components are then combined to dynamically build SQL statements in BusinessObjects.

Figure 5-3: Tables, joins, and contexts are core elements of a universe that only the administrator sees in Designer.

Another strength of BusinessObjects is its ability to support almost any physical table design. As shown in Figure 5-4, most data warehouses use star schemas to ensure fast queries. However, some use snowflake schemas to make for smaller dimension tables. OLTPs use normalized schemas to eliminate data redundancy and speed data inputs (but which make for very slow queries). BusinessObjects supports all three of these designs, alone or in combination.

Figure 5-4: Business Objects supports star schemas, snowflake schemas, and normalized table structures.

Tables

Tables are individual database tables that provide data. A table may be a physical table in the RDBMS, or it may be a view or synonym. Further, Designer provides functionality to create aliases that are treated like tables.

In a data warehouse or data mart environment, you will have two types of tables: 1) a fact table that contains numeric information and 2) dimension tables that allow a user to analyze the numeric data from different perspectives such as product, time, or geography. The fact table can have millions of detailed rows of data or can be smaller, with summary numbers. One fact table together with its associated dimension tables is referred to as a star schema. There can be multiple fact tables and star schemas within a universe.

Dimension tables are also referred to as lookup tables or reference tables. The dimension tables can be broken into more than one table; for example, detailed material IDs may reside in a MATERIAL_ID table. The groupings and product hierarchy for the material IDs may reside in a separate table such as PRODUCT_GROUPING. This type of structure, referred to as a snowflake design, is used in some data warehouses that have extremely large dimensions as well as certain ROLAP tools.

In a normalized OLTP, both the fact tables and the dimension tables may be spread across many tables. For example, order information may exist in both an ORDER_HEADER table and an ORDER_LINES table. Dimensions and hierarchies often do not exist in the OLTP (note in Figure 5-4 that there is no Time or Plant table, just the individual facility that produced the product). Only the individual material IDs, customer IDs, and so on, are stored with detailed records. BusinessObjects does not allow a universe to point to two different databases, so having data that users want to analyze together generally calls for a data warehouse or data mart. However, if this is not immediately possible, BusinessObjects provides a workaround in the end-user tool. You, as the designer, can create two separate universes: one that points to the OLTP and one that points to the dimension database. Users then would have to build two queries; however, as long as the detailed key information is named consistently between the universes, the results will be nicely displayed in one table, without the user having to manually stitch the two result sets together. This technique is discussed further in Chapter 22.

When you build a universe, you are not replicating any data from these tables. Instead, you are basically creating pointers to tell BusinessObjects where to find the data; no data is stored in the universe itself. This is a drastically different approach than a full MOLAP tool such as Hyperion Essbase, Cognos Powerplay, or Microsoft Analysis Services. Data gets replicated only when a BusinessObjects user launches a report and the RDBMS sends results back to the report, populating a micro cube in a .rep file on either the WebI middle tier or the Windows client.

Joins

Joins specify how tables, views, synonyms, or aliases relate to one another. Joins allow a user to combine information from two or more tables. For example, in the following diagram, there are joins between ORDERS_FACT and the dimension table PLANT as well as between ORDERS_FACT and the dimension table PRODUCTS. There are no joins to the SUPPLIERS table. Without this join, a user is not able to determine which suppliers provide various products. There are many types of joins; they are discussed further in Chapter 7.

Contexts

Contexts group related joins. A context may group a set of joins together for each star schema. Without contexts, BusinessObjects would generate SQL that contained a loop. Loops generally result in incorrect queries with fewer rows returned than expected. Earlier versions of BusinessObjects supported queries that contained only one context. As contexts were generally confusing for end users, they were best avoided. BusinessObjects now allows one query to generate multiple SQL statements, one for each context. This allows users to query multiple star schemas to create powerful business reports. Two examples follow.

Days Sales Inventory (DSIs) How many days worth of inventory do you have according to the daily sales volume? As shown in Figure 5-5, this query would involve two contexts, one with all the joins for the star schema with a SALES_FACT table and a second context with all the joins related to INVENTORY_FACT.

Figure 5-5: A context groups a set of tables into one star schema.

Account Balance/Debit/Credit While MOLAP tools allow an administrator to specify measures that relate to one point in time versus a period in time, SQL-based tools cannot. Contexts provide a workaround, as a user can run a query, selecting individual month-end balances from an ACCOUNT_FACT table and then requesting summaries for all 30 days from a DAILY_DEBIT_CREDIT_FACT table. The results are again stitched together dynamically in the report. Contexts are discussed further in Chapter 7.

---