For many, the best attractions are those that really include the guests as part of the story, rather than just passively watching. For me, one of the best for this is the canoes.
While paddling my way, it's like I'm a pioneer exploring the frontier. Another of my favorites at this is Star Tours. I'm not just watching an adventure on a screen, but I'm "actually" on the spaceship trying to escape with the rebel spy. I may even BE that rebel. Haunted Mansion is another home run when it comes to this. I feel like I am really exploring a haunted house. I'm a part of the story.
On the other hand, Finding Nemo subs fails at this since I really only sit and watch the action happening to someone else. I'd say the same about Splash Mountain. As great a flume ride as it is, it is still really about Bre'r Fox trying to catch Bre'r Rabbit, and not me.
So, what do you think are the most successful at pulling you into the story? Like the canoes, it doesn't necessarily have to be the most technically advanced effects that do this. If you disagree with any examples, (nicely) explain why. Polite disagreement can make for some good discussions.