Exporting EPM Dimensional Security to Oracle Autonomous DB

This is a quick-tip post that has been sitting in my drafts folder for quite some time. I had a requirement to export the Planning dimensional security to an Oracle Autonomous database for further processing in OCI so I thought I share the steps on how to achieve that with you.

The steps are quite straight-forward:

1. Export the security in the source application (Planning, Consolidation, etc.)
2. Create a bucket in OCI to store the latest file (one time setup)
3. Create a virtual table in Autonomous based on the file in step 2 (one time setup)

First step, I will be using exportAppSecurity epmautomate command to export the dimensional security to a CSV file.

epmautomate exportAppSecurity secFile.csv        

Second step, create an OCI storage bucket -you only need to do this once.

Third step, create a virtual table in the Oracle Autonomous database to virtually read the security file from the bucket directly without having to physically load the data in the table . To do this, I will use CREATE_EXTERNAL_TABLE procedure in DBMS_CLOUD package.

The procedure is self-explanatory, you just need to provide the columns list and ensure it matches the secfile. In the code below, I have already created an authentication token to use called "OBJ_STORE_CRED" so if you may need to do that if you don't have one already. (If you are using Oracle Identity Cloud Service user then you must add oracleidentitycloudservcice/ before your username)

This is the PL/SQL code to create the virtual table based on the CSV file that is uploaded in the storage bucket.

Voila! That's it.

If I query the table SEC_FILE, I can see the dimensional security in the table as follows:

Finally, if you want to automate this process then you can create a simple script to do so (In a future post, I will show how you can do it using Oracle Integration Cloud).

I won't go through the code line by line, I have already used a similar script in a previous post so have a look at it if you get stuck here.

That is it for the day, I hope you find this post helpful.