Forum Discussion

hoaphan's avatar
hoaphan
New Contributor III
4 years ago

REST API USE

Hello OneStream Community, I'm working on how to implement a REST API in OS, but i'm not sure to understand all the steps, i've already read the documentation and i've found it very hard to unders...
Data Integration
REST Api
  • pvanerp's avatar
    pvanerp
    13 days ago

    Ahhh, thank you CoPilot. I got it to work with this code:

    		Private Function GetSourceDataREST(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Transformer) As DataTable
			Try
				Dim strEntityWF As String = api.WorkflowProfile.Name.Split(".")(0)	
				
'				Use already encoded credentials (eg.,In Postman) key
				Dim wdCreds As String = "<CREDENTIALS>"
				'Call the REST API		
				Dim wc As New WebClient
				wc.Encoding = Encoding.UTF8
				wc.Headers("cache-control") = "no-cache"
				wc.Headers("ContentType") = "application/json"
				wc.Headers("Accept") = "application/json"
				wc.Headers("Authorization") = "Basic " & wdCreds
						
				Dim jsonURL As String = "<AURL>"
				Dim json As String = wc.DownloadString(jsonurl)
						
				Dim pullData As DataTable = ParseJsonToDataTable(json, strEntityWF)

				'Close Web connection
				wc.dispose
				'Return the dataset
				Return pullData
			Catch ex As Exception
			Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try				
				
			End Function		
			
			
 			Function ParseJsonToDataTable(json As String, EntityName As String) As DataTable
		        Dim dataTable As New DataTable()
		        dataTable.Columns.Add("entity", GetType(String))
		        dataTable.Columns.Add("displayName", GetType(String))
		        dataTable.Columns.Add("amount", GetType(Integer))

		        Dim jObject As JObject = JObject.Parse(json)
		        Dim dataArray As JArray = jObject("employees")

		        For Each item As JObject In dataArray
		            Dim row As DataRow = dataTable.NewRow()
		            row("entity") = EntityName
		            row("displayName") = item("displayName")
		            row("amount") = 1
		            dataTable.Rows.Add(row)
		        Next

        		Return dataTable
    		End Function

     

pvanerp's avatar
pvanerp
New Contributor II

Hi indu,

Thanks for your prompt reply. I'm afraid the problem occurs a bit sooner, on the deserialisation step. The error I'm getting is: 

Unable to execute Business Rule 'cnHiBob_v1'. Unexpected JSON token when reading DataTable: StartObject. Path 'employees[0].employee', line 1, position 59.

The JSON I'm accessing has a general structure as (anonymised and shortened):

{
  "employees": [
    {
      "displayName": "John Johnson",
      "employee": {
        "payrollManager": "1234567890",
        "hrbp": "1234567890",
        "itAdmin": "1234567890",
        "buddy": null
      },
      "work": {
        "shortStartDate": "01-01",
        "startDate": "2023-01-01",
        "manager": "1234567890",
        "tenureDuration": {
          "periodISO": "P1Y9M9D",
          "sortFactor": 000,
          "humanize": "1 year, 6 months and 15 days"
        },
        "custom": {
          "field_1698922567656": null,
          "field_1697701034939": "1234567890"
        },
        "durationOfEmployment": {
          "periodISO": "P1Y9M9D",
          "sortFactor": 00,
          "humanize": "1 year, 6 months and 15 days"
        },
        "reportsToIdInCompany": null,
        "employeeIdInCompany": 2,
        "reportsTo": {
          "displayName": "John Doe",
          "email": "johnd@mycompany.com",
          "surname": "Doe",
          "firstName": "John",
          "id": "1234567890"
        },
        "indirectReports": 3,
        "siteId": 0000887,
        "tenureDurationYears": 1.777,
        "department": "1234567890",
        "tenureYears": 1,
        "customColumns": {
          "column_1694007818663": "1234567890",
          "column_1694007758524": null,
          "column_1692966052634": "1234567890",
          "column_1695048623305": null,
          "column_1697624280234": null
        },
        "isManager": true,
        "title": "1234567890",
        "site": "mycompany
        "originalStartDate": "2023-01-01",
        "activeEffectiveDate": "2024-01-01",
        "directReports": 3,
        "secondLevelManager": "1234567890",
        "daysOfPreviousService": 0,
        "yearsOfService": 1
      },
      "companyId": 1234567890,
      "email": "johnj@mycompany.com",
      "surname": "Johnson",
      "id": "1234567890",
      "firstName": "John"
    },
  ]
}

Thanks for any help or guidance!

pvanerp's avatar
pvanerp
New Contributor II
13 days ago

Ahhh, thank you CoPilot. I got it to work with this code:

		Private Function GetSourceDataREST(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Transformer) As DataTable
			Try
				Dim strEntityWF As String = api.WorkflowProfile.Name.Split(".")(0)	
				
'				Use already encoded credentials (eg.,In Postman) key
				Dim wdCreds As String = "<CREDENTIALS>"
				'Call the REST API		
				Dim wc As New WebClient
				wc.Encoding = Encoding.UTF8
				wc.Headers("cache-control") = "no-cache"
				wc.Headers("ContentType") = "application/json"
				wc.Headers("Accept") = "application/json"
				wc.Headers("Authorization") = "Basic " & wdCreds
						
				Dim jsonURL As String = "<AURL>"
				Dim json As String = wc.DownloadString(jsonurl)
						
				Dim pullData As DataTable = ParseJsonToDataTable(json, strEntityWF)

				'Close Web connection
				wc.dispose
				'Return the dataset
				Return pullData
			Catch ex As Exception
			Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try				
				
			End Function		
			
			
 			Function ParseJsonToDataTable(json As String, EntityName As String) As DataTable
		        Dim dataTable As New DataTable()
		        dataTable.Columns.Add("entity", GetType(String))
		        dataTable.Columns.Add("displayName", GetType(String))
		        dataTable.Columns.Add("amount", GetType(Integer))

		        Dim jObject As JObject = JObject.Parse(json)
		        Dim dataArray As JArray = jObject("employees")

		        For Each item As JObject In dataArray
		            Dim row As DataRow = dataTable.NewRow()
		            row("entity") = EntityName
		            row("displayName") = item("displayName")
		            row("amount") = 1
		            dataTable.Rows.Add(row)
		        Next

        		Return dataTable
    		End Function