In this article I'm presenting a working example of integrating our beloved Platform with one of most widely used ERPs in the world: Dynamics 365, specifically the FinOps suite. This said, the solution below can be adapted to pretty much any OAUTH-based api!
Note: we will be only covering the development of the OneStream side of such integration. Like with many other products, basic Dynamic 365 APIs don't allow for retrieval of all necessary data (e.g. a trial balance); custom service development will likely be needed on the ERP side to expose a complete interface.
For the purpose of this integration, we are going to build a very generic API connector; it will allow us to pass an endpoint (with filters) as parameter, and handle any result coming our way. In order to do so, we create a business rule which will contain all necessary connection logic and data retrieval logic.
We want to be able to use this system from Data Sources but also directly from a Dashboard Data Adapter, in order to be able to view live data as well (before a load, for example). The best approach is then to create a Dashboard Dataset Business Rule, which we can later reference from both a Connector Business Rule and a Dashboard Data Adapter.
The Dashboard DataSet
For this solution we are going to use C#, which can be better suited to this sort of low-level network programming. That means the following examples will only work on OneStream 7.1 and above. Let's have a look at the basic code we need!
Note: while this business rule has cliend ID and client secret in plain text, there is a much better way to handle this type of sensitive data on OneStream 8.0 and above: you can upload your secrets to a secure Key Vault and access them at runtime through Cloud Administration Tools. See comments in code for details.
Dashboard DataSet BR: D365_HelperQueries
Language: C#
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.CSharp;
using System.Data;
using OneStream.Shared.Common;
using OneStream.Shared.Wcf;
using OneStream.Shared.Engine;
using OneStream.Shared.Database;
using OneStream.Stage.Engine;
using OneStream.Stage.Database;
using OneStream.Finance.Engine;
using OneStream.Finance.Database;
using System.Net.Http;
using System.Net.Http.Headers;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
namespace OneStream.BusinessRule.DashboardDataSet.D365_HelperQueries
{
public class MainClass
{
// As of 8.0, CAT (Cloud Administration Tools) can be used to setup secrets in an Azure Key Vault
// https://documentation.onestream.com/release_8.2/Content/CAT/Key%20Management.html?Highlight=key%20vault
// You can use BRApi.Utilities.GetSecretValue(SessionInfo si, string secretName) instead of putting client ID and Secret in plain code
private const string m_ClientId = "yourclientid";
private const string m_ClientSecret = "yourclientsecret";
private const string m_Resource = "https://yourtenant.sandbox.operations.dynamics.com";
private const string m_AuthUrl = "https://login.microsoftonline.com/yourguid/oauth2/token";
public object Main(SessionInfo si, BRGlobals globals, object api, DashboardDataSetArgs args)
{
try
{
switch (args.FunctionType)
{
case DashboardDataSetFunctionType.GetDataSetNames:
break;
case DashboardDataSetFunctionType.GetDataSet:
// Usage will be:
// {D365_HelperQueries}{GetDataFromEndpoint}{Endpoint=[/data/....]}
if (args.DataSetName.XFEqualsIgnoreCase("GetDataFromEndpoint"))
{
// get the parameterized endpoint,
// and pass it to our data-retrieval method
string endpoint = args.NameValuePairs.XFGetValue("Endpoint");
return GetData(si, endpoint, false).Result;
}
break;
}
return null;
}
catch (Exception ex)
{
throw ErrorHandler.LogWrite(si, new XFException(si, ex));
}
}
/* Utility to handle security details */
public async Task<string> GetBearerToken(SessionInfo si)
{
try
{
string bearerToken;
using (HttpClient hc = new HttpClient())
{
HttpRequestMessage request = new HttpRequestMessage(HttpMethod.Post, m_AuthUrl);
MultipartFormDataContent content = new MultipartFormDataContent();
content.Add(new StringContent("client_credentials"), "grant_type");
content.Add(new StringContent(m_ClientId), "client_id");
content.Add(new StringContent(m_ClientSecret), "client_secret");
content.Add(new StringContent(m_Resource), "resource");
request.Content = content;
// response
HttpResponseMessage response = await hc.SendAsync(request);
response.EnsureSuccessStatusCode();
string result = await response.Content.ReadAsStringAsync();
var responseJson = JObject.Parse(result);
bearerToken = responseJson["access_token"].ToString();
}
return bearerToken;
}
catch (Exception ex)
{
throw ErrorHandler.LogWrite(si, new XFException(si, ex));
}
}
/* entry point for our data-retrieval logic */
public async Task<DataTable> GetData(SessionInfo si, string endpoint, bool LimitRecords = false)
{
string bearerToken = GetBearerToken(si).Result;
DataTable dt = null;
if (bearerToken != string.Empty)
{
try
{
using (HttpClient hc = new HttpClient())
{
// set up our request...
string limitOpt = string.Empty;
if (LimitRecords)
limitOpt = "&$top=1";
HttpRequestMessage request = new HttpRequestMessage(HttpMethod.Get, m_Resource + endpoint + limitOpt);
request.Headers.Authorization = new AuthenticationHeaderValue("Bearer", bearerToken);
// ... then handle the response
HttpResponseMessage response = await hc.SendAsync(request);
string result = await response.Content.ReadAsStringAsync();
// APIs typically return JSON, so we parse that as a DataTable
var resultValues = JObject.Parse(result)["value"];
dt = JsonConvert.DeserializeObject<DataTable>(resultValues.ToString());
}
}
catch (Exception ex)
{
throw ErrorHandler.LogWrite(si, new XFException(si, ex));
}
}
return dt;
}
}
}
Once this is set, we can create a Dashboard Data Adapter and test connectivity. It will be a Business Rule method type adapter, with the following method query:
{D365_HelperQueries}{GetDataFromEndpoint}{EndPoint=[/data/YourEndpoint?$filter=TransDate ge 2022-01-01T12:00:00Z]}
Before proceeding, we should test it with a few different interfaces (e.g. customers, trial balance) and verify it is working properly.
We now have a very simple way to call any endpoint in Dynamics 365 FinOps and visualize the data from there! We can also apply any required filtering to limit our datasets.
The DataSource Connector
The next step is to create a Connector Business Rule, which will access the methods we added in the Dashboard Dataset BR and allow us to load data in an Import workflow step. We can set the endpoint dynamically, by using Text properties in the Workflow profile definition for an import step.
Connector BR: D365_HelperQueries
Language: C#
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Windows.Forms;
using Microsoft.CSharp;
using OneStream.Finance.Database;
using OneStream.Finance.Engine;
using OneStream.Shared.Common;
using OneStream.Shared.Database;
using OneStream.Shared.Engine;
using OneStream.Shared.Wcf;
using OneStream.Stage.Database;
using OneStream.Stage.Engine;
// import our custom DDS and name it HelperQueries
using HelperQueries = OneStream.BusinessRule.DashboardDataSet.D365_HelperQueries;
namespace OneStream.BusinessRule.Connector.D365_DataSource
{
public class MainClass
{
public object Main(SessionInfo si, BRGlobals globals, Transformer api, ConnectorArgs args)
{
// Workflow varies by Scenario Type, so let's find that first
var scenType = BRApi.Finance.Scenario.GetScenarioType(si, si.WorkflowClusterPk.ScenarioKey);
// get Text1 property on the current Workflow Profile
string endpoint = BRApi.Workflow.Metadata.GetProfile(
si, si.WorkflowClusterPk
).GetAttributeValue(
scenType.Id,
SharedConstants.WorkflowProfileAttributeIndexes.Text1
);
try
{
switch (args.ActionType)
{
case ConnectorActionTypes.GetFieldList:
{
// use our DDS rule to fetch data,
// then return column names
DataTable dt = HelperQueries.MainClass.GetData(si, endpoint, true).Result;
List<string> columnNames = dt.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToList();
return columnNames;
}
case ConnectorActionTypes.GetData:
{
// use our DDS rule to fetch data,
// then pass it to transformation
DataTable dt = HelperQueries.MainClass.GetData(si, endpoint).Result;
api.Parser.ProcessDataTable(si,
dt, false, api.ProcessInfo);
break;
}
}
return null;
}
catch (Exception ex)
{
throw ErrorHandler.LogWrite(si, new XFException(si, ex));
}
}
}
}We can now use this generic datasource from multiple Import profiles to retrieve different data.
Conclusion
As we have seen, by combining Dashboard DataSets with Connectors we can provide both data visualization and data load services with (effectively) a single codebase. This is particularly useful when low-level connection logic is complex, with lengthy security processes - like in the case of the OAUTH calls required by Dynamic 365.
