question

ManojMuthusamy-2751 avatar image
0 Votes"
ManojMuthusamy-2751 asked LukasYu-msft commented

Recommended provider for Snowflake DW Connectivity

Hi -

Am looking for Microsoft recommended providers for establishing datasource connection with Snowflake Data warehouse via SQL Server Analysis Services Tabular 2016 (On-prem).

Came across articles suggesting CData providers enabling this set up but with SSAS MultiDim.

Would like to know if anyone has success in configuring this -> SSAS Tabular 2016 sourced with Snowflake Data warehouse.

Thanks

sql-server-analysis-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered LukasYu-msft commented

Hi,

CData Provider is a commercial third party tool, it should work and be supported by their team.


From the Snowflake documentation, I would suggest you to use ODBC driver for SSAS to connect to Snowflake. This should be a reliable way.

The documentation you could find here :Connecting to Snowflake

Regards,
Lukas



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your response.

0 Votes 0 ·

Hi,

What finally did you choose ,did you solve this issue ?
Could we help further anything else on this ?

0 Votes 0 ·
ManojMuthusamy-2751 avatar image
0 Votes"
ManojMuthusamy-2751 answered LukasYu-msft commented

Hi @LukasYu-msft

Sorry for getting back late. Am kind of stuck with testing the connection. I have the 'Snowflake ODBC 32/64 bit' driver installed and configured as 'System DSN' in local with Snowflake Service Account. (By default snowflake driver does not save password, so had to update the 'Registry Editor' to get the password saved)

Now in Visual Studio 2017 (SSDT), when i try to create a sample tabular model to test the connection/import am faced with the below error -
/
Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application, IM014.
A connection could not be made to the data source with the Name of 'SF_Connection'.
/

It is with 'Integrated Workspace Mode - True' and impersonation as 'Service Account'. I understand this is due to the impersonation i have provided as i could see the query getting parsed/previewed but failed while importing.

Thanks,

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I suspect the error is due to the authentication process, that the snowflake dw could not authenticate and varify SSAS connection.

I am honestly sorry here I could not help directly on this, since I don't have any snow flake test environment nor doing any test.

0 Votes 0 ·