Using the profiler I found, that Excel sends an EXEC sys.sp_prepare to return the meta data and compiles the execution plan.
When I executed the prepare statement in SSMS it hangs too, the same when I tried to display the estimated execution plan for the
SELECT on the view itself. I let it run for a while and after about 7 minutes it was done compiling the query and showing me the estimated plan (sp_WhoIsActive showed me only about 30 sek CMEMTHREAD plus some minor wait stats for my query).
After this "initial" compiling both - SSMS and Excel - were able to load the data / show execution plans / preparing the statment without any delay.
When I executed
SELECT STATS_DATE(s.object_id, s.stats_id) AS last_updated, s.*
FROM sys.stats AS s
WHERE s.object_id = OBJECT_ID('dwh.big_table')
ORDER BY last_updated DESC
I found, that there are 4 auto generated statistics that were updated today, the last one exactly at the moment when the execution plan was finally generated. All updated statistics were on columns that were used in the query / view.
Obviously we had a problem with manually updating the statics (e.g. using Ola Hallengreens Maintenance Solution) after loading the latest data to the DWH and the Auto Update Statistics feature kicked in, when the query was first executed today. And while the statistics were updating, it could not compile an execution plan (e.g. since it does not know how many rows we have for the last x days and how much memory it needs to reserve).
Excel itself seems to have an internal timeout (found no way to configure it) of about 30 seconds regarding the sp_prepare statement and shows the very vague error message (Exception of type Microsoft.Office.PowerPivot.BackEnd.StorageException) when it does not get back the metadata for the query in this time.
I moved the statistics update to a new (later) time frame, to ensure that the data was loaded first (before updating the statistics), so that there is no need for the Auto Update Statistics feature to kick in and block the table for several minutes...