Forum Discussion

rudoce's avatar
rudoce
Frequent Visitor
4 years ago
Solved

Direct Query unexpected behaviour

Hello to you all. We are facing a problem modelling a star with a very big fact table (>13.000 Millions of rows) and 5 dimensions linked using common Kimball approach. Those dimensions feed filters ...
Need Help
  • rudoce's avatar
    rudoce
    3 years ago

    Hello frtcndkm.

    We solved the issue avoiding the sql overwriting in the native query when accessing the source data.  Instead of using a native query, we pointed to the fact table using the UI and then we selected the required columns and applied the transformations needed.

    I know it is anoying and something Power BI must work on, but is the only workaround I found.

    To stick with query folding and better performance, I recommend you to create views in your source system with the required queries and then access them from Power BI using the navigation widget.

v-chenwuz-msft's avatar
v-chenwuz-msft
Community Support
4 years ago

Hi rudoce ,

 

Maybe you can try Advanced options SQL statement when you connect the Snowflake.

 

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

rudoce's avatar
rudoce
Frequent Visitor
4 years ago

Hi, chenwu zhu, and thanks for your reply.

 

Actually, we are doing so in both fact and dimension tables. We are writing sql commands in advanced options where we just select the columns we need.

 

We do not understand why Power BI sends to Snowflake queries where it selects the whole table with a limit 1 clause. Looks like it checks the model by doing so... but with massive tables obviously the performance is very poor and most of the times the report fails.

 

We have designed the same scenario with Microstrategy and it works fine...