# 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](https://medium.com/@arun-raj/export-data-slice-epm-rest-api-c5c69c1b35bf) 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

![](https://miro.medium.com/v2/resize:fit:875/1*pV1o194hXtX4mZ6L6KF00w.png align="left")

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

![](https://miro.medium.com/v2/resize:fit:875/1*PzfrGwB1fHoM1oB00W0vLg.png align="left")

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
    

![](https://miro.medium.com/v2/resize:fit:409/1*KcabBQRbDSgcMJnWkQ_GSg.png align="left")

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
    

![](https://miro.medium.com/v2/resize:fit:484/1*S5SUmpYyq0SNZO4jWazerA.png align="left")

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

![](https://miro.medium.com/v2/resize:fit:875/1*gl2U-Ch_Lvc0Mo5LVJJg8Q.png align="left")

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

![](https://miro.medium.com/v2/resize:fit:875/1*miDN_V_vmGnad0jUl7T2Gw.png align="left")

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

![](https://miro.medium.com/v2/resize:fit:875/1*ppL5RHJmZ9F-LCgn_o-Haw.png align="left")

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

![](https://miro.medium.com/v2/resize:fit:875/1*ut_gTIekau8JLhSCzxd5-g.png align="left")

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](https://docs.oracle.com/en/cloud/saas/enterprise-performance-management-common/prest/export_dataslices.html).

Press enter or click to view image in full size

![](https://miro.medium.com/v2/resize:fit:875/1*Dy8mVuIgnnhgZWufnHz_Gw.png align="left")

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

![](https://miro.medium.com/v2/resize:fit:875/1*fN3zMbFIyiWlRRNy9Rc5HQ.png align="left")

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

![](https://miro.medium.com/v2/resize:fit:875/1*pSuEkjyPPjgRoQhFTm8lvA.png align="left")

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

![](https://miro.medium.com/v2/resize:fit:875/1*LhvpOfHJj33o9kflI0vQBQ.png align="left")

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

![](https://miro.medium.com/v2/resize:fit:875/1*yEqIyvYegOWhfNMGl96QMg.png align="left")

### **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](https://colab.research.google.com/drive/10WVqT1cXqC4zTCGvkfIx_aKU6L-zFyvv?usp=sharing).