What's in Connection Schema

This section provides complete information on the definition of each field/parameter in the Connection schema file, specifying what and how to update each field value. The Connection file schema consists of six sheets, each with its own set of fields and purpose. Based on each section, connections, instances, parameters, and data tables are created.

General

This section captures general information that will be used for creating a new file connection. The file connection is created based on the details below.

Information required:

• Name: File connection name to be created.

• Description: Connection description for details.

• Type: Type of file to be uploaded by users for this connection.

• Prefix: To be used for creating the remote tables.

• Load Workbook: Indicator to load the entire workbook (Y) or by worksheet (N).

Instance

The instance table is created based on the details below.

Instance Header

This section captures the Excel workbook instances to be created to import data.

• Instance ID: Unique name that will be used to create the instance for remote tables for Excel sheets/workbook to be uploaded.

• Apply to Sheets: Instance created to be applicable for different sheets referred here.

• Instance Model: Existing model in the workspace, to be used as a reference for fields.

Instance Fields

This section comprises instance details like fields and their reference:

• Instance ID: Specify the instance ID created from the Instance Header sheet above.

• Instance Field: Fields that would be identified for instance creation, which are variable for loading multiple sets of data.

• Reference type: Fields defined above will need reference, either from the Excel file or user selection.

• Reference: If the reference type is Excel file, you need to specify the reference cell.

Parameters

Refers to parameters that are required for the connection and their references to read data. The parameters table is created based on the details below.

Information required :

• Instance ID: As created in the Instance Header sheet.

• Parameters: List of parameters/variables for each instance values to be imported.

• Parameter type: Type of parameter values to be recorded in the parameter table.

• Linked Reference field: To link and map the parameter value to a specific field value.

• Linked Reference Model: To link the model in the workspace to identify the fields.

• Reference type: Identify where to retrieve the parameter value from for each parameter.

• Reference: Specify the details of the reference type.

• Ignore Blanks: Identifier to record the parameters even with blanks (Y) or not (N).

Tables

Data tables are created based on the details below.

Tables Header

• Instance ID: As created in the Instance Header sheet.

• Table Name: Unique ID for the data tables to be created, for each of the data range in Excel.

• Table Description: Specify the description to identify the purpose of table data.

• Reference type: Specify the reference of data to be retrieved from the Excel.

• Reference: Reference type details.

• Start Row for Column range: Start row to import data from (valid only for column range reference).

• Has Column Header: Identifier to tag if table header exists.

• No. of Header rows: Number of rows to ignore for header.

• Skip first few data rows: Number of rows to be skipped before data starts from the start of the range.

• Enable Rows to Field Mapping: Option to enable field mapping during data transformation.

Tables Details

Data tables details are captured using the information below:

• Table Name: As created in the Table header section, unique Table ID.

• Field Name: Define the field names for each of the tables, each row/column in the table definition can be identified with field name.

• Field Description: Specify the description for details of the field.

• Field Type: Type of values that will be populated for the field.

• Linked Reference Field: Link and map the field values from the specified field values.

• Linked Reference Model: Link and map the fields from the specified model.

• Axis: From the data range defined in Table header for each Table name, we would map row/column from the data range to remote fields for importing. Axis will help in specifying the row/column/measure to be populated for the field.

• Reference: Data values details.

• Column Range: For Axis = rows, meaning field values to be mapped from rows, specify the column range to transpose as field values.

• Ignore Blanks: Identify to consider blanks as field values or not.

• Ignore Zeros: Identify to consider zeros as field values or not.