direct lake - columns into memory

[m-kovalsky]

When vertipaq analyzer is run (inside DAX Studio) against a Direct Lake model, it fires off a bunch of DAX queries which essentially put all the model's columns into memory. This defeats the purpose of a Direct Lake model having only the columns which a user queries put into memory. There should be some sort of configuration made to Veritpaq Analyzer so that it does not do this by default for Direct Lake models. And, that it obtains as many properties as possible from DMVs and as few as possible from DAX statements. IMO, the default setting for Direct Lake should be to not run any DAX queries against the model so as not to put any unnecessary columns into memory. This is highly problematic for Direct Lake.

[marcosqlbi]

You can already disable all the queries to DirectLake by disabling the "Read statistics from data" setting.
We could add a setting similar to "Read statistics from DirectQuery tables" for DirectLake, but I would keep the default setting enabled (in that case) because the purpose of VertiPaq Analyzer is to provide detailed information about the data model and not to provide information about the transient state of data (e.g. segments temperature and so on). I would like another tool/library to do that.
What do you think about that?

[m-kovalsky]

If I turn off the 'Read statistics from data' I don't get back the column cardinality (which is quite important) because that was being calculated by doing a DISTINCTCOUNT against every column in the model. Why isn't this using the Statistics_DistinctStates from TMSCHEMA_COLUMN_STORAGES? Then you don't need to run all that DAX and put the columns into memory. That seems to potentially solve the problem in an easier fashion.

Also, I'm not sure if Vertipaq Analyzer considers only the columns where IsResident=True for the memory usage (for Direct Lake models). That would be useful (if it's not done already).

[marcosqlbi]

Good point - I have to investigate. Originally, we were using the dictionary size as column cardinality (before we had TMSCHEMA DMVs) and the DISTINCTCOUNT calculation was the only way to get the right number as the dictionary can be larger than the actual unique values in the data (it happens with incremental refresh or when you refresh a partition).
I will investigate it and keep you posted (not very soon, unfortunately).

[m-kovalsky]

What should we do in the meantime? I've had customers complain about this and I've told them not to use Vertipaq Analyzer for now. There are many others who simply don't know this is happening behind the scenes and I feel they should know. Large models are more likely to be checked by Vertipaq Analyzer and those are exactly the ones for which this is problematic.

[marcosqlbi]

Well, the code is open source, if you have time for a pull request... :)

[dgosbell]

I don't think any of the DMVs will have cardinality information until the column is paged into memory.

I think a better option might be to treat DL models like DQ models and assume that reading the cardinality statistics is potentially very expensive. We could change the "Read statistics from Direct Query Tables" option to be "Read statistics from Direct Query / Direct Lake Tables". This is already disabled by default, but if people want to take the performance hit to get the cardinality they can choose to do so.

A better option for DL models is probably for people to run a notebook against the source delta tables to view the cardinality

[marcosqlbi]

@dgosbell I would use two separate settings because, depending on the size of DirectLake databases, the approach could be very different, and I might have scenarios where I use different defaults for the two cases. I am not sure about the default, though. There are reasons why it should be enabled also for DirectLake, but if we remove the statistics collection for all the columns, then it would be clear that something is missing and users should act accordingly.

[dgosbell]

Yes, a separate setting would also work. And You are right that it should be very rare that it's needed for DQ models, but there may be situations where someone wants to force a full scan of their DL model.

I'm thinking that it might make sense for client tools to have some visual indicator like an icon or a different background color when a column has isResident=false as that will indicate that the cardinality and size columns are probably not accurate

[marcosqlbi]

Yep, I want to spend time on it to improve VertiPaq Analyzer for these cases. I don't have time soon, unfortunately, but it's on the list.

[dgosbell]

I'll have a look at doing a pull request for this.

[otykier]

This is also relevant for Tabular Editor 3, so I'm just posting here to be notified of any updates. Thanks @dgosbell!

[dgosbell]

I'm currently testing adding an extra parameter currently called analyzeDirectLake to the GetDaxModel function which I default to false (similar to the analyzeDirectQuery parameter). But the difference with the DirectLake parameter is that I always run the column and relationship statistics if the column is resident in memory even if analyzeDirectLake is set to false.

The thinking here is that people will open one or more reports and navigate through all the pages then run an analysis to see what the typical memory usage is to support viewing those reports. They could use this information to see how close they are to the premium SKU limits or to drop unused columns from the ETL.

If the analyzeDirectLake is set to true I run the distinct count queries across all columns, then I re-run the DMV queries to re-collect the memory usage since the memory values will have changed for any columns that were not resident during the initial DMV run.

I'm looking at maybe adding a warning symbol like this mock-up for columns that are not resident

I might even see if I can add a toggle switch to the ribbon in DAX Studio to make it easier to toggle this setting on. Since I can imagine there may be some scenarios where people will want to just see the current "warm" cache size and others where they will want to force all columns into memory to see the peak size.

[marcosqlbi]

I'm not sure this is the right way to go long-term.
Probably, a faster and simplified analysis of the columns and their temperature, with a scheduled refresh (seconds/minutes?) is what people need for "real-time" evaluation. VertiPaq Analyzer in its current form is designed for a complete analysis, and I'd say that saving the VPAX should be enabled only when a full analysis has been completed, or we should display a big warning if a user wants to save the VPAX in an "incomplete" state (DAX Optimizer should probably not accept such "incomplete" files, for example).
If the warning is visible in the VPAX visualization, there should be a button to run a complete analysis without changing the setting in the Options for all future analyses.

[dgosbell]

I don't think people would need "real-time" analysis. It would be more of a specific point in time snapshot. I don't really like the idea of doing an automated refresh.

I'd say that saving the VPAX should be enabled only when a full analysis has been completed, or we should display a big warning if a user wants to save the VPAX in an "incomplete" state (DAX Optimizer should probably not accept such "incomplete" files, for example).

The problem here is that users can currently turn off "Read statistics from data" and generate an "incomplete" file like this today.

The problem I see with doing a "complete" analysis with Direct Lake models is the definition of "complete". Is it:

• every column in the model
• every column used by reports
• every column referenced by explicit measures

The problem with querying every column is you could have a hidden column like a fact ID which normally would not cause issues in a DL model if it was not queried, but VPA could cause issues either evicting other columns from memory or forcing a fallback to DQ mode.

And obviously with reports in other workspaces and external tools and Excel reports it's tricky to accurately cover columns used in reports. We'd have to ask the user to try and do this manually which is not ideal.

One possible compromise might be to use DISCOVER_CALC_DEPENDENCY to get columns referenced in measures and then collect stats for those plus any columns involved in relationships. Would that collect enough information for something like DAX Optimizer?

Then we could have an enum rather than a boolean for the Direct Lake options. And we could do something like the following:

• ResidentOnly - only collect stats on columns already resident
• Minimal - only collect stats on columns already resident or those referenced by measures or relationships
• Full - collect stats on every column

It would probably also make sense to store this in the VPAX somewhere so that we can easily tell what option was selected when generating the file and tools like DAX Optimizer can then treat them differently if it needs to. And a client tool like DAX Studio could display a warning with a button to trigger a higher level of scan if the full scan was not performed.

What do you think of this as an approach?

[otykier]

Tagging a few more people for visibility, @DBojsen @greggyb @mlonsk