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.

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.

Why to open ports when you can stream the data to Essbase (OAC) over HTTPS?

Migrating to cloud is sometimes hard when few things are on premise and few are moved to cloud. It becomes harder when cloud is connecting back to on premise systems. Say we have a large warehouse on premise and some application is on cloud and now cloud application wants to connect to this warehouse.
Some cloud providers came with *** Direct Connect, few came with **** Connect.
We may have to open the system and ports to public network and maintain those ports backed by strong IT team. Well, good luck with that!

One other approach could be to upload the huge files and process those files on cloud. Not a bad approach security wise but time consuming.

What could be really cool is someone comes up with a solution where we do not have to open the ports but we can stream on premise data to the cloud using industry standard HTTPS.
Wish listened! OAC- Essbase  streaming command line will allow us to do that as it comes with a tiny stream loader for a giant problem.
Let's try it out...

Playing with essbase ad hoc analysis using command line

If you are not familiar with cURL, JSON and REST, please read my earlier blog. Initial section of it has few links to quick start.

In this blog, we are going to play with a grid using command line. Wait! Is this even possible?
Let's try...

Introduction to Cube Designer - Files

OAC-Essbase has introduced a new platform concept called a catalog. The catalog is an area of storage within the cloud for both Essbase artifacts and end user artifacts. The catalog uses the OAC-Essbase security to restrict access.

Can Python digest a cube?

Then, I drew the inside of the boa constrictor, so that the grown-ups could see it clearly. They always need to have things explained.

But let's put elephants and boa constrictors aside. The more important question would be -- Can Python digest a cube? Nowadays even kids in elementary schools do python, so I thought it would be useful to show how Python code can call OAC Essbase REST APIs. Let's try to list files under /gallery/Cubes directory in the Essbase catalog.

Introduction to outline editing using JSON and REST apis

In this blog, we will cover how we can automate the outline editing using simple JSON file and REST apis.

Read more about JSON here: https://www.json.org/
and REST apis here: https://en.wikipedia.org/wiki/Representational_state_transfer

We will use cURL for executing the outline REST. cURL is a command line library to execute HTTP requests.
https://en.wikipedia.org/wiki/CURL

cURL is installed by default on the Linux systems but on windows, you have to download and install it before trying the examples. https://curl.haxx.se/download.html

If you want to try any REST clients like POSTMAN etc., you can skip the cURL installation.

Can we visualize zigzag?

Yes, you can

What are the index based rules and why they are used by cube designer?

In the previous blog, we discussed how to create a dimension build rules file and how it creates a dimension with few clicks from raw data.

In this blog, we will cover the basics of index based rules and the difference between them.

• Good news is they are the similar except the way column operations(join, create columns, ...) are handled. Another difference is index based rules are supported for dimension build only and not for data load. 

• Another good news is, if your rules are in regular format, there is no need to convert them to index based. If you are writing new rules, index based rules are easy to develop and understand.

(Re)Introducing load rules in OAC

Rules define operations that Essbase performs on data values or on dimensions and members when it processes a data source. Use rules to map data values to an Essbase database or to map dimensions and members to an Essbase outline.

 In other words, rules file is the small out of the box ETL utility to extract, transform and load the data into Essbase. You can either build/edit dimension or load data into Essbase using these rules.

In this blog, we will convert the flat data into Essbase dimension called "Year". There is no need to create this empty dimension in the outline. Rules will take care of creating it too.