Loading Data from ADW/ATP to Essbase using CLI

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.

https://www.oracle.com/technetwork/database/application-development/jdbc/downloads/jdbc-ucp-183-5013470.html

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+

$esscs.sh login -user weblogic -password welcome1 –url <OAC-ESSBASE-URL>

where OAC-ESSBASE-URL – https://essbase.example.oraclecloud.com/essbase

$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 oraConn -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.

Tip: A fix for Excel process hanging in memory after exiting excel

From time to time, the Excel process on my machine will remain in memory for a long time after I close Excel.  This will cause Excel add-ins like Smart View and Cube Designer from loading properly.

The culprit is a corrupted Excel15.xlb file that has grown to be a large file size.  On my machine, this file was ~10 MB.  The normal file size is ~10 KB.

Simply go to this location on your Windows machine and rename the file:

C:\Users\\AppData\Roaming\Microsoft\Excel\Excel15.xlb

Next time you start Excel, this file will be rebuilt automatically.

Thanks to Eran Wass for sharing this tip!

OAC - Essbase Drill-Through In Action

In this article, we will try to create a simple drill through report using a data source. Data source can be built from various sources so once you create a drill through report and want to switch from say spreadsheet to database, there will be no impact on the report definition and it will be hot deployed so I would recommend to start on the simple CSV and once all set, switch to data warehouse or keep CSV as is.

Let's start with the data...

In Sample Basic cube, say we have weekly sales but cube will be created with month level onward.
For simplicity, I have divided the month into 4 weeks. week1 to week4. Our goal is to drill through to these weekly sales.

Let's create a connection and data source to point to your data.
Once we create data source, go to applications tab and expand to Sample Basic cube and go to database inspector. (Actions -> Inspect...)

Scripts -> Drill Through Reports - New Data Source based.
Provide a name "weekly_sales"
Select data source

In column mapping section - select all report columns which will come in the report.
In Filter, select SKU generation for Product as we want to recursively go down till SKU level and fetch the data.
Same for Market and Month.
For Scenario, we want to pull only the selected member to be filtered so no generations selected. In the drill-able regions add - "Sales" (w/o quotes) as we want to view this report only on sales measures.
You can provide combinations of cells as well. e.g. "Sales 100" will restrict to Sales and 100 intersection.
You can add such multiple combinations by adding rows. e.g. If you want to show this to Sales and 200 as well, add  a row with "Sales 200"

Save, close inspector and launch Smart View.

If you do not see colors for the drill-through enabled ranges, change option below in SmartView Tab - > Options

Once you select any cell(or multiple cells as well in coming patches) , drill through will work.


Try it out!

Connecting OAC DV & DVD to OAC Essbase

I have seen a lot of users asking about how to setup connectivity from Oracle Data Visualization Desktop with OAC Essbase. In this post we will see how users can setup that connection. Contrary to the belief that additional ports needs to be opened for connectivity from DVD client to OAC Essbase, is not true. OAC Essbase recently included ESSNet over HTTP(S), which does not require additional ports opening.(Details here)

Cube Designer - Tips and Tricks #1

For me, the Cube Designer is a powerful and natural way to design an Essbase cube.  I love the fact I can use the power of Excel to aid me in all aspects of dimension building.  However, one of the drawbacks of the DBX paradigm is that it is fairly rigid in it's support for incrementally updating an existing cube.  For example, by default you cannot move members from one parent to another in an existing cube.


But there is a way around this limitation.  It utilizes a few undocumented features of the DBX process.  I will discuss the process for enabling DBX to do everything except rename a member in an existing cube.

OAC - Essbase Drill-Through Reports

Essbase drill through reports had various versions in the past years. There were studio drill-through reports, EIS drill through reports and GL drill through reports. All of them served well for viewing the supporting details but all had pros and cons.

OAC - Essbase has a new and easy way to maintain drill through reports and is entirely re-written to solve all the problems for supporting details. As this is based on connections and data sources abstraction, there are many opportunities to innovate and make it a lot better.  In this article, we are going to discuss the mappings involved in the drill through reports. In the next article, I will show you how to create one from scratch.

Loading data into OAC-Essbase from legacy systems using REST apis

As promised in my previous blog, we will try to load the data from a legacy on-premise system to OAC-Essbase.
As we already discussed, not all legacy systems generate data which can be directly imported into Essbase and not all support JDBC. We may need to write a connector which understands both systems.  After moving to the OAC-Essbase this task will be even challenging as Essbase will stay in the cloud and legacy system may stay at on-premise.

Well, there is a good news! OAC-Essbase does have streaming REST apis to do exactly this.