There is no demo as such that I am aware of.
Dimension LE is included with most versions of Sonar, and that should give you a basic feel for DimPro.
You can manipulate the sounds, so it would be difficult to really say what DimPro sounds like.
For instance, in my Khyber Pass9 song, I wanted a shaken tambourine.
The tambourine in DimPro was more of a hit sound, so I adjusted the ADSR envelope for a softer attack to get what I was after.
This was simple, but you can get much more complex.
The hammered dulcimer is unchanged, straight from DimPro.
The libraries that come with it are fairly large, plus Cakewalk has additional libraries.
I don't use it all the time, but it is a staple in my studio.