PQ Too many merges - but works in SQL Server

I am currently working through some fresh logic to make it easier for users to map a Chart Of Accounts to report lines. For my SQL server customers this all works fine and quickly, but I’m struggling to get the logic to run quickly in XL/PBI PQ for my XL sourced customers.

The user will map a single account from the Chart of Accounts to a single report line ONCE. Then on the report page he will, in any of Columns SMap01 to SMap10, determine where the Report Line sums to. The mappings do not necessarily follow a hierarchical structure allowing the user the facility to easily perform any sub analysis requirements, aside from the usual financial statements.

Two attempts at this in the attachment – I will describe the process in PQ Folder LedgerAlloc4.The task is to unpivot SMap01 to SMap10 into a single MappingKey column. In the example this is query “Unpivoted SMap”. Then reference this query as “Collect All mappings” and perform multiple merges to build the mapping table. So merge Mapping Key to RepKey and return MappingKey, rename as Map01, merge Map01 to RepKey and return MappingKey, rename Map02 etc.
With the example file this involves 20 merges.
Once this is complete we need to unpivot the 20 columns and merge with the Chart of accounts to provide a final map of each RepKey to ledger code. Each reporting line will have multiple rows for each Mapping Key/RepKey combination. A final merge will map the ledger code to the RepKey. For example, all PBT related ledger codes will map to PBT.

In the example the column ExpenseCode should be understood as a LedgerCode – quirky terminology from the ERP system used for example purposes.

An earlier version of this is contained in PQ folder LedgerAlloc, using nested joins which was very slow. I saw Chris webs article and worked it through with Table.Join and Table.Buffer and reworked in LedgerAlloc4 (Still refreshing whilst I’m typing this!) I have also tried a non-merge approach using List.PositionOf but that didn’t really work either.

So having tried all this I’m big time stymied as to where to go next, especially since I can’t get the 20 merges I need in some cases. The desired output is shown on a dedicated tab, SQL Server script which works also attached. In the demo file I can no longer get either version of the query to refresh.

Looking forward to help
Pete
MAStructureEDNA2.xlsx (962.9 KB)
LedgerAllocation.sql (3.5 KB)

Hi All
Still working on this one and failing fast.

Attached example dilutes the problem a little bit by using a smaller dataset.
The multiple merge idea works here but there are only just over 100 ledger codes and very few report lines. The production version has 10-15 times this.

I had a thought that it could be done more efficiently with lists but struggling to get that to work too.
MAStructurePQ.xlsx (314.0 KB)

Hi guys

If you need Chris Webb you know it’s heavy :grinning:
After another day of trial and error and Chris Webb’s blog, finally something working nicely.

Key problem as we know was 20 merges - result01 against LookupTable, result 2 against lookup table etc.

The end result worked by:

  • Buffering the lookup table with Table.Buffer, this enabled the table to be held in memory for its 20 merges.

  • Using Table.Join (instead of the GUI Table.NestedJoin) with JoinAlgorithm.RightHash - No idea why this is faster really but Chris’ blog showed me the way to go.

  • Unpivoting and deduping after every merge to reduce the load on the next merge.

The query is running fast enough in production for 1900 report lines and 1500 ledger codes.

Whilst I’m happy with this now, there must be a way to do it with lists. Gut feel that might more optimal.
But no idea where to start.

Thanks to everyone who had a look at this one.

Pete

2 Likes

Why wouldn’t you want to do the joins before it comes into Power BI or (if possible) create a view to take the toll down on the report?

HI @cmalone1112

I am joining before power bi. I have two finance solutions - one for clients operating SQL server in some way. SQL script for this took me about an hour to sort out, fairly straight forward and runs quickly.

Then I have the same solution where the client’s data is XL based. So the query will run in XL PQ to prep a reasonably static table for Power BI upload. Table changes a lot on set up but once finance reporting structure in play and numbers work it will only change on introduction of a new account code.

I am good boy - following all that push the problem upstream if you can. :grinning:

Pete