The specific cause of the nightmares stems from the waning power of a spell, which set the other cultists and her Daedric artifact – the Skull of Corruption – into a comatose stasis. The dungeon was once the center of her worship in Skyrim, and during the quest's plot, Erandur reveals himself to be a reformed, former follower of the Prince. Still, the particular Daedric Prince players will be going up against this time around is Vaermina, the god of dreams and nightmares (and subsequently, the cause of Dawnstar's recent slew of ceaseless night terrors).
Fortunately, it's not among the longest, but it'll still be a lengthier trek than the usual dungeon delving. The Waking Nightmare quest, aside from being necessary to opening the Pale's path to thanehood, is also one of many Daedric quests in the game.
