PowerPivot creates and consumes a SQL Server Analysis Services data-source to provide users information. This is built via the PowerPivot GUI as an Add-In for Excel. It works pretty well, but we recently had the opportunity to learn more about the inner workings of PowerPivot.
When one creates a PowerPivot Workbook, you can connect to a variety of Data Sources, and whne you are finished ‘building’ your PowerPivot workbook, it builds out an Analysis Services sytle databse in a ‘PowerPivot’ SQL Instance on the server. Nice. What happens when the source data needs to be moved to a new location, i.e. a SQL Instance is being replaced on a server or moved to a different server. When PowerPivot goes to update the Analysis Services Instance Database, it cannot connect to the original source, and there is no simple way to advertise the change of server location fo rhte source database. PowerPivot does however, allow us to do this, by opening the PowerPivot add-in and choosing the Design Tab. From there click on the Table Properties button.
From here you can point to a different Source Name or create a new SQL Query in order to bring the data from an original source location into the Analysis Services Instance :: ‘PowerPivot’…when you build out the data source, it will import all the data into the Analysis Services instance Database, and then you can set a refresh schedule for th analysis services data to refresh from the source data. This will improve preformance as the PowerPivot workbook only talks to the PowerPivot Analysis Services Database, and not all the way through the process to the backend source databse.