There are a number of ways to import and export data to and from Workday Adaptive Planning. Most commonly, users perform this task within the integration module in Adaptive to connect to other systems such as ERPs, AWS S3, Snowflake, SalesForce, Excel, Google Sheets (via various connection types: JDBC, Custom Cloud / Javascripting, out-of-the-box etc.).
In today’s edition of Adaptive Corner, we are highlighting one way to import/export data using Workday Adaptive Planning’s API, which allows you to write your own scripts in Python, Javascript, Powershell, or leverage 3rd party platforms such as Tray.IO, Boomi, PandaFlow, etc.
Typical Use cases
To Import Data
- Import Data from an ERP
- Actuals
- Assets lists, Amortization, and Depreciation
- ARR
- Spend by Vendor
- Travel spend details
- Payroll taxes details, Etc.
- Other custom metrics
- Import Sales data from a CRM
- Pipeline, opportunities, customers
- Bookings
- ARR/MRR, Etc.
- Import Personnel Data
- Existing headcount and requisitions
- Wages, demographics
- Import Exchange rates
To Export Data
- Export of budget/forecast information to be exploited by other systems (eg. Tableau, ClickView, Snowflake, ERPs, etc.)
- Export of certain calculations and metrics (eg. COA, expense/revenue allocations, etc.)
- Export for external monitoring dashboard or variance analysis tools
- Export of Actuals + Forecast data for backups
Possible Actions and Documentation
The Adaptive Planning API has three areas of action:
- Metadata Retrieval and Manipulation
- GL accounts
- Dimensions
- Versions
- Etc.
- Data Retrieval (sheets content, including calculated accounts)
- Data Creation / Update
Workday Adaptive Planning documentation walks through the process of setting up an API in detail: it can be found here. But if you want a quick overview with key information summarized, continue reading!
History of API Changes and Availability
API Versions are generally updated when new releases of Adaptive Planning are published. Each version will continue to be supported for at least 1 year after the release of the subsequent one. To view Adaptive’s API change history click here.
Call Types, Security and Permissions
Call Type: HTTP POST / GET
Security & Permissions
- All requests are single-action requests. User must be authenticated on each separate invocation (thus reducing risk of hijacking an existing web service session).
- API requests are encrypted – using HTTPS web protocol
- Authentication credentials are transmitted as part of the body of the web service request
- Login+password are encrypted by web layer before leaving the computer requesting and decrypted by the target server
- There is no special permission for a user to access web services. However, the user making a request must have the required permissions to actually perform the requested action.
- For example: if calling importStandardData → the user must have Import permission
- Output is also restricted to the user’s level access.
- For example: if user is calling exportData → the returned data will only be for the levels owned by this user.
💡 It is best practice to create a separate integration user!
API Methods
Below is an excerpt of Workday Adaptive Planning’s API documentation. The full list of methods can be found here.
Metadata and Data Create, Update, and Read Methods
- createAccount : lets users create new GL, Custom, or Assumption accounts
- createDimension : lets users create new dimensions.
- createDimensionValue : lets users create new dimension values.
- createLevel : lets users create new levels.
- createUser : lets users create new users
- customReportValues : returns a set of data for the requested report criteria in the requested instance
- exportAccounts : retrieve metadata about accounts in the system
- exportActiveCurrencies : retrieve metadata about currencies which have been configured in the system
- exportAttributes : retrieve metadata for all custom attributes in the system
- exportCalendar : retreive metadata for calendar information.
- exportConfigurableModelData : retrieve a set of rows from the requested modeled sheet in the requested version and instance.
- exportCustomerLogo : retrieve a URL that can be used to retrieve the Customer Logo
- exportData : retrieve a set of values from a specified version
- exportDimensionFamilies : retrieve metadata about how dimensions are related to the accounts in the system
- exportDimensions : retrieve metadata about custom dimensions in the system
- exportDimensionMapping : export user-defined dimension mapping rules.
- exportGroups: retrieve the complete list of all groups defined for the given instance.
- exportInstances : retrieve metadata about the instances to which a user has access (if the user has access to multiple instances)
- exportLevels : retrieve metadata about organization levels in the system
- exportLocales : Returns a list of all locales for a company.
- exportModeledSheet : returns the definition of the given modeled sheet
- exportRoles : retrieve metadata about roles in the system
- exportPermissionSets : retrieve metadata about permission sets in the system
- exportSecurityAudit : retrieve security event audit logs for a specified time range
- exportSheetDefinition : retrieve the definition of a modeled or cube sheet
- exportSheets : retrieve a list of all sheets with type, id and name
- exportTime : returns time metadata either for a specific version, or for all versions if none is specified
- exportTransactionDefinition : retrieve the definition of the transaction sheet
- exportUsers : retrieve metadata about users in the system
- exportVersions : retrieve metadata about versions in the system
- importDimensionMapping : imports derived dimension values.
- importGroups : imports a set of user groups.
- importModeledSheet : imports the given modeled sheet
- publishChanges : lets users publish any unpublished changes
- unpublishedChangesStatus : lets users determine if admin publishing is enabled, as well as how many unpublished changes they have
- updateAccessRules : lets users update access rules for instances that use access rule security.
- updateAccount : lets users update the properties of existing GL, Custom, or Assumption accounts.
- updateAssociations : lets users update user level ownership and user dimension associations.
- updateDimension : lets users update properties of existing dimensions.
- updateDimensionValue : lets users update properties of existing dimension values.
- updateLevel : lets users update properties of existing levels.
- updateUser : lets users update user information (does not function once users synchronize from Workday).
Metadata Bulk Update
- updateAccounts: lets users update GL accounts in-bulk by uploading an XML file.
- updateAttributes: lets users update attribute values in-bulk by uploading an XML file.
- updateDimensions : lets users update dimensions and dimension values in-bulk by uploading an XML file.
- updateLevels : lets users update levels in-bulk by uploading an XML file
Data Submission Methods
- importConfigurableModelData : submit a set of rows for a modeled sheet
- importCubeData : submit a set of data to be inserted into a cube sheet
- importStandardData : submit a set of data to be inserted into standard accounts (GL accounts, assumptions, or custom accounts)
- importTransactions : submit a set of transactions to be inserted into the transactions data, if the transactions feature has been enabled
- eraseActuals : erase numeric data in specified time periods and accounts of an actuals version.
- eraseData: erase numeric data from a plan or actuals version for the specified set of accounts for a given time frame.
- recalculateSheet : recalculate sheets with the Recalculate on Demand property.
Example in Javascript
The following sample java program that calls the “exportAccounts” API method. The results of the API method call are printed to the screen. In a real application, the XML data is parsed and the underlying account data is processed.
import java.net.HttpURLConnection;
import java.net.URL;
public class WebServiceClient {
/**
* Make an "exportAccounts" method call.
*/
public static void main(String[] args) throws Exception {
String request = "<?xml version='1.0' encoding='UTF-8'?>\n" +
"<call method=\"exportAccounts\">\n" +
" <credentials login=\"sampleuser@company.com\" password=\"my.pw\"/ callerName=\"test program\">\n" +
"</call>";
// Make a URL connection to the Adaptive Planning web service URL
URL url = new URL("https://api.adaptiveinsights.com/api/v35");
HttpURLConnection conn = (HttpURLConnection) url.openConnection();
// Set content type, HTTP method, and expected output
conn.setRequestProperty("content-type", "text/xml;charset=UTF-8");
conn.setRequestMethod("POST");
conn.setDoOutput(true);
// Send the request
writeRequest(conn, request);
// Read the response
String response = readResponse(conn);
// Print it out
System.out.println(response);
}
/**
* Write the request to the given URLConnection
*/
private static void writeRequest(HttpURLConnection conn, String request) throws Exception {
conn.getOutputStream().write(request.getBytes("UTF-8"));
}
/**
* Read the response from the given URL Connection
*/
private static String readResponse(HttpURLConnection conn) throws Exception {
byte[] buffer = new byte[4096];
StringBuilder sb = new StringBuilder();
int amt;
while ((amt = conn.getInputStream().read(buffer)) != -1) {
String s = new String(buffer, 0, amt, "UTF-8");
sb.append(s);
}
return sb.toString();
}
}
Example in Python
import requests
import xml.etree.ElementTree as ET
xmlRequest = '''<?xml version='1.0' encoding='UTF-8'?>
<call method="exportData" callerName="query_data">
<credentials login="username@xxxxxx.com" password="yyyyyyyyyyyyyy" instanceCode="INSTANCE1"/>
<version name="Actuals" isDefault="false"/>
<format useInternalCodes="true" includeUnmappedItems="false" />
<filters>
<accounts>
<account code="Expenses" isAssumption="false" includeDescendants="true"/>
</accounts>
<levels>
<level name='HR_Department' isRollup="true" includeDescendants="true"/>
</levels>
<timeSpan start="01/2021" end="01/2021"/>
</filters>
<rules includeZeroRows='false' markInvalidValues='false' markBlanks='false' timeRollups='false'>
<currency useCorporate='false' useLocal='true'/>
</rules>
</call>'''
headers = {"Content-Type": "text/xml;charset=UTF-8", "Accept-Encoding": "deflate"}
response = requests.post('https://api.adaptiveplanning.com/api/v36', data=xmlRequest, headers=headers)
response.encoding = 'utf-8'
output = ET.fromstring(response.text).find('output').text
with open("output.txt", "w") as f:
f.write(output)
f.close()
JSON APIs
A limited number of REST APIs use the JSON format for submitting or getting data.
The good news is that there will be more JSON format APIs released in the future.
Details on JSON requests are found here.
JSON Request requirements
- HTTP Method
- URL Endpoint
- Authorization Header
HTTP verb or method
NB: the methods or verbs depends on the service and resource
- GET → Retrieves a collection of data or a single object
- POST → Creates a single data instance with specified data
- PUT → Updates and replaces existing data with the specified data in the request body
- PATCH → Partially updates existing data
- DELETE → Deletes existing data
URL endpoint
https://api.adaptiveinsights.com/rest/<service name>/<version>/<tenant>/<resource path>
- service name: A component name, based on functionality like modeling, security, or report.
- version: The version of the service. Current version for all services: v1.
- tenant: The tenant for this service, indicating the Adaptive Planning instance. Use default for the default instance of the user in the authorization.
- resource path: The path to the resource, using nouns like sheet and availability. The path also supports query parameters, like:
- sheetName to specify the name of a sheet in Adaptive Planning.
- columnName to specify the name of a column in an Adaptive Planning sheet.
- limit to specify the limit of object data entries included in a single response.
- offset to specify the offset to the first object in a collection to include in the response.
Example
https://api.adaptiveinsights.com/api/rest/modeling/v1/globosales/sheet/availability?sheetName=Expense Cube&columnName=Level&columnType=Level
Authorization header:
- Example: –header ‘Authorization: Basic c5RldmVjQLdsb2JvLmNabTpjaGFuZ2VtZQ==’
Have Questions or Want to Learn More?
The QBIX team is here to help! Click the link below to find time to discuss your Workday Adaptive Planning Needs today!