Refreshing worksheet table from Data Model [DAX] temporarily freezes

owenv

New Member
Joined
Jul 29, 2020
Messages
4
Office Version
365
Platform
Windows
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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

owenv

New Member
Joined
Jul 29, 2020
Messages
4
Office Version
365
Platform
Windows
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

New Member
Joined
Jul 29, 2020
Messages
4
Office Version
365
Platform
Windows
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

New Member
Joined
Jul 29, 2020
Messages
4
Office Version
365
Platform
Windows
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....
 

Watch MrExcel Video

Forum statistics

Threads
1,102,565
Messages
5,487,592
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top