Refreshing worksheet table from Data Model [DAX] temporarily freezes

[owenv]

Hi folks, I'm a MS Power stuff amateur and was hoping someone could help me out a bit here:

Situation
* Excel 365 Semi-Annual channel
* Data sources are on Sharepoint in Excel files
* 3 simple connection only queries to merge data into main queries
* 3 main queries (based on data of three main WorkDay reports) - these are loaded directly into the data model
* Data model currently has no relationships configured (and only 3 tables)
* Each of the tables in the Data Model I have loaded into a separate worksheet (using 'Existing Connections')
* In each of these worksheets are certain input fields for lookups in the data (e.g. show all records from employees based in lookup = 'London')
* To do this I have created a VBA subroutine that generates a DAX query on the data model
* Table 1 and Table 2 are 40K - 50K records each - they are surprisingly fast
* Table 3 (the latest addition to the workbook) has only 2,300 rows and a much smaller data source to begin with

(DAX) querying Table 3 is the problem
* Table 1 and 2 queries are relatively fast, excel does not 'hang', takes a couple of seconds if a create a DAX query that basically loads everything
* Table 3 DAX query is fast as long as there is a lookup text. If I do a full load (basically looking up "") it seems to be doing nothing for a few seconds, than Excel screen get's jibberish and Excel seems to hang. After a second or 20-30 the results are there...

Question: How can this be?
What am I missing that this DAX query's execution is so much slower/causing Excel temp freezes?

* Oh yes: the VBA code to create and run the query is basically identical except for some different variables.

This was my last try:
EVALUATE
CALCULATETABLE (
JobCatalog,
SEARCH ( "", JobCatalog[Compensation Grade], 1, 0 ) > 0
)
ORDER BY JobCatalog[Compensation Grade]

VBA code to run it: ActiveWorkbook.Connections("ModelConnection_JobCatalog").Refresh

The other two tables (larger!) have very similar DAX queries run a lot better.

[Sidenote question: all tables when refreshing the query from the Connections tab, say at the end something like 'Waiting for other Data Model...'.
Anyone has a clue why it does that because standard refreshing of all queries is not selected and the queries are not related apparently (except for on of the connection only's)

Any help is so much appreciated as this drives me mad

Thanks Owen

 

[owenv]

Hi folks, I'm a MS Power stuff amateur and was hoping someone could help me out a bit here:

Situation
* Excel 365 Semi-Annual channel
* Data sources are on Sharepoint in Excel files
* 3 simple connection only queries to merge data into main queries
* 3 main queries (based on data of three main WorkDay reports) - these are loaded directly into the data model
* Data model currently has no relationships configured (and only 3 tables)
* Each of the tables in the Data Model I have loaded into a separate worksheet (using 'Existing Connections')
* In each of these worksheets are certain input fields for lookups in the data (e.g. show all records from employees based in lookup = 'London')
* To do this I have created a VBA subroutine that generates a DAX query on the data model
* Table 1 and Table 2 are 40K - 50K records each - they are surprisingly fast
* Table 3 (the latest addition to the workbook) has only 2,300 rows and a much smaller data source to begin with

(DAX) querying Table 3 is the problem
* Table 1 and 2 queries are relatively fast, excel does not 'hang', takes a couple of seconds if a create a DAX query that basically loads everything
* Table 3 DAX query is fast as long as there is a lookup text. If I do a full load (basically looking up "") it seems to be doing nothing for a few seconds, than Excel screen get's jibberish and Excel seems to hang. After a second or 20-30 the results are there...

Question: How can this be?
What am I missing that this DAX query's execution is so much slower/causing Excel temp freezes?

* Oh yes: the VBA code to create and run the query is basically identical except for some different variables.

This was my last try:
EVALUATE
CALCULATETABLE (
JobCatalog,
SEARCH ( "", JobCatalog[Compensation Grade], 1, 0 ) > 0
)
ORDER BY JobCatalog[Compensation Grade]

VBA code to run it: ActiveWorkbook.Connections("ModelConnection_JobCatalog").Refresh

The other two tables (larger!) have very similar DAX queries run a lot better.

[Sidenote question: all tables when refreshing the query from the Connections tab, say at the end something like 'Waiting for other Data Model...'.
Anyone has a clue why it does that because standard refreshing of all queries is not selected and the queries are not related apparently (except for on of the connection only's)

Any help is so much appreciated as this drives me mad

Thanks Owen

One addition: I tried all the DAX queries n DAX studio connecting to the same Data Model: DAX studio was streaking fast.

 

[owenv]

Anyone any clue?

having Had a little distance from the problem I did think to try to go back to before I added the third table. See if I get the same situation again. I might just copy the query from one of the other tables as these appear to act normal.
I’ll keep you posted on the progress.
in the meantime any insights are much appreciated.

 

[owenv]

OK an update after trying a few hours...

It puzzled me why this query on a smaller table managed to get my computer steaming so much more than the others who have a lot more records.
It appears the following is the main culprit: WORDWRAP.

Because this smaller (i.e. lower record count) data table has a column with more text in it, I selected the query results table body in Excel and enabled word wrap.
Whenever you do that, you see a "Cells..." message in the bottom bar showing that this somehow is not something Excel finds particularly easy probably. Even though the results table is only 2,250 records big.

Every time I now hit the refresh query when no additional filters are selected, meaning requesting the full table from the data model, Excel first appears to freeze for a while.
I guess and bet that this has something to do with the word wrap I put in there....