I finished it recently too, and thought the hallucinating dream was really cool. Plus I was shocked when the mansion blew up too.
Desmond was an interesting character and I kinda liked how you never really found out what he wanted from Calvert in terms of information. Adds more to the mystery of the place.
It will remind you a lot of Dementia from the Oblivion expansion Shivering Isles, if you played that.
Overall, it was the best DLC of the lot.