none
Bizarre behaviour of sql query in Excel 2013 data model.

    Question

  • I know nothing about sql as have come to it as an excel user. I have created a powerpivot data model that is drawing some of its data from an sql query.  The sql query is for 196,000 rows which is what it pulls in if I put it on a clean tab with no calculated columns.  In my actual tab I have lots of calculated columns. When I refesh I see it saying 190,000 rows and then it sits for hours not doing anything until eventually I see 89,000,000 or 105,000,000 rows!!!!   At this point it is compeltely dead and I have to kill the process. Does anyone have any idea how it thinks it is pulling in so many rows that clearly don't exist?  Is it cycling round and round trying to pull in the 190,000 rows and its just adding up? It's not my database so can't be sure what is going on inside it.

    I should mention this used to work fine until recently. 

    Thansk for any advice

    Mike

    Tuesday, November 05, 2013 1:00 PM

Answers

  • This is really an Excel problem, not a SQL Server problem.  I would suggest you try the Excel forums.

    I have not done it in a long time.  However, I had to create a data file, and then a seperate Excel file which referenced the data file.  That made it much easier to deal with.  You don't want to put your calculations in the "data" file, just the raw data.

    Wednesday, November 06, 2013 8:36 PM
  • Unfortunately for Calculated columns at the moment PowerPivot does throw everything away and re-calculate the lot every time you change something. For a lot of models this is so fast that it's not too much of an issue, but for others it can be a real pain, hopefully this will be fixed in a future version.

    Tom - The PowerPivot engine inside Excel was actually developed by the SQL Analysis Services team which is why this forum is under the SQL Server area, so I believe this is the correct place to be asking questions about the PowerPivot engine and DAX calculations. You can't actually split out DAX calculated columns into a separate workbook as you suggested.


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

    Thursday, November 07, 2013 1:44 AM
    Answerer

All replies

  • So if the query works fine when you have no calculated columns then the issue is not with the query.

    It is possible to write calculated columns which force the engine to rescan the whole table (possibly multiple times) and this sounds like what may be happening in your case. I suspect that it's not actually reading the data from SQL multiple times, but more so that the data gets scanned multiple times after it's been read from SQL.

    You may have to remove all the calc columns and add them back one at a time (or in small groups) until you can isolation which one(s) are causing your issue. Then we would need to look at re-working or improving the performance of the problem columns.


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

    Wednesday, November 06, 2013 3:14 AM
    Answerer
  • Hi Darren. I've been deleting tables and columns until eventually it worked. I thought ah must the last thing I deleted that was causing it so tried just deleting that and it instead it crashed!  Is there any type of claculated column that can cause this behaviour so I could narrow down the options as have a of of them and takes 10 minutes to check each one?
    Wednesday, November 06, 2013 8:44 AM
  • It's most likely to be ones that use functions like FILTER, CALCULATETABLE or maybe even SUMX.

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

    Wednesday, November 06, 2013 8:59 AM
    Answerer
  • That covers 50% of my calculated columns!!!! So when you have 190,000 rows and do an update to 196,000 does it actually throw everyhing away and start from scratch?  I can undersntad that al lthe filter functions i have is a lot to handle. I guess whne you add a new column it only has to calculate that one, but seems when you update it tries to do them all together and blows up.  Seems like really poor code if it allows yopu to build it but not update it...probably poor code by me!!!

    I'm working thorugh deleting stuff. at them oment it just crashes witohut any warning which microsoft paid support are looking into.  Eventually I'll get to the stage where it doesn't crash but loads 100mio rows and then hopefully narrow down which column allows it to complete.  I'll be back!

    mike

    Wednesday, November 06, 2013 6:12 PM
  • This is really an Excel problem, not a SQL Server problem.  I would suggest you try the Excel forums.

    I have not done it in a long time.  However, I had to create a data file, and then a seperate Excel file which referenced the data file.  That made it much easier to deal with.  You don't want to put your calculations in the "data" file, just the raw data.

    Wednesday, November 06, 2013 8:36 PM
  • Unfortunately for Calculated columns at the moment PowerPivot does throw everything away and re-calculate the lot every time you change something. For a lot of models this is so fast that it's not too much of an issue, but for others it can be a real pain, hopefully this will be fixed in a future version.

    Tom - The PowerPivot engine inside Excel was actually developed by the SQL Analysis Services team which is why this forum is under the SQL Server area, so I believe this is the correct place to be asking questions about the PowerPivot engine and DAX calculations. You can't actually split out DAX calculated columns into a separate workbook as you suggested.


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

    Thursday, November 07, 2013 1:44 AM
    Answerer
  • Darren, you are correct, I missed the reference to PowerPivot and thought the OP was using data driven from Excel. 
    • Edited by Tom Phillips Thursday, November 07, 2013 1:28 PM
    Thursday, November 07, 2013 1:28 PM
  • Basicalyl I deleted nearly 75% of my modle before it updated. This is of course nuts as definately hd more calcualted columns and updated a few months back.  Seems idiotic coding that lets you expnad them odle without any errors, but then is incapable of actualyl reloading the existing data.  I'm going to rebuild it by breaking it down into phases and using 2 models. Really suboptimal and not what I would have expected form the latest version.
    Thursday, November 14, 2013 9:56 AM