Loss of Refresh link to Data Model

[jaustin]

I've been using Power Pivot/Data Model for over a year in which I've built and directly updated a workbook table on a monthly basis from an outside source. I then have been doing a refresh from within the Data Model from the updated workbook table (all in the same workbook). I've built many PTs from the Data Model data set and they have been updating and working successfully. When I attempted my latest update the "refresh" option within Power Pivot data model was greyed out. All PTs and graphics from the current/non-updated version of the Data Model are working. Is it possible to re-establish the data model link to my data table source without having to rebuild my PTs from scratch? No error messages.

Thanks in advance

jaustin

 

[mrshl9898]

If the PT is connected to the whole model then refreshing the table should refresh the model. Is this an option? That or Refresh all in Excel if that would be suitable.

Otherwise if you go into Powerpivot>Home>Existing Connections>(select the connection)>Open>Test Connection. What happens then?

 

[jaustin]

If I go into the workbook table ("Combined") that I'm using as a source for the Power Pivot data model (also named "Combined") and do a Refresh All, I get an error that says "Can't open Pivot Table source file '[JV3_Excel_Base.xlsb]Regression_All_Status_Cat". I hadn't tried this before. But, since I never knowlingly ever linked the table Combined to that table, it appears that somehow, in the past that connection was made in error or corruption has occurred. Also, interestingly, I had deleted that sheet/table recently as I didn't need it. Didn't know that there was a connection and didn't have any errors or notices when I did the delete.

For your second option, if I go into PowerPivot and select Existing Connections, for the three type connections it states "No connections found". It appears that my "Combined" data model has lost all connections. As I noted in my original post, the "Refresh" option in Power Pivot/Manage Data Model is greyed out. In the past I've created at least 5 PTs from the data model "Combined". They are all still working (from independent caches?) but don't have the updates that are present in my workbook table "Combined".

So, I will add the "missing" sheet/table back into the workbook from a backup and see what happens. Also, it there an ability to see the various links/connections from the data model in a backup that was functioning properly? I've been doing a monthly update to the worksheet "Combined" and then successfully refreshing the data model "Combined" from that table. I've done quite a bit of work on the overall program and having to rebuild from scratch will be a major undertaking.

Thanks for your response and suggestions. Will proceed with the sheet/table replacement and document here.

jaustin

 

[mrshl9898]

Not sure about viewing a backup that was functioning... Depends if you or if at work your IT dept does backups periodically.

Hopefully by replicating the data source the functionality will return. Possibly if not, go into Powerpivot and existing connections and try and relink it.

 

[jaustin]

I'm an individual without an IT department, but I do 3-5 backups everyday so I have full access hundreds of backups (fortunately each one pretty well documented). Unfortunately, since everything seemed to be working, I was not testing the data model refresh function for several weeks. Sometime in that timeframe I deleted the now missing sheet/table. I will look for a backup that has the sheet/table that the refresh error is looking for.

Prior to the current data model issue, I had an issue that the data model would not even load. I had to back track through my backups to find a version where the data model would load. Could not see or determine any actions that might have triggered that corruption. My solution for that issue was to rebuild my program/model from that point. The data model is loading in my latest build but the data model refresh is greyed out. Hopefully, there was not a corruption created during the re-build.

Thanks again for your comment and I will post any progress.