Why I created a blog

Its been many years since I first created this blog. It has remained true to Essbase and related information over those years. Hopefully it has answered questions and given you insight over those years. I will continue to provide my observations and comments on the ever changing world of EPM. Don't be surprised if the scope of the blog changes and brings in other Hyperion topics.

Thursday, February 16, 2023

New options in Essbase 21c LcmExport command

 The other day I was showing a client the LcmExport utility and noticed there are a few new options in it.  (I saw it in 21.4, I'm not sure if it is there in 21.3. 

I looked at the embedded help on the options and noticed there was nothing there to tell me what these new options did. I reached out to my friends at Oracle Development and they enlightened me on what the options do.

First, what does the new command look like? I've highlighted in RED what the new options are

EssbaseLCM.bat export -server essbasehost:port -user username -password password -application appname -zipfile zipfilename [-nodata] [-include-server-level] [-converttoutf8] [-forceutf8] [-generateartifactlist] [-exportepmroles] [-allApp] [-exportdata] [-cube] [-filetype] [-partitions] [-filters]

-AllApp - This option exports all of the applications on a server to a single zip file. I didn't know it, but the LCMImport can handle multiple applications at once. To use this option, you have to have access to all of the applications on the server. Note, this is used instead of -application  in the command. 

 -cube - This allows you to just export a single database within an application. It can come in handy to just migrate one DB

-exportdata - This option only exports the data from the cube (I gues this is the zame as a level-0 export. 

-filetype - Allows you to export specific file types:

  •  OTL (outline) 
  • TXT (text)
  •  RUL (rule)
  •  CSC (calc script)
  •  DTR (drill through report definition),
  •  Excel (only .xls files are exported. No .xlsx files are exported)

Note, apparently you can only do one file type at a time and the last one wins.  I had done 

-filetype otl -filetype rul 

and only the rules files were exported. 

-exportpartitions - will just export the partition definition

-filters - only exports the filter definitions .

 I can see where these new options could be handy during a migration. Initially I could export everything and make changes and test and as we get close to production, remigrate certain artifacts like the updated outline from production.  



Thursday, January 26, 2023

A semi-ending and semi-beginning

 It has been over 30 years that I have been in the Essbase space and have had a wonderful time learning and helping others. For 14 of those years I have been with interRel that became a part of Argano. As of Friday January 27, 2023, I will be retiring from Argano (well semi-retiring). I say semi-retiring because although I am leaving Argano, I won't be completely retiring. I will be easing into retirement by consulting part time. To do that I have created a company "GSchwartzberg Consulting LLC". I'm not going to work full time, but take on projects for others. I'm already going to do some minor projects for Argano, but will entertain other offers. I'm not sure how long this will last, I do know if my presentations are accepted, I'll be attending KScope this summer probably for the last time.  I expect to blog less and enjoy life more.

Enjoy building your cubes

Monday, December 19, 2022

Essbase EssCLI Session Expired Please log in

 This will be a short post about a problem I encountered and the resolution. 

A client had just been upgraded to Essbase 21.4 from 21.3 on OCI and the automated backup script we created using EssCLI started failing with a message "Session Expired Please log in". I don't know if it matters but their instance uses IAM instead of IDCS.

Trying to just login with EssCLI would login and then give the same message (failing). 

 We tried a number of things and got Oracle support involved. Nothing any of us did would work. We could log into the Essbase instance Web UI with the ID with no issue, but through ESSCLI we couldn't. 

Most of the time we were cutting and pasting the ID and password into the command. Finally one time I manually typed it and it logged me in. Another person tried and it failed. Then I noticed the difference. the id they were using was essprodadmin   and I had typed in EssProdAdmin which is how it was set up in IAM.

Somehow in 21.4 EssCLI the case of the ID makes a difference.  

Hopefully if you encounter this issue, this will help you solve it quicker than I did. 

setting variables with quotes in them

 Dear reader, I writing this post because every time I need to programmatically set a variable with quotes around it (Like a date or a member name with a space)  I have to look it up to remember how to do it. So this post is more for me to have a place to look it up. You get the benefit of me posting this to remind me. 

A couple of things to know. 

  1. The code you see was tested on Essbase 21c, but should work on other version
  2.  For this to work, the variable must already exist.
  3. This sample is for a system variable, the code is not much different for a application of database level variable , you just need to change the alter System statement to an Alter Application or Alter Database statement and put in the application and/or the application.database name.
  4. This sample is passing today's date to the variable specifically in the format "mm/dd/yyyy" including the quotes
  5. The key to this is the statement set CurDate="'\"%mm%/%dd%/%yyyy%\"'"   I kow it is hard to read, so I'll make the right hand side easy  Double quote single quote backslash double quote the date in the format I want %mm%/%dd%/%yyyy% backslash double quote single quote double quote.  The beginning and ending double quotes are to enclose the variable when it is passed to MaxL. They will get stripped off. The single quotes are to enclose the variable within MaxL. the backslash is an escape character to make sure the double quote following it is passed to within the statement.  In MaxL it looks like '"12/19/2022"'  (Note that is single quote double quote at the beginning and double quote single quote at the end) 
  6. This batch file was stolen for something bigger and has a little extra stuff in it 
  7. I did not put error checking in the bat file Call StartMaxl or in the MaxL file itself

So here is the simple batch file 



REM =============================================================================
REM    Batch Name: Test.bat
REM    Created By: Glenn Schwartzberg, Argano
REM    Creation Date: 19-Dec-2022

REM    Purpose:
REM               update substitution variable
REM =============================================================================

REM =============================================================================
REM =============================================================================


CALL :GetDate
echo %CurDate%

REM Unless the path to the MaxL is in the system environment variables change to the MAxL directory

cd C:\Oracle\19cMaxL

REM you might need to use essmsh instead Also set the path to where the MaxL file exists

startmaxl.bat    C:\clientfiles\XXXXX\Automation\Set_today.mxl %CurDate%

goto :eof

set yyyy=

set $tok=1-3
for /f "tokens=1 delims=.:/-, " %%u in ('date /t') do set $d1=%%u
if "%$d1:~0,1%" GTR "9" set $tok=2-4
for /f "tokens=%$tok% delims=.:/-, " %%u in ('date /t') do (
for /f "skip=1 tokens=2-4 delims=/-,()." %%x in ('echo.^|date') do (
set %%x=%%u
set %%y=%%v
set %%z=%%w
set $d1=
set $tok=))

if "%yyyy%"=="" set yyyy=%yy%
if /I %yyyy% LSS 100 set /A yyyy=2000 + 1%yyyy% - 100
set CurDate="'\"%mm%/%dd%/%yyyy%\"'"

goto :eof 

The MaxL file 


Spool on to C:/XXXXX/Automation/set_Date.log;

Alter System Set variable TodayDate $1;

Tuesday, July 26, 2022

Using Datasoures based rules in MaxL

 This will be a short post about how to use Rules files (either Dimension build or Data load) from within MaxL. 

If we look at the documentation for the import command; it has information on file based and SQL based load, but what if we want to load from a connection and Datasource?  It is not there. I thought it might be the same as a connection but did not know hat to put into the userID or password fields. I tried leavin them blank and putting in dummy information, but neither worked. 

Finally, I reached out and got the answer. I was correct that you treat it like a SQL connection, but what you put into the UserID and Password IS important. For those inputs you need to use the ID and password of an Essbase user that has a minimum of Database Manager access to the cube you are loading

So your import statement might look something like:

import database 'Sample'.'Basic' Dimensions connect as 'ESSBASE USERID' identified by  'PASSWORD'  using server rules_file 'test_DBX.rul' on error write to 'c:/temp/Dim_DBX.err';


The output of the MaxL shows it worked  (names have been changed to protect the innocent)

 OK/INFO - 1053012 - Object [Basic] is locked by user [xxxxxxxxxxx].
 OK/INFO - 1021117 - REST API is enabled to extract data from the Oracle database.
 OK/INFO - 1021006 - SELECT Statement [SELECT  * FROM SAMPLE.DS_DBX] is generated.
 OK/INFO - 1350007 - Creating connection pool of size [1] for connection string [REST;URL=LOCAL;DS=Sample.DS_DBX;UID=....;PWD=....].
 OK/INFO - 1350032 - No message for message #1350032 in message database.
 OK/INFO - 1021002 - SQL Connection is Freed.
 OK/INFO - 1007132 - Building Dimensions Elapsed Time : [2.5] seconds.
 OK/INFO - 1019024 - Reading Outline Transaction For Database [Drxxxxxx].
 OK/INFO - 1007043 - Declared Dimension Sizes = [19 18 23 25 5 3 5 3 15 8 6 ].
 OK/INFO - 1007042 - Actual Dimension Sizes = [19 15 20 25 4 0 0 0 0 0 5 ].
 OK/INFO - 1007192 - Dimension sizes consist of stored and dynamic calc members = [19 15 20 25 4 3 5 3 15 8 5 ].
 OK/INFO - 1025030 - The IMPLIED_SHARE setting in essbase.cfg is OFF.
 OK/INFO - 1025035 - The implied share setting for the database is forced to OFF.
 OK/INFO - 1010007 - Maximum number of stored and dynamically calculated blocks is [500] with data block size of [288].
 OK/INFO - 1007047 - Restructuring of Database [Basic] is not needed.
 OK/INFO - 1007118 - Loading New Outline for Database [Basic].
 OK/INFO - 1019013 - Writing Outline For Database [Basic].
 OK/INFO - 1007120 - Loading New Outline for Database [Basic] Succeeded.
 OK/INFO - 1007067 - Total Restructure Elapsed Time : [1.55] seconds.
 OK/INFO - 1053013 - Object [Basic] unlocked by user [xxxxxxxxxxx].
 OK/INFO - 1241113 - Database import completed ['Sample'.'Basic'].

There you have it quick and easy, now the hard part, to get the documentation updated.


Monday, April 11, 2022

Is EAS all that lite in 21c?

 I have been getting a lot of questions about Essbase Administration Services (EAS) lite in 21c and what it can and can't do. This blog will attempt to shed some light  (as opposed to Lite) on the capabilities and restrictions. 

Before I go into the detail, I think it prudent to mention that EAS is really meant to be an interim solution during your migration to 21c. Most things can be done through the Web UI and Cube Designer.

You also have to tun on the Application to be managed by EAS in the Web Console. Remember if you turn this off, you can't turn it back on  (You could trick the system by doing an LCM Export or Cube Designer Export,, deleting the application and reloading it)


Really the only thing it is better at is outline editing when you want to copy parts of a hierarchy from one cube to another. You also lose some 21c functionality using EAS Lite. 

So what can't you do? 

  1. Outline editing in the Web UI. The outline will be view only
  2. You can't use Scenario management (sandboxes ) as it is a new feature
  3. You can't use Connections and  Datasources  
  4. Sorry, No Drill-through reports
  5. Prior to 21.2 you could not view logs or having multiple Essbase instances in a single EAS instance. 21.2 or later, you can. 
  6. Server level configuration settings are disabled
  7. No Provider services connections are available
  8. There is no security. you can't externalize users nor can you list users or groups 
  9. There is no migration wizard
  10. Nor is there an ASO conversion wizard
  11. Preview Data is not available. Use the Web UI Analyze data instead 
  12. There is no View Log Charts. Instead, download the performance Analyzer data from the console in the Web interface and download the  Health and Performance Analyzer from Files->Gallery to get more information about the system.
  13. In Filters you can't set up intelligent filters using Datasources
  14. Also in Filters, you can't assign access to users or groups.



That's about it. Let me know if you have any questions