DeepSee Implementation Guide
Defining Data Connectors
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

This chapter describes how to define data connectors. It discusses the following topics:

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 DeepSee Models.)
The SQL query can use a combination of the following:
If a cube is based on a data connector, all listings for that cube must also be based on data connectors.
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:
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:
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 DeepSee.
Notes:
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 by using the test page or by printing output in the Terminal.
Viewing the Test Page
To view the test page for a data connector:
  1. Open the data connector class in Studio.
The system then displays a page like the following:
Printing the Output in the Terminal
To easily see the output for a data connector, use its %Print() class method in the Terminal. For example:
d ##class(DeepSee.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:
  1. If you are using this data connector for multiple purposes, examine the %mode property of the data connector instance. DeepSee automatically sets this property when it creates the data connector instance. This property has one of the following values:
  2. Creates an instance of %SQL.Statement. 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:
  3. 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.StatementResult.
  4. Return the instance of %SQL.StatementResult 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 chapter Keeping the Cubes Current.”
Restricting the Records When a Listing Is Requested
When a user requests a listing, DeepSee retrieves the IDs of the records used in the given context and stores them for later use. For a default listing, DeepSee 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 DeepSee stores the IDs for a listing. It writes these IDs to a table (the listing table for this cube), which includes the following columns:
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:
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.DataConnector 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:
  1. Create an instance of it.
  2. Invoke its %Execute() method, which returns a result set. This method also returns a status by reference.
  3. Check the returned status.
  4. If the status is not an error, you can now use the result set, which is an instance of %SQL.StatementResult.
For example:
    Set tConnector=..%New()
    Set tRS=tConnector.%Execute(,.tSC)
    If $$$ISERR(tSC) {Quit}
    //use tRS as needed ...