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.
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.
I get the following error:
./installTool.sh: line 39: syntax error near unexpected token `fi'
./installTool.sh: line 39: `fi'
WebAnalysis usage tracking is not captured anywhere. So that cannot be tracked.
I'll look into this.
Hi Celvin, this seems to have been fixed in the latest release 3.3 THANKS !
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…
did you copy the required jar files to the lib folder?
I was testing it with 3.3 and found that it is working, i'll look at 3.2 and fix it. thank you for the update
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
Currently the installer is not having a logging property. Let me try add that and I'll keep you posted
Much appreciated !
Try this version, I've added logging to the installer.
NUMSys 3.4
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
That one tells me that you don't have the right JDBC jar file in the classpath.
Can you paste the CLASSPATH line that you updated in the sh file.
# — 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
If you are using SQL server the jar file is different. ojdbc6.jar is for Oracle. Search for SQL jar files and download (I guess it is called as sqljdbc.jar) and update the classpath
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:
The error itself says that it is not the right JDBC driver. What you need is sqljdbc4.jar
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
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.
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!
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 🙁
Can you post the error message you are getting this time.
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
I've changed couple of things in the new installer and tested it with SQL server, download it from here NUMSys 3.5
You'll need sqljdbc4.jar file to install against SQL server
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
You need sqljdbc4.jar not sqljdbc.jar
Send me an email using the contact option and we can try to setup a session during weekend