Reply
Frequent Visitor
Posts: 12
Registered: ‎01-12-2017
Accepted Solution

Refresh very slow when using SharePoint people picker fields in report

Hi all,

 

We have an ongoing issue where we have a report connected to a SharePoint online list that has 3 people finder fields. When the list is added to PowerBi these fields are automatically shown as List in the data so then I have to expand this and select Name as the data I want to get. However after doing this for the required columns that are the People Picker columns on SharePoint the data refresh jumps from around 1 minute to 10 minutes +.

 

Is there a way to overcome this issue at all as we need to use the data in these fields from our SharePoint list but cannot live with the average time it take PowerBi to refresh the data when we use these columns.

 

Thanks,


Accepted Solutions
Frequent Visitor
Posts: 6
Registered: ‎09-13-2017

Re: Refresh very slow when using SharePoint people picker fields in report

[ Edited ]

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.

View solution in original post


All Replies
Community Support Team
Posts: 5,709
Registered: ‎09-21-2016

Re: Refresh very slow when using SharePoint people picker fields in report

Hi @lampard1987,

 

Did you import a large dataset into desktop? If so, that would affect the performance of data refresh.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 12
Registered: ‎01-12-2017

Re: Refresh very slow when using SharePoint people picker fields in report

Hi @v-yulgu-msft,

 

The database is a SharePoint Online list with around 1900 rows in it so I wouldn't say it is an extensive database at all. It refreshes absolutley fine until I expand the people picker columns that I want to use so Ideally I need a different method to get the data from these people picker columns that woun't need me to exapnd these columns and cause the refresh to shoot up in time.

 

Thanks,

James

Frequent Visitor
Posts: 2
Registered: ‎09-04-2018

Re: Refresh very slow when using SharePoint people picker fields in report

Same problem here, I guess; all other fields update just fine, but when I expand a person field to get the name, the update grinds to a halt. Interestingly enough, the queries without name expansion show the update progress as # of rows, while the query with the names shows progress as # of MB's (with three name fields and 2000 rows, it sums up to 17 MB..). The list is on Sharepoint Online (SP2016).

 

I have referenced the name fields into a separate table so that I can work on the other tables without the long wait, but it's really just a temporary fix during development; I need to improve the speed of the "names" query.  I have also used Table.Buffer and FieldValueAsText in an attemt to speed up the process, but it doesn't seem to help much. 

 

Our total database of names is quite large, > 20 000, and it could almost look like the query runs through every possible name for each row that's expanded. 

 

Anyone out there been able to fix this issue?

Frequent Visitor
Posts: 6
Registered: ‎09-13-2017

Re: Refresh very slow when using SharePoint people picker fields in report

[ Edited ]

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.