Incorporating Personal Data Files

---

Throughout this chapter, I've warned against exporting data with the universe list of values, as it can unnecessarily slow universe updates. However, there are three instances in which you may want to break this rule:

1. The list is extremely small and it is faster for the repository to send the users a preexecuted query than it is for the users to execute the object.lov query themselves.

2. The dimensional data is not available in a central RDBMS to which all universe users have access.

3. The dimensional data in the data warehouse or OLTP is in transition, and new groupings and codes are not available in a central RDBMS.

For these last two reasons, a universe designer can incorporate personal data into the customized list of values and export it to the repository. Users may also do this on an individual basis. At this point, any data warehouse purist is cringing and rightly so. Of course, you want everyone viewing the same reference data! Of course, that reference data should be universally available! Sadly, this is not always the case. Remember, I am a business person at heart, so here is what you will need to do to keep business users happy (and to ensure you don't have dozens of them attempting this themselves!).

Personal data files can be text, spreadsheet, or database files. Each file must follow a specific layout:

• The condition column or pick list value must be in the first column.

• The remaining columns may contain additional information. Unlike standard customized list of values, these additional columns cannot be used for sorting once in BusinessObjects, so do the sorting first in the data file.

• It is okay to have column headings, but you must tell BusinessObjects they exist.

  Tip 

  If your list of values is in a spreadsheet, create an Excel range name with just those columns and rows you want to appear as a list of values.

Using the EFASHION universe as an example, let's assume that you want to regroup certain SKU numbers into different product lines, as shown in Table 9-2, which contains a proposed product grouping that does not exist in the data warehouse or OLTP. The new groupings are available in a spreadsheet file that not all users can access. In the RDBMS, Lounge wear and Jewelry are grouped under the line Accessories. In the personal data file, they are regrouped into a newly created line, Jewelry. Other SKUs are moved into existing but different Proposed Lines such as 161363 Double-Breasted Silk Jacket from Accessories to Jackets.

Table 9-2: List of Values Can Access Personal Data Files

SKU Number	SKU Description	Category	Proposed Lines	Current Lines
160812	Multicolored Pearl Necklace	Jewelry	Jewelry	Accessories
162520	Diamond Star Earrings	Jewelry	Jewelry	Accessories
168852	Silver Hoop Earrings	Jewelry	Jewelry	Accessories
185114	2 Row Pearl Necklace	Jewelry	Jewelry	Accessories
141406	Belted Tunic	Lounge wear	Sweat-T-Shirts	Accessories
150850	Zipper Vest	Lounge wear	Sweat-T-Shirts	Accessories
155576	Rudolph Shirt	Lounge wear	Sweat-T-Shirts	Accessories
159421	Striped Leggings	Lounge wear	Trousers	Accessories
160556	Spotty Leggings	Lounge wear	Trousers	Accessories
161363	Double- Breasted Silk Jacket	Lounge wear	Jackets	Accessories
167119	Jacquard T-Shirt	Lounge wear	Sweat-T-Shirts	Accessories
182379	Lurex Leotard	Lounge wear	Sweat-T-Shirts	Accessories
182488	Chenille Leotard T-Shirt	Lounge wear	Sweat-T-Shirts	Accessories
182880	Diamond Cigarette Holder	Lounge wear	Jewelry	Accessories
183861	Lycra Culotte Shorts	Lounge wear	Trousers	Accessories

Once you have ensured your data file is in the appropriate format, you can associate this data file with the object list of values:

1. From the pull-down menu, select Tools | List of Values.

   

2. This will present you with a list of all objects in the universe that have an associated list of values. Expand the Product class, then select SKU number.

   

3. Change the radio button from Corporate Data to Personal Data. When you change this option, you will receive the following warning message:

   

4. Click OK. Designer presents you with a Personal Data selection screen.

5. Specify the data file that contains your list of values and the format. If it is an Excel spreadsheet, you can specify a range name. An example using an MS Excel spreadsheet that uses a range name in which the first row contains column headings is shown here:

   

6. Click Run to have Business Objects access your personal data file and return to the List of Values dialog box.

7. Click Display to see this new list. Change your view from hierarchical to tabular. Designer displays the customized list of values in the format that will appear to users when they request a list of values for a condition object.

8. Click OK twice to close the display list and List of Values dialog box.

9. Modify the object properties for SKU number to mark the box Export with Universe.

When you click Run in step 6, the data from the spreadsheet (or other personal data file) is dynamically added into the custid.lov file. Designer exports this file with the universe definitions when you export the modified universe to the repository. If you skip step 6 and users do not have access to the personal data file on a central server, they will receive an error message when trying to run the list of values. Similarly, if users ever click Refresh, they will need access to the data file or will receive an error message.

---