Connect Your Cube From On-premises Sources of Data

After you migrate your cube from on-premises to the cloud, perform dataload and dimension build operations for connecting source data that is external to the Oracle Analytics Cloud – Essbase cube.

If your source data is not in the Oracle Public Cloud, then you can either securely stream data from an on-premises SQL sources by creating a local connection or by uploading flat files using the CLI Tool.

Update Your Cube Data Using Flat Files

Connect your cubes to update easily by using flat files. Ensure that you have dataload and the dimension rule files in the cloud so that you can create a batch program by using the CLI Tool to update Oracle Analytics Cloud – Essbase.

To connect from a local directory to an instance of Oracle Analytics Cloud – Essbase:

Start the command-line interface.
Ensure that JDK is configured on the client. When you migrated your on-premises cube to the cloud, you must have downloaded Java SE Development Kit 8 and set the JAVA_HOME environment variable to point to the JDK installation folder.
- Windows: Run esscs.bat
- UNIX: Run esscs.sh

esscs login -u <username> –p <password> -url https://<instance-name>:<tenant>/essbase

Upload the flat files that map to your dataload and dimension build rules.

To upload the file, run this command:

upload [-verbose] -file filename [-application appname [-db cubename]] [-overwrite] [-nocompression][-compressionalgorithm]

For example,

esscs upload -v -f c:/temp/datasample.txt -a Sample -d Basic -o -ca lz4

If one or more dimensions change, you need to update the dimensions before you update the data:

dimbuild [-verbose] -application appname -db cubename -file fileName -rule rulesFile [-user userName [-password password]] [-stream] [-connection connectionName][-query queryString] [-rows n]] [-restructureOption restructureOption] [-forcedimbuild]

For example,

esscs dimbuild -a Sample -d Basic -r Basic.rul -S -conn oraConn -q "Select * from Data" -rows 50 -R NO_DATA

Set up and run your dataload job so that Essbase can read the data values or dimensions in the flat file and loads them based on the dataload rule:

dataload [-verbose] -application appname -db cubename [-file filename] [-rule rulesFile] [-user username [-password password]] [-stream] [-connection connectionName][-query queryString] [-rows n]] [-abortOnError]

For example,

esscs dataload -application Sample -db Basic -rule Basic.rul -stream -connection oraConn -query "Select * from Data" -rows 50

After you run this command, the data is refreshed in the cube.

Update Your Cube Data From an On-Premises Database

You can stream data and dimension member metadata from an on-premises database to an Oracle Analytics Cloud – Essbase cube. By using SQL, you can connect to a relational database management system (RDBMS) and refresh or load dimensions and data to the cube.

When you use the stream option for the CLI, you must also reference a saved Java Database Connectivity (JDBC) connection that reflects your driver and connection strings. So it is important to use the SQL functions that are supported by JDBC.

Open the CLI tool and start the command-line interface. Ensure that JDK is configured on the client. When you migrated your on-premises cube to the cloud, you must have downloaded Java SE Development Kit 8 and set the JAVA_HOME environment variable to point to the JDK installation folder.
- Windows: Run esscs.bat
- UNIX: Run esscs.sh

esscs login -u <username> –p <password> -url https://<instance-name>:<tenant>/essbase

Create a saved JDBC connection string that reflects your data source driver and connection strings:

Syntax:

createLocalConnection [-verbose] -name streamConnection -connectionstring  connectionString -user userName [-driver jdbcDriver]

Example:

esscs createLocalConnection -N OracleDBConnection1 -cs jdbc:oracle:thin:@myhostname01:1234:ORCL -u OracleUser -D oracle.jdbc.driver.OracleDriver

If one or more dimensions change, you need to update the dimensions before you update the data:

dimbuild [-verbose] -application appname -db cubename -file fileName -rule rulesFile [-user userName [-password password]] [-stream] [-connection connectionName][-query queryString] [-rows n]] [-restructureOption restructureOption] [-forcedimbuild]

For example,

esscs dimbuild -a Sample -d Basic -r Basic.rul -S -conn oraConn -q "Select * from Data" -rows 50 -R NO_DATA

Set up and run your dataload job so that Essbase can read the data values or dimensions and loads them based on the rules:

dataload [-verbose] -application appname -db cubename [-file filename] [-rule rulesFile] [-user username [-password password]] [-stream] [-connection connectionName][-query queryString] [-rows n]] [-abortOnError]

For example,

esscs dataload -application Sample -db Basic -rule Basic.rul -stream -connection oraConn -query "Select * from Data" -rows 50

After you run this command, the data is refreshed in the cube.