It can be fun if you do it with the right people but it loses its novelty over time. I used to own a hookah and people would never finish the session. Sometimes they just want a couple puffs but they don't realize the effort/costs per session. It can be cooler if you make smoke bubbles.

http://www.youtube.com/watch?v=Gj9o3YyqX54