DirectQuery - Very inefficient queries being generated to Snowflake

Question

I am connecting PBI to Snowflake using DirectQuery. To keep it simple, I have two tables, a product dimension table and a sales fact table. There are 3.7M rows in the product dimension table and 100M in the sales fact table. I also have a measure that calculates total sales which uses SUM to sum a column in the fact table.

I create a table visual in PBI and put the product description as the first column. The query generated by PBI is good. It retrieves 501 rows and displays them. So far, so good. Next I put the total sales measure as the second column. Now PBI generates several queries retrieving 1,000,001 rows. Of course I get an error stating the 1M row limit for DirectQuery has been reached.

This should not be happening. Has anyone run into something like this? Is there anything I can do?

I do not have experience with Snowflake, but it looks like the query folding doesn't work. Try to find why it doesn't work in your case - check how you connect to the datasource, what transformations are made, etc. — Andrey Nikolov
– Andrey Nikolov, Commented Sep 23, 2021 at 5:41
Thanks Andrey - I know it's not a transformation affecting query folding issue because I'm not doing any transformations on the dimension table. — Randy Minder
– Randy Minder, Commented Sep 23, 2021 at 12:34

patrick_at_snowflake · Accepted Answer · 2021-09-23 00:45:13Z

-1

I had a dig around and there is a capability to adjust the limit if you have a premium license

https://powerbi.microsoft.com/en-gb/blog/five-new-power-bi-premium-capacity-settings-is-available-on-the-portal-preloaded-with-default-values-admin-can-review-and-override-the-defaults-with-their-preference-to-better-fence-their-capacity/

answered Sep 23, 2021 at 0:45

patrick_at_snowflake

4634 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Randy Minder Over a year ago

You missed the point. I never said I wanted to increase the limit. The queries being sent to Snowflake are very suboptimal.

patrick_at_snowflake Over a year ago

no i didn't miss the point, if you are pushing the limit then you need to aggregate before grabbing the result. Pretty standard really.

Collectives™ on Stack Overflow

DirectQuery - Very inefficient queries being generated to Snowflake

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related