Optimizing Universe Lists of Values

Lately, I’ve seen a lot of heartache over universe Lists of Values (LOV). Lists of Values can be a great benefit to users, as well as your worst nightmare for developers and DBAs. So, I thought it might be handy to document some of the best practices for keeping your LOVs user friendly and performing well. Let’s start by going over the various options for LOVs, just so we have a good understanding of what is available, and what makes sense.

There are two times when a user may see a list of values for an object. When they are building a filter in a query, based on a universe object, they may want to choose values for the filter from a List of Values. Or, when a prompt is applied to an object in a query filter, the user may see a LOV at run time when the prompt window appears. Each object in a SAP BusinessObjects universe has the below options available for LOVs. Make sure you put some thought into these options for every object in a universe.

In Information Design Tool (IDT) LOVs can be created in the Data Foundation or in the Business Layer, as standalone objects. If you create the LOV in the Data Foundation, it can be used with any object, in any Business Layer built on that Data Foundation. You can create a LOV using custom SQL, or by importing or creating a static list.

If you create the LOV in the Business Layer, you can use that LOV with any object in that Business Layer only. However, in addition to creating a LOV with custom SQL, or making a static list, you can also create a LOV using a query panel. We’ll talk about static lists later.

Associate a List of Values

This check box determines if the object will have a List of Values. If this box is not checked, the users will not be able to choose from a list when selecting values in a query filter or query prompt. Many dimension or detail objects will need a LOV. However, there are many objects that shouldn’t have a LOV. For example, measure objects don’t need LOVs, as it doesn’t make sense for them. Date/Time objects should not have LOVs. In fact, Date objects usually won’t need a LOV, as the user can select from the calendar widget, or type in the date they want.

What about objects with very large LOVs? For example, SKU Number could have millions of distinct values. So it may not make sense for that object to have a LOV. However, there may be a way to provide a LOV for SKU Number, while keeping the list manageable. Stay tuned.

Allow users to edit this list of values

This is an amusing one, as it tends to scare many developers. Fear not, this isn’t a bad option. It’s just a somewhat obsolete option. Let me explain. When a user of Desktop Intelligence (Deski) creates a query, the universe and associated LOV files are downloaded and stored locally on the client’s machine. Deski allows users to edit their own copy of the LOV for an object. For example, they can add columns, or filters, to the query panel that generates the LOV, so it only returns the values that interest them. This is a good thing.

Deski is currently the only product that allows users to edit their LOVs. I suspect that eventually Web Intelligence (Webi) will also have this feature. But for now, only Deski. And since Deski doesn’t exist in 4.x, this options is currently obsolete in 4.x. So, there’s no need to un-check this option.

Automatic refresh before use

This is one of those options that can be good, or bad. If you have a small list, that is fairly dynamic, such as Employee Name, it might be good to use this option. However, make sure that the list can be generated in two seconds or less. You don’t want users waiting for large lists to generate every time they use them. If the list is very large, this can cause significant pain for the users. Use this feature with caution.

On the other hand, if the list is fairly static, don’t use this option. For example, a LOV for State Name isn’t likely to change any time soon. So there’s no need to refresh it on every use.

Hierarchical Display

This is another feature that was designed for Deski. If you have a LOV that is based on data that is part of a hierarchy, you can add the other objects of the hierarchy to the query for the LOV, and have them displayed in hierarchical folders. For example, a LOV for City can be displayed in a hierarchy that starts with Country, then State or Region, then City. This feature only works well with Deski. So, if you’re not using Deski, don’t use this.

Export with universe

The LOV is not stored in the universe. It is stored in a separate file with a .lov extension. If you make any edits to the LOV, those edits are also stored in the LOV file. For example, if you have modified the query that generates the LOV, that query is stored in the LOV file. Or, if you have created a static LOV, that data is stored in the LOV file.

So, in order for the users to benefit from the changes you have made, you will need to export that LOV file along with the universe, into the repository. Also, if you have applied Index Awareness to an object (that’s a topic for another post), the indexes are stored in the LOV file. You will need to export that LOV with the universe.

Delegate search/Allows user to search values in the database

This is an option that can be beneficial for large lists. Looking at the earlier example of SKU Number, which could have millions of values, you can use this option to provide the users with a LOV, but without having to generate a huge list.

Here’s how it works. When you turn on this option, and a user attempts to access the list of values for an object, they will see a blank list, and a set of instructions telling them to enter a search string, including wild card characters. For example, a user could enter “XYZ%” and they will get a list that lists all values that start with XYZ.

This is called Delegate Search because you’re allowing the users for search for what they need, and you are delegating that search to the database, rather than searching the LOV file for specific values.

Note that, in IDT, there is a related option called Force users to filter values before use. This option allows users to enter search values to filter the existing LOV.

List Name

This is the name of the .lov file for this list of values. In earlier versions of BusinessObjects, we were limited to eight characters, and no spaces. But times have changed. You can now create a file name that has more than eight characters, and includes spaces. I like to give it a name that relates to the object, or the data in the LOV. That way, it’s easier to find the file if I ever have to do troubleshooting.

In the new universe tool, Information Design Tool (IDT), LOVs can be standalone objects that can be reused for multiple objects. But did you know that you can use one LOV for multiple objects in Designer as well? For example, let’s say you have an object that will return only the values of Yes, No, or Maybe. So, you generate a static LOV file, and name it YesNoMaybe. Make sure you check the “Export with universe” box. Now, if you have other objects that will return the same three values, enter the same name for those object’s LOV, but don’t check the “Export with universe” box for those. You only need to check it for one object. In this way, all objects with the same name for their LOV will share the same LOV file.

You will notice that the default file name is pretty cryptic, especially if the object was created by using the New Object button. If you ever want to return to the default name (I have no idea why you would want to), click the Restore Default button.

Edit

If you want to customize the list for an object, click the Edit button. This will open the Query Panel for this universe. You can use the Query Panel to customize the query, or you can click the SQL button on the Query Panel tool bar to write custom SQL for this LOV.

If you want to create a hierarchical list (discussed earlier), make sure that you leave the current object, as it is, in the Query Panel, and add the rest of the hierarchical objects after that, in hierarchical order. For example, if you want to create a hierarchical list for City, then City must be the first object in the Query Panel. Then, after that, add the rest of the hierarchy from top to bottom. So, the objects in the Query Panel might be as follow, in this order:

City – Country – State – County

I know, that’s counter intuitive, but trust me. It works. Also, make sure that you apply sorts to each of these objects in the query, in the order of the hierarchy.

You can also use custom SQL to create a list with values that don’t appear in the database. For example, maybe to want your City LOV to include a “All” value, in addition to the list of cities. So you might write custom SQL as follows:

Select Distinct City

From dbo.location

Union

Select ‘ All’

From Dual

Obviously, this is Oracle SQL, but it will work with any database with minor modifications. Notice that I put a Space in front of the word “All”. This will make sure that All is at the top of the list. This allows you to use an old trick, which allows users to select All from the list to get All cities in their results.

When you edit the query for a LOV, the Export with universe button is automatically checked. Don’t un-check it. 😉

Display/Preview

This button is more important than it may seem. When you click this button, the list is generated, and displayed. In other words, the query for this LOV is run, then the results are displayed. Of course, the query is only run if you haven’t run the query before, or if you have the “Automatic refresh before use” option turned on. Otherwise, this button will simply display the existing list.

So why is this button so important? Well, if you have the “Export with universe” option checked, then you need to generate the LOV file in order to have it exported with the universe. If you don’t generate the file, then there is nothing to export.

Query Limits

In IDT, you have the option of setting limits on the query that generate the LOV. You can set a time limit, as well as a row limit. By default, neither of these are turned on. I would not suggest setting a row limit. There’s nothing more frustrating to a user than a partial LOV.

Static Lists of Values

A static LOV is a list that never changes, unless you take manual action to change it. The most common method of creating a static LOV is to import a file, such as an Excel spreadsheet, or a text file. Depending on which tool you’re using, the method, as well as the options, will vary.

So let’s start with Designer (a/k/a Universe Design Tool). If you want to create a static LOV, you start, of course, by creating a text file, or spreadsheet. Simply create a file with the needed values in a column. Make sure that the first row is the name of the object, or list.

Next, go to Tools – Lists of Values – Edit a list of values. Find the object for which you want to create the static list, and click on it. Then, at the bottom of the window, click the Personal Data radio button.  This will open the Access Personal Data window. Browse for the file. Make sure you select the “First row contains column names” option. Then click Run. This will generate the LOV file from the text file or spreadsheet.

In IDT, you have the additional option of entering the list manually, simply by adding rows and typing the in values. In the Data Foundation or Business Layer, go to the Parameters and Lists of Values section. Next, click the New button at the top of the Lists of Values window, and select Static list of values. If you want to create the list here, click the Add Column button on top to add additional columns, or click the + button on the right to add additional rows. You can change the name of the column by double-clicking on the column header.

If you want to import an existing file, click the Import button, and browse for the file. Complete all the relevant options, including “First row contains column names”. When you click OK, the list is imported.

Applying a LOV to an Object

In IDT, once you create a LOV, you must then apply it to one or more objects. To do this, go to the Business Layer, and select the object. Next, click the Advanced tab, and you will see the LOV options on the right. Make sure that Associate a List of Values is selected. Then, click the little ellipses button on the right of the field on the right. Select the appropriate LOV and click OK. Note that if you click the X button to the right of the LOV name, it will remove that LOV, and the object will simply use the default “Select Distinct” LOV.

Sourcing a List of Values

Where do you get the data for a LOV? Well, ideally, from a small dimension table. But what happens when you have a dimension object built against a large fact table, with millions, or billions of rows? Well, by default, the LOV will be generated by running a “Select Distinct” against the fact table. This can take a significant amount of time to populate.

So the first thing you want to do is to see if the same values are available in a dimension table. If so, re-point your LOV to the dimension table. Or, if possible, re-point the object to the dimension table. But, at the very least, get the data for the LOV from the dimension table. However, if the data is not available in a dimension table, talk to your DBA, and see if they can create a reference table with the distinct values you need for the LOV. If the list is fairly dynamic, the reference table can be updated with the nightly ETL process.

The bottom line is that you want to avoid getting LOVs from fact tables. Users will get very frustrated waiting minutes for the LOV to populate.

Conclusion

As you can see, there’s a lot more to a List of Values than meets the eye. There are many options, and getting them right can make a significant difference for the users. Take the time evaluate each object, and set the correct options that make sense for that object. Let me know if you have additional tips for Lists of Values.