Desirable hope. High ideal. Inane or impossible ambition. An over-rated desire. Using ‘pipe’ might suggest that someone has been smoking something to give them unrealistic hopes. (Compare to Stars in one’s eyes or Chasing my dream.)

“His pipe dream is to travel around the world, yet he can’t seem to get off the couch from playing video games.”