Issues with Linked Tables (Power Pivot)

[xelaa]

Dear all,

I am using Win7 32bit, Excel 2013.

I have to work with excel-based data and need to create calculations that go beyond the limitations of excel which made me work with SAS in the past. With the new PowerPivot functionalities I am actually able to get my work done completely in Excel but started to face some issues on which I couldn’t find a solution yet. But up front I will explain what I am doing and what doesn’t work.

I have a calculation model that uses about 4 input tables, one of them is the primary table with the highest level of detail and the other tables are just additional ones that can be related to the primary. I basically just need to calculate a big amount of new variables based on the existing tables and then in the end I need to create an aggregated overview which works fine with pivot table functionalities.

My initial approach was creating for each table a separate workbook with just one worksheet in it for that specific table. Use then a new workbook where I have been using the POWER PIVOT functionalities to connect to external data (which are the mentioned workbooks) and build up the relationships between the tables. In the next step I create all the new variables with DAX functions and so on. And in the final step I create a pivot table which is aggregating the results.

Model Illustration can be seen here: View image: model pp

With having the tables linked as external data things work fine and I can for instance update the primary input file and will get the changes in my output pivot table.

Based on the output I actually will need to implement some VBA code which will need to adjust and update the primary input table. This is supposed to be an iterative run until a specific output variable value is reached. Therefore I planned to set everything up in just one excel sheet and use linked tables instead of external data connections.

I created in one workbook different worksheets for the tables, defined the relationships and made all PowerPivot calculations with having the primary table as a base. Then I created a Pivot table in a new worksheet to aggregate the results and doing it for the first time it worked fine.

Now, when I add different values in the primary table to create basically a new case to be calculated, I get serious problems. Copying some flat data in the primary table works fine, the formats are all the same by the way. Unfortunately the changes are not being taken over in the Data Model and of course the output Pivot table is not changed. Right click on the output Pivot and refreshing will lead to the following error message:
“We couldn’t get data from the Data Model. Here’s the error message we got:
A circular dependency was detected: xxx several varialbes are listed xxx”

At first I realized that I am not even using in the output these variables and just deleted them. Then it will just list in the same error message other variables.

I then went to the POWERPIVOT menu and clicked on “Update All” in the Tables section and got the following error:
“Errors in Linked Tables
There were errors trying to update one or more linked tables. Use Options below to see ways to fix these problems or OK to continue without fixing”

Options just gives me the choice to “Do Nothing”, “Remove Link to Excel Table” and “Delete Power Pivot Table”. Not very helpful.

Can anybody help me with these issues? I don’t get why linked tables is such an issue.
Any hint or just opinion could be helpful.
Many thanks.

 

[scottsen]

In general, if you are seeing the "problems" dialog... you are in a pretty sad state, with a corrupt file, and you generally grab a back up or start over

 

[xelaa]

Seriously?
I have been reading yesterday evening through some MSDN forums without further hints. Actually the whole problems and errors topics seems to be not that well thought through with BI or Power Pivot.
I guess my state is even worse as I already built up a new model from scratch and get in the end the same issues. :-/

Why does it work fine with external data connections but fails with linked tables within a workbook??? :-/

 

[xelaa]

I found an interesting article about the possible reason for my issue which relates to the way how CALCULATE works, I have plenty of calculate statements but also mainly in use with ALLEXCEPT. The article is here:
Understanding Circular Dependencies in Tabular and PowerPivot – SQLBI

I tried to set up a row identifier but then I get to read in the following link at the first Note that it only works for SQL Server and not Excel:
https://msdn.microsoft.com/en-us/library/hh560542(v=sql.110).aspx#bkmk_setprop

This actually limits the functionality of Power Pivot in Excel a lot. Still searching for a solution...

 

[marco.russo]

I found an interesting article about the possible reason for my issue which relates to the way how CALCULATE works, I have plenty of calculate statements but also mainly in use with ALLEXCEPT. The article is here:
Understanding Circular Dependencies in Tabular and PowerPivot – SQLBI

I tried to set up a row identifier but then I get to read in the following link at the first Note that it only works for SQL Server and not Excel:
https://msdn.microsoft.com/en-us/library/hh560542(v=sql.110).aspx#bkmk_setprop

This actually limits the functionality of Power Pivot in Excel a lot. Still searching for a solution...

The row identifier remove the problem of circular reference also in Power Pivot. The note only says that it does not have its effects on a Pivot Table result, but that's unrelated to your issue.

 

[xelaa]

Hi Marco,

Thanks for your double reply.
In my data model the variable 'Postal_Code' is set as the row identifier. For 'Keep Unique Rows' I tried two variations, one was to just select Postal_Code, another was to select all variables. In both cases I get after refreshing the resulting pivot table:
"We couldnt get data from the Data Model. Here's the error message we got: The 'AttributeRelationship' with 'AttributeID' = 'Postal_Code' doesnt exist in the collection."

No luck on investigating this issue :-/

 

[marco.russo]

Hi Marco,

Thanks for your double reply.
In my data model the variable 'Postal_Code' is set as the row identifier. For 'Keep Unique Rows' I tried two variations, one was to just select Postal_Code, another was to select all variables. In both cases I get after refreshing the resulting pivot table:
"We couldnt get data from the Data Model. Here's the error message we got: The 'AttributeRelationship' with 'AttributeID' = 'Postal_Code' doesnt exist in the collection."

No luck on investigating this issue :-/

You don't need to set "Keep Unique Rows" to solve the circular reference issue. Just set the "Row Identifier" and that's it. Of course, it has to be a column that uniquely identify every row of the table.

 

[xelaa]

Good morning.

Thanks again Marco, I believe the issue is solved. Once everything is related and all variables calculated it appears to be too late to set the identifier as I got that recent error message. What I did this morning was to set up the tables in a new workbook. Once I linked a table to the data model I set right away an identifier, did that for all 4 tables. Once each table had the identifier I set the relationships between the tables. Then I created all the calculated variables and finally made the resulting pivot table. Updating now the primary table and refreshing the resulting pivot tables leads to the updated results without any error message. So the main problem that I had was that the identifier apparently had to be set in an early stage of the creation of the model.

I will further test it but what I initially saw appeared quite promising!

Cheers.

 

[scottsen]

Thanks for jumping in Marco, since I clearly didn't read this message carefully and thought they were hitting the model corruption dialog...