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.

Advertisements

30 Responses to Optimizing Universe Lists of Values

  1. Akhil says:

    Hello Michael,
    Very informative post! Thanks a Lot.

    I have 2 doubts:
    1. Whenever I import my Universe I see an LOV file which is like 10MB or so. I need to find which object in the Universe is using it. Unfortunately, I have used the default names for it. Is there a way to find it?
    2. If I tick “Export with Universe” for an object and export the Universe to repository, and later on I decided to untick it, will the LOV file be removed automatically from repository as well?

    • Hi, Akhil;

      1. Try creating a detailed PDF of the universe, then search the PDF for that LOV name. You might be able to find the object that way.
      2. I believe that the LOV would be deleted, but I’m not 100% sure of that, as I’ve never tested it.

      • Joel Tamblyn says:

        WIth regards to finding the underlying object that is referenced to the LOV you can open the LOV in Notepad or something similar and if you scan through the file you should be able to find the associated SQL statement for the LOV which may help you find the object tied to this bloated LOV. One you have identified the Object go to Tools/List Of Values/Edit List of Values, find the object, select it and then click Purge.

  2. Akhil says:

    Hello Michael,
    One more doubt regarding the static LOVs. While creating a static LOV, what should be the definition of the object to which we map the txt or spreadsheet? Can we map it to a blank object? or is it mandatory that the object should be assigned to a table. My intention is to create a dummy object just for static LOV (for eg. flags=> ‘Y’,’N’) and use it for multiple flag prompts.

    • That’s an interesting question. It depends on which tool you are using. If you are using Designer, I believe it will work, though I’ve never tried it. If you’re using IDT, you don’t need an object at all, since the LOV is created as a stand-alone object in the universe.

      • Akhil says:

        Hi Michael,
        I’m using UDT. I first tried assigning the static lovs to a blank object. It gave an error and didn’t display lov values in webi. Then I hardcoded object defenition to ‘flag’ and then tried. The lov is then comming as ‘flag’ in webi, and not the static lovs I assigned to the object. Now I m stuck. My backup plan is to create a derived table with unions from dual and create the object/lov based on this derived table. Is it possible to make the static lov work? Is there any better approach?

      • Why not assign the static LOV to an object that can use it?

  3. Akhil says:

    Hello Michael,
    I tried assigning static lov to the original object also. But, strange thing I noticed is that, static lovs from a file is not appearing if I try to check in Webi! There it always displays the values from the object definition, even if we have a static lov created! Not sure if its a known issue or I’m doing something wrong. When I try in Universe Query panel, I can see the proper static values which I assigned.

  4. Akhil says:

    Hi Michael,
    I created a txt file with values:
    ‘-ALL-‘
    ‘Y’
    ‘N’
    Then I added this text file to the object lov. Afterwards when I press Display in object lov properties, I’m correctly getting flags as mentioned above. In addition, it is working fine in Universe Query panel as well. Only webi has issues!
    And Yes.. I did tick Export with Universe option for the object lov.

  5. Well, something must have been done incorrectly, as I’ve never seen this not work. Keep at it, and make sure you’ve followed all the steps correctly. There’s not much more assistance I can offer at this time.

  6. Dave Rathbun says:

    Another use of “Refresh before use” is when you have different security profiles in your universe. For example, assume you have a list of locations that are restricted by user profiles. You would not want the results of that LOV to be shared (or cached) so making sure the list is always refreshed ensures that the data matches the security profile.

  7. Mathi says:

    Hi Michael,

    I have Two issues.

    1. I have created LOV based on business layer objecs for yearweek(yyyyww) using some conditions to get yearweeks less than current week. And that is fine i get the result as expected(yearweeks less than current week). But when i associate this LOV with an object it behave strange. I am getting all the yearweeks (greater than current week also).
    If i right click that object and click on show list of values it shows those values which is there in LOV which is fine,

    but if i right click on obj and click on show values/use it in query it shows all the yearweeks 😦

    2. I have created a LOV based on sql query in data foundation and I am able to see it in business layer(inherited). but if i want to assign this LOV to an obj it is not showing any LOV when i click on eclipse of associate LOV.

    • 1. When you preview values for an object in IDT, it does not use the LOV for that preview. However, in Webi, when you view the LOV on a query filter, you should only see the values returned by the LOV.

      2. Make sure that the LOV is returning the same data type as the object.

      • Mathi says:

        Yes. I am able to see LOV when i use that object in query filter. But in my case i do not have to use it in query panel. I am using that object in result objects and i am getting all the values if i use it in result obj. Is there any other way to restrict values for an obj using “less than” condition.

        In my object I have values of yearweeks and i am restricting those values only till current week.(All yearweeks which are less than current yearweek should come in that object)

  8. If you want to always limit the results of a particular object, you can put a filter on that column in the Data Foundation.

    • Ananya says:

      Hi Michael,
      I am using sorting to display LOV in the prompt. when I apply sort to LOV and click on display/preview it shows error partial multibyte characters. However same LOV object works fine without sorting. ( LOV object contains 140000 distinct values.) Could you please help me in this?

  9. 21karthik91 says:

    Hi Micheal,
    Its a clear document. But i am new to BO. Can you explain me how prompts and Lov are related. In my universe, i have 4 prompts and 1 lov. How they are related. But the 4 prompts will have the same value as lov. Can you explain me this?

    • A prompt is a request to the user to enter, or select, a value to be used in a query filter. If the user selects a value, they select it from a List of Values (LOV). A LOV can be presented to a user when the create a query filter, or when they are answering a prompt. I hope that makes sense.

  10. Amit says:

    Hi Michael,

    I could not understand the following statement under your last point “Sourcing a List of Values”–

    “If so, re‑point your LOV to the dimension table. Or, if possible, re‑point the object to the dimension table.”

    1. How to re-point?
    2.Can you please give some real time situation where we can need this re-pointing?

    • Amit, if you have a dimension object built on a fact table, it can take considerable time to generate a list of values. This is because the query is running a “Select Distinct” against a very large fact table. However, if you can have the object get the same values from a dimension table, it will run much faster. It’s possible to have the object built against a fact table, while the LOV is accessing a dimension table. This is done by editing the LOV.

  11. Ranjit P says:

    Hi Michael,
    Using BI 4.1 SP5, I am using LOV for Dimension objects in universe. For some reason the LOV in the WebI report prompt shows another objects’ name & values in the prompt window. When I create a new object it works correct. It seems like the it is pointing to another objects’ LOV. We have thousands of objects and changing each object LOV will take time. Have you come across something like this.

  12. Ranjit P says:

    Thanks Michael. Changing the list name fixes it. But this means changing the list name for all objects with LOV.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: