Remote Query Setup for Database

Database type remotes use a connection to a database to get remote data. There are two types of databases one can use:

• Perfion database. This is the database, which is used with Perfion application. Users can request data from Perfion database to make complex data processing.

• Remote database. This can be any user chosen SQL based database.

Remote Connection

Perfion type database connection does not require any parameters and is basically an empty text. Refer to Figure 13 to see an example of remote connection for Perfion database.

Note that Perfion database can be used for remotes, but it is always a subject for change. Perfion is continuously improving Perfion application and database structure updates are part of this process.

Open

Figure 13: Remote Connection Setup - Perfion database

For remote database connection Perfion uses a standard ADO.NET connection string which will specify all parameters needed in order to connect to different databases.

Some examples how one can connect to database:

• By using user credentials. Refer to Figure 14.

• By using Windows Authentication (sometimes referred to as integrated security). Refer to Figure 15.

The format of a connection string is a semicolon-delimited list of key/value parameter pairs. Keywords are not case sensitive, and spaces between key/value pairs are ignored. However, values may be case sensitive, depending on the data source. Any values containing a semicolon, single quotation marks, or double quotation marks must be enclosed in double quotation marks. Valid connection string syntax depends on the provider, so each database connection may differ. Refer to Microsoft documentation to learn more about connection string format and different usage scenarios: Connection String Syntax - ADO.NET .

Perfion supports two data providers: ODBC and OLE DB. If database connection string has “provider” defined, then Perfion will use OLE DB data provider and if not, then ODBC.

Open

Figure 14: Remote Connection Setup - Remote database with user credentials

Select Statement

In Select Statement one can define database SQL query. It has to be setup so, that Perfion can link data from remote source to the data in Perfion. The query must be set to be able dynamically return data based on context, e.g. in the grid or in Report Designer only the data relevant to these environments should be retrieved from remote data source. After query is set up, one can run a test by pressing “Test” button or F5 to execute it using test parameters.

All incoming data from remote source must be mapped to Perfion data. For this to work the remote data must be requested so, that it conforms to remote data requirements. For example, some remote data needs “KEYVALUE” data column to be present with unique values, other remotes need also “VARIANT” data column, etc. Refer to Remote Data for more information.

In most cases the data from remote source will not be used directly as remote data and has to be modified. SQL query is flexible in formatting data, which simplifies this process. For example, to create “KEYVALUE” remote data column from other data column, one can use data projection, e.g. “SELECT ParamName AS KEYVALUE”, where “ParamName” is original parameter in database source data and “KEYVALUE” is the new name for that parameter.

From SQL query point of view one can define any type of SQL queries. For example, one can define constants, create temporary tables before selecting actual data from it, etc.

Migrate SQL Query Setup to Use Setup XML

At first Perfion was not using Setup XML document for database type remotes, so in this section we will show how one can migrate from Select Statement only setup to setup using Select Statement and Setup XML.

Perfion introduced Setup XML document usage for database type remotes since version 4.5.28 and it is recommended to use it for all type of remotes. In this section we will show how to migrate your existing SQL query setup so it can use the new Setup XML document. Setup XML has more features, it simplifies SQL query and it is also used with all types of remotes.

Example 1 – Relocation of Manually Defined Values

When Perfion parameters are used in SQL query one can define values immediately after colon (:) after parameter name. We recommend to move these values to Setup XML, which will also allow to control values based on different mode depending on where from query is running in Perfion. If parameters are specified directly in query, then the same parameters will be used in Test mode and also in Default mode. If parameter values are moved to Setup XML, then one can use some parameter values in Test mode and other values in Default mode. Note that once Setup XML document is created and parameter is defined, which match the name of parameter in SQL query (in Select Statement), then any values defined for that parameter will be used/overwritten from Setup XML.

The parameter values defined in SQL query may have multiple values and in Setup XML one can define only single values. If multi-value parameter is still important, one can use @REPLACE parameter instead, which allows to specify values exactly, without any preprocessing.

Example 2 – Relocation of Key Feature Parameter

When key feature parameter is specified in SQL query, there can be three types of parameters used: #@ID#, #101# and #@KEYFEATURE#. The first one defines that key feature ID value is zero (0) and the 2^nd one defines that the key feature value is 101. Instead of using those two parameters we recommend to use the #@KEYFEATURE# parameter and define actual key feature value in Setup XML. Setup XML allows to define key feature ID value directly (e.g. 0, 101, 102, etc.) or use key feature name (e.g. “Product”, “ItemNumber”, etc.). The table below shows how one can define the #@ID# and #101# parameters using #@KEYFEATURE# parameter.

Example 3 – Relocation of Key and Variant Values

Just like in Example 1 – Relocation of Manually Defined Values, manually defined values can be relocated to Setup XML. The Setup XML has reserved word “Key” for key parameter values and “Variant” for variant parameter values. One can define multiple keys and multiple variants in Setup XML.

Example 4 – Relocation of Languages Parameter

Languages parameter is not much different from other parameters. The only difference is that it may have several values in Setup XML as compared to custom parameter.

Languages parameter is overwritten by Perfion in Default mode, so it is not used in example.

There is also Languages() parameter used in Perfion, which is very special parameter and it cannot be moved to Setup XML. However, if Languages() parameter is used in SQL query, then Languages parameter must also be used.

Database Remote Data Examples

In all examples we will use Perfion database, but all examples will work in a similar way with any other database. The only major difference will be remote connection string. We also use examples, where data is created “in line”, e.g. not read from actual database, but created as temporary data directly in SQL query. This allows using SQL queries in any database.

Single Type Remote

A simple example using Single type remote.

Let’s say remote source has following data:

Database remote connection is shown in Figure 13 and remote query setup is shown in Figure 16.

In this example the table “remoteData” (from Figure 16) is the source data and after execution of SQL query the data will be transformed to remote data. In this example, it will rename “ProductNumber” column to “KEYVALUE” and it will also limit the number of returned rows by using test parameters. Test parameters are only key parameters with values 2, 4 and 6.

The #@# parameter in SQL query is called headers only breaker. The data returned after SQL query execution in Test (setup) mode is shown in the table below.

 The data returned in Test (headers only) mode:

The data returned in Default mode:

Note that in Default mode results will depend on where remote is used in Perfion (grid, Item Editor, etc.) and which type of remote is used. In this case we have created a new remote feature and selected “Name” column as remote data field. Then we used this remote feature in grid, where key feature values 1, 2, 3 and 4 were used to request remote data.

Cluster Type Remote

A more complex example using Cluster type remote.

Let’s say remote source has product data as shown in the table below. Products have variants, e.g. “Price” in this case is available in different currencies (“EUR” and “USD”): 

Remote connection is shown in Figure 13 and remote query setup is shown in Figure 17.

In this example the table “remoteData” (from Figure 17) is the source data and after execution of SQL query the data will be transformed to remote data. In this example, it will rename “ProductNumber” column to “KEYVALUE”, rename “Currency” column to “VARIANT” and it will also limit the number of returned rows by using test parameters. Test parameters are only key parameters with values 2, 4 and 6 and variants with values “USD” and “EUR”.

The #@# parameter in SQL query is called headers only breaker. The data returned after SQL query execution in Test (setup) mode is shown in the table below. The “Currency” column was chosen as variant data column. 

Localizable Remote

In this example we will show how one can create localizable remote data. We will use data, which is similar to Cluster type data, but instead of using language related data as variants we will use it to create localizable remote data dynamically. This example will show how one can use Languages() parameter. Let’s say that data in remote data source is as shown in the table below.

Database remote connection is shown in Figure 13 and remote query setup is shown in Figure 18.

In this example the table “RemoteData” (from Figure 18) is the source data and after execution of SQL query the data will be transformed to remote data. In this example, the “ProductNumber” column will be renamed to “KEYVALUE” and columns “Language” and “Name” will be used to create new columns with localizable names, e.g. in this case “Name_EN” and “Name_DE”. In order to get localizable names, the original remote data is first updated, by adding a new column “NewName”, which will be used instead of “Languages” column to group language related data.

The data generated after this transformation will be in “WithNewName” table (refer to Figure 18) as shown in the table below.

In “WithNewName” table all language related data is in one column and it has to be transformed into structure, where data for each language is located in its own column. This is done by using PIVOT function and Perfion special Languages() parameter. From Figure 18 the parameter “#@LANGUAGES(Name_)#” will be replaced with “Name_EN,Name_DE”. The final data returned after SQL query execution in Test (setup) mode is shown in the table below. 

Note that only two rows are returned, because only two keys are specified in Setup XML. In Default mode all values will be accessible from remote data source and Perfion will take only those needed dependent on the context where remote data is used.

The #@# parameter in SQL query is called headers only breaker.