Visualize Oracle EPM Data in Python Jupyter Notebook
This was originally part of my Medium Articles.
Another day, another experiment with the Export Data Slice REST API. I have written about the Export Data Slice rest api earlier. Do read that for an explanation for the beginners. Here is a link to the previous article. Today I wanted to show you how you can visualize data in Jupyter Notebooks.
Export data slice can be used to export data for a specified region or intersection. The exported data will be in the form of a JSON grid with pov, columns, and 0 or more data rows.
Understanding the Data Structure
To export the data using the REST API, we need to understand the intersection where we have data. The below data form will you give you a better idea of the data we are looking to export using the API. We will be focusing on the Plan scenario numbers.
Press enter or click to view image in full size
Expense By Category Data Form in EPM
Grid Definition
We need to provide the grid definition as part of the JSON Payload. It will be easier to understand the grid definition by looking at the existing Form design. As you can see the POV (Point of View) has the following Dimensions and corresponding members:
HSP_View: BaseData
Version: Working
Product: P_000
Period: YearTotal
Press enter or click to view image in full size
POV Members in Form Definition
Now let’s look at the rows. You will notice we have two Dimensions.
Entity: Descendants(TD)
Account: 6000, Children(7001), 7300
Rows in the Form Definition
Finally, let’s check the column. We are going to look at the Plan scenario column. The column has two dimensions:
Year: FY22
Scenario: Plan
Columns in the Form Definition
JSON Payload
Based on the Form design, let’s now create the Grid Definition in the JSON Payload. I have broken down the Grid definition into three separate sections for easy explanation. Below is the Point of View.
provide the dimensions
provide the members
Press enter or click to view image in full size
Define Point of View in the JSON Payload
I have modified the POV to include HSP_View, Scenario, Year, Product and Version dimensions.
The below section is the columns definition. I am only providing the “Period” dimension.
Press enter or click to view image in full size
Column definition in JSON Payload
The rows section remains the same as the form design. We are providing the Entity and Account dimensions along with the values.
Press enter or click to view image in full size
Row Definition in JSON Payload
Oracle recommends specifying the Dimensions in the grid definition section of the JSON Payload for efficiency.
Jupyter Notebook
Let’s start coding. First things first, lets import the necessary Python libraries.
Press enter or click to view image in full size
Python Libraries
Let’s set the URL and the headers. Make sure to provide the correct URL. To read more about the end point, head over to the Oracle Documentation.
Press enter or click to view image in full size
URL and Headers
Let’s invoke the RESP API. Make sure to use the POST method. You have to pass the user name and password as well.
Press enter or click to view image in full size
Invoke REST API
Once you get the JSON response, you can create a Pandas Data Frame as shown below. There may be a better way of creating the data frame from the JSON response. I am converting the JSON response into a list.
Press enter or click to view image in full size
Create a Pandas Data Frame
The below code will create a bar chart based on the data frame.
Press enter or click to view image in full size
Create a bar Chart
And here is the final visualization created from the data exported from Oracle EPM Planning instance.
Press enter or click to view image in full size
Conclusion
As you can see it is pretty easy to export a data slice using REST API from Oracle EPM. The one draw back that I have seen with this approach is that the “alias” or “description” of the members are not exported.
I will further add data labels and possibly make the chart more interactive. Watch out for another post of Export Data Slice and Import Data Slice coming soon.
Let me know if you have questions on this. Share your comments and feedback. You can view the complete python code here.
