Search code examples
odbcssassnowflake-cloud-data-platform

Using Snowflake database as a SSAS (SQL Server Analysis Services) datasource?

We "need" to feed our SSAS Tabular models with Snowflake data (through ODBC because we have no Snowflake connector).

We tried with SQL Server 2016 and SQL Server 2017 and get atrocious performance (few rows a second...).

Under PowerBI, there is a Snowflake connector and it's fast.

I came across a thread from someone having an apparently similar speed problem when trying to feed Snowflake data to SAS.

He seems to have solved his problem by specifying some (ODBC?) parameters:

  • dbcommit = 10000
  • autocommit = no
  • readbuff = 200
  • insertbuff = 200

Are these parameters specific to Snowflake? Or just ODBC?

Thanks

Eric

Solution

  • Instructions for SSAS Tabular models in Azure Analysis Services are here. Everything except the On Premises Data Gateway should apply to you.

    Make sure you check whether tracing=0 in your ODBC data source as tracing=6 killed performance for me.