Types of Objects
|
| < Day Day Up > |
|
Objects can be one of three types: dimension, measure, or detail.
Dimension
A dimension object is denoted with a blue cube and is typically textual information by which users analyze numeric measures. A dimension object often comes from the lookup or reference tables within the universe. Dimensions are typically character or date information or numeric codes such as product numbers and customer numbers.
Some universe developers will mistakenly point their dimension object to the fact table.
If your data warehouse uses keys for reference information, then your dimension objects will point to a lookup table. However, if your universe accesses a transaction system or if meaningful codes and IDSs are stored in your fact table, you face a decision whether to point the dimension object to the dimension table or to the fact table. Some universe developers will mistakenly point the dimension object to the fact table, trying to reduce the number of joins. Others will duplicate the object for each occurrence in a different table. Don't do it. Create one dimension object that points to the lookup table.
 |
points to the fact table rather than a dimension table?
In theory, if a user creates a query that contains a product ID and a sales amount, then this query could be answered via the one fact table. One would think that this would be a faster query than joining two tables together to fetch the product ID from a dimension table and the sales amount from a fact table. In practice, though, there is no impact on response time, assuming the product ID is an indexed field (if it's a joined field, it should be indexed!).
The other type of query that will run faster when the dimension object points to the dimension table is a list of values (discussed in Chapter 9). Lists of values are a particular kind of query that provides users with pick lists for their conditions. In providing the pick list, BusinessObjects runs a query against a dimension table to display a list of valid product IDs for which they can retrieve sales information. The list-of-values query should be fast (preferably instantaneous). However, if your product ID object points to a fact table, BusinessObjects must do a full table scan against the larger fact table to generate the pick list. This can be very, very slow, and I have seen users give up after an hour, thinking the system was unavailable.
If your particular database lacks an optimizer or has not been well indexed, then you may want to test the impact on your response time. In some rare cases, you may indeed find that it is faster to have a dimension object point to the fact table.
|
| Tip | Have one object point to the dimension table. |
Measure
A measure is a number that users wish to analyze; it is denoted by a pink sphere or circle. Measures often come from a fact table, but measures such as number of products or number of days could come from a dimension table. Measures are almost always aggregated in some form, such as sum, count, average, min, or max.
The only measure that is not aggregated is unit price. Price is a measure, but it applies to one particular product, and it is wrong from a business viewpoint to sum prices across multiple product lines. Average price across multiple product lines would be a more appropriate aggregation; however, the universe should then contain two distinct price objects to ensure users can query both unit price and average price.
|
The other often-cited reason for pointing a dimension object to the fact table is a concern that it would be incorrect to show users a pick list of product IDs for which there may not have been sales in that period. However, there could have been sales; therefore, all potential product IDs should be displayed. If certain products have been discontinued, then the appropriate flags and time stamps should be built into your physical dimension table.
A large hospital system developed a novel approach to address these concerns: they created two objects for every dimension. For example, they had Account number pointing to the fact table and ACCOUNT NUMBER pointing to a dimension table. They trained users to use the uppercase object, ACCOUNT NUMBER, for conditions and the lowercase Account number object for result columns. This was unnecessarily confusing for the users and made the universe larger and more complex than necessary.
|
Universe designers may get confused about measures that apply to one point in time such as inventory quantity or account balances. Figure 8-2 gives an example from my consulting company bank account (purely fictitious). Inventory and account balances should not be aggregated across time. Unfortunately, BusinessObjects does not provide a solution to ensure that users do not aggregate inventory or balances across time; most server-based MOLAP tools do provide such functionality. In this case, many designers will think it is too risky to allow users to make the mistake of aggregating ending inventory/balances across time. How do users make this mistake? Using the data in Figure 8-2 as an example, let's assume that the universe references two fact tables: daily debits and credits and daily account balances. If a user builds one query that accesses both tables, the user will generally insert a condition where month equals September. BusinessObjects would tell the users that the ending account balance would be $129,955, rather than the correct number of $9,134. (If this were true, I would be vacationing in Hawaii rather than diligently writing this book!)
Figure 8-2: Inventory and account balances should be aggregated but not by time.
Hopefully, most users would recognize an inventory or account balance that is so blatantly wrong. However, good universe designers will take extra precautions in designing a universe to guarantee correct answers, no matter how users might construct a query. Some designers will remove the SUM aggregate from all inventory/account balance objects, ensuring users receive a correct value for every row for each day. However, this is not a good solution because now users cannot ask the question "What are my global inventories for a given product across all plants?" or "What is my total account balance across my various bank accounts?" Chapter 10 discusses other ways to prevent users from constructing an inaccurate query, but the best practice is to include the SUM.
| Tip | Always use an aggregate function on a measure object, unless that measure is a unit price or other similar number. |
Detail
A detail object provides additional information about a particular dimension. Most BI and MOLAP vendors call these attributes, but BusinessObjects refers to them as Details. In Figure 8-1, Print, Radio, Television, and Direct mail are detail objects. Within a customer dimension, age, fax, phone number, street address, and notes are typical details. Details and attributes may supply users with additional ways to analyze the measures, or they may be purely informational. For example, users may want to analyze sales by customer age group, but they rarely want to analyze sales by the customers' individual street address. In this respect, the street address is purely informational.
Classifying an object as a dimension or as a detail has no impact on the query or micro cube size, unlike in some MOLAP tools. An object may be classed as a detail rather than a dimension for primarily visual reasons, to ease user querying. The main limitation with detail objects is that in BusinessObjects, they are not hierarchical (for example, if age is a detail, then ranges within age such as Youth, Adult, Senior cannot be grouped). Differentiating between dimensions and details or attributes allows BusinessObjects to work more consistently with MOLAP databases that do differentiate between these information types. For example, Hyperion Essbase distinguishes between a base dimension and an attribute. BusinessObjects understands this difference and correctly presents users with the two different types.
|
| < Day Day Up > |
|
EAN: 2147483647
Pages: 206