I was reading about it earlier and this surely in paper sounds like a great technology from Microsoft, lets hope developers can take advantage of that.
But it would be boring if you have to choose or change graphic options like in PC to use this or not. I would expect it to work automatically, even in SP games, if you have internet give me the better experience and graphics, and if you don't have internet well, the game is supposed not to look so much different anyway.
in the end, this feature really sounds amazing, but its all up to developers to use it, how they use it etc etc.