Defining Data Connectors
This page describes how to define data connectors, as part of the Business Intelligence implementation process.
Introduction to Data Connectors
A data connector maps the results of an arbitrary SQL SELECT query into an object that can be used as the source of a cube, for a detail listing, or both. (For information on defining cubes and listings, see Defining Models for InterSystems Business Intelligence.)
The SQL query can use a combination of the following:
-
Local tables in the namespace in which you are using InterSystems IRIS Business Intelligence.
-
Views in the same namespace.
-
Linked tables in the same namespace. You define a linked table with the Link Table Wizard. The table has a class definition in your namespace but is linked to a table in an external database.
Important:There are restrictions on queries when using linked tables. See Restrictions on SQL Gateway Queries.
You can define a data connector so that it supports updates to the cube. To update this cube, you must either rebuild the entire cube or use ProcessFact(); see Keeping the Cubes Current.
Defining a Basic Data Connector
To define a data connector, create a class as follows:
-
It must extend %DeepSee.DataConnectorOpens in a new tab.
-
It must specify a query. You can specify the query in an XData block, as described in the first subsection.
Another possibility is to implement a callback to construct the query at runtime. This is described later in this page.
-
It must define an output specification, which maps the query columns to properties, as described in the second subsection.
-
If you need to use this data connector for a listing, the class must specify the SUPPORTSIDLIST class parameter as 1:
Parameter SUPPORTSIDLIST = 1;
-
If you need to use this data connector for a cube, and if you want to support cube updates, the class must specify the SUPPORTSSINGLE parameter as 1:
Parameter SUPPORTSSINGLE = 1;
When you compile a data connector, the system generates a class with the name packagename.classname.ResultSet, where packagename.classname is the full name of the data connector class itself. Do not edit the generated class.
Defining the Query in an XData Block
To define the query in an XData block, add an element to the data connector class like the following:
XData SourceQuery [ XMLNamespace = "http://www.intersystems.com/deepsee/connector/query" ]
{
<sql>SELECT %ID, DateOfSale, Product->Name AS ProductName FROM HoleFoods.SalesTransaction</sql>
}
Notes:
-
You cannot use this technique if the data connector must support detail listings or updates. In such cases, instead see Defining the Query at Runtime, later in this page.
-
The name of this XData block must be SourceQuery
-
The XMLNamespace parameter must equal "http://www.intersystems.com/deepsee/connector/query"
-
The XData block must contain one <sql> element, which contains the SQL query to execute.
-
The query must return the IDs of the records, in addition to other fields you need.
-
To include the less than symbol (<) in the query, use <
For example:
<sql>SELECT A,B,C FROM MyApp.MyTable WHERE A<'50'</sql>
Similarly, to include an ampersand (&) in the query, use &
-
If you use arrow syntax to access a field, it might be necessary to also supply an alias for the field. Specifically, an alias is required if you use the data connector as the basis of a cube and you want to use the field in the definition of a cube element.
For example, consider the following query:
SELECT %ID, DateOfSale, Product->Name FROM HoleFoods.SalesTransaction
In this case, there is no way for a cube definition to refer to the Product->Name field; the build process throws an error if you use either Product->Name or Product.Name. As a consequence, you cannot use this field as the basis of a level or measure.
In contrast, consider this query:
SELECT %ID, DateOfSale, Product->Name AS ProductName FROM HoleFoods.SalesTransaction
In this case, you can treat ProductName as a property in the source class, so you can define a level or measure based on it.
Defining the Output Specification
Every data connector class must contain an XData block that maps the query columns to properties, as in the following example:
XData Output [ XMLNamespace = "http://www.intersystems.com/deepsee/connector/output" ]
{
<connector>
<property name="Gender" sourceProperty="Gender" />
<property name="Age" sourceProperty="Age" type="%ZEN.Datatype.integer"/>
<property name="HomeCity" sourceProperty="HomeCity"/>
<property name="PatientGroup" sourceProperty="PatientGroup"
transform='$CASE(%val,"A":"Group A","B":"Group B",:%val)' />
<property name="TestScore" sourceProperty="TestScore" type="%ZEN.Datatype.integer"/>
</connector>
}
Each <property> element is a property of the data connector and can be used by Business Intelligence.
Notes:
-
The name of this XData block must be Output
-
The XMLNamespace parameter must equal "http://www.intersystems.com/deepsee/connector/output"
-
This XData block must contain one <connector> element.
-
The <connector> element must include one or more <property> elements.
-
Each <property> element must specify some or all of the following attributes:
Attribute Purpose name Name of the property, for use as a source property in a cube, in a source expression in a cube, or as a field in a listing. sourceProperty Name of the corresponding column of the result set. type (Optional) Data type for the property. The default is %Library.StringOpens in a new tab. transform (Optional) An expression that uses %val (the current column value) as input and returns a transformed value. -
If you are going to use this data connector for a listing, also specify the idkey attribute for the appropriate <property> element or elements. This attribute indicates that the given property or properties represent the IdKey of the data set.
If you mark multiple fields with idKey="true", the data connector combines these fields.
Note:If you have a cube based on a data connector and listings in that cube that are also based on data connectors, all of these data connectors must have the same property (or properties) marked as idkey="true", because the underlying mechanism uses the same ID values in all cases.
The following shows an example with idkey:
XData Output [ XMLNamespace = " http://www.intersystems.com/deepsee/connector/output" ]
{
<connector >
<property name= "%ID" sourceProperty ="ID" displayName ="Record ID" idKey= "true"/>
<property name= "Product" sourceProperty ="Product" displayName ="Product name"/>
<property name= "AmountOfSale" sourceProperty ="AmountOfSale" displayName ="Amount of sale"/>
</connector >
}
Previewing the Query Results
To test a data connector, you can directly view the query results. To easily see the output for a data connector, use its %Print() class method in the Terminal. For example:
d ##class(BI.Model.PatientsQuery).%Print()
1 1 SUBJ_1003 M 27 Redwood
2 2 SUBJ_1003 M 41 Magnolia
3 3 SUBJ_1003 F 42 Elm Heigh
...
By default, this method prints the first 100 records of the output.
This method has the following signature:
classmethod %Print(ByRef pParameters, pMaxRows As %Integer = 100) as %Status
Where pParameters is currently not used, and pMaxRows is the maximum number of rows to display.
Defining the Query at Runtime
Instead of defining a hardcoded query in an XData block, you can construct the query at runtime. If the data connector must support detail listings or updates, you must use this technique.
To construct the query at runtime, implement the %OnGetSourceResultSet() method. This method has the following signature:
Method %OnGetSourceResultSet(ByRef pParameters, Output pResultSet) As %Status
Where pParameters is currently unused, and pResultSet is the result set.
In your implementation, do the following:
-
If you are using this data connector for multiple purposes, examine the %mode property of the data connector instance. The system automatically sets this property when it creates the data connector instance. This property has one of the following values:
-
"all" — Indicates that the cube is being built or that an All member is being shown.
-
"idlist" — Indicates that a listing is being requested.
-
"single" — Indicates that %ProcessFact() has been invoked.
-
-
Creates an instance of %SQL.StatementOpens in a new tab. The query must return the IDs of the records, in addition to other fields you need.
The details of the query should be different, depending on the mode in which this data connector has been created. Typically:
-
You define a basic query for use with the "all" mode.
-
You add a restriction when the mode is "single", to get the single record that is being updated. The first subsection provides details.
-
You add a different restriction when the mode is "idlist", to get a subset of the records. The second subsection provides details.
-
-
Execute that statement, optionally passing to it any runtime values as parameters. Certain runtime values are available as properties of the statement instance, as discussed in the following subsections.
This step creates an instance of %SQL.StatementResultOpens in a new tab.
-
Return the instance of %SQL.StatementResultOpens in a new tab as an output parameter.
Restricting the Records When an Update Is Requested
When you update a cube with ProcessFact(), you indicate the ID of the record to update. When you create a data connector for use by a cube, you must add logic so that its query uses only the given ID. In this case, you can use the %singleId property of your data connector; it contains the ID of the record that is being updated. For example:
//do this when constructing the SQL statement
if (..%mode="single") {
set sql = sql _ " where %ID = ?"
}
...
//do this when executing the SQL statement
if (..%mode="single") {
set pResultSet = tStatement.%Execute(..%singleId)
}
For information on ProcessFact(), see the article Keeping the Cubes Current.
Restricting the Records When a Listing Is Requested
When a user requests a listing, the system retrieves the IDs of the records used in the given context and stores them for later use. For a default listing, the system automatically uses those IDs in the SQL query of the listing. When you create a data connector for use in a listing, you must add logic so that your query uses the IDs.
In this case, it is necessary to understand how the system stores the IDs for a listing. It writes these IDs to a table (the listing table for this cube), which includes the following columns:
-
_DSqueryKey — Identifies a listing.
-
_DSsourceId — An ID, as in the original source data.
The following shows an example:
Here, the first five rows are associated with the listing 83616140, which uses the IDs of five records, given in the _DSsourceId column. The next two rows are associated with the listing 2139316107, which uses the IDs of two records.
There are two ways to modify the data connector query to use the listing table:
-
Add an IN clause to the query and use the applicable rows from the listing table in a subquery. The following shows an example:
SELECT A,B,C FROM MyApp.MyTable WHERE (ID IN (SELECT _DSsourceId FROM listingtable WHERE _DSqueryKey=somekey))
In this case:
-
listingtable is the name of the listing table for the cube. To get this table name, you use the %listingTable property of your data connector.
-
somekey is the unique key for the current listing. To get this key, you use the %listingKey property of your data connector.
This approach can lead to <MAXSTRING> errors and other size-related issues.
-
-
Perform a JOIN between the source table and the listing table with the correct WHERE clause.
The following shows an example, from a data connector that is used as the source for a cube and as the source for a listing. Notice that the listing key is passed to the query as a parameter.
Method %OnGetSourceResultSet(ByRef pParameters, Output pResultSet) As %Status
{
set tSC = $$$OK
set pResultSet = ""
Try {
set sql = "SELECT %ID, fdate, fname, ftimestamp FROM TestTD.TimeDimensions"
//when we're using this for a listing, add WHERE clause to restrict to
//the appropriate IDs (in the table given by the %listingTable property)
if (..%mode="idlist") {
set sql = sql _ " where %ID in (select _DSsourceId from "
_ ..%listingTable _ " where _DSqueryKey = ?)"
}
set tStatement = ##class(%SQL.Statement).%New()
set tSC = tStatement.%Prepare(.sql)
If $$$ISERR(tSC) {
set ex = ##class(%Exception.StatusException).CreateFromStatus(tSC)
throw ex
}
//if we're using this for a listing, pass in the listing key as a parameter
if (..%mode="idlist") {
set pResultSet = tStatement.%Execute(..%listingKey)
} else {
set pResultSet = tStatement.%Execute()
}
//check %SQLCODE and report if there's an error
If pResultSet.%SQLCODE {
set sqlcode=pResultSet.%SQLCODE
set message=pResultSet.%Message
set ex = ##class(%Exception.SQL).CreateFromSQLCODE(sqlcode, message)
throw ex
}
}
Catch(ex) {
Set tSC = ex.AsStatus()
}
Quit tSC
}
Other Callbacks
The %DeepSee.DataConnectorOpens in a new tab class provides additional callback methods that you can customize to handle errors, perform transformations on rows, perform filtering, and so on. These include %OnNextRecord() and %OnProcessRecord(). For details, see the InterSystems Class Reference.
Using a Data Connector Programmatically
To use a data connector programmatically, do the following:
-
Create an instance of it.
-
Invoke its %Execute() method, which returns a result set. This method also returns a status by reference.
-
Check the returned status.
-
If the status is not an error, you can now use the result set, which is an instance of %SQL.StatementResultOpens in a new tab.
For example:
Set tConnector=..%New()
Set tRS=tConnector.%Execute(,.tSC)
If $$$ISERR(tSC) {Quit}
//use tRS as needed ...