Using Data Preparation Options

The Diagnostics group contains the Test and Tutorials buttons. You can enable the Test button to open the Session Log window, clear the session log, view the synonym being prepared in a text window, and view a list of prepared worksheets.

The options include:

Session Log

Opens the Session Log window. The session log contains all of the commands that have been processed for the current upload session. Using this window, you can filter the log file, clear the log file, download the log file to your PC, refresh the log, and manage your server agents.

Clear Session Log

Deletes all content from the log file.

View MFD

Opens the Master File being prepared in a text window. You can search for text within the Master File.

View AFD

Opens the Access File being prepared in a text window. You can search for text within the Access File.

Prepared Worksheets

Opens a window that lists the prepared Worksheets and the details of each, including the date on which it was prepared, the number of rows and columns, whether there is a header row, and the names of the target application, target synonym, target table, target adapter, and target connection.

The Tutorials button opens a high-level document that outlines the main functions of the Categorize Fields into Measures, Dimensions and Hierarchies screen, as well as the basic flow of common procedures.

The Undo / Redo group contains the Undo and Redo options. You can use these options to reverse the last action you performed, or to cancel your last Undo action. For example, if you renamed a field, but do not want to save it, you can click Undo.

The Preparation group contains options that you can use to review your data and prepare it for upload. The options include:

Preparation Options

Opens the Preparation Options dialog box, where you can set the following options for Excel spreadsheets.

Data scan

Scans all records to determine data type for each column

Row scan limit

Indicates how many worksheet rows are scanned to determine the formats of the columns. Set this value to 0 if you want to scan all of the worksheet rows.

Extend character length

Extends character length by the percentage specified.

Number of header rows

Sets the number of rows used as a header row in the worksheet. Set the value to 0 if a header is not used.

Decompose Date fields into components

Decomposes Date fields into Year, Quarter, Month, and Day components.

Date Order

Allows you to select a format in which the Date fields appear.

Activate GEOGRAPHIC_ROLE assignment

Assigns geographic roles based on column name analysis.

Provides the following additional options that are only available for comma separated values (CSV) files:

Field Delimiter

Allows you to select a column separator from the number of available options, which include: none, comma, tab, pipe, space, semi-colon, colon, type-in delimiter. The default value is comma.

Non-printable Delimiter

Defines whether the string in the delimiter field is a printable string or a non-printable decimal character.

Field Enclosure

Allows you to select an enclosure characters for each field from the number of available options, which include: none, single quote, double quote, space, and type-in enclosure. The default value is double quote.

Header row

Defines if the header line be used as column names.

Preserve format

Indicates whether the original data layout, including empty records and linefeeds, will be preserved.

CODEPAGE

Specified the code page for the stored data that will be added to the Master File of the generated synonym.

CDN

Continental Decimal Notation (CDN) determines the punctuation used in numerical notation for input data.

Raw Data

Displays the raw data of the file to be uploaded in the Output window.

Error Check

Produces informational messages, if the uploaded file contains inconsistent data, based on the first row of data. The Error Check result is displayed in the Output window.

The Templates group contains options that you can use to create and apply a template to your selected file. The options include:

Save

Saves the existing synonym as a template.

Invoke

Invokes saved templates that you created earlier.

The Metadata group contains several data preparation options that you can use to enhance your data. This includes defining virtual fields, creating Compute and Variable fields, joining data sources, and pivoting rows of data into columns. The options include:

Define

Opens the Expression builder, where you can create a define field.

Compute

Opens the Expression builder, where you can create a compute field.

Variable

Opens the properties window, which provides access to the following options:

Variable Name

Specifies the name of the variable.

Prompt Caption

A description of the variable that will appear in the prompt.

Default Value

Specifies the default value of the variable.

Prompt Values

Specifies optional prompt values for the variable.

Quoted

If selected, this option adds single quotation marks around the variable.

Format

Provides a drop-down menu of date formats for the variable. The options are Alpha fixed, Integer, and Decimal Packed.

Joins

Opens the Modeling View window, where you can join multiple worksheets to create a cluster.

Pivot

Opens the Pivot Prepared Data dialog box, where you can pivot any repeating columns or groups of columns into rows.

The Sample Data group contains options that you can use to toggle the Autorun and Data Preview options On and Off, stop the current request, and view the Sample Data in the Output Window to instantly visualize field values.

Autorun

Provides access to the following options:

Off

Disables autorun in the Output Window.

On

Enables autorun in the Output window.

Data Preview

Provides access to the following options:

Off

Disables the display of data values in the Measures/Dimensions Window.

On

Enables the display of data values in the Measures/Dimensions Window.

Sample Data

Runs the Sample Data request in the Output Window.

The Options group consists contains the View and Options functions. You can use the View button to customize the view of your Categorize Fields into Measures, Dimensions and Hierarchies screen. You can use the Options button to specify options for generating and displaying the synonym and set upload limits.

Click View to access the following options to customize your screen:

Reset View

Reverts all your screen customizations to the default view.

Grid Details

Provides access to the following options:

View as Columns

Displays data in columns inside windows. This option is enabled by default.

View as Tooltip

Displays data in tooltips, as you hover over fields.

Table/Column Window

Provides access to the options that control placement of the Table/Column window on the screen, such as:

Tile Horizontal

Displays windows on the screen in the horizontal succession. This option is enabled by default.

Tile Vertical

Displays windows on the screen in the horizontal succession.

Show

Displays the Table/Column window on the screen.

Hide

Hides the Table/Column window from view. This option is enabled by default.

Measures/Dimensions Window

Provides access to the options that control placement of the Measures/Dimensions window on the screen, such as:

Show

Displays the Measures/Dimensions window on the screen. This option is enabled by default.

Hide

Hides the Measures/Dimensions window from view.

Filters/Groups Window

Provides access to the options that control placement of the Filters/Groups window on the screen, such as:

Show

Displays the Filters/Groups window on the screen.

Hide

Hides the Filters/Groups window from view. This option is enabled by default.

Output Window

Provides access to the options that control the placement of the Output window on the screen, such as:

Show Floating

Undocks the Output window, so it can be moved around the screen.

Show Docked

Docks the Output window on the right side of the screen. This option is enabled by default.

Show Maximized

Enlarges the Output Window to the size of the screen.

Hide

Hides the Output Window.

Multiple Output Windows

Provides access to the options that control the appearance of multiple Output windows on the screen, such as:

Show All Tabbed

If this option is selected, all output requests are shown in the Output window in tabs. This option is selected by default.

Show Only Latest

If this options is selected, the single-tab Output window is reloaded to display the latest output request.

Click Options and select Advanced to select the following synonym creation options:

Column Management

Provides access to the following options.

Name display strategy

You can select how the column names are displayed in the Tables/Columns and Measures/Dimensions windows from one of the following choices:

• Name. Assigns the FIELDNAME attribute from the synonym as the column name displayed in the Tables/Columns and Measures/Dimensions windows.
• Title. Assigns the TITLE attribute from the synonym as the column name displayed in the Tables/Columns and Measures/Dimensions windows. TITLE is the default value.
• Description. Assigns the DESCRIPTION attribute from the synonym as the column name displayed in the Tables/Columns and Measures/Dimensions windows.
• Alias. Assigns the ALIAS attribute from the synonym as the column name displayed in the Tables/Columns and Measures/Dimensions windows.

Function display strategy

Selects the functions information that displays in the Expression Builder, and on the context menu for a column, when you add a function to the synonym. Select one of the following values:

• Syntax. The function syntax (function name and parameters) is displayed. This is the default value.
• Short Description. A short description of what the function calculates is displayed.

Filter Options

Provides options for selections in check boxes.

Check boxes display strategy

Describes how selected values display when you select values in a filter. Select one of the following choices:

• Show selected values on top. Moves the selected (checked) values to the top of the list of values. This is the default.
• Keep selected values in place. Leaves the selected values in their original places in the list of values.

Check boxes: delay for server action (seconds)

Sets the time in seconds before the server acts on activation or deactivation of a check box. The default value is 1.5 seconds.

Language Generation Options

Provides options for generating field names and synonym references in the synonym being prepared.

Use segment to qualify field reference

Specifies when the segment name should be added to the field name in order to qualify the field name. Select one of the following values:

• For duplicate fields. Uses the segment name to qualify the field name only when multiple segments contain the same field name. This is the default value.
• Always. Uses the segment name to qualify the field name.

Use application name to qualify synonym reference

Specifies whether synonym references in the synonym being prepared will include the application name. Select one of the following values:

• Yes. Adds the application name to synonym references (appname/synonymname). This is the default value.
• No. Does not add the application name to synonym references.

Limits

Sets limits for working with the Upload or Connect to Data wizard.

Maximum number of rows for test

Sets the maximum number of rows of sample data displayed. The default value is 50.

Maximum number of columns for test

Sets the maximum number of columns of sample data displayed. The default is 999999.

Maximum number of identical error messages

Sets the maximum number of identical error messages that can be generated by the wizard.

Random Sampling limit (in %)

Defines the percentage for the random sample that is used for statistical analysis.

Undo/Redo Limit

Sets the maximum number of undo and redo actions supported. The default value is 50.

The Load group contains the Load Options and Load Data items.

Click Load Options to select the following data loading options. These options may change depending on the target adapter selected.

Load Option

Provides access to the following options:

New

Uploads a new worksheet to create a synonym.

Append Existing Data

Adds new data to the existing synonym without changing the structure and parameters of the synonym.

Target Adapter

Allows you to select a database, in which the unloaded data is stored.

Target Connection

Specifies a connection for the selected adapter.

Target Synonym Application

Specifies the application directory for the synonym. Click the ellipsis button to change this directory.

Target Synonym

Specifies the name of the synonym, as appears in the repository. By default, the name is the same as the name of the source worksheet.

Target Table Name

Indicates the name of the target table in the database.

Bulk Load

Specifies the method of loading data.

Target Data File

Contains the name of the DFIX target data file in the database.

Field Delimiter

Allows you to select a column separator from the number of available options, which include: none, comma, tab, pipe, space, semi-colon, colon, type-in delimiter. The default value is comma.

Header row

Defines if the header line be used as column names.

Field Enclosure

Allows you to select an enclosure characters for each field from the number of available options, which include: none, single quote, double quote, space, and type-in enclosure. The default value is double quote.

Overwrite Existing Synonym

Overwrites the existing synonym with the same name.

Click Load Data to upload your synonym to the repository without leaving the Upload or Connect to Data wizard. The Load Data button also brings up the Load Options dialog box, allowing to review and modify your loading parameters.

The Load and Next group the Load and Next function, which uploads your synonym to the repository and brings you to the next screen, where you can select how to use your synonym.

The Table/Column window displays the fields in the same order as they are displayed in the source file.

The Table/Column Window is shown in the following image.

The Table/Column window has the following options:

Display

Toggles between two display options, Columns and Tables.

Find

Opens the search field, where you can type the name of the field that you want to find and allows you to modify your search options.

View

Provides access to the following options:

Expand All

Expands all nodes, folders, and hierarchies.

Collapse All

Collapses all nodes, folders, and hierarchies.

Choose columns

Opens the Choose Columns dialog box, where you can select the columns that you want to display in the Measures/Dimensions window.

Reset to defaults

Reverts the window to its default view.

The Measures/Dimensions window shows you how the Upload and Connect to Data wizards interpret your data and designates the data type for each field. The numeric values, such as Gross Profit or Cost of Goods, are interpreted as measures. The alphanumeric values, such as Product or Geography, become dimensions. A dimension is a way to categorize data. Dimensions can be organized into hierarchies to define the relationships between the fields in the hierarchies. For example, a Geography hierarchy can contain the Continent, Country, State, and City dimensions. You can also assign dimension attributes to any dimension field, whether or not it is in a hierarchy. When applied to a field, attributes provide supplementary information that can be used for analysis or display. For example, in a Geography hierarchy, which includes the Country, State, and City dimensions, Population can be assigned as an attribute of the City dimension. Each category of data is represented by an icon. The following table identifies these icons and describes what they represent.

Icon	Identifies	Description
	Measure group	Contains individual measures.
	Measure	Measure is a numeric field. It resides inside a measure group. Measures can be moved between measure groups and between measures and dimensions areas.
	Dimension folder	Contains dimensions, dimension hierarchies, and attributes.
	Dimension Hierarchy	Resides inside the dimension folder and contains individual dimensions and associated attributes.
	Dimension	Dimension is an alphanumeric field. It resides inside the dimension hierarchies. Dimensions can be moved between dimension folders, between and outside dimension hierarchies. You can move a dimension into a measure group and turn it into a measure. You can also move a dimension inside an attribute folder and turn it into an attribute.
	Attribute folder	Contains individual attributes associated with a dimension.
	Attribute	Resides inside an attribute folder. Attributes can be moved between attribute folders. If you move an attribute outside an attribute folder, it inherits the identity of a hierarchical level to which it is moved.

The Measures/Dimensions window is displayed in the following image.

The Measures/Dimensions window has the following options:

Reset to Default

Eliminates all changes and reverts the view back to the initial categorization of measures and dimensions.

Remove Hierarchies

Removes dimension hierarchies and displays individual dimensions under the Dimensions folder.

Hide All

Hides all fields from the view.

Note: When a field is hidden, it is moved to the Table/Column window.

Find

Opens the search field, where you can type the name of the field that you want to find and allows you to modify your search options. Any occurrence of the search string found in the window is highlighted.

View

Provides access to the following options:

Expand All

Expands all nodes, folders, and hierarchies.

Collapse All

Collapses all nodes, folders, and hierarchies.

Choose columns

Opens the Choose Columns dialog box, where you can select the columns that you want to display in the Measures/Dimensions window.

Reset to defaults

Reverts the window to its default view.

Each node in the Measures/Dimensions window opens a context-sensitive shortcut menu that can be used to insert and modify fields, view sample data, perform statistical analysis and data preparation techniques. You can access these options by right-clicking each level. These context menu options are conditional and depend on the type of an element and its place in the synonym structure. The following list identifies the context menu options for measure group folders, measure groups, measures, dimensions node, dimension folders, dimensions, hierarchies, and attributes.

Insert

Depending on the context, provides access to the following options:

New Measure Group

Creates a new measure group inside this Measure Group folder.

New Dimension Folder

Creates a new dimension folder inside the Dimensions node.

New Parent/Child Hierarchy

Opens the Create a Parent/Child Hierarchy dialog box, where you can create a new hierarchy.

New Levels Hierarchy

Creates a new level hierarchy inside this dimension folder.

Filter

Opens the Filters/Groups window, where you can create or modify a filter.

Character Function

Provides a choice of preconfigured character functions that open in the Function Assist for New Define dialog box.

Numeric Function

Provides a choice of preconfigured numeric functions that open in the Function Assist for New Define dialog box.

Advanced Function

Opens the Define dialog box, where you configure a new function.

Group Values

Opens the Filters/Groups window, where you can configure group values.

Group Numeric (Binning)

Opens the Binning wizard, where you can configure a numeric group.

Aggregation Function

Provides a choice of preconfigured aggregation functions that open in the Function Assist for New Compute dialog box.

Analytic Function

Provides a choice of preconfigured analytic functions that open in the Function Assist for New Compute dialog box.

Advanced Aggregation

Opens the Compute dialog box, where you can a compute field using advanced aggregation options.

Properties

Opens the Properties dialog box. For more information, see Using the Properties Dialog Box.

Pivot

Opens the Pivot Prepared Data dialog box, where you can pivot any repeating columns or groups of columns into rows.

Geographic Role

Opens the Set Geographic Role dialog box, where you can configure a geographic role for the field.

Create Hierarchy

Transforms the dimension into a hierarchy. This option is only available for standalone dimensions.

Remove Hierarchy

Removes a hierarchy and displays all the items inside this hierarchy as individual dimensions.

Remove from Hierarchy

Removes the field from a hierarchy. This option is only available for the dimensions that are part of a hierarchy.

Show/Hide Attribute

Toggles between displaying or hiding attributes for the field.

Sample Data

Displays the sample data for all the measure groups inside this measure group folder in the Output window.

Sample Map

Displays the data for this field in a map, which runs in the Output window.

Data Profiling

Provides access to the following options:

Statistics

Displays statistic analysis of the field in the Output window.

Values

Displays the values for this field for the selected number of rows in the Output window.

Duplicate Values

Displays the duplicate values for this field for the selected number of rows in the Output window.

Values (Bar Chart)

Displays the values for the field in a bar chart in the Output window. The chart is limited to 50 values.

Values (Pie Chart)

Displays the values for the field in a pie chart in the Output window.

Impact Analysis

Displays the impact analysis results in the Output window.

Rename

Opens the Rename dialog box where you can rename the field.

Hide

Moves the field to the Table/Column window.

Cut

Cuts the field from its directory.

Paste

Pastes an item inside the directory.

The Filters/Groups window automatically opens when you create a new filter or group. When you open two or more items at the same time, they display nested in the same window, as shown in the following image.

Depending on the field type, the Filters/Groups window displays the following options:

Find

Opens the search field, where you can type the name of the field that you want to find and allows you to modify your search options.

Expand

Maximizes the section of the window.

Drop-down arrow

Provides access to the following options for filters:

Display

Determines a display format for the filter. The options include: text box, double list, check-box list, pull-down list, and slider. The default option is slider for Numeric fields and check box for all other fields.

Relation

Creates a relational expression. The options include: EQ, NE, LT, GT, LE, GE, and Range. The default value is Range for Numeric and EQ for all other fields.

Values

Toggles between showing and hiding the NULL values for this field.

Hide Filter Card

Removes filter data from the Filters/Groups window, while leaving the window open.

Delete Filter

Deletes the filter from the synonym.

Properties

Opens the Filter properties dialog box, where you can change the Title for the filter, and view its Name and Base field name properties.

Move Up

Moves the filter up in the Filters/Groups window.

Move Down

Moves the filter down in the Filters/Groups window.

Provides access to the following options for groups:

Display

Determines a display format for the group. The options are: Double List and Single List. The default value is Single List.

Add all shown values to a new group

Selects all values and adds them to a new group.

Create a New Group

Creates a new empty group, to which you can add values manually.

Edit properties

Opens the Edit properties dialog box, where you can edit the Name, Title, and Default Value properties.

Hide Group Card

Removes the group data from the Filters/Groups window.

Move Up

Moves the group up in the Filters/Groups window.

Move Down

Moves the group down in the Filters/Groups window.

Additionally, if the list of values spans through multiple pages, you can increase or decrease the number of values displayed on each page by clicking the Increase Page Size or Decrease Page Size buttons, as shown in the following image. You can use the First Page, Previous Page, Next Page, and Last Page buttons to navigate between pages.

The Output window displays data and status messages for various features. The Output window is shown in the following image.

The Output window has the following options:

Find

Opens the search field, where you can type the name of the field that you want to find and allows you to modify your search options.

View

Provides access to the following options:

Expand All

Expands all nodes, folders, and hierarchies.

Collapse All

Collapses all nodes, folders, and hierarchies.

Choose columns

Opens the Choose Columns dialog box, where you can select the columns that you want to display in the Measures/Dimensions window.

Reset to defaults

Reverts the window to its default view.

If more than one tab is open, you can right-click a tab to access the tab menu, as shown in the following image. The menu allows you to close all other tabs, close tabs to the right, or show only the latest tab.