This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(160, 66%, 25%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ET%3C/text%3E%3C/svg%3E)
Hello,
What is the specific impact to having excel open during an SSAS model refresh. *SSAS 2017
1) An Excel file with 30 tabs each with a connection to the model
2) 30 excel files with 1 tab each with a connection to the model
We have set the time out setting down to 10 seconds but we are debating on it being executed in parallel or linearly
The issue we are investigating/trying to resolve is exceedingly poor performance during model refresh at high use times. 20-30 users would have multiple excel files open with varying number of tabs. Multiple connections per excel file.
One user reported they are unable to use data for 30 minutes each hour during our month end. We process hourly and changing refresh times is not an option. Hourly refreshes are a requirement to support our month end processing.
Hi @Troy , we have not get a reply from you. Did any answers could help you? If there have any answers helped you, please do "Accept Answer". If not, please let us know. By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!
No Sorry, it did not really answer my question.
We are strictly Tabular model processing, and so it comes down to how the open connections are handled.
Hi @Troy ,
Welcome to Microsoft Q&A!
Which model are you going to process, multidimensional model or tabular model? If you are going to process a multidimensional model, you can use Batch Processing (Analysis Services). Batch processing gives you a way to control which objects are to be processed, and in what order. Running the batch processing job as a transaction makes sure that all objects included in the transaction remain available for queries until all processing is completed.
Best regards,
Carrin
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hi @Troy ,
Welcome to Microsoft Q&A!
Sorry for the late reply.
Based on my research, it seems that you could keep excel open during an SSAS refresh. Every changes can be viewed after the refreshing, and also you need to reconnect with Excel to veiw the refreshed data. Please refer to Analyze Service Manager OLAP cube data with Excel to get more information.
When it is opened, a data cube is presented as a worksheet containing a blank PivotTable report. Information defining the OLAP data source is embedded in a worksheet. When you open a report or when you refresh the data connection, Excel uses Microsoft SQL Server Analysis Services (SSAS) to connect to the data warehouse to retrieve key performance indicators (KPIs) and other data. After it is opened, the current worksheet contains a snapshot or subset of data from the data warehouse. If you save a worksheet, the data source connection information, KPIs, and any other customizations you have made are saved with it. If you save the worksheet to an analysis library, you can later reopen it without having to use the Service Manager console.
Best regards,
Carrin
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.