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 

 

@ECHO OFF

REM =============================================================================
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 =============================================================================
REM    GLOBAL VARIABLE SETTING
REM =============================================================================
%~d0

set SCRIPTNAME=%~n0


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

:GetDate
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 

Login 'USERID' 'PASSWORD' on S`ERVER;


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



Alter System Set variable TodayDate $1;
Exit

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.

 

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