Previewing your data and specifying data import options

When you import data into MicroStrategy Web, you can preview your data. You can select options to modify your data before it is imported, or make changes to your data after it has been imported. For example, MicroStrategy automatically designates data columns in your file as attributes or metrics based on the values contained in each data column, with attributes displayed in blue with an attribute icon Attribute icon and metrics displayed in orange with a metric icon Metric icon. You can manually designate data columns as attributes or metrics before the dataset is saved.

You can:

Display a preview of the data before it is imported.
If you are editing your data, such as adding or removing tables to your dataset, display a preview of the changes you make.
Select the worksheet of data to import, If you are importing the data from an Excel workbook with more than one worksheet.
Add more data from the same data source or a different one.
Remove a table from the import process.
Designate a data column of data as an attribute or a metric.

To quickly convert multiple attributes to metrics, or multiple metrics to attributes, use the All Objects View page.

Assign a geo role to a data column, to generate additional geographical data and to allow for easier integration with map-based visualizations.
Group or display imported data based on an existing project attribute by linking a data column to the project attribute.
Use data columns as attribute forms to define a new, multiform attribute, if your data contains different attribute forms of the same attribute saved in separate data columns.
Improve performance by partitioning a very large dataset and creating search indexes for attributes. For reasons to use partitioning and search indexes, as well as steps to create them, see Improving performance: Partitioning large datasets and creating a search index.
Define attribute relationships, which are associations between attributes that specify how attributes are connected. The relationships give meaning to the data by providing logical associations of attributes based on business rules. For steps to define attribute relationships, see Defining relationships between attributes.

For data that is imported from sources other than a database, you can also:

Choose whether to import your data in a tabular or cross-tabbed layout
Insert column headers into the data, if the file does not provide them

Steps to perform these tasks are below.

Prerequisites

This procedure assumes that you have already begun importing data. The Preview page is displayed after you select a data source to import from, select the data to import, and click Prepare Data. For a list of the data sources that you can import from, and links to steps to import data, see About importing data into MicroStrategy Web.
Read Best practices: Importing data into MicroStrategy Web.

To specify importing options using the Preview page

1 If you are importing data from an Excel workbook with more than one sheet of data, the Select Worksheets dialog box opens. From the list of worksheets, select the check box for the worksheet to import data from. Click Select.
2 The Preview page opens, displaying a preview of the data that will be imported. If you selected multiple files, you can change which file is previewed by clicking a different table.

You can hide the preview by clearing the Show Preview check box. If you hide the preview, you can still define data columns. In the Tables area, right-click the data column. The same options listed in Define a data column are displayed.

3 To add more data to this dataset, click Add a New Table. The Connect to Your Data page opens. Import the data, as described in About importing data into MicroStrategy Web. After you select a data source and the data to import, click Prepare Data to return to the Preview page.
4 If you added a table that you do not want to import, you can remove it. Hover your cursor over the table to remove, click the menu icon, and select Delete.

To change the layout of the imported data

5 Hover your cursor over the table to modify, click the menu icon, and select Parse. The Parse Your Data page opens.

The Parse option is not available if you are importing data from a database. Continue specifying data import options beginning with To define a data column.

6 By default, MicroStrategy assumes that your data is stored in a simple tabular layout, with each column in the table containing a separate attribute or metric. You can also import data stored in a cross-tabbed layout, with attributes and metrics stored in both table rows and columns. Select one of the following options:
To import data from a simple tabular layout, select Tabular. By default, MicroStrategy identifies the first row of data to use 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:
a Select the Insert new column headers check box. A default column header is automatically inserted for each data column.
b 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 Crosstab. A preview of your data is displayed, with cells of data that contain metric data, attribute data, and metric header names each displayed in separate colors. The legend in the top right lists the colors in which each type of data is displayed. Perform the following steps:
a Click and drag the edges of the Metrics area to highlight each cell that contains metric data.
b 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 area to highlight the cells that contain metric header data.
7 Click Apply to return to the Preview page.

To define a data column

8 At the top of the page, click the table that contains the data column to define. A preview of the table's data is displayed at the bottom of the page.

You can view, search, and define objects across all tables by clicking All Objects View. The All Objects View page opens. All the options described below are available, but you cannot preview the data. You can convert multiple attributes to metrics, or multiple metrics to attributes, using this page.

9 To exclude a data column when importing your data into Web, hover the cursor over the column, then click the arrow icon, and select Do Not Import.
10 You can define a data column as an attribute or metric. Hover the cursor over a column, then click the arrow icon, and select one of the following:
To define the data column as an attribute, select Convert to Attribute.
To define the data column as a metric, select Convert to Metric.

These options are not available for data imported from a search.

11 You can assign a geo role or shape key to the data column for easier data integration with map-based widgets or visualizations that require geographic information. To assign a geo role or shape key, hover the cursor over a column, then click the arrow icon, and do one of the following:
To assign a geo role to the data column, point to Define Geography, then select the geo role to assign, such as City, State, or Latitude. You can define your data in more specific detail by having MicroStrategy automatically generate additional geographic information based on the contents of the data column. For example, if the column is assigned the City data type, you can have MicroStrategy automatically generate separate attributes for country and state information. Select the check box next to each attribute to have automatically generated based on the data column, then click OK. For detailed information about which attributes can be generated for each geo role, see the Creating and Configuring a Project chapter of the Project Design Guide.
To assign a shape key to the data column, point to Define Geography, 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, point to Define Geography, select None, then click OK.

The Define Geography option is not available for data imported from Hadoop or from a search.

12 To change the data type of the column, hover the cursor over the column, then click the arrow icon, point to Change Data Type, and select the data type to use.
13 To rename the data column, hover the cursor over the column, then click the arrow icon, and select Rename. Type a name in the field and press ENTER.
14 You can choose to group or display imported data in Web based on an attribute that already exists in the current project, as described in About linking imported data to project attributes. If your project attribute and imported attribute have multiple attribute forms, you can map the forms as well.

When you import data directly into a dashboard or a document, MicroStrategy automatically attempts to link imported data to attributes that already exist in the dashboard or the document. To manually link a data column to a project attribute, perform the following steps:

a In the table at the top of the page, right-click the attribute and select Link to Project Attribute. The Map to Project Attribute dialog box opens.
To select multiple columns to use as attribute forms, hold the Shift key while selecting the attributes.
b Browse or search for the project attribute.
c If the project attribute contains multiple forms, select the imported attribute to map to each form.

For example, your imported data contains Region_ID and Region_DESC columns. In your project, the Region attribute contains ID and Description forms. In the row for ID, select Region_ID from the Imported Attribute drop-down list. In the row for Description, select Region_DESC from the Imported Attribute drop-down list.

d Click OK to apply your changes.

To unlink the data column from a project attribute, hover the cursor over the column, then click the arrow icon, and select Unlink.

You cannot link data imported from a search.

15 Each column in a table will be imported as a separate attribute. If your data contains different attribute forms of the same attribute saved in separate columns, you can create a multiform attribute to combine the forms into a single attribute. For example, the LU_CALL_CENTER TABLE contains two columns, Call Ctr ID and Call Center Name, which will be imported as two attributes. You can create a single Call Center attribute that uses the columns as attribute forms. To create a multiform attribute, complete the following steps:
a Select the columns to use as attribute forms by holding the Shift key while selecting them.
b Right-click the selected columns and select Create Multiform Attribute. The Create Multiform Attribute dialog box opens.
c Type the name to use for the new attribute in the New Attribute Name field.
d For each attribute form, select the correct Form Category, such as ID or Description, from the drop-down list.
e For each attribute form, determine whether the form is displayed, as described below:
To display the attribute form, select the Display Form check box.
To hide the attribute form, clear the Display Form check box.
f Click Submit to create the new multiform attribute and return to the Preview page. The multiform attribute replaces the columns that were used to create it.

You cannot create multiform attributes on data imported from a search.

16 Repeat the appropriate steps above, starting at To define a data column, for each data column that you want to define.

To finish importing the data

17 You can explore your data to evaluate its quality and usability, and then refine your data to improve its quality before you import it into MicroStrategy Web. This process is also known as data wrangling. For example, you can remove white space, filter your data, delete duplicate rows or cells, find and replace data, concatenate columns, and so on. For steps, see Refining your data quality before importing.
18 You can improve performance by partitioning a very large dataset and creating search indexes for attributes. For reasons to use partitioning and search indexes, as well as steps to create them, see Improving performance: Partitioning large datasets and creating a search index.
19 Once your data is imported into Web, it is saved as an Intelligent Cube, which you can update to ensure that your dashboards, documents, and reports contain the most recent information available. By default, if the data in an Intelligent Cube needs to be updated, it is re-executed. All the data for the Intelligent Cube is loaded from the data source into Intelligence Server's memory, and the existing data for the Intelligent Cube is overwritten. You can set up incremental refresh settings to update the Intelligent Cube with only new data when the Intelligent Cube is republished or updated according to a schedule. This can reduce the time and system resources necessary to update the Intelligent Cube periodically. For background information on using incremental refresh to update Intelligent Cubes, see the In-memory Analytics Guide.

To define incremental refresh settings, click the Data Refresh Options icon Data Refresh Options icon. The Options - Data Refresh dialog box opens. Select one of the following options to determine how your data will be updated when the Intelligent Cube is run, then click OK to return to the Preview page:

To re-execute the Intelligent Cube and overwrite any existing data, select Replace existing data.
To update the existing data in the Intelligent Cube, as well as add new data that was not previously included in the Intelligent Cube, select Update existing data and add new data.
To update the Intelligent Cube only with new data that was not previously included in the Intelligent Cube, select Keep existing data and add new data.
20 Click Continue. The Save As dialog box opens.
21 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.
22 Click OK. Your data is saved and published as an Intelligent Cube.
If you imported data while creating a dashboard or a document, the data is added to the dashboard or the document as a dataset. For steps to work with datasets on a dashboard, see Adding and removing datasets from a dashboard. For steps to work with datasets on a document, see the Adding Text and Data to Documents chapter in the Document Creation Guide.
If you are not creating a dashboard or a document, the Data Imported page is displayed. You can use the imported data to create a dashboard, report, or document. To do so, select one of the following:
To create a dashboard, select Create Dashboard. For steps to create a dashboard, see Creating a dashboard.
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. See the Adding Text and Data to Documents chapter in the Document Creation Guide for steps to create a document using an Intelligent Cube as a dataset. For general steps to create a document, see the Designing and Creating Documents chapter of the Document Creation Guide.

Related topics

About importing data into MicroStrategy Web
Best practices: Importing data into MicroStrategy Web