Excel Connecting to SSAS Tab Cube, Can I Switch to Another Cube, and Not Effect the Existing Report

Question

Excel Connecting to SSAS Tab Cube, Can I Switch to Another Cube, and Not Effect the Existing Report

quinn.jay

SSCertifiable

Points: 5505
More actions
January 17, 2017 at 2:18 pm

#317181

--Excel Connecting to SSAS Tab Cube, Can I Switch to Another Cube, and Not Effect the Existing Report
I have an Excel report, and it is connected to a SSAS Tabular Cube. I have backed up, and restored that cube, and have it available but under a slightly different name. I want to disconnect Excel from the one cube, and connect to the other identical cube with slightly different name.
When I look at it, the "From Other Sources" is greyed out, and I can only see the Properties and Connections. No clean way to simply switch the source from one cube to the other and not loose the existing report contents.
Can this be done, or do I have to start new and connect to the new target, and recreate the report all over again? I'll trying to find a way for people to go to a static cube while the main prod cube goes down for retooling and historical back-loading.
Thanks,
JPQ

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Martin Schoombee SSCoach Points: 19128 More actions · Answer 1

quinn.jay - Tuesday, January 17, 2017 2:18 PM
--Excel Connecting to SSAS Tab Cube, Can I Switch to Another Cube, and Not Effect the Existing Report
I have an Excel report, and it is connected to a SSAS Tabular Cube. I have backed up, and restored that cube, and have it available but under a slightly different name. I want to disconnect Excel from the one cube, and connect to the other identical cube with slightly different name.
When I look at it, the "From Other Sources" is greyed out, and I can only see the Properties and Connections. No clean way to simply switch the source from one cube to the other and not loose the existing report contents.
Can this be done, or do I have to start new and connect to the new target, and recreate the report all over again? I'll trying to find a way for people to go to a static cube while the main prod cube goes down for retooling and historical back-loading.
Thanks,
JPQ

Create a new connection in the Excel doc, pointing to your new Tabular cube. The you could change your pivot table properties in Excel to point to the new connection.

quinn.jay SSCertifiable Points: 5505 More actions · Answer 2

Martin Schoombee - Tuesday, January 17, 2017 2:33 PM
quinn.jay - Tuesday, January 17, 2017 2:18 PM
--Excel Connecting to SSAS Tab Cube, Can I Switch to Another Cube, and Not Effect the Existing Report
I have an Excel report, and it is connected to a SSAS Tabular Cube. I have backed up, and restored that cube, and have it available but under a slightly different name. I want to disconnect Excel from the one cube, and connect to the other identical cube with slightly different name.
When I look at it, the "From Other Sources" is greyed out, and I can only see the Properties and Connections. No clean way to simply switch the source from one cube to the other and not loose the existing report contents.
Can this be done, or do I have to start new and connect to the new target, and recreate the report all over again? I'll trying to find a way for people to go to a static cube while the main prod cube goes down for retooling and historical back-loading.
Thanks,
JPQ
Create a new connection in the Excel doc, pointing to your new Tabular cube. The you could change your pivot table properties in Excel to point to the new connection.

But doesn't that lead to having to recreate the pivot as its a new data source? Can you please elaborate on the specifics on changing the pivot table props and re-pointing, as I think I would loose the existing report

Martin Schoombee SSCoach Points: 19128 More actions · Answer 3

quinn.jay - Tuesday, January 17, 2017 3:15 PM
Martin Schoombee - Tuesday, January 17, 2017 2:33 PM
quinn.jay - Tuesday, January 17, 2017 2:18 PM
--Excel Connecting to SSAS Tab Cube, Can I Switch to Another Cube, and Not Effect the Existing Report
I have an Excel report, and it is connected to a SSAS Tabular Cube. I have backed up, and restored that cube, and have it available but under a slightly different name. I want to disconnect Excel from the one cube, and connect to the other identical cube with slightly different name.
When I look at it, the "From Other Sources" is greyed out, and I can only see the Properties and Connections. No clean way to simply switch the source from one cube to the other and not loose the existing report contents.
Can this be done, or do I have to start new and connect to the new target, and recreate the report all over again? I'll trying to find a way for people to go to a static cube while the main prod cube goes down for retooling and historical back-loading.
Thanks,
JPQ
Create a new connection in the Excel doc, pointing to your new Tabular cube. The you could change your pivot table properties in Excel to point to the new connection.
But doesn't that lead to having to recreate the pivot as its a new data source? Can you please elaborate on the specifics on changing the pivot table props and re-pointing, as I think I would loose the existing report

In Excel (while you have a cell within the pivot table selected), select "Analyze" in the PivotTable Tools section. and then click on "Change Data Source". As long as the attributes you have displayed in the pivot table are named exactly the same in the new connection, it should not be necessary to recreate the pivot.