What's in Connection Schema

This Section gives complete Information on the definition of each field/parameter in the Connection schema file, also specifying what & how to update each field value.

Connection file Schema consists of Six (6) Sheets, each with its own set of fields & purpose

Based on each section - Connections, Instance, Parameters & Data Tables are created

1. General

This section captures general Information that will be used for creation new file Connection

Purpose: File Connection is created based on 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 entire workbook (Y) or by worksheet(N)

Field	Type	Allowed Values	Validations
Name	Text	Free Text	Maximum 60 characters
			Start with alphabet
			Only special chars "-", "_" & Space allowed
Description	Text	Free Text	Maximum 100 characters
Type	Text	Excel Workbook	Default: Excel Workbook
		CSV
		TXT
Prefix	Text	3 digit characters
load Workbook	Text	Y	workbook is Imported
		N	specific worksheets only

2. Instance

Purpose : Instance Table is created based on details below

2.1 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 reference for fields

Field	Type	Allowed Values	Validation
Instance ID	Text	Free Text	Maximum 60 characters
Apply to Sheets	Worksheet name	Worksheet name	applicable to only worksheet
		Worksheet prefix *	applicable to all Worksheets with the prefix mentioned
		*	applicable to all Worksheets
Instance Model	Text	Model Name	Valid Model ID from the workspace

2.2 Instance Fields

This section comprises of Instance details like fields & their reference

Instance ID - specify the Instance ID created from 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 excel file or user selection

Reference - If reference type is excel file, need to specify the reference cell

Field	Type	Allowed Values	Validation
Instance ID	Text	Should be Instance ID defined in Instance Header sheet
Instance Field	Text	Only Field Names from the Instance Model	At least one field should be defined
Reference type	Text	Enable Selection	Field value will be user selection during the data import execution step
		Workbook Cell	Field value will be retrieved from excel workbook specific cell
		Name Range	Field value will be retrieved from excel workbook name range 1st cell
Reference	Text	Specific excel cell	Valid only for Reference type is Workbook cell
		Specific Name range	Valid only for Reference type is Name range

3. Parameters

Refers to parameters that are required for the connection & their references to read data

Purpose : Parameters Table is created based on details below

Information required :

Instance ID - as created in Instance Header sheet

Parameters - list of parameters/variables that 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 & map the parameter value to specific field value

Linked Reference Model - to link model in the workspace, to Identify the fields

Reference type - Identify where to retrieve the parameter value from for the each parameter

Reference - specify the details of reference type

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

Field	Type	Allowed Values	Validation
Instance ID	Text	Should be Instance ID defined in Instance Header sheet
Parameters	Text	Free Text
Parameter type	Text	Text	Default
		Number
		Field	Fields will refer to fields in a model selected as part of instance
		List	List will refer to fields (without linking to model)
Linked Reference field	Text	Specific Field within the Reference model	Mandatory for Parameter type "Field" / "List"
Linked Reference Model	Text	Specific Model within the Workspace
Reference type	Text	Workbook Cell	reads from specific workbook cell
		Name Range Cell	reads from specific Name Range
		VLOOKUP	Identifies from VLOOKUP value
		HLOOKUP	Identifies from HLOOKUP value
Reference	Text	Specific excel cell	Valid only for Reference type is Workbook cell
		Specific Name range	Valid for Reference type is Name range
		Specific Name range	Valid for VLOOKUP/HLOOKUP Note - assumption of only 2 columns(VLOOKUP) or 2 rows (HLOOKUP)
Ignore Blanks	Y/N	Y	parameter saved with blank value
		N	parameter ignored if value is blank

4. Tables

Purpose : Data Tables are created based on details below

4.1 Tables Header

Instance ID - as created in 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 or rows to be skipped before data starts from start of the range

Enable Rows to Field Mapping - Option to enable field mapping during data transformation

Field	Type	Allowed Values	Validation
Instance ID	Text	Should be Instance ID defined in Instance Header sheet
Table Name	Text	Unique ID	Maximum 30 Chars & No Special Characters
Table Description	Text	Free Text Description	Maximum 60 Chars
Reference type	Text	Fixed Range	specific cell range for rows & columns that will be retrieved for creating data table
		Name Range	specific name range defined in the excel that will be retrieved for creating data table
		Column Range	specific column range defined in the excel that will be retrieved for creating data table Note Column range will be like uploading csv, where we select from which row, data is available and which row has header
Reference	Text	Specific Fixed Range	(example - Sheet'!$A$67:$X$68)
		Specific Name Range	(example - _NCF or _PL or _DIV)
		Specific Column Range	(example - C:I or Sheet!$B:$G)
Start Row for Column range	Number	specify the start row	Valid only for Reference type = Column Range
Has Column Header	Y/N	Y	Column Header will be ignored
		N
No. of Header rows	Number	0 & above	Header rows will be ignored
Skip first few data rows	Number	0 & above	Data range starts after skip rows
Enable Rows to Field Mapping	Y/N	Y	Column Header = Y, is prerequisite for this option
		N

4.2 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 field

Field Type - type of values that will be populated for the field

Linked Reference Field - Link & map the field values from the specified Field values

Linked Reference Model - link & 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 Zeroes as field values or not

Field	Type	Allowed Values	Validation
Table Name	Text	as Created in the Table Header section
Field Name	Text	Unique field id	Maximum 30 chars, & No Special Characters
Field Description	Text	Free text for details
Field Type	Text	Text
		Number
		Field	Fields will refer to fields in a model selected as part of instance
		List	List will refer to fields (without linking to model)
Linked Reference Field	Text	specify valid field ID	fields within the instance model
Linked Reference Model	Text	specify valid model ID within the workspace	Mandatory for Field type "Field" / "List"
Axis	Text	Columns	field values are mapped from columns of data range specified
		Rows	field values are mapped from rows of data range specified
		Measure	Data rows will be mapped to fields
Reference	Text	Specify the number of column in the excel file data range to map using the below format Column_1, Column_2, Column_3...	for Axis = Columns
		Row_1, Row_2, Row_3...	for Axis = Rows
		Data	for Axis = Measure
Column Range	Text	specify the column range to map the field values	example - Column_2:Column_24 Applicable only for Axis = Rows
Ignore Blanks	Y/N	Y	field values will be mapped for blanks
		N	blank field values will be ignore
Ignore Zeros	Y/N	Y	field values will be mapped for zeroes
		N	Zero field values will be ignore