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

1
votes

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

2
odbcssassnowflake-cloud-data-platform

2 Answers

2
votes

These are SAS options, not ODBC or Snowflake.

For the load issue, ODBC row by row inserts is typically a slow approach.

Is there an option for you to export the data to S3 or Blob and then bulk load in SSAS before running your models - or alternatively could you push down the queries to Snowflake itself?

1
votes

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.