For Excel spreadsheets, the name of the file is not important but the name of the worksheet that contains your data is used to generate the synonym name. For the best results, follow the guidelines below:

• Ensure that the worksheet name is meaningful. For example, Store Sales is better than Sheet1.
• Remove special characters from the worksheet name. Spaces will be converted to underscores but all non-alphanumeric characters should be removed from the name, such as:

  /`~!@#$%^()-+={}|;,

• Remove or replace NLS characters with standard alphanumeric characters.

The image below shows a worksheet with a meaningful name.

While you have an opportunity to edit the worksheet name prior to upload within the Upload wizard doing it in the Excel spreadsheet may be preferable. Note that your spreadsheet data and column titles may contain NLS data and special characters.

For CSV files, there is no worksheet name, so the CSV filename is used to generate the synonym name. For this reason, all the above limitations for Excel worksheet names apply to the CSV filename, so be sure to check and adjust the filename prior to upload for best results.

Sometimes an Excel spreadsheet contains formatted headings in the first few rows. This information cannot be imported into WebFOCUS BUE and should be removed. Delete the introductory rows and save the file before uploading. Alternatively, you can define a data range within your worksheet and leave the introductory information in place. Data ranges tips are presented later in this topic. The following image shows an example spreadsheet with introductory information highlighted.

For your data to be useful in WebFOCUS BUE, your data columns will need to be identified and properly described in the synonym that is generated during the upload process. You can make this easier by ensuring that the first few rows of your Excel spreadsheet contains column titles that are meaningful to you and to other users who will be using it, as shown in the following image.

If your spreadsheet has more than one row of column titles, WebFOCUS BUE can merge the information when creating the synonym. You will be given an option to specify how many first rows of the Excel file contain title information in the Upload wizard.

Excel spreadsheets may contain subtotals, grand totals, and other non-data row information. Data aggregation is performed by WebFOCUS BUE, so you should remove these kinds of rows from your spreadsheet and save the file before uploading it.

Data ranges defined within your Excel worksheet can be helpful for a couple of reasons:

• Your worksheet may have introductory information, such as formatted headings or non-data information, in the first few columns.
• You may not be interested importing all of the data columns found on your worksheet.

You can define a data range in your worksheet to crop out the data that you want WebFOCUS BUE to process during upload and leave your spreadsheet in its original format.

WebFOCUS BUE can recognize columns of data that have a hierarchical relationship. This is useful because the field names are arranged more intuitively in InfoAssist+ and because it facilitates Auto-drill in the content. Auto-drill lets users drill up and down the field hierarchy automatically, making the content much more engaging and useful.

To help WebFOCUS BUE recognize hierarchical columns correctly, ensure that the column titles begin with a common word or words and are arranged left-to-right in the correct top-to-bottom direction, as shown in the image below. The Upload wizard allows you to define and edit dimension hierarchies prior to creating the synonym, but you may find is preferable to follow these recommendations before upload.

Some Excel spreadsheets may contain repeating columns, such as sales figures for a series of years. There may even be repeating column groups, such as both budget and actual figures for a series of years, as shown in the following image. WebFOCUS BUE can pivot the repeating data columns into data rows automatically. Select the Pivot option in the Upload wizard and follow the steps in the resulting panel.

Spreadsheets may contain a date column where the values are computed by Excel using a formula. You need to convert these computed values into simple values before uploading using the Upload wizard. Select a column, right-click, and then click Copy. Then right-click the selected column again and click Values. This can be found under the Paste Options menu, as shown in the following image. The menu options may vary by Excel release. Now you can see that each cell contains a date value and WebFOCUS BUE can decompose your dates into useful components for use in InfoAssist+.