x

In Developer Studio, right-click the Master Files folder and select Upload Data from the New menu.

In the BI Portal and BI Dashboard, the Upload Data tool is launched by right-clicking a folder and selecting Upload/Data from the context menu.

The following image is an example of working in the Projects area of Developer Studio. Similar steps are performed for working in the Data Servers area. As shown in the following image, in the Repository area the option to Upload Data is available from the folder level, and the option is controlled by user privileges.

The Upload Data File dialog box opens, as shown in the following image.

Note: When you upload the following types of files, a synonym is created: delimited flat files (txt, csv, ftm, dat, tmp), Excel Workbooks (xls, xlsx), XML files (xml), and JSON files (json).

1. Select an application. To change the selected application, click the ellipsis button next to the Application field.

   The applications listed are based on applications the user is authorized to access. Server privileges can be set to control user permissions such as the applications they can see and write to. The baseapp application will be visible if it exists on the server. The foccache application (used to store user temporary files for the duration of the user web session) is also visible by default.

2. Select a file. Click the Browse button to the right of the Select a file field.

   A Choose file dialog box opens.

3. Navigate to the file location and select Open.
4. Click Next.

   The next page displayed depends on the options and type of file you selected:

   • If a synonym was not created, a status screen displays indicating whether the upload was successful. You can then upload another file, go back, or close the Upload Data page.
   • If a synonym was created, the next screen lets you select parameters needed to create the synonym. The screen displayed depends on the file type of the file to be uploaded.
5. Select an upload type. The choices are to leave the file in its original format or to upload to a relational DBMS, which is the default option if an RDBMS is configured. For information about uploading to a relational DBMS, see Uploading a File Into a Relational Data Source.

Select or enter values for the attributes needed to upload the file and create the synonym, as described in one of the following topics:

• Uploading a Delimited Flat File.
• Uploading an Excel Workbook.
• Uploading an XML File.
• Uploading a JSON File.

x

When you select a delimited file to upload, the Select Synonym Candidates dialog box opens, as shown in the following image.

You can set the following attributes:

• Record Specification.
  • The Record delimiter identifies the separators between records or data rows. The Valid values are Comma (,), which is the default, Pipe (|), Space, and Type-in delimiter.
  • Non-printable delimiter. Valid values are No (the default) or Yes.
• Field Specification.
  • The Record delimiter identifies the separators between records or data rows. The Valid values are Comma (,), which is the default, Pipe (|), Space, and Type-in delimiter.
  • Non-printable delimiter. Valid values are No (the default) or Yes.
  • Header. Indicates whether the file has a header record.

    If you select Yes, the first record is used to create the field names in the Master File. If Header=No, Create Synonym assigns default field names (FIELD_1, FIELD_2, ...).

  • Enclosure. Identifies the enclosure character for alphanumeric values.
  • Record scan limit. By default, all records are scanned to determine the lengths of the columns. You can set a number of records to scan.
  • Preserve format. Indicates whether to preserve blank spaces and line feeds in the data.
  • Synonym name. By default, the synonym name is the original file name.
  • Application.
  • Prefix. Assigns a prefix to the synonym name.
  • Suffix. Assigns a suffix to the synonym name.
  • Set MISSING=ON for NULL value.
• Extended data format attributes.
  • CODEPAGE.
  • Overwrite existing synonyms. Check this box if you want to replace a previous synonym with the same name with the one being created for this upload.

For information about completing the upload process, see Completing the Upload Process.

x

When you select an Excel Workbook to upload, the Select Synonym candidates for Excel dialog box opens, as shown in the following image.

The table at the bottom of the page shows the worksheets available in the selected Excel workbook. If any of the worksheets are empty and selected, an error will display.

The column titled Number of Header Rows identifies the number of header rows on the worksheet. If it is greater than zero, the first row is used to create the field names in the Master File. If the number of header rows is zero, Create Synonym assigns default field names (FIELD_1, FIELD_2, ...).

You can set the following attributes:

• Row scan limit. By default it is zero (0), all rows are scanned to determine the column lengths.
• Application.
• Prefix. Assigns a prefix to the synonym name.
• Suffix. Assigns a suffix to the synonym name.
• Overwrite existing synonyms. Check this box if you want to replace a previous synonym with the same name with the one being created for this upload.

Check the boxes next to the Worksheets you want to upload. The default synonym name is the Worksheet name. If there is a named range, it will be a candidate for a synonym separately, and the synonym name will be the Worksheet name with the named range added.

For information about completing the upload process, see Completing the Upload Process.

x

When you select an xml file to upload, the Create Synonym dialog box opens, as shown in the following image.

You can set the following attributes:

• Validate. This changes special characters to underscores and prevents the use of reserved words in the resulting synonym.
• Make unique. This ensures that no duplicate names are created in the resulting synonym.
• Synonym name. By default, the original file name is entered.
• Write. This ensures strict schema compliance.
• Position. Enter the XPATH to element subtree on which the synonym will be based.
• Application.
• Prefix. Assigns a prefix to the synonym name.
• Suffix. Assigns a suffix to the synonym name.
• Overwrite existing synonyms. Check this box if you want to replace a previous synonym with the same name with the one being created for this upload.

For information about completing the upload process, see Completing the Upload Process.

x

When you select a JSON (JavaScript Object Notation) file to upload, the following Create Synonym for JSON dialog box opens, as shown in the following image.

You can set the following attributes:

• Validate. This changes special characters to underscores and prevents the use of reserved words in the resulting synonym.
• Make unique. This ensures that no duplicate names are created in the resulting synonym.
• Synonym name. By default, the original file name is entered.
• Write. This ensures strict schema compliance.
• Position. Enter the XPATH to element subtree on which the synonym will be based.
• Application.
• Prefix. Assigns a prefix to the synonym name.
• Suffix. Assigns a suffix to the synonym name.
• Overwrite existing synonyms. Check this box if you want to replace a previous synonym with the same name with the one being created for this upload.

For information about completing the upload process, see Completing the Upload Process.

x

Uploading a file to an RDBMS provides very good performance when reporting from a large uploaded file.

Note: Uploading a large file to an RDBMS may take a long time, depending on the file size.

If you chose to upload into a relational data source, the Select a File dialog box opens, as shown in the following image.

1. Select a relational adapter from the Adapter drop-down list.
2. Select a connection from the Connection drop-down list.
3. Select an upload type from the Load Type drop-down list. The choices are to insert data from memory (the default) or to use a bulk load facility and upload from a file.
4. Enter a commit frequency. By default, a commit will be issued every 1000 rows.
5. Enter a block size. By default, the block size is 1000.
6. Select an application. If you want to change the application in the Application text box, click the ellipsis next to the box.
7. To stop the upload process when no rows will be uploaded, check the Stop processing if 0 rows selected box.
8. Browse to the file you want to upload and click Open.

   Note: The file type must be valid for the RDBMS.

9. Click Next.

   The next page that displays depends on the type of file you are uploading.

x

Click Create Synonym to upload the file and create the synonym.

Three files are created in the selected Reporting Server application:

• Master File
• Access File
• Data file

You can upload another file or edit a synonym for the file just uploaded. For information about editing the synonym, see Editing the Resulting Synonym.

x

To edit a synonym, click the radio button next to the synonym name and click Edit Selected Synonym.

The Quick Edit dialog box opens to the Metadata tab, as shown in the following image.

• You can edit the field formats and titles by double-clicking the appropriate text box and entering a new value.
• You can click the Data tab to see the data in each field.

Click Apply to save your changes.

Synonyms are visible from the left frame. Users that have the METAP server privilege are allowed to edit Master Files with the Create Synonym tools and customize fields, get sample data and statistics, and perform all of the functions available in the Synonym Editor:

x

• The time required to complete the upload depends on the machine configuration (for example, type of processor, amount of memory, and speed). The Upload utility has been tested with Excel files up to 60MB with 140K lines.
• If your input file contains empty lines, such as a carriage return or a line feed character at the end of the file, they will be preserved in the upload file.
• If your input file contains any other empty lines, they will not get included in the uploaded file.
• When uploading data from an Excel spreadsheet:
  • The data must be arranged in an appropriate tabular format and the spreadsheet must have the same type of data in each column and the same number of fields in every row.
  • Cells with formulas and special characters are not supported. For example, if percentages are used, the cells should be formatted using the percentage data type and should not have the percentage special character "%" in the cell.
  • Files can reside on the network, local machine and can be accessed using the drive letter (for example, c:\) or a UNC path.
    • The folder location and file name can contain spaces.
    • The file name can be up to 64 characters.