EPM Export Data REST API Enhancements

As part of the March 2024 updates, there was an enhancement to the Export Data REST API. In this article, I want to dive deeper and share with you what I have found during my testing.

What is the Enhancement

At a high level, you can now use the REST API to export data without a job definition. The API uses the parameter values provided without an explicit predefined export data job definition. The file containing the exported data is stored in the repository.

Exporting data supports substitution variables. You can use substitution variables while providing the?rowMembers,?columnMembers, and?povMembers?definitions.

You can read more about the Export Data REST API here.

The Past... Before the Enhancement

Let's look at what it used to be.

1. You had to first define an "Export Data Job" definition by navigating to Applications > Overview > Export Data
2. Save the "Export Data" as a job and give it a name
3. Use the REST API to invoke the saved "Export Data" job
4. The file is generated in the "Inbox/Outbox" folder

The payload for the REST API would look something like below:

{"jobType":"EXPORT_DATA","jobName":"ExportWeeklySalesForecast",
     "parameters":{
          "exportFileName":"SalesForecst_Weekly.zip"
          }
}        

You specify the job type as "EXPORT_DATA", provide the job name and the name of the Export File Name.

The Future... So What's New

The enhancement to the REST API allows you to export the data without creating a "Export Data" job definition. So your workflow could look like this.

1. Use the REST API to invoke "Export Data" job
2. The file is generated in the "Inbox/Outbox" folder

Is it really that simple? ????

If there is no saved job definition, you need to make sure to include the following details as part of the REST API Payload.

1. Cube Name
2. PoV Members
3. Row Members
4. Column Members

Now this should remind you of "Export Data Slice" REST API.

So in the new world, if you dont have a saved "Export Data" job definition, the payload would look like below.

{"jobType":"EXPORT_DATA","jobName":"ExportJobExpense",
"parameters":{
     "cube":"OEP_FS",
     "rowMembers":"Salaries,Bonus,Payroll Taxes,Labor Expenses",
     "columnMembers":"YearTotal",
     "povMembers":"OEP_Forecast,FY24,OEP_Working,USD,IT US,No Product,No Market,OFS_Total Plan"
     }
}        

This looks more like defining the job definition on the fly, exporting the data and not saving it as a job!!!

You can use substitution variables while providing the?rowMembers,?columnMembers, and?povMembers?definitions.

Conclusion

I believe this is a great enhancement. I wish it created a "JSON" response instead of creating a file in "Inbox/Outbox" folder. But worry not, we can use Data Pipeline file operations to unzip file, copy to a separate instance and then import the data into the other instance, if needed.

Along with Groovy, I think this is a powerful feature. What are your thoughts? Let me know in the comments. I will be testing couple more scenarios and will write about my experience.

Check out this video if you want to learn more about the file operations available in Data Pipeline. Happy EPM-ing!!