I was thinking about getting it to add some flair and "coolness" to my composites rather than trying to accurately simulate fire, steam or whatever else. Would this software be over kill for that sort of use? How do you all use this software?
Not sure what you mean by overkill - do you mean expense wise? pIllusion is totally worth the money. It paid for itself on my first gig.
I use the program for my Motion Graphics work, 2D animation, 3D animation, and just about anywhere else I can. pIllusion saves me so much time, I try to do as much as possible with it.
If you want to see some examples of my pIllusion work, you can see a lot of it in my reel at http://www.allbetsareoff.com/ in the reel section of my website.
Also, I just released an Animation Training DVD for using particleIllusion with After Effects. You can check out the site for this Creative Cow Master Series DVD at http://www.pillusionfusion.com/. It will show you how to get the most out of compositing your particles in AE.
Also, there are a bunch of tutorials at the wondertouch site which can give you an idea of what it's good for, and how you might use it when compositing or with 3D: http://www.wondertouch.com/tutorials.asp