Changing a data source in PowerPivot


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.

Advertisements

Published by

wattersbill72

Dad, Husband, Brother, Son , Soccer Player/Fan, SharePoint Person, All Around Geek

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s