Pointing PowerPivot pivot tables to tabular models
After you import or restore from PowerPivot, you are left with a workbook with pivot tables and slicers to point to the PowerPivot model. The next logical step in the import/restore process is to re-point these objects to your new tabular model. How do you do that?
Attempt 1: Change the PowerPivot data connection to point to the new tabular model. Problem: this connection is not editable. Try again.
Attempt 2: Edit the data source for each pivot table to point to the new tabular model. Problem: if the pivot table is connected to slicers, the data source cannot be changed. You will need to disconnect the slicers first. Try again.
Attempt 3: Right click your slicer, try to disconnect it from the pivot table so that you can point the pivot table to your new data source. Problem: if you added the slicer by dropping a field into the PowerPivot field list, the slicer disconnection does not stick. The slicer is under PowerPivot’s control, and PowerPivot will always ensure that the slicer is connected to the pivot table. Try again.
Attempt 4: Remove all slicers from PowerPivot field list. Disconnect all regular Excel slicers. Change the data source for every single pivot table to point to the new data source. Now add back or reconnect your slicers. Success.
That was painful. Fortunately there is a way to avoid this tedious manual labour through automation. Although the Excel UI doesn’t allow you to change the PowerPivot connection, the Office APIs have no such restriction.
The guts of a program that changes the connection string are pretty simple (if not robust):
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook ppWorkbook = (Microsoft.Office.Interop.Excel.Workbook)xlApp.Workbooks.Open(fileNameBox.Text);
Microsoft.Office.Interop.Excel.Connections connections = ppWorkbook.Connections;
string NewConnectionString = "OLEDB;Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=" + dbNameBox.Text + ";Data Source=" + serverNameBox.Text + ";MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error";
for (int i = 1; i <= connections.Count; i++)
{
Microsoft.Office.Interop.Excel.WorkbookConnection conn = connections.Item(i);
if (conn.Name == "PowerPivot Data")
{
conn.Name = serverNameBox.Text + " " + dbNameBox.Text;
conn.Description = "";
conn.OLEDBConnection.Connection = NewConnectionString;
}
}
ppWorkbook.RefreshAll();
ppWorkbook.Save();
xlApp.Quit();
Basically, this example uses the Office APIs to change the name and connection string of the data source from the special PowerPivot connection string to the Analysis Services data source. Once the data source is refreshed, all pivot tables and slicers point to the new data source. The comments in the code sample explain in more detail what each part does.
We change both the name and the connection string for the PowerPivot data connection on purpose. If you don’t change the name, terrible fates will befall you. PowerPivot will get confused, your workbook will appear corrupted, PowerPivot ribbon items may not show up, and generally you are in for a world of hurt. Changing both the name and the connection string tricks PowerPivot into thinking that your workbook is not PowerPivot workbook and you can proceed as you have a regular Excel workbook. In fact, you can now open this new workbook on a machine without PowerPivot installed, and everything will work normally.
Now, this approach has some problems. Sometimes when you force refresh, the engine crashes. This error is mostly harmless and can be ignored. The only problem with the engine crashing is that if the error happens frequently enough, the PowerPivot add-in may become disabled. You can always re-enable the PowerPivot add-in by following Deva's instructions in this thread.
Also, the embedded PowerPivot data remains in your workbook after upgrade. To remove the embedded PowerPivot data:
- Open your upgraded workbook
- Open the PowerPivot window
- Close the PowerPivot window
- Save your workbook
Clearly, this ain’t a production grade tool. Use it at your own risk. However, it’s handy to have around for those times after import/restore.