Excel report with SSAS taking more time to refresh over Wan
We had seen scenarios where Excel Sheet with Pivot table takes less time locally but over Wan it takes more than hour to refresh.
The issue which we fixed was on Excel 2007 with SQL 2008 AS (Cube had Cell Level Security Enabled)
The resetting of the connection to Analysis Services within a report each time a cube formula receives an error from Analysis Services due to restricted security access to a cube member referenced on a report.
Resolution
On analyzing the SQL analysis service profiler traces we found that lot of time lag was there between when one query ends and next query is fired to SSAS when Excel cube is refreshed. During that time lot of network traffic was too observed
We changed the value of the MdxMissingMemberMode XMLA property in the connection string to ignore missing members in MDX queries which helped in tuning the refresh time of Excel power pivot report significantly over local network and also over WAN.
Extended Properties="MDXMissingMemberMode=Ignore"
Connection String will look something like this:
Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Adventure Works DW 2008;Data Source=servername;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Extended Properties="MDXMissingMemberMode=Ignore";
Don’t get surprise to see two MDXMissingMemberMode appearing in your connection string, by default whenever you will reopen the excel sheet it will add MDX Missing Member Mode=Error but it will be override with Ignore if Extended Properties is specified.
Why we use this property:
We changed the value of the MdxMissingMemberMode XMLA property in the connection string to ignore missing members in MDX queries.With Excel, changing this setting avoids the resetting of the connection to Analysis Services within a report each time a cube formula receives an error from Analysis Services due to restricted security access to a cube member referenced on a report.
Note: Excel sets this property to "Error" by default, which overrides this setting if set as a standard connection string setting. To override an Analysis Services connection property that is set by Excel, you must specify the setting as an extended property (Extended Properties="MDXMissingMemberMode=Ignore"). The extended properties are applied after the Excel properties have been set using the OLE DB APIs – so extended property settings override what Excel sets.
Additional References:
Supported XMLA Properties (XMLA)
Ashutosh Sharma
Support Engineer, SQL Team