Be careful to SSAS caching
👤 Marco Pozzan | Modified: May 8th, 2023|SQL | 1 Minute Reading
We take the following scheme in Power BI
below are a screen shots illustrating what’s going (using sql profiler) when creating a simple report query in Power BI
observe that the (select *) behavior that occurs on all subqueries (even if one column was requested in a report query). The the select * is an intentional SSAS decision to take advantage of caching. If the data volumes are big, the cache never live long so it is a detriment rather than a help.
Shows the statement (select *) is even worse for a snowflake design as it traverses every table with a (select *) to take to the category name in the product table: