When working with data connections to relational databases or other non-cube data sources, you get the option to select one or more tables from the data source in the Views in Connection dialog. Here, you may also have the option to create your own custom database query, depending on your licenses. A custom query results in a custom table which in turn can be used to set up a view in the selected connection in the same way as you would do with other database tables. Queries are written in a language that the database understands. For example, for a Microsoft SQL Server database, you would write your custom query in the Microsoft SQL Server dialect of SQL.
Requirements
The custom query entered must be possible to insert inside a 'from clause' in the query. Whether or not this is possible is system dependent and may vary from database to database. The custom query can only consist of a single statement, that is, it not supported to chain statements together separated by ';'.
The custom query will be evaluated each time a regular query is performed from the Spotfire application. If the query is used in-database, then the custom query will be executed each time a visualization or filter is updated. For the in-memory option, that is, when data is loaded into the Spotfire data engine, the query will only be executed when data is imported or explicitly refreshed.
There is support for keywords specific to each connector in the custom query input field.
A custom query and the resulting table will be stored as part of the data connection, either as part of an analysis file or as a shared data connection in the library.
Example of supported queries:
SELECT Col1, Col2, Col3 FROM Table1 WHERE Col2 > 20.0 Group By Col1
SELECT T.Col1, T.Col2 FROM Table AS T WHERE T.Col2 BETWEEN '2012-12-12' AND '2012-12-15'
Example of an unsupported query:
It is not supported to call stored procedures directly, for example:
EXEC Sp_get_conv_factor('VCV6_STAT_HRS_10')
Data Types
When you create a custom query you should take care only to include columns from the database of data types that are actually supported by Spotfire. See the documentation for the connector you are currently using for a list of supported data types. For most connectors there is a separate help file available, where this information is included, but for some connectors details are included in this help file. For example, the supported SQL Server Data Types are listed here.
If an unsupported column happens to be included in the query it will be marked as invalid in the verification step and it will not be possible to use the resulting table until the issue has been resolved. This is done by either rewriting the query so the unsupported columns are excluded, or by removing the column. You can always assign an arbitrary data type to a custom query column. However, there is no guarantee that the resulting Spotfire table for such a custom query will work.
Parameters
If you want the query to retrieve different results depending on the value of one or more variables, you can set up the query to use parameters. A parameter can be mapped to, for example, a property control in the text area.
A parameter is referred to using the syntax ?parametername where parametername is a name that you select for your parameter and define the data type for on the Parameters tab.
Example of a query using a parameter:
SELECT Col1, Col2 FROM Table WHERE Col1 > ?Param1
In a lot of cases, multiple database types will map to a single Spotfire type. As an example, when connecting to Microsoft SQL Server the database types varchar, nvarchar, text, ntext, etc. will all map to the Spotfire String type. In those cases, when declaring the parameter type, it is also possible to specify the actual type in the external system. That is, the parameter is of the Spotfire data type String but in the external database it is an nvarchar. While Spotfire cannot automatically map the external type of the parameter a manual specification of the external type can make the query more effective. If there is a one-to-one mapping between the Spotfire type and the external type, then the external type drop-down list will be disabled.
Provided that you and the end users of the analysis are logged into a Spotfire Server, you can also use one or more of the predefined parameters for personalized queries; current_user, current_groups and current_user_domain, which become enabled by selecting the "Use parameters for personalized queries (users and groups)" check box. These parameters work the same way as they do when using them from the Information Designer, which is described under Personalized Information Links.
Once the check box is enabled you can use the personalized query parameters in your custom query. The type of these parameters is always String in Spotfire and the external type is the default value, e.g., NVARCHAR in SQL Server.
Security
In order to create custom queries you must have the Custom Query in Connections license, which can only be enabled for the Custom Query Author group or the Administrator group. However, members which only belong to the Administrator group can only save local copies of the analysis to disk, not publish signed analyses to the library. Custom queries are signed when a member of the Custom Query Author group saves an analysis file, or a data connection to the library. Only files published to the library can be signed.
If a custom query cannot be validated, that is, if the current checksum calculation does not match that of the original one, the custom query will not run. To get a working version of the custom query it would have to be saved again by someone with the appropriate permissions.
Anyone, regardless of group or license function belonging, can execute an already created custom query, provided the pre-requisites described above are fulfilled.
See also: