I use it because it does allow access to the 'in camera' settings and correction for lens distortion. I've added ColorEfex as a plugin (best when used sparingly) and really like the control point technology for finer control over the image.
Things I don't like:
It's (as noted elsewhere) a resource, e.g., memory pig. I have 8GB of ram on a quad core processor and a 78 MB TIFF can result in an out of memory error. Say what?
It doesn't get updated often.
When it crashes it crashes ugly, i.e., you've just spent 10 minutes getting all your CPs in just the right places, click save...and it crashes.
In my workflow I identify photos I like with Bridge (don't have lightroom...maybe someday) and post edit in CNX2. I find the UI in CX2 leaves a lot to be desired for window resizing, closing, etc., Bridge is just easier.
If I've got some brackets to play with (HDR on occasion for highlight recovery) I apply the changes in CNX2 for distortion, CA, etc, then export as 16 bit TIFF to PS4 to the HDR Efex Pro (Nik software again). (I wonder if HDREP would read the changes made to the NEFs by CNX2? Hmmm...must try that).
I think you'll like it, just be sure to save "early and often".