Remote Query

Remote query defines how data will be retrieved from remote data source and how the remote will be used in Perfion. All remotes must have remote query.

Create New or Edit Existing Remote Query

There are two ways to create new or edit existing remote query: from the Administration top menu or from the “Feature Definition” window.

Create Remote Query from the Administration Top Menu

This is a standard way to create or edit any type of remote query. The procedure (refer to Figure 6):

Select Administration in the top menu and then choose Remote Queries.
The “Remote Queries” window will open. This window contains all remote queries.
Right click anywhere in the remote query list to open a context menu.
From the context menu select to create a new remote query or edit the selected remote query. In order to edit existing remote query one can also double click on selected remote query.
If options “Add” or “Edit” were selected, then the “Remote Query Setup” window will open.
Set up remote query. Refer to Remote Query Setup for more information.
Click “Save” and then close the “Remote Queries” window.

Create Remote Query from the “Feature Definition” Window

Remote query can also be created directly from “Feature Definition” window when assigning remote query to a feature in order to make it a remote feature. The procedure (refer to Figure 7):

Create a new feature or open an existing feature for editing from “Features” pane.
The “Feature Definition” window will open.
In the “Feature Definition” tab “Remote” area “Query” select box shows the selected remote query which is used with this feature. Here one can select an existing remote query and press “Edit” button or select an empty option and then press “New” button.
The “Remote Query Setup” window will open.
Set up remote query. Refer to Remote Query Setup for more information.
Click “Save” button.
At this point the new remote query was created or existing remote query was updated and one can continue setting up the feature.

Figure 6: Create or edit remote query from the Administration top menu

Figure 7: Create or edit remote query from “Feature Definition” window

Test, Default and Custom Modes

The remote can be used in four different modes which control where and how in Perfion they will be used.

Mode	Usage	Description
Test (setup)	“Remote Query Setup” window	Test mode is used in order to test remote if it works using test parameters. It is recommended to set test mode so, that it will return only small amounts of data from remote source. This mode is activated when the “Test” button is pressed in “Remote Query Setup” window.
Test (Headers only)	Remote feature data field selection (in “Feature Definition” window)	It uses test data like in Test (setup) mode, but the purpose of it is to retrieve only headers information from remote data. It is used from “Feature Definition” window when selecting remote data field.
Default	Grid, “Item Editor” window Report Designer, etc.	Default mode is used everywhere in Perfion except two cases as described in Test (setup) and Test (headers only) modes.
Custom	Report Designer etc.	This mode is the same as Default mode, but with custom parameters. In some cases in Perfion where remotes are used (e.g. Report Designer) it is possible to overwrite some of parameters for remote with custom parameter values. In this case the parameters specified in Select Statement and/or Setup XML will be overwritten with those custom parameter values coming e.g. from the Report Designer. For more information how to use such parameter refer to the manual “Perfion Parameters”.

Remote Query Setup

All remote query configurations are done from “Remote Query Setup” window. Overview of parameters (refer to Figure 8):

Name. Remote query name.
Remote Connection. Connection which is used to get access to remote data. Remote connection specifies all parameters needed in order to connect to remote data source. It will determine which type of remote data source will be used (Database, OData or Web Service) which will also affect the layout of Remote Query Setup window. Note that Web Service type remotes do not have Select Statement window and requires Setup XML data, while remotes which have other data sources (Database or OData) use Select Statement and Setup XML is optional.
Select Statement. In this window one can specify SQL query (for Database remotes) or OData query (for OData remotes). Web Service remotes do not use this window.
Remote Query Type. Remote query type selector which allows to define if remote query will be of Single or Cluster type.
Setup XML. XML document, which allows specifying various parameters for remote query.
Test data preview window. This window is used to show preview of remote data returned for remote query when using test mode. It is hidden at first and is shown only after executing data retrieval test which can be done by pressing the “Test” button or F5 button on the keyboard.
Setup XML template. This is a button which when pressed will insert the template of fully configured Setup XML document with all parameter types.

Figure 8: Remote Query Setup

Query Name

Remote query name is unique name to identify the remote query. We recommend to specify a name, which would explain what type of remote query it is, e.g. what type of remote data it will retrieve.

Remote Connection

Remote connection will specify all parameters needed in order to connect to remote data source. One can select one of available remote connections or create a new one.

To create a new or to edit existing remote connection (refer to Figure 8):

Open remote connection select box and choose a blank field or existing remote connection.
If the existing remote connection is chosen, then press “Edit” button to modify and if the blank field was selected, then press the “New” button to create a new remote connection.
“Remote Connection Setup” window will open.
Edit or define a new remote connection name. The name should be unique and we recommend to use names, which describe the connection. Note that the same connection can be use not just for remote queries, but also in other places in Perfion, e.g. Actions and in such case the remote connection name will be used as identifier for remote connection.
Specify the remote connection information in Connection Info text box. The remote connection type will be determined based on how the data was specified. Refer to Remote Connection Type.
1. If it is an URL, then the remote connection will be of Web Service type.
2. If the data starts with “Provider="OData"”, then the remote connection will be of OData type.
3. If none of the above were detected, then remote connection will be of Database type.
4. If no data is provided
Click “Save”.

The type of remote connection will be defined based on remote connection type. If, for example, one uses web service URL as connection, then remote will become web service type remote. Connections for different data sources are specified using different specifications and Perfion will detect which one is used to define the remote type. Refer to remote query setups for different data sources to see remote connection specifications.

Remote Connection Type

Remote connection type will be determined based on what data is provided in “Connection Info” field in Remote Connection Setup window.

If it is an URL, then the remote connection will be of Web Service type.
If the text starts with “Provider="OData"”, then the remote connection will be of OData type.
If none of the above were detected, then remote connection will be of Database type.
If the “Connection Info” field is left empty, then it will be a Database connection to Perfion database.

Remote Connection Templates

To make it easier to define the remote connection one can click the right mouse button inside the Connection Info field to activate the context menu from which one can select various connection templates. The templates are predefined to reflect various supported types of configurations which could be used in Perfion. The template after it is inserted has to be updated by replacing placeholder fields (which start with “<” and end with “>”) with actual data. Refer to Figure 9.

Figure 9: Remote Connection Setup window - templates

Create Remote Connection from the Administration Top Menu

One can create a new remote connection from the Administration top menu. The procedure:

Select Administration in the top menu and then choose Remote Connections.
The “Remote Connections” window will open. This window contains all remote connections.
Right click anywhere in the remote connections list to open a context menu.
From the context menu select to create a new remote connection or to edit the selected remote connection. In order to edit existing remote connection one can also double click on selected remote connection.
If options “Add” or “Edit” were selected, then the “Remote Connection Setup” window will open.
Set up remote connection.
Click “Save” and then close the “Remote Connections” window.

Remote Query Type

The select box controls the type of data Perfion expects from remote data source. If the “Cluster” type is selected, then remote query will be expected to use variant data.

The expected remote type can be also defined in the Setup XML using the attribute “type”. However, the information provided in Setup XML is optional and is only used to support special cases, e.g. when using Web Service type remotes. In any case the select box will have priority and in case the types do not match, Perfion will overwrite the value in Setup XML to have the same value as defined in “Remote Query Type” select box.

Select Statement

Select statement window allows to define the query to be used for data retrieval. For database remotes one can define SQL query in this window, while for OData remote – OData query.

The Select Statement window is not used for Web Service remotes, because all the configurations are made in the Setup XML. The Select Statement window will be automatically hidden after a Web Service type remote connection is chosen.

The query defined in this window in most cases will be a standard query with an extra Perfion controls added to make query usable in different scenarios. For example, in order to use the same query in the grid and in Item Editor, the different amounts of data have to be retrieved using the same query. To control the amount of data, Perfion needs to know which data columns from remote are the key columns, which data columns are the variant columns, etc.

Users can also specify extra parameters, which allows controlling retrieved data more precisely for each scenario where data may be used in Perfion application. For example, users can specify available languages as a custom parameter, so data can be customized to retrieve data based on e.g. languages to create localizable remote feature, which returns data in several languages per each item.

Select Statement – Parameters

Select statement parameters can be of various types, but the rules are almost the same for all type of parameters. The differences will be only in how they are used, e.g. which keywords are used to distinguish one from another.

The parameter format

#<parameter>[:<parameter values>]#

There are several types of parameters:

Type	Format	Example	Description
Key feature	#<Key feature ID>#	#101#	This is a special parameter to detect key feature ID. In this case the parameter does not have name, but instead integer number >= 0. In example, key feature ID is equal to 101. This type of parameter is allowed only once in select statement, unless exactly the same parameter (with the same key feature ID value) is repeated multiple times.
Variable	#@<parameter name>#	#@ID#	This is a normal parameter. The parameter name can be chosen by user, but there are also some parameter names, which are reserved by Perfion for special use. Reserved keywords: <no name> - Headers only breaker ID – Key feature KEYFEATURE – Key feature LANGUAGES – System languages LANGUAGES() – System languages (a special case) VARIANT – Variant parameter REPLACE – Replace parameters. All parameters, which starts with “REPLACE” falls into this category. Note that parameter names are case insensitive.
Perfion.In	Perfion.In(<query parameter name>, #<parameter name>#) or Perfion.In(<query parameter name>, <parameter values>)	Perfion.In(ID,#@ID:2,3#) or Perfion.In(ID,1,2,3) or Perfion.In(ID,’1’,’2’,’3’)	The parameter is used only with OData query. Perfion.In is like a function, which takes two parameters as arguments. The first one (query parameter name) is used to identify entity name in OData source data and the second one is Perfion parameter or comma separated list of parameter values. Refer to Perfion.In Block for more information.

Parameter values are optional. If parameter values are defined, then the values will be valid for all modes (Test, Default and Custom), but some values can be automatically overwritten by Perfion or by configuration, e.g. by Report Designer in Custom mode. Moreover, if Setup XML is defined and has parameter with the same name as in Select Statement, then parameter values from Setup XML will be used instead (in most cases, but there are exceptions, e.g. with key feature).

Parameter values can be of several types:

Parameter value type	Parameters	Example	After evaluation (Test mode)	Description
No value	#@#	#@:1,2,3#	1=0 or 1=1	All parameters may be used without values, but headers only breaker parameter is not considered as a real parameter and has no values. The values can be defined, but they will be ignored.
No value	#@LANGUAGES()#	#@LANGUAGES(Name_)#	Name_EN,Name_DE	This parameter values will be ignored if defined. The parameter is special, because it will use multiple values, but these values will come from @LANGUAGES parameter instead.
Single value	#@REPLACE#	#@REPLACE:1,2,3#	1,2,3	Most parameters can be specified with a single value, but only @REPLACE type parameter can be specified with a single value only. Everything defined after the colon (:) will be treated as a single value.
Multiple values	All other	#@ID:1,2,3#	1,2,3	All parameters, which are not as defined by “No value” or “Single value” may have multiple values. Values are comma separated and can be used with single quotes or without. All parameter values after processing will be escaped with single quotes if at least one value is not of numeric type.
		#@ID:1,2,3a#	‘1’,’2’,’3a’
		#@ID:’1’,’2’,’3’#	‘1’,’2’,’3’

Perfion parameters in Select Statement are just placeholders, which will be replaced with actual values when Select Statement is evaluated. All Select Statement parameters with multiple values will become a list of comma separated values after evaluation. The table below shows examples of various cases how parameters are defined and how they will look after evaluation when used in different modes. The values are shown taking into account that Setup XML is not used, e.g. not defined or if defined, then has no matching values.

Mode	Before evaluation	After evaluation	Description
Test	#101# #@ID# #@KEYFEATURE# #@VARIANT# #@LANGUAGES# #@LANGUAGES()# #@REPLACE# #@CustomParam#	<empty string>	Test mode will use only manually defined values regardless of parameter type. In this case there are none. NOTE: @CustomParam is user defined parameter with any name, which is not reserved.
	#102:1,2# #@ID:1,2# #@KEYFEATURE:1,2# #@VARIANT:1,2# #@LANGUAGES:1,2# #@CustomParam:1,2#	1,2	Manually defined values will be added using formatting, e.g. using single quotes around each parameter value if at least one parameter is not of numeric type.
	#@LANGUAGES(Name_):1,2#	Name_EN,Name_D E	Manually defined values are not used and will be ignored. The values will be taken from another parameter - @LANGUAGES instead. In the example it shows evaluated values in case @LANGUAGES parameter is specified to use EN and DE values.
	#@REPLACE:”1,2”#	“1,2”	Manually defined values will be added exactly as they are defined, e.g. with no pre-processing or changing formatting.
Default	#101# #@ID# #@KEYFEATURE#	<Comma separated key values e.g. from the grid>	Default mode for key feature will always overwrite manually defined key values with values coming from Perfion.
	#102:1,2# #@ID:1,2# #@KEYFEATURE:1,2#	<Comma separated key values e.g. from the grid>	Default mode for key feature will always overwrite manually defined key values with values coming from Perfion.
	#@VARIANT:1,2# #@CustomParam:1,2#	1,2	Variant and any other parameter type will always have only manually defined values.
Custom	#@VARIANT:1,2# #@CustomParam:1,2#	1,2 or <Comma separated values e.g. from Report Designer>	The same as in Default mode, but if remote feature is used from some place which has possibility to define custom parameters, then manually defined values can be overwritten. For example, this can be done from the Report Designer.
Default	#@LANGUAGES# #@LANGUAGES:1,2#	‘EN’,‘DE’,‘DAN’	Perfion will replace parameter with user language codes as they are specified in Perfion.
Custom	#@LANGUAGES# #@LANGUAGES:1,2#	‘EN’,‘DE’,‘DAN’ or <Comma separated values e.g. from Report Designer>	The same as above, but if remote feature is used from some place which has possibility to define custom parameters, then manually defined values can be overwritten. For example, this can be done from the Report Designer.
Test	Perfion.In(ID,#@ID:2,3 #)	(ID eq 2 or ID eq 3) (ID eq ‘2’ or ID eq ‘3’)	If the second argument will be used as parameter (e.g. #@ID:2,3#), then it will be evaluated 1^st and only then evaluated values will be used in “Perfion.In” block. “Perfion.In” block will process data values based on their type as defined in OData metadata. For example, the 1^st result will be produced if ID in OData metadata is defined as numeric and the 2^nd result will be produced if it is defined as text/string. Refer to Perfion.In Block – Data Formatting for more information.
Test	Perfion.In(ID,2,3)	(ID eq 2 or ID eq 3) (ID eq ‘2’ or ID eq ‘3’)	As above, but the second argument is defined directly, so it does not need an extra evaluation step like it was required in the previous example.
Test ( Headers only)	#@#	1=0	Headers only breaker parameter has no values and will be always replaced with the same value.
Test (setup) Default Custom	#@#	1=1	Same as above, but will be replaced with another value in all the other modes.

The table shows how parameters are evaluated in absence of Setup XML document. To see how each parameter will behave when Setup XML document is present refer to Remote Parameters and then follow from there based on parameter type.

Note that it is allowed to specify the same parameter multiple times in the same Select Statement, but then, manually defined values will be used as specified in the 1^st found instance of those parameters. For example, if #@ID:1,2# is defined 1^st, and #@ID:3,4# is defined 2^nd, then both will become as the 1^st one, e.g. #@ID:1,2#.

Setup XML

Setup XML allows to configure most of parameters which can be defined in Select Statement and several more. It also allows configuring parameter values based on mode, e.g. Test or Default. Setup XML is also the only way to configure Web Service type remotes, because Select Statement is not used with Web Service remotes.

When creating a new remote query the Setup XML is blank, but one can insert the template of Setup XML document by pressing the button “Setup XML” over Setup XML window. The template has most types of parameters predefined so it would easier to select those which may be required for remote query configuration. After inserting the template one can remote unwanted parameters and update those which will be used for remote query configuration. Note also that the Setup XML document is a standard XML document and one can comment out the unwanted parameters, but one must also use parameters so that XML format is valid, e.g. without specifying any illegal characters. For example, the following characters are not allowed in XML documents and must be escaped:

Ampersand (&) character is not allowed. Use & instead.
Less then (<) character is not allowed. Use < instead.
Greater then (>) character is not allowed. Use > instead.

Setup XML document allows specifying some parameters, which can also be specified directly from Select Statement. In case there are some parameters which are the same in Select Statement and in Setup XML, then Setup XML in most cases will have priority and will overwrite any values defined in Select Statement. Therefore, it is recommended to place parameters in Select Statement without predefining in-line values and to use Setup XML document to define the values for those parameters.

The Setup XML document is mandatory for Web Service type remotes. However, we recommend to use it with all remotes. It has more features and it also simplifies Select Statement configuration.

Note that parameter names in Select Statement and in Setup XML document are case insensitive and will match if parameter name is the same, but in different letter casing.

Moreover, parameters in Select Statement are identified with @ prefix, but Setup XML does not use prefixes and only actual parameter name will be used.

Setup XML – Structure

Setup XML consist of three parts (refer to Figure 10):

Metadata. The root element of setup XML has five attributes:
- “name”. Setup XML document name. It does not have any special meaning in Perfion, but can be used by Web Service to identify remote query.
- “keyFeature”. Key feature ID. Defines key feature for remote.
- “type”. Remote query type. Defines remote query type, e.g. Single or Cluster.
- “localizable”. Localization status. Defines the preferred remote data output from localization perspective. If set to “true” it tells to Web Service that remote query expects data, which will be used with localizable remote feature. Used only with Web Service remotes.
- “timeout”. Timeout in seconds. Only integer values >=0 are accepted. Defines the timeout for remote. If it is defined, then it will overwrite the default value, which is 30 seconds.

Parameter value usage depends on remote data source:

Database. The value will be used as “CommandTimeout” parameter, which controls the wait time before terminating the attempt to execute a command (SQL query). Database connection string configured in Remote Connection Setup window may have another timeout parameter which controls the connection timeout. This one is different and controls the SQL query execution timeout.
OData. This value will be used as request timeout.
Web Service. This value will be used as request timeout.
Test setup. All parameters specified in element “<Test>” will be used for tests of remote, e.g. used in “Test (headers only)” and “Test (setup)” modes.
Default setup. All parameters specified in element “<Default>” will be used in “Default: and/or “Custom” modes.

Figure 10: Setup XML structure

Setup XML – Parameters

One can define many types of parameters in Setup XML and these parameters are defined in the same way in Test and Default modes. Refer to Figure 11 which shows the fully configured Test block. The Default mode can use the same parameters.

In order to define any parameters we recommend inserting the template Setup XML document (Setup XML button above the Select XML window) and then modifying the parameters based on application requirements.

Figure 11: Setup XML - Test mode parameters

The table below shows examples of various cases how parameters are defined, what they mean and how they will be handled in different modes. For examples of how each parameter is defined in Setup XML refer to Figure 11.

Parameter	Location	Description
Group	Test Default	Headers only breaker parameter and it can also be used to select data by some grouping category. It is used only with Web Service remotes. Values: “All” and “None” are reserved by Perfion, but custom values can also be used if Web Service supports it. “None” value means that only headers are requested from remote data source and “All” means that it must return all data. If parameter is not defined, Perfion will use “All” as default value.
Key	Test	Key values for key feature.
Key	Default	Key values for key feature. Perfion will overwrite key values, which will be taken from Perfion depending on the context where from the remote feature is used. For example, if remote feature is shown in the grid, then all key feature values shown in the grid will be used as key values.
Field	Test	Fields defines remote data columns needed for retrieval. These parameters are used only with Web Service remotes and are optional. By default, if they are not defined, then all available remote data should be returned. If fields are defined, then Web Service is supposed to return only remote data columns specified by field parameter values.
Field	Default	Field parameters will be modified by Perfion. It will add required field parameters based on the context if they are not manually defined by user.
Variant	Test Default	Variant parameter values will be used based on what is the type of the remote, e.g. Single or Cluster type. For Cluster type remote Variant parameter will work like custom string type parameters, but it will use multiple values instead of a single value. For Single type remote Variant parameter will not be used by Perfion for special data handling. However, Variant parameter will work like a custom multi-value parameters and can still be used. However, we do not recommend using it in this way and instead use other type of parameters.
Languages	Test	Allows to specify language codes. The primary intention is to allow creating remote queries, which can deliver data for localizable remote feature. Localizable remote features will have multiple data columns instead of one, e.g. one data column for each language. Languages parameter is mandatory if remote is specified as localizable (localizable attribute is set to “true” in Setup XML).
Languages	Default	As above, but here Perfion will overwrite all languages based on the context, where data is used from. If remote feature is used in the grid, then it will overwrite the languages parameter using default user language. If remote feature is used from the Item Editor, then Perfion may use all languages defined for the user.
Custom parameter	Test Default	Custom parameter allows defining a single value of chosen type. One can define custom parameters of five types: Number, String, Bool, Date and GUID. The type names are self-explanatory. Refer to Figure 11 for examples of custom parameters defined with each supported data type. Note that one can skip the type definition if it is a String type parameter. Parameters will be used as any other parameters, but before usage Perfion will try to parse the value and will convert it to the type as it was defined in parameter “datatype” attribute. String type parameters will be used as defined, e.g. without conversion. Note: “Number” and “Date” parameter conversion and usage depends on Windows OS culture settings on computer where Perfion application is running. For example, if “Number” parameter value is defined as “1.5” and Windows OS culture is set to Danish, then “1.5” value will be converted to “1,5” value (e.g. with comma).
Replace parameter	Test Default	The same rules apply as for Custom parameter. The only difference is that this parameter must be defined as String type parameter (or without using any data type definition, e.g. without “datatype” attribute) and its name must start with “Replace”. Setup XML parameters are case insensitive, so letter casing is not important.

Remote Parameters

All parameters for remote are configured in different places (e.g. Select Statement, Setup XML, etc.) and some parameters may overlap. The relations are complex and also depends on various conditions. There are priorities defined, which are used to decide where from parameter must be taken in case they overlap. It also depends how parameters are updated based on in which mode remote is executed (e.g. Test, Default, Custom). The setup process is complex because different type remotes are configured based on context. Perfion is a flexible platform, which allows complex relations and some of configuration options are left for backwards compatibility with previous versions of Perfion.

In general, all parameters can be categorized:

Headers only breaker. A special parameter used to control query when Perfion needs to retrieve only the headers of remote data.
Key feature. Selection of key feature ID.
Keys. Key values allows to select only the data which is relevant in current context. Keys are key feature parameter values.
Variants. Values for Cluster type remotes.
Custom parameters. Parameters with special data formatting.
Replace parameters. Parameters without special data formatting.
Remote type. Defining of remote type, e.g. Single or Cluster.
Languages. Usage of system default languages.
Localizable. Setup for localizable remote feature.
Fields. Defines remote data columns needed for retrieval.

Remote Parameters – Headers Only Breaker

Headers only breaker parameter is a special parameter, which is used to control remote query in such way, that in Test (headers only) mode Perfion could read only remote data headers instead of full data set. For example, in Test (headers only) mode Perfion needs to know which headers (column names) remote data have, but it does not need the data. The Test (headers only) mode is executed in “Feature Definition” window and it may become slow if all data must be retrieved from the remote source when Perfion should only use the headers. This parameter is optional.

Practically it is not a requirement that remote query executed in Test (headers only) mode will return only headers, e.g. without any data. It is also OK to return some data, but it is important to make sure that data amount is limited, because it may become a performance issue when one wants to edit remote feature.

Headers only breaker parameters differ based on data source where from the data is retrieved:

Database. Database type remote has a special headers only breaker parameter #@#. Database can also use @REPLACE parameter to control the amount of data returned for Test and Default modes.

Using #@# headers only breaker is the fastest way to get headers only remote data for Database remote. It must be used immediately after the last WHERE clause in SQL query. If SQL query does not have any WHERE clause, then it has to be added, e.g. like this: WHERE #@#.

Another way to write SQL query in order to get good performance in Test (headers only) mode is to use

@REPLACE parameter. For example, one can use “Top 1” value in Test mode and “” (empty string) in the Default mode. In this way test data will have only one line of data and default data will not be limited in any way.

Comparing #@# to @REPLACE, the first one is quicker, because it allows to retrieve only headers information from remote database, while with @REPLACE parameter remote database will return some rows of data (one in this case), which will be slower, but still comparable in performance to the other method.

Web Service. Web Service headers only request is performed by defining Group parameter to use “None” value in Setup XML, but this is optional. Perfion does this automatically for Test (headers only) mode regardless of the Group parameter values set by user.
OData. OData can use @REPLACE type parameter to control data amount returned from remote data source in Test mode. OData requires minimum one row of data to be retrieved from OData service in order to get header data. For example, one can use “$Top=1” value in OData query in Test mode and “” (empty string) in the Default mode. In this way test data will have only one line of data and data in Default mode will not be limited in any way. Note: in order to control parameter values in Test and Default modes one will need to specify it in Setup XML and the actual parameter value will be dependent on where in OData query it is used. E.g. it has to be set to either “?$Top=1” if the Top parameter is the first parameter in OData query or “&$Top=1” if it is not.

If headers only breaker is not defined, then Perfion will use the Test (Setup) mode to execute the remote query. Perfion will select the first found key feature value defined for Test data in order to limit the data quantity returned by the remote query.

Examples of headers only breaker usage with different type remotes:

Mode	Remote	Select Statement parameter	Setup XML parameter	Select Statement
		Select Statement parameter		Before evaluation	After evaluation
Test (headers only)	Database	#@# (in WHERE clause)	-	SELECT * FROM xxx WHERE #@#	SELECT * FROM xxx WHERE 1=0
Test (setup) Default Custom	Database	#@# (in WHERE clause)	-	SELECT * FROM xxx WHERE #@#	SELECT * FROM xxx WHERE 1=1
Test	Database	#@REPLACE# (in WHERE clause)	<Test><Parameter id='Replace'>1=0</Para meter></Test>	SELECT * FROM xxx WHERE #@REPLACE#	SELECT * FROM xxx WHERE 1=0
Default Custom	Database	#@REPLACE# (in WHERE clause)	<Default><Parameter id='Replace'>1=1</Para meter></Default>	SELECT * FROM xxx WHERE #@REPLACE#	SELECT * FROM xxx WHERE 1=1
Test *	Database	#@REPLACE#	<Test><Parameter id='Replace'>TOP 1</Parameter></Test>	SELECT #@REPLACE# * FROM xxx	SELECT TOP 1 * FROM xxx
Default Custom	Database	#@REPLACE#	<Default><Parameter id='Replace'></Parame ter></Default>	SELECT #@REPLACE# * FROM xxx	SELECT * FROM xxx
Test	OData	#@REPLACE#	<Test><Parameter id='Replace'>?$top=1</ Parameter></Test>	\Products#@REPLACE#	\Products?$top=1
Default Custom	OData	#@REPLACE#	<Default><Parameter id='Replace'></Parame ter></Default>	\Products#@REPLACE#	\Products
Test Default Custom **	Web Service	-	<Test></Test> <Default></Default>	-	-

*#@REPLACE# parameter with “TOP 1” will allow returning only one data row in Test mode. This is not a requirement and one can return more data rows, but we recommend to optimize the query for performance.

**For Web Service the Test (headers only) mode will automatically use the correct Group parameter value.

Remote Parameters – Key Feature

Key feature is used to relate incoming remote data to the data in Perfion. For example, if Perfion has a product data and remote system has a product data, then the key feature will be used to relate both products. If the products are identified by product number, then key feature have to be set to a feature in Perfion, which stores product numbers. The remote data should be prepared so, that product number data will come in a data column specified as “KEYVALUE”.

Key feature can be defined by specifying the key feature ID, which is greater or equal to zero (>=0), or if defined in Setup XML then one can use the same ID value, but can also use key feature name, e.g. “ItemNumber”.

If key feature is defined using ID, then there are several ID intervals which will affect how the key feature is used.

Key feature ID	Description
0	Each remote feature, when used in Perfion will be related to another feature. If user specifies key feature ID equal to zero, then that feature is not selected by its ID, but instead it is chosen based on the context, where the current feature (e.g. remote feature) is used from. The actual values used as key values will be taken as item ID values of that selected feature. For example, if data is currently shown in the grid, then Item IDs will be base feature item IDs. If the product feature is selected as base feature, then Item IDs will be product feature item IDs. In Item Editor it will be ID of the item which is loaded in the Item Editor.
1-99	Each remote feature, when used in Perfion will be related to another feature. If user specifies key feature ID in range from 1 to 99, then that feature is not selected by its ID, but instead chosen based on the context, where the current feature (e.g. remote feature) is used from. The actual values used as key values will be taken as base values of that selected feature and key feature ID value will define what type of base value will be used. If the feature has String base type, then one must use ID=2, if Number, then ID=1, etc. One must know exact ID values to be able to use remote feature binding to selected feature’s base values. Moreover, there is also a requirement, that selected feature (from the context) must not have Localizable or Multi-Value data properties. The use of key feature IDs in range from 1 to 99 is complex and will work only in very specific cases. We recommend to avoid selecting key feature like this, unless you know exactly how to do it correctly.
>99	Any user defined feature ID values will be used as key values. The key feature will be used regardless of context, but key feature must be present in that context in order to be able to use its values. For example, if in the grid one wants to show remote feature, which has “ItemNumber” key feature, then “ItemNumber” key feature must be also added to the grid in order to show remote feature values.

NOTE: If the key feature is defined using key feature name, then key feature ID will be evaluated by Perfion and will have ID values greater than 99. One cannot define relative key feature by using key feature name.

In Perfion all feature IDs from 1 to 99 (both included) are reserved for internal use. Features created by users will get automatically assigned feature IDs, which are greater than 99. Features with IDs in range from 1 to 99 hold base values for other features. For example, a String type feature will have its value saved in another special feature with ID equal to 2, the Number type feature will use a special feature with ID equal to 1, etc.

Key feature can be configured in Select Statement and in Setup XML. In Setup XML key feature is defined as a root element attribute, e.g. “keyFeature=’ItemNumber’” or “keyFeature=’101’” (refer to Figure 10).

In Select Statement key feature is defined by using special parameter names and there are several reserved for key feature:

#<key feature ID>#, e.g. #101#, where 101 is key feature ID. Note that one cannot use key feature name here. Only a number greater than or equal to zero (>=0) can be used here as key feature ID value.
#@ID#. Key feature ID will be set to zero (0).
#@KEYFEATURE#. Key feature will be defined from Setup XML.

There can be only one key feature specified in Select Statement. For example, if #@ID# was specified and also #@KEYFEATURE#, then the error will be shown.

Key feature can be defined several times in Select Statement, but then it has to be the same in all instances. For example, if #@ID# was defined once, and then again as #@ID#, then it is not an error and both will be used.

If the same parameter is used several times in Select Statement, but with different values, then the same values will always be used with all parameters. The values will be taken from the 1^st found parameter.

In Select Statement key feature definition has two purposes:

To define key feature ID in order to know which key feature values or Item IDs have to be used in Select Statement.
To show where key values have to be inserted into query defined in Select Statement. The key feature parameter placeholders will be replaced by the list of actual key feature values or Item IDs when Select Statement is evaluated.

Web Service type remotes do not use Select Statement, so key feature will be always taken from the Setup XML.

The table below shows various situations how key feature ID will be determined.

Data source	Key feature in Select Statement	Key feature in Setup XML	Actual key feature ID value
OData Database Web Service	<Not defined>	keyFeature=’102’	ID = 102 (from Setup XML)
OData Database Web Service	<Not defined>	<Not defined>	Error
OData Database	#101#	keyFeature=’102’ or <Not defined>	ID = 101 (from Select Statement)
	#@ID#	keyFeature=’102’ or <Not defined>	ID = 0 (from Select Statement)
	#@KEYFEATURE#	keyFeature=’102’	ID = 102 (from Setup XML)
	#@KEYFEATURE#	<Not defined>	Error

There are several ways to define the key feature in Select Statement because Perfion must be backwards compatible with older versions. We recommend to always use #@KEYFEATURE# parameter in Database and OData remote queries and then specify key feature ID value in Setup XML. We also recommend not to use key feature values (keys) directly in Select Statement, but instead to define them in Setup XML using key parameters.

Remote Parameters – Keys

Keys are key feature values, which are used to control remote data based on the context where from it was requested. Key values will depend on key feature ID. If key feature ID is specified as greater than zero (>0), then keys will be key feature values. If key feature ID is specified as zero (0), then Perfion will use item IDs as key values for remote and local data mapping. Item ID values depends on context. For example, if data is currently shown in the grid, then item IDs will be base feature item IDs. If the product feature is selected, then item IDs will be product feature item IDs. In Item Editor it will be ID of the item which is loaded in the editor.

Keys can be defined in Select Statement and in Setup XML. If Setup XML is used, then keys will always be used from Setup XML and key values defined in Select Statement will be ignored. Moreover, in Default and Custom modes Perfion will overwrite keys for remote feature based on where from remote feature is used.

In order to use keys for Database and OData type remotes, the Select Statement must have key feature parameter. It is used as a placeholder for key values.

The table below shows various situations and how key values will be determined.

Data source	Mode	Key feature in Select Statement	Key values in Setup XML	Values after evaluation
OData Database	Test	<Not defined>	<Test> <Key>1</Key> <Key> 2</Key> </Test>	Error (key feature defines the place in Select Statement where keys must be inserted and it is not defined in this case)
	Test	#101:3,4#	<Test> <Key>1</Key> <Key> 2</Key> </Test>	1,2 (from Setup XML)
	Default or Custom	#101:3,4#	<Default> <Key>11</Key> <Key>12</Key> </Default>	Values inserted by Perfion based on context, e.g. the key values from Select Statement and Setup XML will not be used.
Web Service	Test	<Not defined>	<Test> <Key>1</Key> <Key>2</Key> </Test>	1,2 (from Setup XML)
Web Service	Default or Custom	<Not defined>	<Default> <Key>11</Key> < Key>12</Key> </Default>	Values inserted by Perfion based on context, e.g. values from Setup XML will not be used.

Remote Parameters – Variants

Variant parameters are used to configure Cluster type remotes.

For Database and OData remotes, variants can be defined in Select Statement and in Setup XML. Web Service type remotes will use only Setup XML. If Setup XML is used, then variant values will always be used from the Setup XML and values defined in Select Statement will be ignored.

There can be multiple variant parameter values defined in Select Statement and also in Setup XML. Variant values will be used in the same way in Test mode and in Default mode, but variant values can be overwritten by Perfion in Custom mode.

When using remote of Cluster type, then remote data must have a column with name “VARIANT”. Values provided in “VARIANT” data column should match values defined in remote query setup. OData remotes, for example, will filter “VARIANT” data column values based on variant values provided in remote configuration.

In case remote feature is of Single type, then variant values will be used as simple parameters.

The table below shows various situations and how variant values will be determined.

Data source	Mode	Remote type	Variant parameter in Select Statement	Variant parameter in Setup XML	Values after evaluation
OData Database	Test	Cluster