Importing data from a file

You can import data from different data sources, such as an Excel spreadsheet or a text file, then use your data to create a report, document, or Visual Insight quick dashboard.

The following file types can be used to import data:

.xls

.xlsx

.txt

.csv

You can provide the location of the file using the following methods:

Select a file on your computer or network
Specify the location of your file using a UNC path, as follows:

Note: Before you can import data from a file by providing a UNC path, your administrator must first enable importing data from a UNC path in the Project Configuration Editor. The following examples:
- UNC Path: You can specify the location of a local file using a UNC path. For example, you can provide the location of your file as File://ComputerName/Path/FileName, where ComputerName is the name of the machine on which the file is located, Path is the file path in which the file is located, and FileName is the name of the file. Specifying the location of your file as a UNC path allows you to republish or schedule updates for an Intelligent Cube created using the file, without having to manually upload the file each time the Intelligent Cube is updated.

For text/CSV files, XBRi uses English (United States) localization settings to import data. For example, a comma is used as the separator symbol for every three digits.

For Excel files, XBRi uses the localization settings specified in the User Preferences.

Password-protected or compressed files (such as .zip files) cannot be used to upload data.

Assigning geo roles and shape keys to your data

During the import process, XBRi automatically attempts to determine if any data columns in the data that you have chosen to import contain geographical information, such as city or latitude information. A data column that contains geographical information is assigned a geo role to identify the type of information it contains. Once a data column has been assigned a geo role, you can improve the depth of the geographical information available for your data by having automatically generating attributes containing higher levels of geographical data based on the data column. For example, if the data column contains city data, you can arrange to automatically generate the State attribute, which contains the state each city is located in.

Assigning a geo role to a data column also allows for easier integration with map-based widgets or visualizations that require latitude and longitude information, such as the Map visualization. For example, you can import a data column called City, which contains the names of multiple cities, then define it as an attribute with the City geo role. XBRi automatically creates the City attribute with the attribute forms Latitude and Longitude, which contain the latitude and longitude of each city. When you create a Map visualization using the imported data, you can use the City attribute to provide latitude and longitude information for map markers in the visualization. XBRi automatically adds latitude and longitude information as attribute forms to data columns with the Country, State, Zip Code, City, or Location geo role, allowing you to provide latitude and longitude information using these forms.

Administrators can customize the geographical information used to automatically generate higher-level attributes. For example, they can specify whether XBRi assumes that Springfield refers to Springfield, Ohio or Springfield, Illinois.

A shape file is an HTML file that contains the background image of an Image Layout visualization or widget, as well as the location of each area or bubble marker you want to display on top of the image. You can determine which shape file to display in an Image Layout visualization or widget by default, by assigning a geo role or shape key to the data column. For more information and steps to create an Image Layout visualization, see Creating an Image Layout visualization. Steps are included below to assign a geo role or shape key to a data column.

Prerequisites

You must have Adobe Flash Player to import data from a file. For specific version requirements, see the release notes for your version of XBRi.
You must have the Import Data privilege.
Before you can import data from a file by providing a UNC path, your administrator must first enable importing data from a UNC path in the Project Configuration Editor.
Read Best practices: Importing Data.

To import and publish data from a file

Open the My Reports or Shared Reports folder.
From the icon bar at the top, click the Import Data icon.
Under Select your data source, click File. The File Upload options are displayed.
Do one of the following:
- To import data from a file on your computer or network, select the From My Computer/Network option, then perform the following steps:
- 1. Click Browse. The Select a File to Upload dialog box is displayed.
  2. Navigate to and select a file to import, then click Open.
- To import data from a file using a UNC path, select the From the URL option, then type the location of the file in the field below.
Click Next. A preview of the imported data is displayed on the Preview page
If the file is an Excel workbook with more than one sheet of data, from the Sheet Name drop-down list, select the name of the sheet from which you want to import data.
By default, XBRi assumes that your data is stored in a simple tabular layout. You can also choose to import data stored in a cross-tabbed layout. Select one of the following options:
- To import data from a simple tabular layout, select Tabular, then perform the following steps:
- 1. By default, XBRi uses the first row of data as headers for the imported data columns. These column headers are then displayed as the names of the attributes and metrics you define. If the data in the imported file does not provide column headers for the data columns, you can insert and specify the column headers manually by doing the following:
    - Select the Insert new column headers check box. A default column header is automatically inserted for each data column.
    - To specify a name for a column header, hover the cursor over the column header and click the arrow icon in the top right. Select Rename, then type a name for the header in the field and press ENTER.
- To import data from a crosstabbed layout, select Crosstabbed. A preview of your data is displayed, with cells of data that contain metric data, attribute element data, and metric header names each displayed in separate colors. The legend in the top left lists the colors in which each type of data is displayed. Ensure that each type of data is included in the appropriate colored area by performing the following steps:
- 1. Click and drag the edges of the Metric Data area to highlight each cell that contains metric data.
  2. Specify which cells contain metric header information by doing one of the following:
    - If no metric headers are contained in the file, select the No Metric Headers check box.
    - If metric headers are included in the file, clear the No Metric Headers check box. Click and drag the Metric Header Block area to highlight the cells that contain metric header data.
  3. To display a preview of your data and continue, click the Convert to Tabular Sample icon . You can return to specifying attribute, metric, and metric header data by clicking the Tabular Representation of the Crosstabbed Data icon.
You can select options to define a data column as an attribute or metric, choose not to import a column of data, rename data columns, and so on. Hover the cursor over the header of the data column you want to select options for, then click the arrow icon to display a menu. Select from the following:
- To define the data column as an attribute, point to Attribute.
- 1. If the column's data type is Date, Time, or DateTime, you can define your data in more specific detail by having XBRi automatically generate additional time-related information based on the contents of the data column. For example, if the column is assigned the Date data type, you can have XBRi automatically generate separate attributes for year and month information. Select the check box next to each attribute you want to have automatically generated, then click OK.
  2. You can assign a geo role or shape key to the data column, as described above. Do one of the following:
    - To assign a geo role to the data column, select the geo role you want to assign, such as City, State, or Country. Select the check box next to each attribute you want to have automatically generated based on the data column, then click OK.
    - To assign a shape key to the data column, select Others, then select the shape key you want to assign, such as Countries of the World or States of USA. Click OK.
    - To define the data column without assigning it a geo role, select None, then click OK.
- To define the data column as a metric, select Metric.
- To avoid including the data column in the imported data, select Do Not Import.
- To rename the data column, select Rename. Type a name in the field and press ENTER.
- You can map a data column to an attribute that already exists in the current project. To do so, you must provide a data column to map to the ID attribute form of the existing attribute. This column must be the same data type as the ID attribute form. You can also map additional data columns to the attribute, by mapping each data column to a different attribute form.
  
  For more information on mapping data columns and how it affects grouping and selectors, see About mapping imported data to project attributes. If the data column is already mapped, you can map the data column to a different attribute form to replace the existing mapping. Perform the following steps:
- 1. Select Map to Project. The Select Mapping Object panel is displayed. This option is only available for data columns designated as attributes.
  2. Navigate to and select the attribute form you want to map. You can expand and collapse the list of attribute forms in an attribute by clicking the arrow icon next to the attribute.
  3. Do one of the following:
    - To map the data column you selected to an attribute form, click the name of the attribute form to select it.
    - To map multiple data columns, drag the name of each attribute form you want to map onto the alias of the data column you want to map it to.
    - To unmap a data column, hover the cursor over its alias, then click the x icon. The data column is unmapped, and its alias is changed back to the alias applied to the data column before it was mapped.
  4. Click OK to close the Select Mapping Object panel.
- To unmap the data column from a project attribute, select Unmap from Project. This option is only available for data columns designated as attributes.
- To change the data type of the column, point to Data Type, then select the data type you want to use.
Repeat the appropriate steps above for each data column you want to define.
Click Publish. The Save Cube dialog box opens.
Browse to the location to publish the imported data to, then type a name and description for the published data in the Name and Description fields.
Click OK. Your data is saved and published as an Intelligent Cube.

Once you have saved your data, you can use it to create a report, document, or quick dashboard. To do so, select one of the following:

To create a report, select Create Report. For steps to create a report that runs against your new Intelligent Cube data, see Creating a report that accesses an Intelligent Cube. For general steps to create a report, see Creating a report.
To create a document, select Create Document. For general steps to create a document, see About creating documents.
To create a quick dashboard, select Create Dashboard. For steps to create a quick dashboard, see Creating a quick dashboard.