Posts

OCI - Installing EPM Automate on Always Free Oracle Linux

Image
Using Oracle's FREE Cloud VMs to Store your EPM Cloud Snapshots Unless you have been living under a rock for the past month you will know that Oracle have released their "Oracle Cloud Free Tier". This consists of the following services FOR FREE!
https://www.oracle.com/uk/cloud/free/


I've enjoyed playing with ADW and APEX, an incredible combo which will enable us to build simple applications to enhance our existing EPM Cloud apps.

The two free Oracle Cloud Infrastructure Compute VMs also caught my attention. These could of course be used for a multitude of things, one of which could be a host for EPM Automate and a location to store EPM Cloud snapshots.
Installing EPM Automate on Oracle Linux OCI There are several blogs out there on how to setup your instance. Here is a good one from APEX guru Dimitri:
https://dgielis.blogspot.com/2019/09/free-oracle-cloud-6-create-vm-instance.html
So, once you've got your VM running you can install EPM Automate.

Download the Linux …

OAC - Starting and stopping instances using the REST API (Autonomous/Oracle Managed)

Image
We waited a while to be able to start and stop OAC Oracle Managed (autonomous) instances through the OAC console but there isn't a way to schedule the starting and stopping of instances in the console. This is of course very important if you want to keep costs down by automatically shutting down your instances overnight for example.


Thankfully there are now REST API calls available for Oracle Managed OAC instances which allow you to stop and start your instances. You can schedule these commands from a scripting tool of your choice to automate this process e.g. create a batch script and schedule the start/stop using the Windows scheduler.

OAC Start Instance REST API Call The call to start the instance is a follows:

curl -v -i -X POST -u username:password -H
"Content-Type:application/vnd.com.oracle.oracloud.provisioning.Service+json"
-H "X-ID-TENANT-NAME:idcs-<IDCS-Account-Name>"
"https://psm-cacct-<PSM-Account-Name>.console.oraclecloud.com/paas/api/…

OAC Remote Data Gateway: Connecting to MS SQL Server

Image
The objective of this blog is to describe what the OAC Remote Data Gateway is, how it is configured and how to us it to connect an OAC instance to an on-premises MS SQL Server database (although the process will be the same connecting to any other on-premises data source).

The Oracle Analytics team have released a utility called the ‘Oracle Analytics Cloud Data Gateway’ which enables OAC to easily connect to your on-premises datasources with minimal fuss and WITHOUT HAVING TO OPEN UP FIREWALL PORTS!

No more awkward conversations with your network admin. No more grovelling and bribing them to open up ports in their firewall.

Current supported data sources are:

Oracle databaseSQL ServerDB2Teradata
The Oracle A-Team have written a blog here on what it does and how to configure and there is also a useful youtube video here.

What is the Oracle Analytics Remote Data Gateway? The Data Gateway is a replacement for the Remote Data Connector which is the OAC utility to connect to your on-premis…

OAC - Essbase Streaming Dataload from SQL Server Database

Image
Here's a short blog on streaming to the OAC Essbase cloud from a MS SQL Server source. The team at essbaselabs have written a nice blog on streaming Essbase data loads and the Oracle docs are here.

Download your MS SQL driver from hereSave it to your client machine and add the path to the jar file to your EXTERNAL_CLASSPATH in the esscs.bat file:
Using essCLI create the local connection to your on-prem data source:esscs createLocalConnection -N MSSQL_EPMDB -cs jdbc:sqlserver://epmdb:1433 -u epmsql -D com.microsoft.sqlserver.jdbc.SQLServerDriver


Run the dataload command. Here I'm passing the SQL query into the command but you can also store the query in your Essbase rules file:esscs dataload -application Sample -db Basic -rule SQLData.rul -stream -connection MSSQL_EPMDB -query "Select * from Sales"



Error handling is good and logged in the essbase-cli.log file:



I then correct my SQL statement and the query is successful:


For rules related errors a familiar dataload.err f…

OAC - Scripted Backups on Autonomous

Image
So, you’ve got Oracle Autonomous Analytics Cloud and you want to schedule a backup of your BI and Essbase applications.

Essbase has the nice esscs/esscli command line REST API wrapper to which will perform an LCM backup for you and you’ve got the MaxL client which you can use to perform data exports. I’ll show an example of this further down this blog

However, at the time of writing this blog there aren’t any documented REST API commands to perform an automated BI BAR extract (these are in the pipeline). This means there isn’t an obvious way to schedule your own non-Oracle backup.

BI Admin Tool and Catalog Manager to the Rescue! Fortunately, you can use the command line to launch BI Admin Tool and Catalog Manager commands. There’s not much documentation so I’ve blogged how you use these tools to connect to your Oracle Analytics Cloud instance.

OAC RPD Backup Here we will download and backup our RPD using the datamodel.cmd tool which comes with BI Client. There’s some documentation on…