The Embarcadero is one of my favorite areas in San Francisco. Close to the water, great area to walk or jog, with absolutely stunning views.
The Pros to staying in this area: Beautiful water views without the madness of Fisherman's Wharf, quiet at night, very calm, beautiful area.
Cons: Quiet at night, not as central to downtown as Union Square, not too many hotel choices.
The Vitale - Luxury boutique hotel. Great restaurant and bar and one of my favorite after work spots (I'm sure my stalkers are taking notes. ;) ) Lovely views and amazing suites. I was lucky enough to stay in a panoramic suite (see photo) one weekend and it was one of the best experiences I've ever had. It is small and intimate but with the luxuries of some of the more well known chains. Small, but nice spa. Simply put, you can't go wrong here. Cable car 2 blocks, historic street cars and buses stop next door.
The Hyatt Regency - Typical Hyatt hotel. California Street cable car stops right next to the hotel. If you've stayed at one Hyatt (or Hilton, or other similar chain) you know what this is all about.
The Harbor Court Hotel - Not as great a location as the first two mentioned but you can still get around by walking a few extra blocks.