If you're looking for something that's sweet and brings back memory of childhood birthday cakes, I'd suggest either Lyndell's bakery in Somerville (Ball Square) or the Shaw's/Star Market in Central Square or Porter Square. I've found Rosie's cakes to be a bit disappointing, with the frosting (esp. the white frosting) a bit too greasy and not sweet enough. If you want something totally non-traditional (and expensive), there's always Finale in Harvard Square.
Finale will look nice, but be flavorless in my experience there.
Quebrada in Arlington or Wellesley makes outstanding cakes, and the special order at Lakota in Arlington Heights can be pretty great as well.
If sugary and huge but really fresh is what you seek, go for the $15 sheet cake at Costco - it is surprisingly good.