Collaborate 19

I wanted to let everyone know that my colleague Jason Jones will be speaking at Collaborate19 in San Antonio on April 7th and 10th. He is giving two great presentations on a couple of really current topics.

The first is Up & Running with the OAC Essbase REST API. This session will help you get started automating your Essbase cloud environment.

Abstract: Oracle's Essbase Cloud offering introduces a new and modern REST API in addition to its traditional Java API. The new REST API opens up many development, integration, and automation capabilities and differs greatly from the traditional API. This session will introduce the REST API, exploring how it's similar to and different from traditional APIs. We’ll also cover some real-world examples and how to get started with development.
Time: 3:00 PM–4:00 PM Apr 7, 2019
Location: CC 2ND FL 214C

The second is Docker & Essbase - Bring the Cloud to You. This session will teach you about using Docker to create an Essbase server. I love using Essbase in Docker. Gone are the days of lengthy and complicated Essbase installations or running slow virtual machines. I use Essbase in Docker everyday and it's improved my productivity tremendously.

Abstract: Docker is a modern technology for rapidly deploying and containerizing servers both on-premise and on the cloud. This presentation will introduce the Docker technology in a practical way, exploring interesting Essbase/EPM use cases in which Docker can be used to open up and enhance infrastructure, development, and testing in EPM organizations of any size.

Time: 4:30 PM–5:30 PM Apr 10, 2019

Location: CC 2ND FL 217D

Dodeca Essbase Outline Viewer

Dodeca allows you to easily give users the ability to view Essbase outlines. Watch this video for a quick demo then check out Jason Jones' blog about how to set it up.

OAC Next Generation Outline Extractor

The Oracle Analytics Cloud (OAC) Next Generation Outline Extractor is now available for download from the Applied OLAP website. I've also added a few tips for using it.

Background

• For those unaware, Applied OLAP supports a free utility for extracting Essbase outlines. The utility is simple to install and use. I've used it many times over the years. One client of mine used it to extract dimensions so they could be compared to the source relational tables during UAT saving many hours of work.

Version

• On your OAC interface page, click on your name in the top right corner. Then select About. Download the version of the Outline Extractor that corresponds to your cloud version. In this case the version is 12.2.1.1.114.

Source

• Using the Essbase API will result in many calls to the server and could be very slow. Instead, on the Input Source choose "Extract and Process MaxL Outline XML".

Connection

• When making a connection, use the URL that you use in your browser to connect to the OAC interface. After the 'essbase' part of the URL place '/japi' just like you see below. Then enter your Username and Password.

As always, let us know if you have any issues at Support@AppliedOlap.com.

Cascade Reporting in Dodeca

Cascade reports are multiple reports that show a standard template from different points of view. This post will show how to create a Dodeca Cascade Report using an MDX script to choose the intersections.

I started with a template that shows scenarios and a single roll-up account: EBITDA. This account will be automatically zoomed-in to create our final report. Using a roll-up will ensure that any account changes are reflected in our report without needing future maintenance.

I also added tokens for Periods, Location, Departments and Years. I added an Excel formula using simple subtraction to find the previous year.

I added two named ranges. One for the Essbase retrieve and another for the cell on which the zoom-in will be performed.

I hid the header rows and saved the template.

Next I created an Essbase MDX Script called PaloMartCascade.

I used the Crossjoin() function to create my column headers of Periods and Scenarios.

I also used the Crossjoin() function to pull all level zero members under the user selected Department and Location.

Note the use of the NON EMPTY keyword here. Using this will ensure that no sheet will be generated for any missing or irrelevant intersections.

Next I set my view properties, specifically for CascadeSources.

In the Cascade Sources dialog I changed the Source Type to EssbaseMdxScript. Note that this feature is only available in version 7.6.1 and beyond.

I then selected the Script I created in the previous step.

I also set the tokens that will be used to generate the Cascade Report. In this case, the MDX report will return Departments in the first column and Locations in the second column. The order that the tokens are defined should match the order in which they are retrieved in the MDX report.

Finally, I added a bit of Workbook Scripting.

First I added a property called SheetCount and set the Value to 0. This will track how many sheets get built during the Cascade.

Next I added an EventLink that will be triggered by the AfterCascadeSheetBuild. This will be fired after each individual sheet is built.

The first method in this procedure is AddProperty. This method uses the @PVal() Dodeca function to get the current SheetCount property and increment it.

The next method in the OnAfterCascadeSheetBuild Procedure is EssbaseZoomIn. This is the method that will drill in on the EBITDA account on our template. Note how I reference the zoom.cell in the SelectedRange. If we move the cell at some point we won't have to update this Workbook Script.

The next procedure I added was OnAfterBuild. This will be used to show a message box to the user. I use the @PVAL() Dodeca function to get the final SheetCount property.

Then I used the AddProperty method to set the SheetCount back to zero. This will ensure that the property does not keep adding to previous cascades if the view is built again.

There you have it. Creating Cascade Reports in fast, flexible and powerful in Dodeca. From here you could add a Workbook Script to save the sheets to individual files or send them in emails.