It worked pretty well in SM64 and Sunshine to some, but in SMG not so much.
That's because it wasn't done right in
either game. The castle in 64 was great because it encouraged a lot of exploration and had a lot to unlock and view. And that hub world inspired a lot of stuff in future Mario games, and even influenced hub worlds in other franchises. Sunshine was even better in this regard...Delfino Plaza was practically a level in its own right, and a lively one at that (if somewhat calmer than the other levels, but I wouldn't want to be dodging dangers in a hub world anyway). There was always something to do or to look for if you wanted to, but if you didn't want to, the regular levels were never
too far away (with the possible exception of Pianta Village, which was in quite an annoying location).
In SMG1, however, some of this stuff was present in the Comet Observatory, but not in the same degrees as the previous two games. There wasn't as much to do or see or interact with, and it just seemed to take longer to get everywhere (partly because of the structure of the Comet Observatory, and also because Mario lacked the Slide Attack, which in the previous two games was a faster means of locomotion than dashing, particularly in Sunshine, when it could be spammed endlessly.) The scenery in the Comet Observatory was better than Peach's Castle in 64 but worse than Delfino Plaza in Sunshine. I also just didn't find the Lumas as interesting to talk to as the Piantas...they were often funny, but they didn't do as much.
In short, the only reason that the hub world setup didn't "fit" SMG as well as it did the other two was because they did a half-assed job with it. They could have done a much better job in SMG2...for instance, the Starship Mario could have been traveling back and forth between "hub galaxies", from which various levels could be accessed. In fact, this might have worked well in SMG1, too; the Comet Observatory could have still had all the secret galaxies on it, but the main galaxies could have been situated on central galaxies which the Comet Observatory could travel between.
Bottom line, I think they could have been a lot more creative for how the intra-level travel was, in both games, and especially in the new one.