Autonomous data ware house and transaction processing cloud
provides a new way to connect and that will be adapted by many cloud vendors in
coming releases. The new approach is more secure and wallet based.
In current version, OAC - Essbase does not support direct
load from wallet based connections. This may come eventually in coming
releases, this article will show a hack till that time. Even though there are
ways to export to file and load that file to Essbase, CLI seems a better
approach. As Essbase CLI gives you full control over the classpath, you can
tweak it to suit your needs and support any JDBC.
In this article, we will see how we can achieve the
dimension build or data load with CLI in minimal steps.
Estimated time to complete:
15 minutes
Download the ADW/ATP JDBC jars from OTN.
Download the ojdbc8-full.tar.gz and extract it into
CLI-INSTALL-DIR/lib
Replace the files on conflict.
Change the esscs.sh (Linux client) or esscs.bat (Windows
client) to link the new jars.
CMD file changes (highlighted
in green color)
set
CLASSPATH=.;%REST_CLI_HOME%/ess_rest_cli.jar;%REST_CLI_HOME%/ess_es_server.jar;%REST_CLI_HOME%/ess_japi.jar;%REST_CLI_HOME%/ess_svp.jar;%REST_CLI_HOME%/commons-cli.jar;%REST_CLI_HOME%/commons-io.jar;%REST_CLI_HOME%/jersey-client.jar;%REST_CLI_HOME%/javax.ws.rs-api.jar;%REST_CLI_HOME%/jersey-common.jar;%REST_CLI_HOME%/hk2-utils.jar;%REST_CLI_HOME%/javax.inject.jar;%REST_CLI_HOME%/hk2-locator.jar;%REST_CLI_HOME%/hk2-api.jar;%REST_CLI_HOME%/javax-annotation-javax-annotation-api.jar;%REST_CLI_HOME%/jackson-annotations.jar;%REST_CLI_HOME%/jackson-core.jar;%REST_CLI_HOME%/jackson-databind.jar;%REST_CLI_HOME%/jackson-mapper-asl-1.9.2.jar;%REST_CLI_HOME%/ojdl.jar;%REST_CLI_HOME%/jersey-guava.jar;%REST_CLI_HOME%/cglib.jar;%REST_CLI_HOME%/jackson-core-asl-1.9.2.jar;%JAVA_HOME%/db/lib/derby.jar;%REST_CLI_HOME%/ojdbc8.jar;%REST_CLI_HOME%/ess-platform-common.jar;%REST_CLI_HOME%/commons-lang.jar;%REST_CLI_HOME%/datasource-model.jar;%REST_CLI_HOME%/excel-core.jar;%REST_CLI_HOME%/lz4-java.jar;%REST_CLI_HOME%/avatica-core.jar;%REST_CLI_HOME%/calcite-core.jar;%REST_CLI_HOME%/calcite-linq4j.jar;%REST_CLI_HOME%/protobuf-java.jar;%REST_CLI_HOME%/janino.jar;%REST_CLI_HOME%/commons-compiler.jar;%REST_CLI_HOME%/guava.jar;%REST_CLI_HOME%/slf4j-api.jar;%REST_CLI_HOME%/slf4j-nop.jar;%REST_CLI_HOME%/commons-lang3.jar;%REST_CLI_HOME%/ucp.jar;%REST_CLI_HOME%/ons.jar;%REST_CLI_HOME%/oraclepki.jar;%REST_CLI_HOME%/orai18n.jar;%REST_CLI_HOME%/osdt_cert.jar;%REST_CLI_HOME%/osdt_core.jar;%REST_CLI_HOME%/simplefan.jar;%REST_CLI_HOME%/xdb6.jar
Bash File changes (Highlighted in Green color)
export
CLASSPATH=.:$REST_CLI_HOME/ess_rest_cli.jar:$REST_CLI_HOME/ess_es_server.jar:$REST_CLI_HOME/ess_japi.jar:$REST_CLI_HOME/ess_svp.jar:$REST_CLI_HOME/commons-cli.jar:$REST_CLI_HOME/commons-io.jar:$REST_CLI_HOME/jersey-client.jar:$REST_CLI_HOME/javax.ws.rs-api.jar:$REST_CLI_HOME/jersey-common.jar:$REST_CLI_HOME/hk2-utils.jar:$REST_CLI_HOME/hk2-apijar:$REST_CLI_HOME/javax.inject.jar:$REST_CLI_HOME/hk2-locator.jar:$REST_CLI_HOME/hk2-api.jar:$REST_CLI_HOME/javax-annotation-javax-annotation-api.jar:$REST_CLI_HOME/jackson-annotations.jar:$REST_CLI_HOME/jackson-core.jar:$REST_CLI_HOME/jackson-databind.jar:$REST_CLI_HOME/jackson-mapper-asl-1.9.2.jar:$REST_CLI_HOME/ojdl.jar:$REST_CLI_HOME/jersey-guava.jar:$REST_CLI_HOME/cglib.jar:$REST_CLI_HOME/jackson-core-asl-1.9.2.jar:$JAVA_HOME/db/lib/derby.jar:$REST_CLI_HOME/ojdbc8.jar:$REST_CLI_HOME/ess-platform-common.jar:$REST_CLI_HOME/commons-lang.jar:$REST_CLI_HOME/datasource-model.jar:$REST_CLI_HOME/excel-core.jar:$REST_CLI_HOME/lz4-java.jar:$REST_CLI_HOME/avatica-core.jar:$REST_CLI_HOME/calcite-core.jar:$REST_CLI_HOME/calcite-linq4j.jar:$REST_CLI_HOME/protobuf-java.jar:$REST_CLI_HOME/janino.jar:$REST_CLI_HOME/commons-compiler.jar:$REST_CLI_HOME/guava.jar:$REST_CLI_HOME/slf4j-api.jar:$REST_CLI_HOME/slf4j-nop.jar:$REST_CLI_HOME/commons-lang3.jar:$REST_CLI_HOME/ons.jar:$REST_CLI_HOME/oraclepki.jar:$REST_CLI_HOME/orai18n.jar:$REST_CLI_HOME/osdt_core.jar:$REST_CLI_HOME/osdt_cert.jar:$REST_CLI_HOME/simplefan.jar:$REST_CLI_HOME/ucp.jar:$REST_CLI_HOME/xdb6.jar
Extract the DB wallet file and store it in some path. I am
going to use /scratch/wallets/adwwallet in this example.
Use the proxy (in tnsnames.ora) if your client machine is
behind the firewall as per document: https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/connect-preparing.html#GUID-EFAFA00E-54CC-47C7-8C71-E7868279EF3B
e.g.
ADWC1_high
=
(description=
(address=
(https_proxy=proxyhostname)(https_proxy_port=80)(protocol=tcps)(port=1522)(host=adwc.example.oraclecloud.com)
)
(connect_data=(service_name=adwc1_high.adwc.oraclecloud.com)
)
(security=(ssl_server_cert_dn="adwc.example.oraclecloud.com,OU=Oracle
BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")
)
)
All set. Let’s try CLI commands now.
Make sure you have latest JDK 1.8.162+
Make sure you have latest JDK 1.8.162+
$esscs.sh login -user weblogic -password welcome1 –url <OAC-ESSBASE-URL>
$esscs.sh createLocalConnection -name adwConn
-connectionString "jdbc:oracle:thin:@ adw_service_name?TNS_ADMIN=/scratch/wallets/adwwallet"
-user adw_user -password adw_password
Pay attention to the new format of
JDBC string.
To build a dimension:
$esscs.sh dimbuild -application Sample -db Basic -rule
prod1m.rul -stream -restructureOption ALL_DATA -connection adwConn -query
" SELECT PRODUCT, MARKET, …. FROM TABLE
WHERE …."
To load data:
$esscs.sh dataload -application Sample -db Basic -stream
-connection adwConn -query "SELECT PRODUCT, MARKET, …. FROM TABLE WHERE …." -rule prod1mdata.rul
Output:
Streaming to Essbase...
.............................................................................................................................................................................................
Streamed 1000005 rows to cube
And it works!
Make sure that your select query returns exactly same number
of columns as specified in the rules file, otherwise Essbase will fail to load.
