Basic List of Values

---

When you first create an object, Designer enables lists of values by default and assigns a name based on the object name. In the following example, you will modify the Month Number object that you used in Chapter 8:

1. Click the Time class.

2. Double-click the object Month Number.

3. Select the Properties tab. Notice that the check box Associate A List Of Values is enabled. By default, this check box is enabled on all objects, including details and measures. I will discuss why this makes no sense later in the chapter. The Month Number object is a dimension and should have a list of values.

   

4. You can accept Designer's default name, or you can modify the name to be more meaningful. If you did not change the system generated List Name (MONTHO5A), change it to a more meaningful name such as MONTHNN. I prefer to change the List Name, as it helps when using the same list of values for multiple objects and helps when you need to use Windows Explorer to clean up the files on your local disk.

5. The check box Allow users to edit this list of values is checked by default. Table 9-1 explains the purpose of each of these checkboxes.

   Table 9-1: Options that Control List of Values Functionality

   Option	Explanation
   Allow users to edit list of values	This is allowed by default. This option lets users create their own custom lists, adding whatever filters, sorts, or personal data files they find most useful. I have rarely seen this used, even by power users, yet I find it an excellent feature. The main caveat here is that if the designer customizes and exports a .lov file, the universe .lov will overwrite the user's .lov, regardless of which is more recent.
   Automatic refresh before use	This should rarely be checked and only for those objects in which the dimension information changes frequently. Otherwise, the users can easily refresh lists of values by request themselves. Particularly with large dimension lists or slow RDBMS response times, it is important not to force an automatic refresh. However, if your company has recently gone through a major reorganization, or there have been a number of RDBMS changes that would cause an old list of values to be massively out of date, you may want to enable the automatic refresh for a defined period.
   Export with universe	This box should be checked only when the designer has customized the list of values with the universe. Leaving this checked for even simple lists that have not been customized will unnecessarily increase the size of the universe and slow user logon times. When you check this box, the query file object.lov gets exported to the repository with the universe. If you, as the designer, have refreshed the list of values, the data files get exported in addition to the query file. This can be a bad thing! It will almost always be faster for the users to refresh their list of values directly from the RDBMS, rather than downloading the large associated result set from the repository. Further, large lists of values in the repository can degrade repository performance. The only times you will want to export the result set with the list of values file are 1) for very small, frequently used lists, such as Time objects, and 2) when the results are coming from a personal data file or other source to which users may not have direct access.

6. Click Display to see the same list of values that users will see when using them in a query.

   

7. Click OK to return to the Object Properties panel.

8. Click OK to close the Object Properties panel.

When to Disable LOVS

By default, list of values is enabled on all objects, but there are instances in which it is better to disable it by removing the check from the checkbox Associate a List of Values.

Nonindexed Fields

One would think if you have disallowed an object to be used as a condition (Select Edit | Object Properties, then click the Advanced tab. [Refer to Table 8-6 for more information.]), it would automatically remove the list of values functionality from the object. It doesn't. So for many of the same reasons that you disallow an object to be used as a condition, you also may not want to associate a list of values. If the field is not indexed, you may not want to associate a list of values.

For example, let's assume that Customer Name is an object whose source system field is not indexed. It is still allowed as a condition because you want users to be able to search for all customers that start with the name Deloitte%. You want to discourage users from using Customer Name as a condition and encourage them to use Customer Code as a condition, since it's indexed. In this case, allow the list of values on Customer Code and disable it on Customer Name.

'Unfair! Unfriendly!' you say? Read the upcoming section 'Simple Customization.' You can still help users pick their customer codes according to customer name, but through a customized list of values.

Measure Objects

This is one of those times when I don't understand why the vendor doesn't make this functionality automatic: if an object is a measure, it should not have an associated list of values.

As discussed in Chapter 8, all measure objects (except things such as unit price) should have a SQL aggregate (SUM, COUNT, and so on). Therefore, if you have a list of values on a measure object, the list of values will always return only one row of data, since there is no GROUP BY clause in the SQL. For example, look at these two lists of values:

These are from the Quantity Sold object in the EFASHION universe. The left screen represents the default list of values. It is one row of data, 223229, because the SQL SUM aggregate will always return one grand total from the fact table unless there are dimensions in the GROUP BY section. The following SQL generated the single-row value:

SELECT DISTINCT   sum(Shop_facts.Quantity_sold) FROM   Shop_facts

The screen on the right shows a list of values that I have customized to force a GROUP BY clause. As the query contains each key in the fact table, the list of values will display a row for every record in the fact table using the following SQL:

SELECT DISTINCT   sum(Shop_facts.Quantity_sold),   Shop_facts.Id,   Shop_facts.Article_code,   Shop_facts.Color_code,   Shop_facts.Week_key,   Shop_facts.Shop_code FROM   Shop_facts GROUP BY   Shop_facts.Id,   Shop_facts.Article_code,   Shop_facts.Color_code,   Shop_facts.Week_key,   Shop_facts.Shop_code

This is my long-winded way of showing you that lists of values against a measure make no sense. If users want to look for Quantity Sold, greater than a certain value, they can easily enter this manually and do not need to choose from a list that will never match the numbers in their particular query (because the GROUP BY in their query will generally be different than the GROUP BY in the list of values query).

The one exception to this rule continues to be unit price. Here, a list of values may make sense, and this measure should not contain a SQL aggregate. Users may want to look for products by unit price. In this case, my only concern is the impact on response time. Unit price is most likely not an indexed field, and some product dimension tables can be huge; therefore, test this list of values before enabling it for your users.

Details

Rarely will users want to use details as conditions in queries. For example, when users are looking for customer sales, do they use detail objects such as street address or phone number for the exact condition when it's unknown to them? No. If they knew the phone number, they may use it as a condition, but then they are usually entering the phone number manually and not choosing it from a pick list.

However, if you look at the EFASHION universe, it makes a lot of sense to allow a list of values for the object ZIP code. I would recommend customizing it to display the state; I would also have ZIP code as a dimension object, not a detail (refer to Chapter 8).

---