DATE_DIM tables and Direct Query

[Jack_W_KY]

In my org, we default all cloud data sources to direct query.  My group is sourcing our data in Snowflake sitting on Azure.  We need to use date heirarchy logic in several reports we've built and we created a DATE_DIM table using DAX and loaded that onto the service to provide that solution.

We are seeing really poor performance with this option.  In function, it can add 40 seconds of time for a visual to render.  I've used this solution when working in Oracle and in Databricks with previous companies and did not see such an impact.  What am I not aware of that could be causing this?  I'm exploring options with our IT department to create a DATE_DIM table in Snowflake but in the mean time is there another solution I should try?

[v-sdhruv]

Hi @Jack_W_KY ,

Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @Jack_W_KY ,

Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @Jack_W_KY ,

Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv · Accepted solution]

Hi @Jack_W_KY ,
You can try these approaches-

Run Query diagnostics in Power BI desktop

Go to: Tools > Query Diagnostics > Start Diagnostics

Check logs for steps that take too long

If you see long durations between “Data retrieved” and “Data displayed” — it's not Snowflake, it’s post-processing or service-side bottleneck

Try:
1.Temporarily using Import Mode for DATE_DIM Only (if Composite Models are enabled)
2.Move DATE_DIM into Snowflake
3.Reduce number of visuals, filters, bookmarks
4.Test different gateway config
5.Reduce result set by limiting rows/columns

If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[collinq]

Hi @Jack_W_KY ,

I am not sure that I have seen this problem before.  I am wondering if you have a connection issue with Snowflake that might be slowing things down?  I am also wondering if you have Kerberos security or something that might be slowing it down?

You might want to check the Perfomance Analyzer to see if it helps you find the spot that is an issue.  Or, perhaps use Fiddler to see if it is a network issue.

[Jack_W_KY]

When I look at the performance analyzer I see the DAX and Direct Query are only a few seconds off each other in total run time.  When our IT group pulls the queries sent back to Snowflake, the Direct Query is executing in less than 3 seconds of server time. 

Our gateway server is an Onprem solution.  The Admin of that server set the gateway server up so it doesn't cache the data before sending it back to the service in hopes of improving overall processing but I'm still getting poor results in a handfull of reports.