Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Snowflake - disable cache (USE_CACHED_RESULT = FALSE)?

Hi!

 

I'm trying to test true performance of a report and wondering it it's possible to disable Snowflake cache in Power BI connection -> USE_CACHED_RESULT = FALSE?

 

https://docs.snowflake.net/manuals/sql-reference/parameters.html#use-cached-result

 

Couldn't figure out how disabling cache could be included in query if it's possible at all...

 

let
Source = Snowflake.Databases("****.west-europe.azure.snowflakecomputing.com", "****_WH", []),
****_Database = Source{[Name="****_DB",Kind="Database"]}[Data],
****_Schema = ****_Database{[Name="****",Kind="Schema"]}[Data],
****_Table = ****_Schema{[Name="****",Kind="Table"]}[Data]
in
****_Table

 

BR,

Mika Kouvo

Tutorial Requests

3 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Mika,

     

    It seems we can't do that within a connection. 

    1. As we can see from your link, it's a server-side parameter. "Can be set for Account » User » Session".

    2. The Snowflake connector is based on Snowflake ODBC Driver. As we can see from #connection-parameters, no such connection string can be sent through ODBC connection.

     

     

    Best Regards,
    Dale

    • jersey417's avatar
      jersey417
      Icon for Helper I rankHelper I
      4 years ago

      v-jiascu-msft 

       

      Is this still a limitation with the new connector that was release for Snowflake? We would like to use the Query_tag option with the Line 

      ALTER SESSION SET QUERY_TAG = 'Workspace name - Report name';

      However, we've been unable to get this to work. Leveraging the query_tag option within the query would allow us to allocate Snowflake costs to the workspace/report level. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    You can’t change it directly through PowerBI, but as a work-around, you can change the default setting of the user account attached to PowerBI in Snowflake with:

     

    ALTER ACCOUNT SET USE_CACHED_RESULT = FALSE

     

    Make sure you are in the right context as you have to be an ACCOUNTADMIN to change these settings.

     

    Check that the changes worked with:

     

    SHOW PARAMETERS

     

    Be careful with this though, remember to turn on USE_CACHED_RESULT after you’re done your testing.

     

    Hope this helped!