It can be spectacular or subtle. Consider hiring a pro to install it and be aware that some glues they use are very, very difficult to remove years later. When using prepasted wallpaper, the wet wallpaper may be too heavy to stick easily. Wet wallpaper cascading down onto your head is unforgettable.

Proper preparation is everything - don't skip suggested steps or skimp on using products especially on e ceiling. It will cost you 10X more to eventually take it down if you skip steps now.
If you are an experienced DIY and very patient then great otherwise hire a pro. And make sure you have a clear design plan for the entire room prior to starting - you can't easily paint the crown after the ceiling is papered.