Refreshing worksheet table from Data Model [DAX] temporarily freezes

owenv

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

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

owenv

New Member
Joined
Jul 29, 2020
Messages
4
Office Version
  1. 365
Platform
  1. 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
  1. 365
Platform
  1. 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
  1. 365
Platform
  1. 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,132,875
Messages
5,655,754
Members
418,234
Latest member
jdorfma

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top