Power BI Refresh Time using Sharepoint OData Form Library Source

[MarkDGaal]

Hi guys I'm experiencing some very long refresh times for data I'm importing into Power BI / Query using the OData connector. 

I have a InfoPath Form Library on SharePoint with around 500 record and 100 elements. We'll call this Table1

I import Table1 into PowerBI and aside from identifying the data type on a few elements I don't do anything else with Table1. 

Then, I create 10 reference tables from Table1 where I do some moderately processor intensive data transformation, pivoting, value replacing, grouping, conditional columns, etc. We'll call these Table2-11

Finally, I reimport another copy of Table1 as Table12 and I merge table's 2-11 with Table12 to get my final clean data structure. 

If I try and refresh the preview on these tables, or even just the refresh on Table 1 it takes somewhere around 3 hours to complete... I've been monitoring network utilization during these refreshes and it seems to start off reasonably strong something like 10Mbps, but soon tapers off to something unbearably slow like 200Kbps (25KBps). Further, sometimes I can catch the download status of a table in the lower right-hand corner of the query editor ticking along slowly.

My question is.... why are these refreshes taking so long on a 500x100 Data source.... and further if Table1 and Table12 are the only table that uses the OData connector why do the other tables Referencing Table1 utilize the network when refreshing their previews? 

[pqian · Accepted solution]

@MarkDGaal Referencing queries doesn't mean the base query will only get loaded once. It is merely a code factoring tool. At evaluation time, each query will still be evaluated separately. The existence of a on disk cache will help you in certain cases (when parallel loading is off). Read this thread for more context:

http://community.powerbi.com/t5/Desktop/How-to-Improve-Query-Reference-performance-for-large-tables/...

Now, back to your SP question. One thing I think you can try is to remove all "navigation columns" (columns that are FKs, not data, they show up with a bunch of links inside like Table/Record/ etc) immediately after you imported the sharepoint table. There may be problems in the SP connector where these links get expanded all the time.

[MarkDGaal]

@pqian "One thing I think you can try is to remove all "navigation columns" (columns that are FKs, not data, they show up with a bunch of links inside like Table/Record/ etc) immediately after you imported the sharepoint table. There may be problems in the SP connector where these links get expanded all the time."

Kudos, that was exactly the problem. Had elements like "createdby" "modifiedby" "checkedoutto" that were pulling in all of the user information into the structured column. With those removed all 12 queries execute in about 5-6 mins.