Why Essbase?

Recently I had a chance to meet Jim Dorian and Bob Earle, the original founders of Essbase.  We met at the 25th birthday party for Essbase ... it has been 25 years since Arbor Soft went public.  This motivated me to produce this video to re-enforce the great features of Essbase, which is going very strong in cloud and on-premise and has a strong following among customers and partners.

I talk about the foundations needed in any Analytic Engine and why Essbase is well suited and best data structure for an analytic database. This talk could have been given 30 years back by the original founders.  We have been continuing to improve the aggregation and calculations capabilities and the scale at which we operate. Also, our focus has been on the cloud to provide this wonderful software on the cloud.

Please stay tuned for my next presentation coming soon which will talk about "20 reasons to consider Essbase in cloud".

Here is the link to the video of "Why Essbase?":

https://youtu.be/dw6dOSP9Jd8

Here are some pictures from 25th birthday party for Essbase:

Regards

Kumar

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

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\username\AppData\Roaming\Microsoft\Excel\Excel15.xlb

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

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.