Hmm, well, it's pretty much all of Metroid Prime in pinball form. You collect a few powerups and fight the cool bosses (one in particular's pinball version is INSANE). Oddly, most of the playing I've done on it so far has been single card multiplayer with friends. Like SM64DS, the multiplayer is single card no matter what and pretty simple. I'd say it's more fun than SM64DS's multiplayer, though. Supports up to 8 - the most I've done is 3.
The music is phenomenal, with some Metroid Prime tunes remixed so that they use the same instruments but are kind of more complicated and some Metroid Prime tunes that are just played in metal-style. They sound like
Metroid Metal but better.
What it really comes down to is if you like pinball. There are a few diversions like wall-jumping, laying bombs, and periodically standing up and blasting waves of enemies with your gun and missiles, but it's pretty straightforward pinball most of the time. If you really like Metroid Prime but don't like pinball, it's not worth it. If you're down with electronic pinball, then it's pretty flippin' sweet. Oh, I like the physics a lot, too. It feels really natural to me and I can aim shots accurately now. With a lot of pinball games, I can never get that way.
I give it an Eldee rating of slightly below Pokemon Pinball and significantly above The Pinball of the Dead.
P.S. You get the DS Rumble Pak also. I have no problem with the volume of the rumbling. I don't even notice it (admittedly, I usually use headphones). The rumbling on Metroid Prime Pinball is very nice and pinball feel-y.