I used it once and it wasn't that impressive (but I was also not in a great state then too....high and sleep deprived).
Supposedly it isn't any safer than cigarette smoke even though it passes through water.
Here is a report on it (also with an explanation of what a Hookah is):
http://www.mayoclinic.com/health/hookah/AN01265
Personally, I have nothing against it or for it. It is more social thing since you pass the piece around but it isn't anything special. I don't really plan on using a Hookah every again but if I do once or twice I do, whatever.
But I also don't smoke cigarettes or cigars so keep that in mind but I think it is popular because the tobacco is usually flavored and you can add other plants to it as well.