Use custom queries

You can add, rename, and build custom query expressions, and delete custom queries in a data source.

You can build custom query expressions to create custom source data items for an external data source system. Custom source data items help you to prepare and optimize the data structure of the external source system for data extraction operations required by the AVEVA BI Gateway model.

Add custom queries to a data source

You can add any number of custom queries to a data source.

Add a custom query to a data source

1. Click the Custom Queries tab and then click Edit. The Custom Queries page appears.

   

2. In the Queries area, click the Add + icon to add a custom query. A custom query with a default name is added in the Queries pane. You can update the default query name.

   For more information about renaming a custom query, see Renaming a Custom Query.

3. Click Save.

Delete a custom query

You can delete a custom query in the Queries pane. After you save the data source, the system shows a warning message about the dependent dimensions and measures that could be affected by the custom query deletion.

Delete a custom query

4. Under the Data Sources column, click the data source of which you want to delete the custom query. The data source editor appears.

5. Click the Custom Queries tab and then click the Edit icon. The Custom Queries page appears.

6. Under the Queries area, select the Custom Queries that you want to delete.

7. Click the Delete icon. A confirmation message appears.

8. Click Delete.

Rename a custom query

The custom query name must be unique within the data source object. The custom query name:

• Can have a maximum length of 100 characters.

• Follows the Microsoft SQL Server naming conventions.

• Follows the AVEVA BI Gateway database restrictions and should not be a AVEVA BI Gateway database reserved name.

• Is case insensitive.

• Must have a letter or an underscore (_) as the first character.

Rename a custom query

9. In the Data Source editor, click the Custom Queries tab and then click the Edit icon. The Custom Queries page appears.

10. In the Queries area, click the custom query that you want to rename, and then type a new name for the custom query.

11. Click Save.

Build a custom query expression to create custom source data items

You can build a custom query expression to create custom source data items from AVEVA Insight. Custom source data items help you to prepare and optimize the data structure of the external source system for data extraction operations required by the AVEVA BI Gateway model.

For example, you can build a custom query to combine the information from multiple source data items into a single one, such as merging shift descriptions with the operational schedule.

Build a custom query expression

12. Under the Data Sources tab, click the data source for which you want to build a custom query expression. The data source editor appears.

13. Click the Custom Queries tab and then click the Edit icon. The Custom Queries page appears. The Available Source Data Items pane shows the source data items of the current data source. You can expand the source data items to view the fields, along with the field type, within the source data item.

14. In the Queries area, click a custom query for which you want to build a custom query expression.

15. Click Validate to manually validate the custom query expression syntax.

16. You can slide to enable the Preview Data option. If the query validation succeeds, the data preview area below the Query Expression editor shows the top ten data rows from the data source.

    You can use a valid JSON expression to create a custom query.

    

    Note:
    - A JSON expression must have "QueryName". This is a mandatory parameter name. Query name denotes the name of the custom query defined under the Azure function.
    - The other common parameter is "SelectColumn" and is an optional parameter to specify required columns.
    - The query returns all the properties (columns) when it is not specified in the query expression or when it is specified, such as SelectColumn: ["*"].
    - Apart from the "QueryName" and "SelectColumn" parameter, other parameters are query-specific parameter(s).
    - A query expression is case insensitive.

17. Click Save. The system automatically validates the custom query syntax after you save the data source.

Use BI Gateway keywords in a custom query

Two special AVEVA BI Gateway keywords are available for you to use in custom queries:

• [[RequestedStartTime]]: This is evaluated by the data adapter based on the requested start time from the AVEVA BI Gateway.

• [[RequestedEndTime]]: This is evaluated by the data adapter based on the requested end time from the AVEVA BI Gateway.

  Note: Custom queries built with these keywords are used for measure calculations only. They should not be used for a dimension because they are not translated into a time-slice.

The AVEVA BI Gateway has a specific requested start and end time based on the time-slicing calculation that is made by using the Measure object’s configuration.

Use stored procedures in a custom query

You can use stored procedures in a custom query for a Microsoft Azure SQL Server data source only.

You must use a different syntax to execute stored procedures than you would for regular custom queries. This is due to the different execution paths that occur when the query is executed against the data source. The stored procedure syntax:

• Must start with the EXEC statement. Spaces and line breaks preceding this keyword are allowed, however.

• May take zero or multiple parameters.

• Is case-insensitive unless the database itself is set to be case-sensitive.

The following is a list of examples:

EXEC usp_storedProcName

EXEC dbo.usp_storedProcName 1, 'abc'

EXEC myDb.dbo.usp_storedProcName @param1 = 1, @param2 = 'abc'

Stored procedures can also use the [[RequestedStartTime]] and [[RequestedEndTime]] keywords in place of their parameter values. This allows the value to be dynamic. Two examples are:

EXEC usp_storedProcName @param1 = [[RequestedStartTime]]

EXEC usp_storedProcName @param1 = [[RequestedStartTime]], @param2 = [[RequestedEndTime]]

• For more information on these keywords, see "Using BI Gateway Keywords in a Custom Query".

SQL comments, such as ones starting with a double hyphen (-- comment) or within comment blocks (/*…*/), that precede the EXEC statement are not supported. For example, the following query will fail because the query does not start with the EXEC statement:

-- Code Comment;

EXEC usp_storedprocName

You will get an error or invalid syntax message if you create an invalid query or try to use an EXEC statement with a data adapter other than the SQL Server data adapter. A warning will also be logged to the Application Insight.

If you change a stored procedure used by a deployed data source, you must redeploy the data source.

Required modifications to stored procedures

There are two scenarios that require a slight modification to stored procedures:

• Stored procedures that use temporary tables

• Stored procedures that return a different schema depending on the value of the passed parameter

For these scenarios, use the SET FMTONLY OFF setting at the top of the stored procedure that your custom query will call.

For example:

CREATE PROCEDURE usp_Sample

( @x varchar(10) )

AS

SET FMTONLY OFF;

... --body of procedure

By adding the SET FMTONLY OFF setting at the top of the stored procedure, temporary tables can be used, and only the schema that is retrieved depending on the parameter value will be returned by the data adapter. For more information, see "Limitations When Using Stored Procedures".

Limitations when using stored procedures

The following limitations apply to stored procedures in custom queries.

• The stored procedure must return at least an empty record set. If the stored procedure does not return anything (not even an empty record set), then the data source preview will be empty and the following message appears: "The Stored Procedure that was used in the custom query did not return any schema information. Try running the stored procedure directly to ensure that it returns at least an empty recordset." At run time, the same message appears in the Application Insight. However, access to the Application Insight Logger is available only to DevOps.

• Stored procedures that use "out" parameters are not supported.

• Stored procedures that return multiple result sets are not supported.

• For the result set data, some SQL Server data types are not supported or a data type conversion is performed by the data adapter. For more information, see "Supported Data Types for Stored Procedures."

• Stored procedures that use temporary tables are supported if they include the SET FMTONLY OFF setting at the top of the stored procedure. For a sample example, see "Required Modifications to Stored Procedures."

• If the SET FMTONLY OFF setting is not included, the AVEVA BI Gateway data adapter will return an error during the preview. If the custom query is executed at run time, the Application Insight will also log an error with a message indicating that the <name of the temporary table> cannot be found.

• Stored procedures that have multiple branches (multiple IF statements)—where one branch returns a different schema than another branch—are not supported unless the stored procedure can be modified or if only the first branch is exercised when the stored procedure executes.

To illustrate an example of a stored procedure that has multiple branches where each branch returns a different schema, examine the code below:

IF @X = 'Good'

SELECT * FROM Production_Good

ELSE

SELECT * FROM Production_Bad

In this case, the data adapter query succeeds if one of the following conditions are true:

• If the custom query passes @x = 'Good'. This query would succeed because passing 'Good' as the value to parameter @x would exercise the first returned result set. If, however, another value would be passed, then the ELSE block would get executed. In this case, you would get an error stating that the Column name or number of supplied values does not match the table definition. This is because the OLEDB data adapter sets the FMTONLY setting to ON, which retrieves all result sets for all branches, and the OLEDB data adapter will only accept the first result set as its returned schema definition. Therefore we recommend that you do not use stored procedures that return different result sets depending on which branch was executed.

• If the SET FMTONLY OFF setting is included at the top of the stored procedure, then either branch can be executed successfully, and this limitation is resolved. For more information, see "Required Modifications to Stored Procedures."

• Stored procedures that include the WITH RESULT SETS option are not supported.

• Stored procedures that include the USE clause are not supported.

Supported data types for stored procedures

When a stored procedure returns data through the data adapter, not all SQL Server data types are supported. The following table indicates which data types are supported:

If a data type is supported, the "Converted into" column provides information about whether the data adapter would convert the data into a different type, including the length or precision. Depending on the data type, some supported data types may be truncated to 4000 characters, or an error may occur if 4000 characters are exceeded.

If a data type is not supported, a conversion-type error such as the following may appear: "An error occurred in AdapterBase::GetDataItemData - ...Implicit conversion from data type hierarchyid to nvarchar is not allowed. Use the CONVERT function to run this query."

Supported query expression operators

You can use the following operators in a query filter expression.

Note:
- If you apply the filter on a String column, ensure that you enclose the String value in Single quotes, as mentioned in the examples above. However, if you apply the filter on a non-string column (such as decimal or integer), you can enter the values as-is, without using Single quotes. For example, "Filter": "OpcQuality eq 192"

- AVEVA Insight does not support compound expressions with more than 3 conditions. An example of a compound filter expression is:
"Filter":"(FQN eq 'Weather.Los Angeles.Temperature' and Location eq '/Los Angeles') OR (FQN eq 'Weather.Los Angeles.Temperature' and Location eq '/houston') OR (FQN eq 'Weather.Los Angeles.Temperature' and Location eq '/San Francisco')"