Pivote columns refresh error

[Anonymous]

Hi all,

I'm having problems when trying to refresh my Power Bi Report. I'm maintaining a report, which is using SQL Server and Sharepoint (with excel files) as two main sources. Report is middle size, it has about 400k records in the largest table. I'm using data stored in Sharepoint, to filter values in my main SQL table. When doing transformations, I' doing pivoting and unpivoting coumns. It looks like this is the main problem, as witohut those steps, I don't get any errors when trying to refresh it.

Problem occurs, when I'm trying to refresh report. I takes a lot of time, sometimes about 8 hours, and unfortunately for 99% tries, i get an error.  This is the message error i get, i know it's popular, and it can mean a lot of things. 

AnalysisServices: Internal error: The operation terminated unsuccessfully. Server: The operation was cancelled because of locking conflicts.

Does anyone have an idea, what could be the issue here?

[watkinnc]

Actually, if you are filtering your SQL with a  list of values from Sharepoint, you can preserve query folding if you buffer the list:

= Table.SelectRows(TableName, each List.Contains(TableToFilter[ColumnName], List.Buffer(SharepointTable[ColumnWithFilterValues])))

Buffering the list will also make your query very fast, as far as the filtering. 

--Nate 

[BA_Pete]

@watkinnc ,

Interesting point, I wasn't aware of this.

Do you know how the native query handles the buffered table in terms of sending that step to the server?

Pete

[watkinnc]

Yessir, it sends the list as an IN clause, like

Customer ID IN (A12345, A42956, A23584, A65485, A46584).

--Nate

[BA_Pete]

Just tested this and sharing findings for future generations of PBIers:

After much confusion, realised the arguments were the wrong way round in the List.Contains function. It should be:

List.Contains(filterValuesTable[columnWithFilterValues], [columnToActuallyFilter])

Also discovered that the list doesn't need to be buffered to preserve folding - PBI is smart enough to generate the IN clause either way:

However, recommendation to buffer the list for performance gains is very valid.

Pete

[edhans · Accepted solution]

Buffering doesn't affect folding. It affects speed. It gathers all of the items in the list at once, then sends the buffered list to the SQL server in the IN clause.

If you don't buffer, it streams the data into the IN clause and takes significantly longer. 

As a result, folding or not, I always use a buffered list for List.Contains. And yup, @watkinnc taught that trick to me.

[watkinnc]

Darnit!  I still get those reversed sometimes!  Yes, the FilterValuesList first, then the TableColumnToFilter second.

I'm usually never at the PC when I write these comments...

But yeah, using a buffered list in a List.Contains Table filter makes filtering extremely fast.  And the fact that it preserves query folding is also pretty seriously awesome.  It can handle a few thousand values, you just have to make sure that your resulting SQL statement doesn't exceed the query character limit, which I can't remember.

@edhans has an article about using List.Contains on his site; I'd already been using it for a while, and I casually made a comment on one of Ed's posts on Twitter about buffereing the list. He, being an actual professional, tried it, measured the performance gains, and added an addendum to the article here https://www.ehansalytics.com/blog/2020/5/20/using-listcontains-to-filter-dimension-tables

His article is much more thorough than the stuff I post on here!

--Nate

[Anonymous]

@BA_Pete @watkinnc  Sorry guys to bother you once again, but I've made a mistake in the first decsirption, and in fact, I'm using SSAS as my data source. As from what I've read, SSAS support MDX/DAX. Therefore, my Native Query is grayed out, is there a possibility, that still, the way I was filtering data, could be the reason why I can't load my data correctly? Thanks!

[BA_Pete]

Beautiful. Thanks 🙂

[BA_Pete]

Hi @Anonymous ,

There's a few things here:

1) Using an Excel/SharePoint source to filter an SQL source is going to break query folding, so your subsequent transformation steps are going to be performed locally, rather than on the source server.

2) Pivot/unpivot are 'whole table' operations, i.e. the whole table has to be loaded into memory before performing the operation, unless you can fold the operation back to the server to do (See Point 1). If you have broken query folding and your workstation and/or gateway do not have sufficient RAM, these types of operations can easily max out the machine and cause excessively long refresh times.

3) I can't be 100% sure, but it appears as if the error is due to server deadlocking. If your queries are taking hours to run (See Point 2), you are applying shared locks to all the tables you are querying on the server for a very long time. When the server actually wants to update one of those tables as part of a larger transaction, it can cause a 'deadlock' situation, where neither PBI nor the server can complete their transaction without the other releasing its locks. In this case, the server will just terminate one of the transactions to let the other complete.

It may not even be a full deadlock that's causing the error, it may just be the fact that the server doesn't want you applying a shared lock to any table for that amount of time.

My suggestion would be to remove the Excel/SharePoint filter in Power Query, but load it to the data model to be used as a dimension table of sorts, effectively filtering your main table via a table relationship. Once this is removed from your main query, then ensure that your query folds back to the server. I would expect this to speed up your refresh by orders of magnitude.

Pete

[Anonymous]

Wow, thanks! 

I have to say, that you answer opened my eyes. I've worked for 1 year with Power BI, but never heard about somehitng as important as query folding. I've just started reading about it, but for sure it will have an impact on my work.

I have one question, as I understand filtering my main table causes problems, as it breaks query folding. Would changing order (first pivoting, then filtering based on excel) help something?

Of course I will try your solution, but I was wondering if my idea would work.

[BA_Pete]

@Anonymous ,

Yes, your query will fold up until the step that you break it, then all subsequent steps will be completed locally.

You can check if/where folding is broken by right-clicking on each of your steps and checking if 'View Native Query' is available. If it is, then your query is folding to the server with the native SQL that can be viewed by clicking that button. If it's greyed-out, then that step is not being folded.

Pete

[Anonymous]

That sounds great, in fact, I haven't exactly specified my table source, I'm using  SQL Server Analysis Services, so I guess should use DAX or MDX language, as it is the native language (according to what i I've in microsoft docs)