I think it'll mostly be used for stuff like The Division or Destiny, where you've got this open world drop in/out MP style where you have multiple people playing the game and then joining with other players. With a system where each group has it's own server you then need to manage handing off control when a new server joins (or such), having everything run over Azure would simplify that a great deal (And it can natively scale to demand, if nobody is playing a game they wouldn't run any server instances)
They've spoken about handing MP matches "in the cloud", it seems like a great fit.
Doing MP on the cloud side is just the start. Basically with the XB1 all the MP in games now should be dedicated servers, no more Peer-to Peer hosting like with the 360. God I hate when the hosts connection dies in the middle of a match, now that won't be a problem. Other developers have said that they're going to offload AI to the cloud, that frees up the CPU to do other things. Also when more of the game world is online, on the server, then even more server side stuff can happen, they also said physics for example.
So how can this help SP? Well, with more and more games starting to have online elements/worlds even for SP (Destiny for example) then a developer could have new parts of the "world" be online, and other parts be local. Smaller levels can be local fully, where performance from the local hardware is fine for that smooth 60fps everyone likes to go on about. Then you can go into these larger open areas that could be online, on the cloud. Game is still SP though, from your point of view, it's just that now you switched to on online level that's maybe, 2-4x the scale with way more things going on in it without having any impact on your gaming experience because you don't notice any performance drop.
Sure when there is no internet connection then those parts of the "world" would be off limits, but I'd say overall, the idea behind this hybrid mixing of pure SP local levels + larger online yet still SP levels with added co-op/team drop-in/drop-out options would make for some very very good games.