none
PowerPivot removed table replicated into the data model on refresh

    Question

  • Hey

    I am facing the following problem which is absolutely persistent and annoying in nature. I am using a data model created with PowerPivot with connections to three different SQL servers and 25 tables. The bug is that every time I refresh my data, a table that was initially added to the data model (and later removed) is replicated in equivalent number of tables that use this connection, which is currently around 12 tables, so I get 12 duplicates of the same unnecessary table on every refresh, named with the name of the schema on the SQL server that is followed by a running number. Removing these from the data model on every refresh simply takes too much time on a daily basis. The additional undesired table which is created is what is defined in the SQL query of the connection string of this connection.

    I tried to solve this problem by zipping the Excel file and modifying this SQL statement present in the connection string in the connections.xml file in a way that it would not return anything, but this always leads into a corrupted workbook.

    After encountering this I have managed to avoid this problem by making sure that the initial table I'm adding to the data model will remain in the model, but I really don't want to rebuild this current model. Is there any fix for this issue without rebuilding the data model?

    Thanks for all the help in advance!


    Thursday, February 27, 2014 11:26 AM

Answers

  • I tried deleting both "original" tables, but adding them back in brings back the replicating behavior. Adding connections to the table increase the amount of duplicates created (and still following the structure as defined in the command text of the connection).

    This model is fairly old and has been imported over from the old PowerPivot (Excel 2010), so maybe it is best for me to just rebuild the data model.


    Tuesday, March 11, 2014 11:30 AM

All replies

  • I tried to solve this problem by zipping the Excel file and modifying this SQL statement present in the connection string in the connections.xml file in a way that it would not return anything, but this always leads into a corrupted workbook.

    Can you post an example of how you have added a SQL statement in the Connection string (doesn't need to be the real SQL or connection details, I'm just wondering which property you have set)?

    Have you tried going into the PowerPivot model, clicking on "Get External Data", then "Existing Connections" and then editting the connection and removing the query from there?


    http://darren.gosbell.com - please mark correct answers

    Monday, March 03, 2014 6:40 AM
  • Hey

    I am facing the following problem which is absolutely persistent and annoying in nature. I am using a data model created with PowerPivot with connections to three different SQL servers and 25 tables. The bug is that every time I refresh my data, a table that was initially added to the data model (and later removed) is replicated in equivalent number of tables that use this connection, which is currently around 12 tables, so I get 12 duplicates of the same unnecessary table on every refresh, named with the name of the schema on the SQL server that is followed by a running number. Removing these from the data model on every refresh simply takes too much time on a daily basis. The additional undesired table which is created is what is defined in the SQL query of the connection string of this connection.

    Hello,

    What's the version of your SQL Server PowerPivot add-in and Office Excel? I tried to reproduce this issue on my test environment but failed. If possible, please share your steps to repro this issue.

    Regards,


    Elvis Long
    TechNet Community Support

    Monday, March 03, 2014 9:24 AM
  • I am using 64-bit Office 2013 with the recent SP1 upgrade and the included PowerPivot plug-in on a Windows 8.1 machine.

    I always create the connection by using "From SQL server" then fill in the server and log in details and use a ready SQL statement that I have written by using SQL Server Management Studio to import the initial table. For all the preceding tables using the same server and schema, I always select it from existing connections and just paste the SQL queries required for any additional tables to the "Write a query that will specify the data to import", rather than using the table import tool.

    I am not sure if it could have any effect that I've been using the type connection "From SQL server" although the database is really running on Azure (there is a possibility to select From Other sources > Microsoft SQL Azure, but the functionality seems the same).

    I don't really understand what I should edit from the existing connections section. Just to clarify, apparently the Connection String parameters can be edited from PowerPivot, but not the Command Text which can be viewed from Excel Data > Connections > Properties, but is shown in gray with a message "Some properties cannot be changed because this connection was modified using the PowerPivot add-in."

    In the data model I am using two sets of identical tables, but connected to two different servers that are running a database 1:1 in structure. Initially the problem considered only the other of these, but I accidentally reproduced it by, again, editing the SQL query in the table that was initially imported when that connection was created (PowerPivot > Design > Table Properties). Now when refreshing I get tables that are structured as in what the "Connection Text" part of the connection would produce, which replicated for the equivalent amount of additional tables using the same connection, so now I'm getting 12 tables (the number of tables using the same connection) each named "*schemaname1* Query", "*schemaname1* Query2", "*schemaname1* Query3" "*schemaname2* Query" and so on.

    Personally I can definitely see a pattern here. If there isn't a table matching what has been specified in the "Command Text" that was described when the connection was created, then it for some reason runs this query anyway on every table that is using the connection.



    Monday, March 03, 2014 11:02 AM
  • Hey, I managed to fix this problem by simply switching SQL Server Native Client 11.0 into .NET Framework Data Provider for SQL Server.

    Previously I've simply used the native client provider for both Azure servers and local SQL servers as it hasn't caused any problems before, but apparently it can cause problems.


    Wednesday, March 05, 2014 12:18 PM
  • Actually changing the connection type didn't solve the problem, but rather hide it and causes an another error of the following type, preventing the connection from refreshing at all:

    "We couldn't refresh multiple tables, including '*table*' from connection '*connection*'. Here's the error message we got:

    Out of line object 'DataSource', referring to ID(s) '*id*', has been specified but has not been used.
    The following exception occurred while the managed IDbConnection interface was being used: Keyword not supported: 'provider'..

    The following exception occurred while the managed IDbConnection interface was being used: Keyword not supported: 'provider'..

    The following exception occurred while the managed IDbConnection interface was being used: Keyword not supported: 'provider'..

    The following exception occurred while the managed IDbConnection interface was being used: Keyword not supported: 'provider'..

    The following exception occurred while the managed IDbConnection interface was being used: Keyword not supported: 'provider'..

    The following exception occurred while the managed IDbConnection interface was being used: Keyword not supported: 'provider'.."

    I can still refresh the connection by switching back into OLE DB or Native Client and accepting the annoying replication of tables bug mentioned earlier.

    Having this data model working is essential for running our business and it is embarrassing for me to have persistent errors with it. Any solutions other than rebuilding the data model from scratch?

    Monday, March 10, 2014 9:08 AM
  • Are you able to reproduce this issue in another workbook or is it just confined to this one?

    We have a reasonably large PowerPivot user base at work with somewhere around 30-40 people creating models from SQL, Oracle and Teradata and I've never seen this behaviour.

    When these new tables get created I'm assuming that you go through and delete them out. Have you tried deleting one of the "original" tables (after moving any measures to the "new" table) and keeping the "new" version? It seems like somehow the link between the query and the tabular model is broken. 


    http://darren.gosbell.com - please mark correct answers

    Tuesday, March 11, 2014 12:35 AM
  • I tried deleting both "original" tables, but adding them back in brings back the replicating behavior. Adding connections to the table increase the amount of duplicates created (and still following the structure as defined in the command text of the connection).

    This model is fairly old and has been imported over from the old PowerPivot (Excel 2010), so maybe it is best for me to just rebuild the data model.


    Tuesday, March 11, 2014 11:30 AM