Consuming Other Data Sources
Consuming other data sources via ODBC (Open Database Connectivity) queries
For importing data from a third-party application to a Longview DataArea or DataTable, you may use an ODBC connection. This is accomplished by using a Longview Data Import and specifying ODBC as the DataSource, along with an appropriate connection string and SQL statement to execute.
The following table lists the available Application Framework ImportSpec functions to support ODBC imports in Longview.
For more information, see ImportSpec and ExportSpec Functions in the Longview Developer Guide.
| Function | Usage |
|---|---|
DataSource ODBC, “Connection String” |
Use this function with an import specification to define the source of the data to be imported. |
SQLBatchSize |
Specify the number of records to retrieve with each fetch against the ODBC data source |
SQLStatement |
Specify the SQL statement to pass along to the ODBC data source |
Example of using ODBC in an ImportSpec document:
DataSource ODBC, "Driver={SQL Server Native Client 10.0};Server=ca127MyServer;Database=MYDB;Trusted_Connection=Yes;"
SQLBatchSize 10
SQLStatement “SELECT [Currency], [Rate] FROM FXRates”
Consuming other data sources by calling REST APIs
REST APIs enable organizations to communicate with each other and with clients, without deep knowledge of each other’s IT systems. The REST API provider defines a format for requests for each of its services and the response that the service generates.
Longview Application Framework can call REST APIs provided by third-party applications using various commands in a Procedure document. These commands will execute the third-party REST APIs using the provider’s defined format and retrieve the responses from the Provider in a defined format. A popular return format for REST APIs is JSON format, which is a lightweight, open-standard, object-oriented format that is easy for humans to read. When an API returns JSON format, Longview Application Framework can translate the response directly into an object. However, Longview Application Framework supports results in other formats as well. Options for handling responses include:
- using a STRING variable as the result target and processing that string in memory or exporting the result, using EXPORTVARIABLE, to a file.
- using an OBJECT variable as the result target and processing that object in memory or writing to a file via EXPORTVARIABLE.
- using a DOCUMENT as the result target and saving the resulting document to a file using SAVE. This method is useful, for example, with csv responses.
The following table lists the available Application Framework Procedure commands to support consuming REST APIs in Longview. For more information, see the Longview Developer Guide.
| Command | Usage |
|---|---|
Rest DeleteRequestHeader |
Delete a header and its value that was previously set using REST SETREQUESTHEADER |
Rest ExecuteDelete |
Delete a resource identified by a URI |
Rest ExecuteGet |
Retrieve information returned from a REST http GET method |
Rest ExecutePost |
Post to a resource using a restful http POST method |
Rest ExecutePut |
Put or update data to a resource using a restful http PUT method |
Rest GetResponseHeader |
Retrieve a specified response header for the REST EXECUTEGET, REST EXECUTEPOST, or REST EXECUTEPUT command that was previously invoked |
Rest GetResponseStatus |
Retrieve the http response status for the REST EXECUTEGET, REST EXECUTEPOST, or REST EXECUTEPUT command that was previously invoked |
Rest SetRequestHeader |
Set a header for the REST call about to be executed |
Example of Calling REST APIs in a Procedure document:
// Example with Target as a variable
CREATE VARIABLE TestResult1 AS String
REST EXECUTEGET "http://api.fixer.io/latest" TO TestResult1
EXPORTVARIABLE TestResult1 TestResult1.json
// Example with Target as a document
CREATE DOCUMENT "TestResult2.json"
REST EXECUTEGET "http://api.fixer.io/latest" TO "TestResult2.json"
SAVE "TestResult2.json"
Consuming Other Data Sources Using Open Business Data Fabric
The Open Business Data Fabric allows you to retrieve data from Angles Business Views, which provides:
shared data across data sources and applications
pre-built business views
To utilize the Open Business Data Fabric, you require the following:
access to the platform
a designer license for Angles
a service to service account set up in platform
an access token created by a user targeting Angles PDS
To configure Longview to connect to Open Data Business Fabric:
Launch Application Administrator.
In server explorer, expand Attributes and click on SYSTEM.
Locate the SAccessToken attribute, right-click it, and select Set Value.
In the Enter New Value field, enter the access token generated for targeting Angles PDS.
Click Assign to Value.
Click OK.
Open the Actions menu and select Maintenance to toggle maintenance mode off.
Retrieving data using Open Business Data Fabric
You can retrieve data using the Get DATA command in application framework. This command can only be executed using the service-to-service account. The command will generate a file with the requested data in oData format. Data retrieved in oData format can be imported into a data area using the Run IMPORT command.
To use the Get DATA command, you will need the following:
The id of the business view you will use.
Any filter or select options you will use to restrict the data retrieved, for example to restrict the data to a single period, or limit the fields returned by the query.
The syntax of the Get DATA command is as follows:
Get DATA businessViewId ["parameters"] TO fileName
Specifying parameters for the Get DATA command
Parameters is optional in the Get DATA command but is useful for filtering the data returned in the request and limiting the properties returned for each item in the oData collection. Parameters are used to include oData system query options such as $filter and $select in the request. There are many system query options available with oData requests, but only the most commonly used options for integration with Longview will be described.
Format for parameters in the Get DATA command
Parameters take the format “system query option”=”expression”. When specifying a system query option, exclude the dollar ‘$’ character used with oData queries.
Multiple parameters are separated with the ampersand “&” character.
For example, “filter=expression&select=expression”.
The expression you use will be dependent on the business view you are querying.
Using filter in the parameters of the Get DATA command
The filter system query option can be used with a business view only if one or more Filter Values Entities have been defined in the business view. The expression for a filter is written as “field name” “operator” “value”. If the value contains spaces, it must be enclosed in single quotes.
For example, filter=accounting_period_name eq 'Feb 2019'.
Valid operators include:
Equal (eq)
Not equal (ne)
Greater than (gt)
Greater than or equal (ge)
Less than (lt)
Less than or equal (le)
Has flags (has)
A filter can also include multiple conditions using logical operators “and”, “or”, and “not”.
Using select in the parameters of the Get DATA command
The select query option can be used to limit the properties returned for each item in the query result set. The expression for a select is written as a command separated list of field names. The field names must match the names defined in the business view.
For example, select=account,base_currency,ytd_consolidated_amount.
Supporting pagination with the Get DATA command
In cases where the amount of data being returned by a Get DATA request is extremely large, you can use pagination to return the results in multiple discrete requests. Open Business Data Fabric only supports pagination on the client side, so it needs to be configured in your process.
To use pagination with the Get DATA command:
The business view must have a unique key defined.
Include the top and skip system query options in the parameters.
-
Iterate through Get DATA requests until the number of records returned not equal to the value defined by top.
-
You can check the number of records by:
Importing the file into an object.
Checking the count of the value property.
-
Import each file generated by the Get DATA command into the target data area.
Error handling with the Get DATA command
In cases where the business view ID or parameters used with the Get DATA command are invalid, the result returned will still be JSON but will not be in oData format.
You can check whether an error occurred by importing the file to object and checking for the existence of the error property. If the error property exists, the error.message property contains the message returned by the invalid request.
Example: invalid business id:
{"error":{"code":null,"message":"Cannot find EntitySet, Singleton, ActionImport or FunctionImport with name 's_66632f48a751af7e6c8c6c3z'."}}
Example: invalid field in select
{"error":{"code":null,"message":"The property 'acc0unt', used in a query expression, is not defined in type '64ccbd4795855d760edb7fb7'."}}
Caveats
Since multiple files are created, this technique works best when used with the ADD option for duplicate record handling in the import specification document.
If the import specification uses the USEFIRST or USELAST option for duplicate record handling, the first or last record in the last file processed will be imported.
If the import specification uses the DISALLOW option for duplicate record handling, an error will only occur if one of the files processed contains duplicate records.
Example: Retrieve oData 500,000 records at a time and import
Create VARIABLE top AS NUM
Create VARIABLE skip AS NUM
Create VARIABLE loop AS NUM
Create VARIABLE parameters AS STRING
Create VARIABLE oData AS OBJECT
Create DATAAREA dataAreaName USING "dataAreaSpec.lvdsp"
Set VARIABLE loop = 1
Set VARIABLE top = 500000
While $loop$
Set VARIABLE parameters = "top=$top$" + "&skip=$skip$"
Get DATA businessViewID "$parameters$" TO "oData_$skip$.json"
Run IMPORT importSpec.lvimp TO dataAreaName
ImportObject oData "oData_$skip$.json"
Set VARIABLE loop = Count(oData.value) == $top$
If $loop$
Set VARIABLE skip = $skip$ + $top$
END If
END While
Related topics:
• To learn more about the insightsoftware Platform, see the Platform Experience documentation.
• To learn more about the Get DATA command, see Get Data.
• To learn more about import specs, see Creating ImportSpecs and Run (for ImportSpecs).