[19] Need to Monitor Clarity Page loads (Performance) ?

There is often a need to know how long it takes to load the page. At least my testing team needs it... to benchmark the performance time to time. So far windows time or wrist watch or Cell phone's stopwatch came to rescue..

I have been using a Firefox Add-on to give me the page load times. It's "Extended Statusbar" that I use to know page load times. You can download (in Firefox) or search for "Extended Statusbar" in Firefox > Tools Add-ons > Get Add-ons > Search box.

Only thing this Add-on will not show accurate time for the portlets which uses AJAX based "Progress-bars" while loading the data.

[18] Troubleshooting Ldap

Though I don't need to troubleshoot LDAP things quite often, but when I need I use LDAP Browser tool for testing/troubleshooting..

Steps : -1. Download Ldap Browser tool from here.
2. Unzip the files and run lbe.bat to open LDAP browser.
3. Click New to create a session. Name it say LDAPTest.
4. In 'Connection' Tab give the hostname (without ldap: //) say comadc.company.com , give the port say 389, give BaseDN say dc=company,dc=com , Uncheck Anonymous Bind option (This will enabled User info fields below), Give the User DN as username (Exchange user id) Or like cn=Niku Ldap Bind User,ou=Users,ou=niku,dc=company,dc=com , give password in password field.
5. Leave the values in Options tab as it is.
6. Click save and 'Edit Session' window will close.
7. Select the LDAPTest Session in list and Click Connect.
8. If everything is correct then you should see the list of all groups & users in your LDAP directory, if not you need to check configuration again.

After Connection is successful you can fill in relevant enteries in NSA.

In LDAP Server Section give enteries as ( Sample enteries )
URL = ldap : //comadc.company.com:389 Root Context = dc=company,dc=com
Search User = cn=Niku Ldap Bind User,ou=Users,ou=niku,dc=company,dc=com
Password = password

* Note that LDAP implementation & security level varies. Configuring NSA for correct settings is not sometimes easy. If it doesn't work for you, then may be userid being used doesn't have proper security access to read LDAP directory. Be friendly with LDAP/AD administrator... Request him/her to give 'good' read privileges to any test user...temporarily. Try LDAP Browser with test userid and see if it works. If it works then LDAP Administrator can further reduce the privileges till it doesn't break LDAP Browser or Clarity LDAP sync job.

[17] Customizing Email Templates

I used to receive "Scheduler job failed" email messages. Unless I open email and see which job failed, I wasn't sure if that was important/critical...

After changing Email Template, I could receive mail with job name in Subject line of Email. (though there could be other templates which can be customized for different reasons)

Steps :
1. Open ..../niku/clarity/clarity/resource/emailmessages_en.properties
2. Search for email.SCHEDULER_JOB_FAILED=
3. Replace that line with following
email.SCHEDULER_JOB_FAILED=Scheduler job failed - {0} on {3};\n\

4. Refresh Cache or restart App

* Few things to remember : This is just to throw idea how it can be done, different versions may have different template text & parameters so just be careful for that. And importantly and as always, this change may get washed away after Upgrade/SP/FP so need to revisit. This particular example for "en" language.

[16] Which users using Clarity the most ?

It has been Clarity Team owner's/Manager's wish to serve Customers/Users in best possible way. And it's always helpful to get feedback/suggestions from Users who uses Clarity the most. Clarity access logs are helpful but they give IP address not the user's id.

Clarity provides a field LOGIN_COUNT in CMN_SEC_USERS table, which I thought could be of use, but I couldn't find any configurable option to have login count stored in the table. So just thought of having a simple trigger to do this job.

Steps :
1. Open Insert Trigger of CMN_SESSIONS Table
2. Add Following piece. ( This code is for Oracle, for MS SQL it's similar thing to do )

UPDATE CMN_SEC_USERS
SET LOGIN_COUNT = NVL(Login_count,0) + 1
WHERE id = :NEW.USER_ID;

* Revisit this trigger after every SP/FP or Upgrade if this piece needs to added again.

[15] "OBS is Required" validation check

Somebody asked me how to make Project OBS fields mandatory... I didn't want to introduce any Trigger based solution as it may obstruct Clarity's own Create/Save Project process. So came up with a Java Script workaround that will display following message if Project OBS is not entered. Download code

* Adding custom Javascript thing was one of the most interesting, challenging research I did on Clarity. Adding "OBS is Required" message was another Fun thing. Intercepting Save/Submit button event and then dynamically adding Error message bar was quite interesting research. Specially dynamically adding custom function in Save/Submit button drove me crazy... but at last it was done. I did this Javascript thing somewhere early in 2006 and I was using it for other "naughty" purpose (still using though) but this OBS Message is just recent thing. I tested on 7.5.x, 8.x and 12.x and it works. Attached Javascript is just a foundation, but it can be extended to do 'anything'. I experimented with AJAX calls, data validation checks, Dependent lookups blah blah ... using this Javascript workaround. But word of caution here, improper handling/coding of Javascript may cause Javascript error messages on the screen and may interfere with Clarity's own Javascript functions. Another thing, you may need to Redo this XSL change after ServicePack/upgrade.

[14] Tracking Database schema changes (Oracle Only)

I needed to track Niku Schema changes and to have audit in place to track what got changed, when and by whom... I researched and got one method to track DB Schema changes on Production database, that I would like to share with you .. ( Remember this is all about Schema changes like SP, Triggers, Functions etc and not the data changes in Tables )

This method is about creating a 'Niku' Schema level trigger and that trigger inserting the change details in a Table. You can download code from here. If you have Niku user's password then it will do the job. I recommend, trying it on Dev/Test first and exclude events/records that you don't want to track in first IF condition of Trigger. It will give you Object name, date when changed/created, Terminal, OS User and other few details. It also stores the DDL of the object before it was changed ( in BeforeDDL field ) so you can track the changes for any object ( SP/Trigger/Function etc ). BeforeDDL field will show up the contents when you doubleclick it .. (in Toad)

Sometimes you may need to compare the contents of say a SP. I prefer using CompFold utility for content comparison that you can download from here . Save contents to file and then compare using CompFold ( or any other comparison tool of your choice )

Note : Disable this Trigger while doing upgrade or applying Service Pack (FP).

[13] Clarity SQL Trace Tip

Clarity SQL Trace thing I often do and mostly to know which Query is performing badly and eating up most chunk of Execution time... For short SQL Trace files it's easy to locate but for some when file runs over 400 kb or so then Grrrrr..... Keep scrolling and scrolling..


Here is tip that can you use to avoid those 'scrolls' ... I use Textpad ( though you can use others ones too that support Regular Expression search ) for searching the SQL's that takes longer time. In Textpad check "Regular Expressions" and search for Time: [0-9][0-9][0-9][0-9] This will highlight the ones which takes over 1 sec, if you want to locate the ones which takes over 10 seconds then add another [0-9] in above expression.

[12] Quick DB Sync between Clarity environments (Oracle Only)

Many a times, I need to Take Production copy and replicate to some other environment to make it Production like. My pain that Export to be done strictly in off peak hours and it's Long process, takes hours and the Exp dmp file is too big to transfer and then import also eating up lots of disk space. Reason : Slices and Datamart tables are quite huge ones and makes this thing crazy.

In my case it was OK not to have Slices & Datamart tables Synch'd. So I thought if we can Exp & Imp all tables except Slices & datamart ones. I researched a bit and came up with a approach that would give me just what I wanted. In my case, Export was done in 20 minutes. File size was 2.2 GB ( when zipped it was 240 MB ). It was easy to transfer 240 mb file to different server and next 38 minutes to import into target database. CMN_CAPTIONS_NLS was largest table with 3.3 million records.

I have documented the complete process that I did. You can download from here. I am not database expert so not sure if there is any better way of doing it. May be if someone has better approach or suggesitons, please leave comment for this Post.

* I see another benefit with this approach. Apart from the backups that DBA has scheduled and goes into tape, I can schedule Exp job on another server which can keeps DMP of last 15 days. Typically backup of all those 15 days will not take more than 50 GB of space. And when needed you can get any of those 15 versions in very quick time ( as compared to Tape to be brought from different physical location, restored on separate server which typically takes 2 days)

[11] ASP.NET Calling Clarity's Query Web Service

Here is an Example of ASP.NET calling Clarity's Query Web Service. This is useful in scenarios like .NET app wants to get Resource OBS by supplying ResourceID or say want to get Team member listing by passing in Projet ID...

Lets see first how it works...

1. Clarity creates a Web Service when a query is created. (Interesting.. Isn't it ?)
2. Any other system (.NET, Java, others ) can access this query and get data from Clarity. (if it knows Clarity login/pwd)
3. External System can supply Query Filter Criteria and get "Filtered" data from that query.

I am taking a simple example where .NET app will pass "user name" to Clarity and will in turn receive "Resource Manager". Here are the steps :

Step 1 : Create NSQL Query (QueryID = "res_manager") with two columns User Name and Resource Manager.
Step 2 : Create .NET Application, Add Web Reference to http://localhost/niku/wsdl/Query/res_manager Set Web Reference name say Q
Step 3 : Create a Webform and add Textbox to it named say TextBox1
Step 4 : Add following code in Page/Form load or under a button

------------------------------------------------------------------------
Dim QS As New Q.res_managerQueryService
Dim Q As New Q.res_managerQuery
Dim Auth As New Q.Auth

Dim Log As New Q.Login
Dim ClaritySessionID As String

Log.Username = "admin"
Log.Password = "clarity"

ClaritySessionID = QS.Login(Log)
Auth.SessionID = ClaritySessionID

Dim F As New Q.res_managerFilter
Dim QueryResult As New Q.res_managerQueryResult
Dim Rec As New Q.res_managerRecord

QS.AuthValue = Auth
Q.Code = "res_manager"
F.user_name = "jsmith"
Q.Filter = F
QueryResult = QS.Query(Q)
TextBox1.Text = QueryResult.Records(0).resource_manager_id

QS.Logout(ClaritySessionID)

--------------------------------------------------------------------------

* This example above would return only one Record as desired. For instances where multiple records are expected, you would need to loop though QueryResult.Records and get field elements.
** "MS Visual Web Developer 2008 Express Edition" is free to download..