Announcing Layouts, Reports and Impersonation in OAC - Essbase

In this post, I am going to explain what are the layouts, reports in the Essbase Analyze UI and how that will help the users to view and export the data in few clicks.

This feature is only introduced in the web UI but soon will be part of the SmartView as well.

Layouts
Layouts are nothing but the saved grids or bookmarked grids. SmartView is great and it allows to zoom in, zoom out and type to view the data required. When we work on the same or few grids again and again, every time launching SmartView and navigating by clicking ad-hoc buttons does not make any sense. It not only waste users time but also waste the resources on server to compute various operations.

Layouts are introduced to address this problem and save the grid as we go and navigate so that can be viewed anytime.

Reports
Reports are nothing but saved MDX queries but will be extended to data source queries as well. It would be interesting to join the MDX query with any relational or files data and view that to get what we need. For now, you can type the MDX queries and save them and run anytime. You can export the result into various formats as well like Excel, CSV, HTML and JSON.

You can run these reports as other user too just to make sure security filters are applied correctly on that user. You have to be system administrator to use impersonation.

Lets dive and see them in details:
1. Click on Analyze data on applications page - > Cube actions
2. You see the default grid.
3. Do some operations and close the browser tab.
4. repeat 1
5. Now you see the report you built in step #3. Its called session layout so system is smart enough to save your last navigated stuff.

If you are a DB manager, you can mark the layout as database default. Once you mark it, every user will see the same on launch unless they have their own default layout.

So algorithm is simple:
1. Looks for my default
2. if not #1, Looks for DB default
3. if not #2, Looks for Session
4. if not #3, Default grid

Here are the few screens:

All the REST apis can be impersonated provided that login user is System administrator.
Just add the X-Essbase-LoginAs header in REST call.

Sandbox and Scenario Workflow in Essbase for 1000's of users

In Essbase, one can always create a new dimension and clone the data and give to different users. This bloats the cube and doesnt scale to large number of users.  One needs to introduce strict limits on number of members in that sandbox/version/scenarion dimension. Also, since it is a metadata, business users cannot create new sandbox members.

We introduced new sandboxing and scenario management capabilities in Essbase in cloud. This is light weight and highly scalable and provides necessary isolation like Excel on your laptop for users. Moreover, end users can create new scenarios without involving admin.

Please watch the video for more details:

Essbase and light weight sandbox

Thanks
Kumar

Announcing Multi-Cell, Multi-Region Drill through

As we have seen in earlier blog posts, how easy it is to create a drill through reports in OAC - Essbase. If you have not read the earlier post yet then read it here.

In this post I am just focusing on multi-cell/multi region drill-through reports. Don't worry, there is no change required on your earlier reports. They will just keep working with single as well as multi selections.

Requirement:
You need latest SmartView for trying this out. If you need time to update SmartView on all your user machines then you can keep old SmartView and single cell based drill through will keep working as earlier.

Make sure that your instance is OAC 5.2 or later.

What is Multi-Cell :
Multi-Cell is when you select continuous cells in the Excel like B3, B4

What is Multi-Region:
Multi-Region is when you select multiple non continuous multi-cells. :)
In other words, when you select any number of regions(use ctrl+select to select multiple non-continuous ) you want in Excel.
e.g. It's very common for us to see the detailed report for all the months till September  excluding quarters like below.

Now go ahead and select such cells where drill through reports are defined and run drill through.

What should you expect when you click on drill through toolbar button:

Listing:
When you select multiple cells or regions, and there are multiple drill through reports, intersections of those reports are shown.
e.g. If you select A1, A2 and A3  and here is the cell to reports mapping
A1: R1
A2: R1, R3, R2
A3:  R1, R3, R4

then only R1 is common so it will be executed directly.

If you select only A2 and A3 then both R1 and R3 are listed. You can select one and then execute it.

Same will happen with multi region. Report(s) should be available for all the cells you selected.

Execution:
For Multi-Cell:
Think of it as running a SQL like select r1,r2,r3 where f1 in (a1,a2,a3) and f2 in (b1,b2,b3)
Where r1,r2 and r3 are the report columns and f1 and f2 are mapped columns for A and B dimension.

For single cell the query was like:
select r1,r2,r3 where f1=a1 and f2=b1

So only change is the replacement of equal operation with IN operation.

If you want sorted data etc then its better to define the data source query itself with sorting or use excel sorting once report is landed.

For Multi-Region:
Now think of it as running multiple SQL statements in parallel (one per region).
select r1,r2,r3 where f1 in (a1,a2,a3) and f2 in (b1,b2,b3)
+
select r1,r2,r3 where f1 in (i1,i2,i3) and f2 in (j1,j2,j3)
+
select r1,r2,r3 where f1 in (x1,x2,x3) and f2 in (y1,y2,y3)

As these queries are run in parallel and appended together in the result to improve the performance, data will remain unsorted. You can sort it in the Excel.

The combination of recursive, multi cell and multi region drill through is so fascinating! 

Try it out!

Autonomous Database support in Essbase

Happy New Year 2019!

In my earlier post, we learnt to use existing CLI to connect to ADW and ATP. This was just a workaround and new OAC-Essbase CLI does not need it anymore.

However the workaround was only for CLI and there was no support through UI and REST.
From OAC 5.2, Essbase CLI, UI and REST support to create connection to autonomous database(Autonomous data warehouse and transaction processing).

This post will demonstrate to create a connection to ADW and that connection can then be used to data load, build dimension, drill through or any other use cases using Essbase data sources abstraction.

Step 1. Download the wallet ZIP from your ADW/ATP instance.

Step 2. If you need any proxy settings to connect to the database then extract the zip, change/add proxy details in the tnsnames.ora. This is specified in the post I mentioned at the start of this post.

Step 3: Create an Oracle Database - Autonomous Connection like below:

Now create a data source and use it to load data, build a dimension, drill through ....

Rest :

To upload wallet file for a connection to be created called myconn:
curl "http://host/essbase/rest/v1/connections/myconn/wallet" -X PUT
-H "Content-type: application/octet-stream"
 --data <Wallet file path>

To create:
curl "http://host/essbase/rest/v1/connections" -H "Content-Type: application/json"
--data "{""name"":""myconn"",""description"":"""",""type"":""DB"",""service"":""essbaseadw"",""walletPath"":""/system/wallets/myconn"",""user"":""ADMIN"",""password"":""password""}"

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.