As you can guess I'm a huge heavy rain fan and was wondering if anyone had any suggestions for other great story oriented games?
The Walking Dead, To the Moon, Katawa Shoujo.
That pretty much is the best answer. I'll also add 999 and its sequel. Haven't played them but everyone says they are great and they fit your description.
EDIT: They are kind of annoying old adventure games, but The Longest Journey and Dreamfall both have really good stories without much of a focus on gameplay.
I'd second Indigo Prophecy (made by same people, and was pretty good IMO). To add: Dreamfall was pretty good, but felt more like an interactive movie (dunno if that's a dealbreaker for you). Otherwise, the Phoenix Wright games also do a lot of story basing, as does The Walking Dead (I haven't played it yet, but it's on the list).
I'm a bit sad that MTV's Club Dead hasn't been mentioned once in here. That shit deserved all the Oscars. Best foreign film? MTV's Club Dead. Best animated short? Club Dead. Best sandwich? Club Dead.
I think a lot of great games have been named, but I'll throw in Full Throttle, the Gabriel Knight series, and Dear Esther. If you're an Agatha Christie and Sherlock Holmes fan, I recommend checking out And Then There Were None, Orient Express, and some of the more highly rated Sherlock Holmes games. The gameplay leaves a lot to be desired in those games, so be cautious about a purchase, but they're at least worht a look.