I'm working with some reasonably large text files, and one frustrating problem I'm running into is that I can't load data into the Excel data model without loading it onto the worksheet first. The problem is that loading data onto the worksheet is very slow, and eventually stops when it hits the maximum number of rows that Excel handles; after that I seem to be able to load all the data into the model though.
Would it be possible in a later release to allow for scenarios where you can bypass the worksheet and load directly to the model...?
Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UKMonday, July 01, 2013 1:22 PM
When you say model, I assume you mean PowerPivot. I had to set LoadToWorkSheet to true the first time for the connection to be made available to the PowerPivot model. After acquiring the query in PP I then disable the LoadToWorkSheet flag and PowerPivot continues to update.
Not sure if this helps.
LeeMonday, July 01, 2013 1:58 PM
Yes, I'm working with Excel 2013 so "The Excel Data Model" is the new name for PowerPivot - or at least the engine bit of it.
It's the option to never have to load to the worksheet that I'd like. It's so slow with the text files I'm working with!
One other thing: I've also just found that setting Enable Download to Off deletes any tables loaded into the Excel Data Model... which means I have to reload the data again...
Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UKMonday, July 01, 2013 2:09 PM
Oh I see. This makes sense. Interestly with 2010 I've not been able to see the Connection in PowerPivot unless the data is first present on the worksheet.
Also, when I disable EnableDownload the data in PowerPivot remains but can't be refreshed without an error being displayed. This kind of makes sense. The deletion of data tables seems like a bug when using Excel 2013.
I imagine you've tried this but could you build the query with 1 text file and then set the load to worksheet to off and then amend your query to include the other files. Not ideal. With the model the worksheet isn't required.
I'll check this out when I get home. I have a brand spanking new install of Office 2013 to play with.
Check out my Performance Management blog at leehbi.com
------------------------------------------------------------------Monday, July 01, 2013 2:36 PM
To be honest, I'm hoping the whole UI/workflow for controlling where the output of queries ends up is temporary. It would be nice to be able to control where the table for the output is created in the worksheet, if the data is to go to the worksheet, and if so what the name of the table is; as I said, it would also be nice to bypass the worksheet completely and go straight to the Excel Data Model. The current UI seems a bit restrictive in that respect.
Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UKMonday, July 01, 2013 3:14 PM
Currently, you should be able to achieve the behavior you described. After reshaping/adding your text file data, ensure that the "Enable Download" toggle is set to On. Then you can set the "Load to Worksheet" toggle to Off. After all of the data is downloaded(this may take some time depending on the size of your text files), the "Load to Data Model" button should become active. Could you try these steps and let me know if you are successful?
RotimiFriday, July 05, 2013 8:02 AM
Thanks, you're right, although Excel becomes a bit unresponsive while the load is taking place which is why I hadn't realised this was possible. I'd still like to be able to load direct to the Excel Model before clicking OK in the Query window though, and I do think the UI here could be improved.
ChrisFriday, July 05, 2013 12:40 PM