connecting to Snowflake via ODBC64, Snowflake's drivers.

[cosminioan-sonos]

hi folks, I have Snowflake ODBC64bit drivers (3.00.02.00) as a connector to my latest Tabular Editor (3.9.0, 64bit) -- I'm trying to save model post importing tables from snowflake, but get the following error:

again, I'm able to connect to snowflake, it's even importing successfully the tables, it's just that I cannot save nor process the tables. Showing the error also, when opening the project.

Am I missing anything elementary?

thank you kindly for any insight,
Cos

[otykier]

Hi Cos,
Is this on an Azure Analysis Services or SQL Server Analysis Services model?
I believe you have to use a Legacy Data Source, for ODBC connections using MSDASQL to work correctly, however, this is not a scenario I have much experience with. I recommend setting up a dummy data model using Visual Studio, so you can see how the data source should be configured for the connection to Snowflake to work correctly. Then, once that works, you can copy the data source over using Tabular Editor, or simply use Tabular Editor to pick up after leaving Visual Studio.

[otykier]

not sure how to copy the data source over using TE, or how to use TE to pick up after leaving VS, but they are both using the same ODBC 64 snowflake driver.

After you create the model with VS, either connect TE to the VS workspace database, or open the Model.bim file produced by VS in TE. Then, inspect the data source in TE. Compare it to the one from the model you attempted to create from scratch using TE. Most likely you’re missing some property or something might be configured differently.

[cosminioan-sonos]

I tried both opening up (within TE3) the Model.bim from Visual Studio (compat 1500) and opening up the workspace created by VS. Unfortunately, neither recreated the datasource nor the table (that I had in VS) within the TE3 GUI. stumped... but see the PWD vs password keywords later in my followup... I think that may be a key to resolve this issue...

[cosminioan-sonos]

hi Daniel, I am using SQL Server Analysis Services 2017.
Setting up a similar Visual Studio 2017 project consuming the ODBC snowflake datasource worked fine, it imported the data fine and processed it without an error as well. I just had to supply the username and password, once the original ODBC64 connection was set up.

verifying the "legacy data source" type connectivity and checking further...

From the error screenshot in the original message, I get a feeling that some commands are not made through ODBC but only through the .Net framework, but I'm seeing your latest dll supplied with the installation.

<<Then, once that works, you can copy the data source over using Tabular Editor, or simply use Tabular Editor to pick up after leaving Visual Studio.>> not sure how to copy the data source over using TE, or how to use TE to pick up after leaving VS, but they are both using the same ODBC 64 snowflake driver.

Is there any way to debug/troubleshoot this further as it's fairly critical for us, as we're moving away from SQL Server.
thanks kindly for any feedback,
Cos

[cosminioan-sonos]

ok... changing to "legacy data source" did the trick... almost.
But I think there's still a programmatic error capturing the column for the password, somewhere in TE connectivity, because it was complaining that it cannot find the "PWD" keyword. Once I changed the "password" keyword to PWD, it worked OK, but I still cannot process the tables (I can import them successfully). When I try to process tables I get only this non-descript error:

any thoughts? -- would be best a more descript error message about the connectivity error.

[cosminioan-sonos]

this is what I'm initially getting as error, I think there's a confusion somewhere between PWD and password keywords.

If, however, I put the PWD=XXX in the connection string, it continues fine and it allows me to import tables successfully.
Upon saving however, and trying to reprocess the newly imported table, I feel it goes back to asking for the password keyword rather than PWD, but like I mentioned above, the error does not seem to be descript enough -- so this is just a hypothesis, at this point.

[cosminioan-sonos]

pls close this ticket. Apparently, with ODBC drivers, first need to SAVE and Deploy without processing and then process tables subsequently. Works like a champ now.

[otykier]

Hi @cosminioan-sonos - thanks for sharing these additional details. We're working on adding detailed documentation for importing tables from various data sources, and will make sure to include your observations. Thanks!