For me, it's perfect. I've got all my music and video library within iTunes and I use it to stream to the AppleTV. I've made backups of all my DVDs for security purposes and they're all saved in a format that I can play on the Apple TV. iTunes itself controls what's physically loaded onto the unit but it doesn't really matter because the rest streams transparently from my Mac. The family know how to wake it up if it's sleeping.
It's possible to hack it so that you can plug a large hard drive into it and store media on there but I'm quite happy with it streaming from iTunes.
The downside is that it only supports up to 720p, not 1080p and it's been around for a while now so there are always rumours that it's going to be either replaced or discontinued.
As far as I'm concerned there are only really three things it would need if it's upgraded: 1080p, a larger hard drive and a gigabit network port. The hard drive would have to be massive though, at least a 1Tb because if I can't store all my media on it then the internal drive is a waste of time. The reason for wanting a gigabit network port is simple; if it has a huge drive, then I need to be able to dump media onto it quickly!