NUMSys Web Edition + Essbase Security Viewer - Oracle

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

SERVERLOG
SERVERODLLOG
APPLICATIONLOG
APPLICATIONODLLOG
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 Lackpour, Tim 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.

28 thoughts on “NUMSys Web Edition + Essbase Security Viewer”

Share this:

Like this:

Related

Leave a Reply Cancel reply

28 thoughts on “NUMSys Web Edition + Essbase Security Viewer”