Transactional APIs - Part 3: SQL Query Anaplan data using Transactional API via Data Virtualization

AnaplanOEG
AnaplanOEG
in Best Practices

Here’s a sample transcript of conversations I routinely have with our customers who are new to Anaplan platform:

  • Customer: I have a need to query Anaplan ‘table’ and integrate Anaplan data to my database. Is this possible?
  • Anaplan: Certainly, using Anaplan Transactional APIs, you can query a saved view and output the results in either a csv or JSON format. You can also accomplish the same using Anaplan Connect 2.0.
  • Customer: My team is more comfortable with SQL. Can I just use SQL to read these saved views on Anaplan to extract data?

For those who are seasoned Anaplanners, how would you answer the second question?

One of Anaplan Customers (whom you’ll hear from later in this article) had a similar question except that their question was more specific. Their question was (I’m paraphrasing here…):

  • Can we use our Data Virtualization platform to extract data from Anaplan and combine it with data from our data sources to provide a unified view?

Short answer to this question is “Yes”.

Before we examine the “how” part, let’s set the stage with introduction to Data Virtualization and its high-level architecture. This will help us understand how such platforms can take advantage of Anaplan Transactional APIs to integrate Anaplan data with data from other data sources to provide a unified view.

What Is Data Virtualization?

Data Virtualization is a data management approach that facilitates access, read, and manipulation of data from disparate data sources without requiring the need to understand intricate details of:

  • Where the data is store
  • What application, database, or file(s) the data resides in
  • What format the data is stored in (ex: delimited, xml, json, tables, etc…)

This approach allows for unification of disparate data into a single view. Traditional database technologies (ex: Oracle) provide similar functionality via feature called “External Tables”. External Tables, for example enables you to access data from external data sources as if they were tables or views in a database. This means, I can run a SQL query against an external data source as if it were a database table without requiring any knowledge of location and format of how that external data is stored. Some software vendors also use the term ‘Data Federation’ to describe this approach.

Figure below describes concept of Data Virtualization on a high level.

annejulie_0-1625851608076.png

There are several vendors that provide Data Virtualization functionality (ex: Denodo Platform, IBM Federation Server, etc…).

In this article, we will present how Data Virtualization platform Denodo can be used to access data from Anaplan platform using Anaplan’s Transactional APIs via a SQL query.

We will also hear from one of Anaplan’s customers and their “real world” use case involving Anaplan Transactional APIs and Data Virtualization.

Intent of this article is to present the solution details and not to provide step-by-step instructions on Denodo. Concepts presented in this article should translate to other Data Virtualization platforms.

What Are Transactional Apis?

Transactional APIs allow users to:

  • Efficiently access Anaplan data without using Export/Import Actions
  • Gain deeper insights into Anaplan workspaces and models
  • Zero in on critical data points and make targeted updates

Learn more about Transactional APIs and other use cases from Part 1 and Part 2 of this article series.

Scenario

Our objective is to be able to access data from a saved view via a SQL statement using Denodo Data Virtualization platform.

Denodo will invoke Anaplan Transactional API via a REST connector. Response from Anaplan REST API call will be parsed by a JSON connector to write to a temporary view in Denodo which allows for SQL access.

Of course, this scenario can be expanded to combine data from other sources (ex: database) with that from Anaplan to provide a unified view via a SQL join. We’ll keep it simple for this article purpose to show the “art of possible” using Transactional APIs with Denodo Data Virtualization Platform.

Anaplan

In this example, I have a saved view in Anaplan named “AccountDetailsInfoAPI”.

annejulie_1-1625851608081.png

Using modelId, and viewId, I can construct a REST API call to read directly from this saved view and output the result set in a csv format. REST API end point may look something like:

GET https://api.anaplan.com/2/0/models/35A6EF893D7F47EEA5A554D5CC7DC330/views/205000000001/data?format=v1

We will not cover details of this API request syntax. Please refer to Part 2 of these series for details on Transactional API.

How can we use this REST API request in Denodo to build a Data Virtualization layer for SQL access to this saved view is what we will present in the next step, under the section Denodo Data Virtualization Platform.

Denodo Data Virtualization Platform

  1. Create a Data Source: First step is to create a Data Source. Since response from Anaplan REST API call is a delimited data (csv), we will create a ‘Delmited File’ Data Source.

annejulie_2-1625851608087.png

  1. Configure Data Source: Once delimited file data source is created, we will configure the data source to execute a REST API call via an HTTP Client.

annejulie_3-1625851608089.png

  1. Configure HTTP Client: We will provide Anaplan REST API request details under HTTP Client configuration. We will provide GET as a request method, request URL end point, and headers for authentication.

annejulie_4-1625851608093.png

 

annejulie_5-1625851608095.png

  1. Test Connection: If provided endpoints and headers are valid, test connection should be successful.

annejulie_6-1625851608096.png

  1. Create a base view: Last step before being able to access Anaplan data via SQL is to create a base view in Denodo.

annejulie_7-1625851608097.png

 

annejulie_8-1625851608101.png

You will have the ability to modify this “base view” and change its column names, data types, Primary Keys, etc…

  1. Execute SQL: You are now ready to read data from Anaplan using SQL statements.

annejulie_9-1625851608114.png

 

annejulie_10-1625851608122.png

Real World Customer Implementation Use Case

Fresenius Medical Care is a global, vertically integrated dialysis care organization with more than 40 production deployed Anaplan models spanning multiple divisions and business use cases.

Legacy Process

As part of our initial deployment, we implemented a batch automation tool (ActiveBatch) to call export/import action(s) within Anaplan and exchange files with a predetermined network location.

This tool also allows us to, then, initiate a process chain within SAP BW to load plan data from Anaplan for reporting purposes using BEX Analyzer and SAP Analytics Cloud.

annejulie_11-1625851608127.png

 

Number of file exchange automations: ~150 scheduled automations

Cons

  1. Data is refreshed only once a day
  2. End to End process is slow
  3. Relies on flat files being exchanged

Real Time Data Integration from FMC Anaplan models to SAP Analytics Cloud using Data Virtualization Platform, Denodo.

Problem Statement

The Anaplan CoE team was asked to establish a process to integrate data from FMC models over to SAP Analytics Cloud (SAC) for real-time reporting & analysis where the end user can trigger a real-time refresh of data.

Solution

The CoE team explored the Anaplan API connection (2.0) and created an integration process using Denodo platform.  

This integration allows for Anaplan data to be pulled directly into a SQL DB within the Denodo tool using transactional API or Rest API calls. The data is then available for consumption by any other tool using a variety of connections.

Steps

  • Step 1: Create export action(s) in Anaplan and extract export ID(s)/file ID(s)
  • Step 2: Set up the Anaplan API request in Denodo to call the export action (Refer to section on Denodo Data Virtualization Platform in this article)
  • Step 3: Login into SAC and call the request for real time data integration from Anaplan (requests can be controlled using triggers, filter criteria, and user permissions)

annejulie_12-1625851608136.png

Pros

  1. Data is refreshed real-time triggered by end user within SAC
  2. End to End refresh time is reduced
  3. Flat file exchange is eliminated from the process

Conclusion

The flexibility of Anaplan APIs, combined with the power of data virtualization platforms allows for creative ways to integrate data in an Anaplan model with other systems using a familiar data query & manipulation language like Structured Query Language (SQL).

We hope this article has provided insights into different ways Anaplan APIs can be leveraged to build efficient and effective integrations across an enterprise.

 

If you want to deep dive into Anaplan and Chat Bots, we got you covered, whether you use AWS or GCP.

 

Got feedback on this content? Let us know in the thread below.

 

Tagged:

Categories