Forum Discussion
Refresh very slow when using SharePoint people picker fields in report
Not sure if you managed to get to the bottom of this, but I had the very same problem and I managed to improve the performance of a Power BI report data refresh from ~11mins to ~1mins by following the below technique that I found in an obscure corner of the web.
Credit goes to Arnaldo Zotomayor: http://dynamicscrmcoe.com/boost-powerbi-performance/ (now a dead link).
-------
Build related tables for lookup items and link based on the “_attributename_value” field rather than ‘expanding’ the attributes within the record.
This may be one of the common mistakes to people who are new with PowerBI (I’m guilty). It can result in a big difference if not implemented. When selecting lookup fields to include in your dataset, you’ll probably see ‘Record’. The intention was you want to see what’s the value of that record and user usually expands the column. This is a no-no as per the performance is concerned.
Instead, choose the “_AttributeNameID_value” (this is the GUID of the Lookup) then reference or build relationship with this entity.
For example, I have a Customer entity then I want to show my ‘Owner’ column in dataset. Select _ownerid_value instead
Create new dataset for SystemUser to build relationship among two entities
-------
Personally, to get this working I built my query and report again from scratch. In each sharepoint site there is a table called "User Information List". Expand the content of this table, remove unnecessary fields leaving: Id, Title, EMail, Department, JobTitle, Office.
I then used this query as a reference and created a table for each person "Record" field that I wanted to expand to get user details for. I.e. you might end up with tables for Modifier Details, Approver Details, Creator Details etc.
Then in your Relationships editor create a 1 to many relationship between the Id field of your new tables (Id in the Modifier table for example) and the appropriate person Id field in your main sharepoint list or library table (EditorId). That should be it.
I've found the same process can be used for any Person or Lookup field that presents itself as a "Record" provided you can access a table that contains the record detail. I.e. instead of expanding the record for a sharepoint lookup value, import the list itself and join via a relationship on Id.
There are a few points to note.
1. It's far more time consuming to apply than simply expanding a column.
2. The technique is difficult to apply to a report after you've already built it. So it's best applied early on if you think the document libraries / lists being referenced will grow to contain a large number of items.Hope this helps.
Not sure if you managed to get to the bottom of this, but I had the very same problem and I managed to improve the performance of a Power BI report data refresh from ~11mins to ~1mins by following the below technique that I found in an obscure corner of the web.
Credit goes to Arnaldo Zotomayor: http://dynamicscrmcoe.com/boost-powerbi-performance/ (now a dead link).
-------
Build related tables for lookup items and link based on the “_attributename_value” field rather than ‘expanding’ the attributes within the record.
This may be one of the common mistakes to people who are new with PowerBI (I’m guilty). It can result in a big difference if not implemented. When selecting lookup fields to include in your dataset, you’ll probably see ‘Record’. The intention was you want to see what’s the value of that record and user usually expands the column. This is a no-no as per the performance is concerned.
Instead, choose the “_AttributeNameID_value” (this is the GUID of the Lookup) then reference or build relationship with this entity.
For example, I have a Customer entity then I want to show my ‘Owner’ column in dataset. Select _ownerid_value instead
Create new dataset for SystemUser to build relationship among two entities
-------
Personally, to get this working I built my query and report again from scratch. In each sharepoint site there is a table called "User Information List". Expand the content of this table, remove unnecessary fields leaving: Id, Title, EMail, Department, JobTitle, Office.
I then used this query as a reference and created a table for each person "Record" field that I wanted to expand to get user details for. I.e. you might end up with tables for Modifier Details, Approver Details, Creator Details etc.
Then in your Relationships editor create a 1 to many relationship between the Id field of your new tables (Id in the Modifier table for example) and the appropriate person Id field in your main sharepoint list or library table (EditorId). That should be it.
I've found the same process can be used for any Person or Lookup field that presents itself as a "Record" provided you can access a table that contains the record detail. I.e. instead of expanding the record for a sharepoint lookup value, import the list itself and join via a relationship on Id.
There are a few points to note.
1. It's far more time consuming to apply than simply expanding a column.
2. The technique is difficult to apply to a report after you've already built it. So it's best applied early on if you think the document libraries / lists being referenced will grow to contain a large number of items.
Hope this helps.