NUMSys Web Edition + Essbase Security Viewer 28


First post of 2016 and I didn’t want to disappoint you.

You might be thinking “What took you so long?” I can definitely answer that question.

Try to fight migraines which comes back after every 5 ~10 days, working on a tight schedule, learning a new framework and finishing NUMSys (yes learning a new framework was part of NUMSys Web Edition). I’ve really spent a lot of time into this pet project of mine and I’m really proud of the results.

I take this opportunity to introduce to you the latest release of NUMSys with Essbase Reports. Even though the blog post calls it as a Security Viewer – it is more than that and you’ll know why.

I did receive lot of feedback on the first version, however I’ve not added all those to this version. The reason why I could not added them was because I wanted to keep up with my promise of “World needs a security Viewer” 🙂

I’m keeping track of all the requests and will be incorporating them to forth coming releases/

From this version onwards you’ll see another reporting component added to NUMSys – “Essbase Reports”. Thank you Kishore Mukkamala, for planting the idea of Essbase Stats Reports.

It’ll pull the existing Essbase applications registered with Shared Services.

Essbase Reports

  • License and Server Report
  • Application Report
  • Database Report
  • Dimension Report

License and Server Report

This will extract license information and some server statistics like security file fragmentation, Essbase CFG errors and some CFG settings.

Application Reports

This extracts the information about your applications (the ones which you can view in EAS and more)
Some information include log file size, count of databases

Database Reports

This is similar to the one above, provides you statistics, caches and compression information of databases.

Dimension Reports

Information on all dimensions, how many members are stored, udas, dimension type,….

Keep in mind that this will not provide a dimension extract.

Filter Report

You can view the existing filters and their assignment by selecting the applications.

Filter definition can be populated by selecting the filter.

You can export the filters as MaxL statement along with their assignments.

Access Control Reports

Access control report is similar to the provisioning reporting option in Shared Services, except that this one shows you the filter assignments and the count of users/groups (direct/indirect) provisioned against that application. You also get the option to filter user(s)/group(s)

Log Analyzer

Now comes the most fun component. I’ve written a post on “Monitor the last login time of a user”, which I think is one of the popular posts (lots of comments) till date. I had to spend lot of time to figure out an easier way to perform this. (well, it is not easy as it seems)

I ended up making the decision of adding a database to NUMSys and loading the log files to that DB. Now came the question which one should I use, I first chose Derby as I’ll be able to ship that along with the software, however went against it as the querying language is slightly different than what we all know.

I had to replicate what EPM configuration tool does :), while installing NUMSys it’ll configure the required databases. You’ve to add the required JDBC jar files in the lib location of the installer.

ojdbc6.jar file if using Oracle
sqljdbc.jar file if using MS SQL server

  1. SERVERLOG
  2. SERVERODLLOG
  3. APPLICATIONLOG
  4. APPLICATIONODLLOG
  5. LASTLOGIN

You can drag and drop the log files into NUMSys (or use the Browse Logs button) to upload the log files.

ODL logs will go the *ODL* tables (I still don’t know why we’ve legacy and ODL logs.)

You’ve the option to select multiple log files.

You can also choose to keep the old logs, if not NUMSys will truncate the tables while uploading the files.

I didn’t test this on MS SQL server

Once the logs are uploaded, you can query the logs from NUMSys, yes you don’t need to start SQL server management studio or TOAD or SQL Developer 🙂

I had hard time figuring out how to name the columns, like what does Local mean, so I turned to the experts (Cameron LackpourTim German and John Booth – thank you for your thoughts).

Cameron was of the opinion that all log files are local to Essbase, so what is it!!!

Anyways this is what I came up with, so if you’ve suggestions let me know I’ll try to update the columns names in coming versions.

You can export the query results if needed using the “Export Results” icon.

Once you upload the log files, you can run the inbuilt reports (only 1 now)

Last Login report will provide you with a list of users, their last login time and from where they accessed it.

In coming releases I’ll add a command line version where you can schedule the log upload and run the log analyze reports.

So now you know what took me so long 🙂

Adding the database now allows me to do more fun stuff, like enable auditing for User/Group operation in NUMSys.

Next planned release will have an option to get Reports from Workspace……

You can find NUMSys documentation here.

You can download NUMSys Web Edition (3.2) from here.


About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

28 thoughts on “NUMSys Web Edition + Essbase Security Viewer

  • Pritz

    It would have been nice if there was a way to track Workspace logins as well as user auditing of workspace objects. I'm looking for a way to figure out what web analysis reports are really used and what are not accessed at all.

  • SDTM

    Sorry, i have another one…and i think it would be usefull for others too…im having issues in setting the Database information…im pretty sure im entering the right details but does the application generates any log files? all i get is a "SQL Exception, check the database propertiers" ? So not sure whats it doesnt like…

  • SDTM

    Many thanks for your quick reply. Yes i have put the ojdbc.jar in lib an uncommented the proper section. Then i have spent 30 mins on figuring out X11 thru putty but thats a different story…anyway i managed to get the installation work but cannot pass database properties, everything is as it should be but keep throwing that message, so i was trying to find any piece of log to pin point the exact issue

  • SDTM

    Thanks, i've downloaded but stuck at the same step. The log says:
    Aug 17, 2016 5:31:24 AM com.cera.hyputils.NUMSys.NUMSysWebInstaller$14 actionPerformed
    SEVERE: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver

    I'll try to debug this sql stuff, i'll keep you posted

  • SDTM

    # — If you are using NUMSys with MS SQL database place the JDBC jar files under lib folder and uncomment the below section
    CLASSPATH="./installer.jar:lib/commons-codec-1.9.jar:lib/commons-codec-1.9.jar:lib/commons-io-2.4.jar:lib/ojdbc6.jar:lib/zip4j_1.3.2.jar:lib/sqljdbc.jar":$CLASSPATH
    export CLASSPATH

    In lib folder there are 4 jar: zip4j_1.3.2 , commons-io-2.4 , commons-codec-1.9 , ojdbc6

  • SDTM

    I have put the sqlijdbc.jar and uncommented the classpath line and now another one..

    Aug 18, 2016 7:26:20 AM com.cera.hyputils.NUMSys.NUMSysWebInstaller$14 actionPerformed
    SEVERE: java.sql.SQLException: No suitable driver found for jdbc:microsoft:

  • SDTM

    OK..something is missing as even with that is not working
    Aug 18, 2016 2:52:07 PM com.cera.hyputils.NUMSys.NUMSysWebInstaller$14 actionPerformed
    SEVERE: java.sql.SQLException: No suitable driver found for jdbc:microsoft:sqlserver://[server]:[port];DatabaseName=[]

    I have copied sqljdbc4.jar into lib and also updated the installTool.sh to

    CLASSPATH=./installer.jar:lib/commons-codec-1.9.jar:lib/commons-codec-1.9.jar:lib/commons-io-2.4.jar:lib/ojdbc6.jar:lib/zip4j_1.3.2.jar:lib/sqljdbc.jar:lib/sqljdbc4-2.0.jar:$CLASSPATH
    export CLASSPATH

  • Celvin Kattookaran

    You don't have sqljdbc4.jar in the classpath, what you've is sqljdbc.jar:lib/sqljdbc4-2.0.jar. I guess you need some help getting this setup. I can do this on weekend. you can email me and we can jump on web session and I can guide you.

  • SDTM

    Wow, you are a brilliant guy offering your support! However i dont want to waste your time nor to transform this into a help forum so I'll try to sort it out myself. I'll keep you posted with the outcome. Many thanks once again!

  • SDTM

    HI Celvin, I gave one my try today but without any luck. I've downloaded sqljdbc4.jar and placed it under lib and updated CLASSPATH as per below

    # — If you are using NUMSys with MS SQL database place the JDBC jar files under lib folder and uncomment the below section
    CLASSPATH=./installer.jar:lib/commons-codec-1.9.jar:lib/commons-codec-1.9.jar:lib/commons-io-2.4.jar:lib/ojdbc6.jar:lib/zip4j_1.3.2.jar:lib/sqljdbc.jar:lib/sqljdbc4.jar:$CLASSPATH
    export CLASSPATH

    Not sure if i do something wrong but at this point I have no other ideea 🙁

  • SDTM

    It's the same error
    Aug 25, 2016 11:02:11 AM com.cera.hyputils.NUMSys.NUMSysWebInstaller$14 actionPerformed
    SEVERE: java.sql.SQLException: No suitable driver found for jdbc:microsoft:sqlserver://xxxx:yyy;DatabaseName=xxxxx
    Aug 25, 2016 11:18:36 AM com.cera.hyputils.NUMSys.NUMSysWebInstaller$14 actionPerformed
    SEVERE: java.sql.SQLException: No suitable driver found for jdbc:microsoft:sqlserver://xxxxxx:yyyy;DatabaseName=xxxxx
    Aug 25, 2016 11:31:30 AM com.cera.hyputils.NUMSys.NUMSysWebInstaller windowClosing
    INFO: Exiting the Installer
    Aug 25, 2016 11:31:31 AM com.cera.hyputils.NUMSys.NUMSysWebInstaller windowClosing
    INFO: Exiting the Installer

    • SDTM

      Hi Celvin, I had another try with your latest version but seems not to want to work…Let me recap maybe you can figure it out whats wrong.
      1. Downloaded and copied to lib folder: sqljdbc.jar
      2. Updated ./installTool.sh to the correct JAVA_HOME path and uncommented line 55 & 56
      3. Updated line 55 with the correct classpath as follows: LASSPATH=./installer.jar:lib/commons-codec-1.9.jar:lib/commons-codec-1.9.jar:lib/commons-io-2.4.jar:lib/ojdbc6.jar:lib/zip4j_1.3.2.jar:lib/sqljdbc.jar:lib/sqljdbc4.jar:$CLASSPATH
      4. Executed the .sh, fill in all the fields needed
      5. Error received as follows:
      Oct 10, 2016 6:28:58 AM com.cera.hyputils.NUMSys.NUMSysWebInstaller$14 actionPerformed
      SEVERE: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver