Recommended provider for Snowflake DW Connectivity

Manoj Muthusamy 1 Reputation point
2021-02-22T16:58:28.997+00:00

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
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,256 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Lukas Yu -MSFT 5,816 Reputation points
    2021-02-23T02:18:36.563+00:00

    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. Manoj Muthusamy 1 Reputation point
    2021-03-08T04:32:38.347+00:00

    Hi @Lukas Yu -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,