none
How to improve slow PowerPivot performance when adding/modifying measures, calculated columns or Relationships?

    Question

  • I have been using PowerPivot for a couple of months now and whilst it is extremely quick when pulling in data to populate Pivot Tables, it is extremely slow to make the following kind of changes to the Data Model:

    - Add a Measure / Calculated Field

    - Add a Calculated Column

    - Rename a Calculated Field

    - Re-name a Calculated Column

    - Modify a relationship

    - Change a tables properties

    - Update a table

    In the status bar of excel I get a very quick 'calculating', then it spends a lot of time 'reading data', then it 'finalises' after which nothing is in the status bar but it still takes approx. 45 seconds before the program becomes responsive again. This waiting time does not change depending on the action, it is the same if I rename a column as it is if I add a new measure.

    My question is what affects performance of these actions and how do I improve it?

    To give you an idea of where my data comes from, I have:

    - 7 tables that feed into the Data Model directly from within the workbook which contains the data model itself. These are a combination of static tables and tables that connect to a MySQL database.

    - 6 separate workbooks which contain static data that is updated manually periodically (copied and pasted from another source)

    - 5 separate workbooks which contain dynamic tables that are linked to our MySQL database and update when opened.

    Now I realise that this is probably where my issue is, however I have no idea how to fix it. You do not seem to be able to connect to a MySQL database directly within the PowerPivot window itself so there is no way to generate and update tables without first creating them either in a worksheet or separate workbook (as far as I know).  If I try to create all of the tables directly within the single workbook containing the Data Model I get performance and crashing issues hence why I separate tables into individual workbooks.

    Any advice on how to improve performance would be tremendously appreciated. I'm new and keen to learn, I'm aware this set-up is far from best practice.

    Hardware wise I am using:

    - Windows 8 64-bit

    - Excel 2013 64-bit

    - Intel Core i7 processor

    - 6 GB Ram

    Thanks,

    James


    • Edited by Maracles Saturday, August 31, 2013 5:08 PM Poor spelling
    Saturday, August 31, 2013 5:07 PM

Answers

  • The first thing I would start with would be trying to eliminate the copy/paste data. Each time you make any of the changes you listed to the model the designer window sends a change script to the in-memory database engine. When you have copy/pasted static data into a table all of that data has to go back and forth as part of the change command.

    A quick google search turned up the following article which suggests that it should be possible to connect PowerPivot to MySQL http://blog.datamensional.com/2011/09/how-to-use-mysql-and-microsoft-powerpivot-together-2/


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

    Monday, September 02, 2013 2:53 AM
  • @Ivan - you have the reference you requested, I have no further comment.

    @James - the big problem with static data in PowerPivot is when it is pasted directly into the PowerPivot window. This bloats the metadata causing every change to the model to be slowed down. If your static data is held in the workbook and imported into PowerPivot as a linked table it should not really cause as much of an issue.

    Another source of performance issues when updating your model is calculated columns (not calculated measures, those are only calculated at query time). With the current versions of PowerPivot (both Excel 2010 & 2013) every calculated column is recalculated after every change. The development team is well aware of this issue, but there have been no announcements as when this might change.

    If you have a lot of calculated columns (or even one or two expensive ones) there are a couple of approaches that you can take to reduce their impact.

    1. Some calculations can be pushed back to the data source either as expressions in your SQL query or Excel  calculations in your case. This means that they will only be re-evaluated during data refresh, not every time a change is made to the model.

    2. Some calculations can be replaced by incorporating the logic into a measure. See this post http://sqlblog.com/blogs/alberto_ferrari/archive/2011/10/26/sum-or-sumx-with-simple-intermediate-calculations-sumx-is-the-way-to-go.aspx for an explanation of that approach.


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

    Tuesday, September 03, 2013 11:49 AM
  • Darren,

    I think the point I was making is its in memory, geez... BTW what do all applications do when they run out of paged memory,  if PowerPivot is using all available memory then wouldn't this force the other applications to use Virtual or essentially write back and forth to the disks? I think Virtual memory white to disk ??, lol Also, there are parts if the architecture of Excel 2013 that when importing data into PowerPivot require memory and when working in SharePoint the PowerPivot data is cached to disk unless recently refreshed... But this conversation isn't help the James who asked the question and as much as I would love to continue its become a little boring..

    Hi James,

    If you download one the ODBC MySQL Connectors http://dev.mysql.com/downloads/connector/odbc/ and I believe yours is the first one for x64 systems and connect directly to the data you should be able to reduce the number of workbooks your opening and if you notice in the following graphic these connection are automatically refreshed by default, the parts in red are the differences between PowerPivot 2010 and 2013

    You should notice a lot of improvement especially when refreshing data please let us know how it goes...

    After registering the ODBC Driver

    Click Add. on the User-DSN sheet, choose the “MySQL ODBC 5.x driver”, fill in the credentials, choose a database (from the select menu) and a data source name and you’re done.

    Back in Excel you go on the PowerPivot section of the ribbon and open the PowerPivot window  (the green icon on the left side). In the ‘Home’ section of that window you will see a small gray cylindrical symbol (the international symbol for “database”) which will suggest to you different data sources to choose from. Take the one where it says “ODBC”.

    In the next dialog you click on create, choose the adapter, and then Ok. Back in the assistant you can check the connection and proceed.

    Now you have the choice between importing the data from tables using the import assistant or Query depends on your skillset..

    Cheers,

    Ivan


    Ivan Sanders <a href="http://www.linkedin.com/in/iasanders">My LinkedIn </a> , <a href="http://msmvps.com/blogs/ivansanders">My Blog</a>, <a href="http://twitter.com/iasanders"> @iasanders</a>, <a href="http://shop.oreilly.com/product/0790145372703.do">BI in SP2013</a>, <a href="http://sharepointdemobuilds.codeplex.com">SP2013 Content Packs</a>.

    Monday, September 02, 2013 11:09 PM
  • To load data from MySQL you may also try Power Query which offers a native MySQL connectivity
    with Power Query you can further modify the dataset being extracted from MySQL and e.g. remove unused columns etc.

    by setting "Load to worksheet=Off" and clicking "Load to Data Model" the result will be directly loaded into your Power Pivot model without any unnecessary storing within any excel sheet

    this should avoid the issue Darren mentioned where stare data is added to the metadata of the model which may cause slow update times

    @Darren: I know that Copy/Paste data is part of the metadata but is this also true for linked tables??
    when the model is imported into SSAS Tabular - yes, linked tables will become part of the metadata, but does it work the same way for Power Pivot in Excel?

    another issue may also be the amount of Pivot Tables that use the data model as each of them is also refreshed after each change
    as Darren already mentioned, every calculated is recalculated after every change so the number of calculated columns/measure may also be a limiting factor here

    hth,
    gerhard


    - www.pmOne.com -

    Wednesday, September 04, 2013 5:34 PM
  • No it does not have the same drawbacks.

    It's not as efficient as loading the data directly into PowerPivot as the data will be loaded in the Excel sheet and also in PowerPivot, but it will not have the negative impact on the metadata.


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

    Tuesday, September 10, 2013 8:44 PM

All replies

  • The first thing I would start with would be trying to eliminate the copy/paste data. Each time you make any of the changes you listed to the model the designer window sends a change script to the in-memory database engine. When you have copy/pasted static data into a table all of that data has to go back and forth as part of the change command.

    A quick google search turned up the following article which suggests that it should be possible to connect PowerPivot to MySQL http://blog.datamensional.com/2011/09/how-to-use-mysql-and-microsoft-powerpivot-together-2/


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

    Monday, September 02, 2013 2:53 AM
  • Hi All,

    Also, the whole point of using the in memory models like xvelocity - PowerPivot is to keep the all of the calculations in memory, with only 6GB or RAM this will never happen and you will be writing to disk just populate your PowerPivot Charts. Start being realistic when using in memory resources and give your server 32GB , then go up from there. My development desktop has 64GB RAM and my Laptop 32GB due in large part to the resources necessary for PowerPivot, PowerView, Tabular Mode, etc...

    Reference

    Performance Tuning http://sqlblog.com/blogs/marco_russo/archive/2010/01/26/memory-considerations-about-powerpivot-for-excel.aspx

    HW and SW Requirements http://technet.microsoft.com/en-us/library/ee210640.aspx

    memory requirements for PowerPivot check out the minimum, I would double that number in DEV though its said you can use 8GB I have never been able to get PowerPivot Charts and Graphs using multiple sources to refresh properly with less than 32GB

    The following table describes a minimum and required hardware configuration:

    Component

    Minimum

    Recommended

    Processor

    64-bit dual-core processor, 3 GHz

    16 Cores

    RAM

    8 gigabytes of RAM 1

    128 gigabytes of RAM

    Storage

    80 gigabyte storage

    80 gigabytes or more

    -Ivan


    Ivan Sanders <a href="http://www.linkedin.com/in/iasanders">My LinkedIn </a> , <a href="http://msmvps.com/blogs/ivansanders">My Blog</a>, <a href="http://twitter.com/iasanders"> @iasanders</a>, <a href="http://shop.oreilly.com/product/0790145372703.do">BI in SP2013</a>, <a href="http://sharepointdemobuilds.codeplex.com">SP2013 Content Packs</a>.

    Monday, September 02, 2013 4:26 AM
  • with only 6GB or RAM this will never happen and you will be writing to disk just populate your PowerPivot Charts. 

    This is not true. Powerpivot is entirely RAM based it will never page to disk when being queried. PowerPivot will throw an error if enough RAM is not available. With a well designed model you could easily load a few hundred million records into a machine with 6Gb of RAM.


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

    Monday, September 02, 2013 6:18 AM
  • Hi  Darren,

    hmm do you think that maybe not meeting minimum requirements may be an issue. Are you saying that that using 7 tables, 6 work books, and 5 workbooks containing the source content on a client system that doesn't contain an AS PowerPivot Installation just Excel Client 2013 that the users information isn't being cached? Of course, if AS PowerPivot was installed and we were talking about publishing PowerPivot to a Document Library, then you would only have SharePoint / Appfabric specifically writing to the cache and receiving errors from PowerPivot with regard to not having enough memory requirements.

    -Ivan


    Ivan Sanders <a href="http://www.linkedin.com/in/iasanders">My LinkedIn </a> , <a href="http://msmvps.com/blogs/ivansanders">My Blog</a>, <a href="http://twitter.com/iasanders"> @iasanders</a>, <a href="http://shop.oreilly.com/product/0790145372703.do">BI in SP2013</a>, <a href="http://sharepointdemobuilds.codeplex.com">SP2013 Content Packs</a>.

    Monday, September 02, 2013 8:44 AM
  • Hi Ivan,

    No, I'm saying that PowerPivot models are always fully cached in RAM or they will throw an out of memory exception. There is no middle ground. xVelocity will not page out to disk if there is not enough RAM. And the original question relates to the Excel design experience there was no mention of any Sharepoint issues in the original question.

    For a server 6Gb would be way too small, but for just the Excel client you can still get useful work done in 6Gb.


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

    Monday, September 02, 2013 10:00 AM
  • Hi Darren,

    First I think you may be either misreading or I may not be clear.

    I agree with the you and you get errors about not having enough memory in PowerPivot to support your environment.

    My point is there wasn't any mention of having anything installed except Excel 2013 Client, this means everything gets cached to the desktop when there isn't enough memory.

    As mentioned previously there isn't any mention of the user having AS PowerPivot installed only the Excel Client. I am not arguing if xvelocity is in memory or better yet what hecaton has in common with PowerPivot. I was talking about using a desktop with 6GB of RAM to do what he is attempting

    I hope this helps clarify, the PowerPivot Client is does not load everything only in memory, its an Excel addon, though its included in Excel 2013 now....

    I hope this helps clarify.....

    -Ivan


    Ivan Sanders <a href="http://www.linkedin.com/in/iasanders">My LinkedIn </a> , <a href="http://msmvps.com/blogs/ivansanders">My Blog</a>, <a href="http://twitter.com/iasanders"> @iasanders</a>, <a href="http://shop.oreilly.com/product/0790145372703.do">BI in SP2013</a>, <a href="http://sharepointdemobuilds.codeplex.com">SP2013 Content Packs</a>.

    Monday, September 02, 2013 10:18 AM
  • I hope this helps clarify, the PowerPivot Client is does not load everything only in memory, its an Excel addon, though its included in Excel 2013 now....


    Excel may not load all it's data into memory, but in terms of the PowerPivot data model the addon (which as you point out comes included with Excel 2013) runs an in-process version of the xVelocity engine. This is the same engine which is part of the SSAS Powerpivot and SSAS Tabular server based instances. And it does load the entire data model into memory. I've been working with PowerPivot since it was in pre-release and was only known by it's code name of "project Gemini" and I know this to be true.

    As I stated earlier, a large part of this performance issue is almost certainly the static pasted data as this will be inlined into the XMLA alter commands that are sent from the UI to the in-process xVelocity instance.


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

    Monday, September 02, 2013 12:10 PM
  • Hi Darren

    My understanding like most peoples understanding of stuff my be flawed. I would love to have you reference the PowerPivot Client Add-On not using or writing to virtual memory...

    My understanding is its not AS while still persisting the Excel Client OM. Please enlighten me.. As I am eager to learn all things SharePoint... We have all been working with multiple pre-releases with this latest version since prior to SQL2012 was released much less the multiple builds prior to CTPs pre beta releases of SQL2012 SP1. But also dating back to the inclusion of RS in 2005 SP2 CU3.. So please, provide any references you at any time since the xVelocity engine was introduced.. and any thoughts you may have on hecaton and the tools we should be using to handle the large memory requirements when design these models as well...

    Thank you,

    -Ivan


    Ivan Sanders <a href="http://www.linkedin.com/in/iasanders">My LinkedIn </a> , <a href="http://msmvps.com/blogs/ivansanders">My Blog</a>, <a href="http://twitter.com/iasanders"> @iasanders</a>, <a href="http://shop.oreilly.com/product/0790145372703.do">BI in SP2013</a>, <a href="http://sharepointdemobuilds.codeplex.com">SP2013 Content Packs</a>.

    Monday, September 02, 2013 7:17 PM
  • From this Article http://blogs.msdn.com/b/analysisservices/archive/2010/03/22/powerpivot-component-architecture.aspx about a quarter of the way down under the "Application Architecture" section:

    The VertiPaq engine performs query processing and implements a column-based data store with efficient compression algorithms to get massive amounts of data directly into memory. With all the data in memory, PowerPivot can perform its query processing, data scans, calculations, and aggregations without having to go to disk.

    Vertipaq is the previous name for the technology that got rebranded as xVelocity with the SQL 2012 release when then incorporated it into column store indexes and the Tabular instance mode for Analysis Services. 

    Hekaton is a SQL Server based technology and while it is an in-memory technology which is more aimed at OLTP workloads not analysis workloads. It has nothing to do with PowerPivot in Excel and hence has no impact on this discussion.


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

    Monday, September 02, 2013 9:28 PM
  • Thank you for your responses, it seems like I've started quite a conversation! Unfortunately some of this is a little over my head however the static workbooks sound like it is a sensible place to start to try and reduce the performance issues I'm having.

    If I replaced these workbooks with workbooks that contained tables generated directly from a MySQL call would this then provide a significant boost in performance?

    If I wanted to take this a step further, should I then try and eliminate separate workbooks by folding those tables and MySQL calls into the central workbook that contains the data model? Does it make sense that I may have problems from having too many such tables in one workbook or does that sound like it could be an unrelated issue?

    Thanks for all advice.

    As for requirements, from my reading a large number of books ad articles have suggested you can crunch 100's of millions of rows with only 4GB ram and 64-Bit Excel provided you are using a well optimised machine.

    James

    Monday, September 02, 2013 10:09 PM
  • Darren,

    I think the point I was making is its in memory, geez... BTW what do all applications do when they run out of paged memory,  if PowerPivot is using all available memory then wouldn't this force the other applications to use Virtual or essentially write back and forth to the disks? I think Virtual memory white to disk ??, lol Also, there are parts if the architecture of Excel 2013 that when importing data into PowerPivot require memory and when working in SharePoint the PowerPivot data is cached to disk unless recently refreshed... But this conversation isn't help the James who asked the question and as much as I would love to continue its become a little boring..

    Hi James,

    If you download one the ODBC MySQL Connectors http://dev.mysql.com/downloads/connector/odbc/ and I believe yours is the first one for x64 systems and connect directly to the data you should be able to reduce the number of workbooks your opening and if you notice in the following graphic these connection are automatically refreshed by default, the parts in red are the differences between PowerPivot 2010 and 2013

    You should notice a lot of improvement especially when refreshing data please let us know how it goes...

    After registering the ODBC Driver

    Click Add. on the User-DSN sheet, choose the “MySQL ODBC 5.x driver”, fill in the credentials, choose a database (from the select menu) and a data source name and you’re done.

    Back in Excel you go on the PowerPivot section of the ribbon and open the PowerPivot window  (the green icon on the left side). In the ‘Home’ section of that window you will see a small gray cylindrical symbol (the international symbol for “database”) which will suggest to you different data sources to choose from. Take the one where it says “ODBC”.

    In the next dialog you click on create, choose the adapter, and then Ok. Back in the assistant you can check the connection and proceed.

    Now you have the choice between importing the data from tables using the import assistant or Query depends on your skillset..

    Cheers,

    Ivan


    Ivan Sanders <a href="http://www.linkedin.com/in/iasanders">My LinkedIn </a> , <a href="http://msmvps.com/blogs/ivansanders">My Blog</a>, <a href="http://twitter.com/iasanders"> @iasanders</a>, <a href="http://shop.oreilly.com/product/0790145372703.do">BI in SP2013</a>, <a href="http://sharepointdemobuilds.codeplex.com">SP2013 Content Packs</a>.

    Monday, September 02, 2013 11:09 PM
  • Hi James.

    All of my professional work is on servers and most of my clients want real time or near real time dashboards. I am a SharePoint Server MVP and typically more concerned with the Presentation of the dashboard and how long a user has to wait for AS, RS or the engine to return results to the dashboard. That said limiting the connections is always a good thing. If I need to combine the resources of multiple connections I would use SQL IS to implement ETLs to bring the data together from disparate locations.

    Because most of my work is server to server based I rarely use excel as a data source. I mostly use SQL and Oracle as data sources. However, I have written a few labs where the user is required to use Excel as a data source.

    My only issue in doing so is what is the benefit of using spreadsheets vs  SQL or another DBEngine. If spreadsheets are being used for data entry then I can understand why folks use them. However, when I see so many connections. My first question would be why do you need so many? After understanding why and given the physical HW restrictions when can then come up with a plan to reduce the data sources you currently have where it makes the most sense to do so.

    I know this doesn't answer your question directly, but yes less is generally better but only if it doesn't create additional issues for users to overcome.

    -Ivan


    Ivan Sanders <a href="http://www.linkedin.com/in/iasanders">My LinkedIn </a> , <a href="http://msmvps.com/blogs/ivansanders">My Blog</a>, <a href="http://twitter.com/iasanders"> @iasanders</a>, <a href="http://shop.oreilly.com/product/0790145372703.do">BI in SP2013</a>, <a href="http://sharepointdemobuilds.codeplex.com">SP2013 Content Packs</a>.

    Monday, September 02, 2013 11:48 PM
  • @Ivan - you have the reference you requested, I have no further comment.

    @James - the big problem with static data in PowerPivot is when it is pasted directly into the PowerPivot window. This bloats the metadata causing every change to the model to be slowed down. If your static data is held in the workbook and imported into PowerPivot as a linked table it should not really cause as much of an issue.

    Another source of performance issues when updating your model is calculated columns (not calculated measures, those are only calculated at query time). With the current versions of PowerPivot (both Excel 2010 & 2013) every calculated column is recalculated after every change. The development team is well aware of this issue, but there have been no announcements as when this might change.

    If you have a lot of calculated columns (or even one or two expensive ones) there are a couple of approaches that you can take to reduce their impact.

    1. Some calculations can be pushed back to the data source either as expressions in your SQL query or Excel  calculations in your case. This means that they will only be re-evaluated during data refresh, not every time a change is made to the model.

    2. Some calculations can be replaced by incorporating the logic into a measure. See this post http://sqlblog.com/blogs/alberto_ferrari/archive/2011/10/26/sum-or-sumx-with-simple-intermediate-calculations-sumx-is-the-way-to-go.aspx for an explanation of that approach.


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

    Tuesday, September 03, 2013 11:49 AM
  • To load data from MySQL you may also try Power Query which offers a native MySQL connectivity
    with Power Query you can further modify the dataset being extracted from MySQL and e.g. remove unused columns etc.

    by setting "Load to worksheet=Off" and clicking "Load to Data Model" the result will be directly loaded into your Power Pivot model without any unnecessary storing within any excel sheet

    this should avoid the issue Darren mentioned where stare data is added to the metadata of the model which may cause slow update times

    @Darren: I know that Copy/Paste data is part of the metadata but is this also true for linked tables??
    when the model is imported into SSAS Tabular - yes, linked tables will become part of the metadata, but does it work the same way for Power Pivot in Excel?

    another issue may also be the amount of Pivot Tables that use the data model as each of them is also refreshed after each change
    as Darren already mentioned, every calculated is recalculated after every change so the number of calculated columns/measure may also be a limiting factor here

    hth,
    gerhard


    - www.pmOne.com -

    Wednesday, September 04, 2013 5:34 PM
  • @Gerhard - no linked tables do not bloat the meta data in the same way that data copy/pasted directly into the PowerPivot does. 

    But your point about having a lot of pivot tables being another possible cause of performance issues is a good one. As you say, any change in the data model will trigger a cascade of refreshes in the pivot tables. I would also include Slicers as a possible problem here two. Each pivot table will issue 1 query to the data model, but each Slicer issues 2 (one to get the list of values and another to get the values which have data)

    Note that in terms of the PowerPivot model itself, measures (expressions that go in the measure grid under the data) don't get re-calculated when the model metadata changes. These are only evaluated at query time. It is calculated  columns which get re-calculated and stored in memory after any metadata change. However Measures could come into play if they are referenced in pivot tables in the same workbook as the model.

    @James - This issue of having a lot of pivot tables matches with the symptoms that you are seeing. A quick test would be to make a copy of your excel file, then in the copy delete all the sheets that have pivot tables and slicers, then try making a change to your model (like renaming a column) and see how it reacts. If this is what is causing issues for you then you may be able to switch off the automatic pivot table refreshing (at least while you are changing your model).


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

    Wednesday, September 04, 2013 9:12 PM
  • Thanks for all the suggestions, I've been away and am only now slowly replying to these helpful posts.

    I have tried connecting directly to the database within the PowerPivot window however I see slightly different options (Excel 2013). On the 'Home' tab I do nto have an ODBC option under the 'From Database' source list, instead I have to go into 'From Other Sources'.

    Once here I'm asked to Build or Test a connection. However when I build a connection I get the following error:

    "[DBNETLIB][ConnectionOpen (Connect()).]SQL server does not exist or access denied"

    Strangely however I can connect to my database, using identical details, if I go through the Data Tab or a normal Excel Worksheet.

    How can I correct for this error? And also, if I already have an existing connection in the worksheet, is there nowhere I can point to this one from inside the PowerPivot window?

    Thanks,

    James

    Tuesday, September 10, 2013 2:06 PM
  • @Darren I may have poorly described my 'Copy/Paste' data. The data is not copied and pasted directly into the PowerPivot window - it is being generated by our internal systems and output as a table on a web page. The data is then copied from the web page into the Excel Workbook, once in the workbook it is converted to a table and subsequently added to the Data Model as a linked table.

    Does this method still have the same drawbacks you've suggested?

    Tuesday, September 10, 2013 2:08 PM
  • No it does not have the same drawbacks.

    It's not as efficient as loading the data directly into PowerPivot as the data will be loaded in the Excel sheet and also in PowerPivot, but it will not have the negative impact on the metadata.


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

    Tuesday, September 10, 2013 8:44 PM
  • Fantastic contributions here. Please see: http://social.technet.microsoft.com/Forums/sqlserver/en-US/a2e22d61-7c32-43f2-900e-d9d6325fa26d/needed-more-answerers

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Wednesday, September 11, 2013 6:16 PM
  • @Darren Thanks for your updates - I really appreciate your contributions to this thread, I have now up voted your responses to indicate this.

    Whilst I will certainly take the time to understand the PowerPivot architecture in more detail, at the moment it is a bit beyond my current technical knowledge and I need to get a solution working asap which I can then revisit and revise once my understanding is more complete.

    With that in mind, based on your suggestions I have three options:

    1) Within the workbook that contains my workbook I use the data sources feature of standard excel to connect, though ODBC, to my MySQL database and then generate the multitude of tables I need. These would then be added to the Data Model as linked tables.

    This cuts out copy/paste data, but I'm worried the file sizes will get to big - my file size is currently 25mb.

    In this situation the data would be refreshed every time I open my document.

    2) Follow the same data sources method above but instead of keeping the tables generated in the workbook containing my data model, I can create a new workbook that I can use exclusively for these tables. I will then add this worksheet and the relevant tables to my data model through the 'Add Excel File' option in PowerPivot.

    In this instance my data model would only get refreshed data if I open this separate Excel, refresh the data, and then do a 'Refresh All' from within the Data Model itself.

    3) I can try and get Power Query to work (I am having severe issues). I don't fully understand Power Query since the documentation seems very incomplete, but if I understand what I've read so far I should be able to query my MySQL database and then have that query pull data directly into my Data Model.

    I have no idea how this gets updated.

    Do I have the right idea here, and if so which of the three options is the best? Or at least, which of options one or two is the best in the event that I cannot get Power Query to work.

    Thanks again,

    James

    Wednesday, September 11, 2013 7:53 PM
  • Is this still an issue?

    Did you try the options?

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Wednesday, September 18, 2013 8:52 PM
  • I am in the process of rebuilding my data model at the moment taking these suggestions into account; currently I am suffering the same performance issues however I have noticed by removing certain calculated columns and limiting the data being imported to more essential items the size of my file has reduced from 25Mb to 18Mb so there is progress.

    Unfortunately my model got quite complex doing it the 'wrong way' and so the untangling process is slow. I was intending on returning to this page once complete and marking the most valuable answers at that point.

    Thursday, September 19, 2013 7:02 AM
  • This is a long thread. But back to the original problem of each model change being very slow, try Disable Auto Refresh in OLAP PivotTable Extensions: http://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=26

    http://artisconsulting.com/Blogs/GregGalloway



    Monday, June 02, 2014 10:26 PM