I'm a simple PBI developer, I see Maco Russo, I click like

This is phenomenal, thank you so much! This video deserves at least 1 million views! :)

This is by far the most innovative technical video I have seen so far. Keep up the good work. Your knowledge is always a great treasure for the Power BI community.

Dropping knowledge bombs from start to finish, I feel smarter than I was 25mins before

Great video, thank you Marco. I like how you drew it out on paper. I now view the SE like a math book... a collection of all of the figures and data required to perform math, however still requires the student (FE) to actually solve for the result.

Really helpful in understanding the two different engines. Loved the way you used the book analogy and paper and pen before moving on to the code. It's helped me in understanding how to analyse queries when optimising code and reminds me of TCP protocol, for example, in that each process is efficient as it specialises in it's own job, storage engine gets the data, formula engine calculates and visualises. Thank you

Thanks for the excellent explanation! Looking forward to more of the videos on this topic

Excellent video. Thank you. I am a very visual learner and this explanation (along with your book) is incredibly helpful.

Great video and hugely informative. Thank you! Although I spend a lot of time in DAX, this did a great job in breaking down concepts and filling in gaps in what I know. :)

For each video you post, I always imagine that you won't be able to produce a better video than the last one you posted... But I'm always wrong! Each video is always better, and it's getting better and better each day! It's unbelievable! You are definitively the DAX Masters! Thanks for sharing! 👏👏👏

@SQLBI

3 жыл бұрын

Thanks!

Excellent tutorial! Very interesting and useful to know! THX a lot Mr. Russo

Thanks Marco! Great video which I will most likely forward to everybody whoever asks about this instead of trying to explain myself :-)

Great explanation, thanks

Very helpfull explanation. Only heart the words but didn't know how it works. I'am greatful for your videos.

Wish more content on this! Thanks

very good explanation

Finished watching

Thanks!!! Really good explanation !! 5 stars XD

A nice long video! Yeaa!

thanks marco,I love this vedio......

Thx

Great explanation, thanks a lot for this great content ! Question - if you have more than one visual and a different measure, like Total Cost, would it re-use the SE values (and the FE aggregates at a higher level) or run another query ? .

@SQLBI

3 жыл бұрын

Every visual generates one or more query, regardless of the result of other visuals. If the visual request the same data requested before (same filters) it does not generate any query and reuse the previous result. Please note this is a cache at the report level and the engine does not receive any DAX query in this case. The FE does not cache any data. The SE has a cache for the last 512 queries per database. Different queries can reuse the result of previous SE queries. The SE cache is unaware of the original DAX query.

Cool

That is an innovative way of explaining the difference between formula engine and storage engine using the book analogy! 👍 Question: I understand FE needs the SE results materialized In-Memory in an uncompressed form to do it's computation. Is the same applicable to DirectQuery? Does the SQL output gets materialized in memory before formula engine acts on it? Also, if FE don't have to do any computation on SE results, still the materialization happens before sending the results to client ?

@SQLBI

3 жыл бұрын

Yes, the materialization always happens when SE moves data to FE, regardless of SE type (so DirectQuery does that, too) and FE processing (materialization happens even though there is no further calculation required).

@akhilannan

3 жыл бұрын

@@SQLBI Thanks for the response. Does this mean a simple Evaluate 'TableName' will have the results materialized In-Memory until the results are returned to the client?

@marcorusso7472

3 жыл бұрын

@@akhilannan Yes, definitely. For this reason, reports usually browse a window of rows in the result using TOPNSKIP.

Hi Marco, Is there any way we can optimize export to excel data from the Power BI report?

Thank you for taking up this topic. So when materialization happens that's when 1 million row limit is triggered with top 1000001. If there was no materialization then Direct query wouldn't have this limit.

@SQLBI

3 жыл бұрын

Correct!

@Bharath_PBI

3 жыл бұрын

@@SQLBI 1 question: Is there a way to avoid materialization and let the entire query execute in underlying SQL server (as with other bi tools) ? Cos, even though the final returned resultset is 10 rows, group by on a column in a dim, which has 1.5M rows on a selected date, it throws this 1M limit error. Users expect to see the data without filters at first.

Thank you @sqlbi for the amazing tutorial as always about SE and FE along with calculation groups. I have implemented the calculation for a report but the complex nature of the requirement is to have 3 layers of drill down at row level and 3 layers of drill down at column level and all of them have time intelligence e.g., MTD, QTD, YOY against Actuals, Budget and Actuals v Budget information per selected criteria e.g., Net sales, Gross Sales, PBT et cetera. The performance just drags completely and just fails later after hitting threshold. Could there be anything that can be done to improve the performance? The measures uses keep filters, selected measure with time intelligence calculation. Needless to say it is a matrix view. Any tip, suggestion would help.

@SQLBI

3 жыл бұрын

As usual, it depends. Analyzing performance issues requires deeper evaluation of query plans. Knowing the difference between SE and FE is just the starting point. We have several chapters in The Definitive Guide to DAX ( www.sqlbi.com/books/ ) and an entire videocourse ( www.sqlbi.com/p/optimizing-dax-video-course/ ) to walk through the required steps! There could be just too many reasons - for example, time intelligence mixed to distinct count measures or bidirectional filters could have a big impact, but it's just one of the many possible reasons...

If I am seeing 99% in the Formula Engine with 9 SE Queries but the total time is 87,357ms I know I have a problem. BUT where do I start looking? I assume its not a data issue due to the storage engine was fast (469ms) so I guess a DAX issue...?

@SQLBI

3 жыл бұрын

Probably yes. We have several chapters about DAX Optimization in The Definitive Guide to DAX (the book) and we have a specific video course about that. Links: www.sqlbi.com/p/optimizing-dax-video-course/ www.sqlbi.com/books/the-definitive-guide-to-dax-2nd-edition/