PostgreSQL data source support

[ish1mura]

Hello! We are using PostgreSQL data sources in our tabular model. SQL Server Analysis Services uses NpgSQL library (only 4.0.10 version), which must be installed on server and developer computer. (https://github.com/npgsql/npgsql/releases?q=4.0.10) Visual Studio plugin for SSAS correctly uses NpgSQL and we can import and change PostgreSQL tables in model.

There is and error in TE3 when import new tables from PostgreSQL data source through NpgSQL (same error message when using ODBC driver):

Exception message: Object reference not set to an instance of an object.
Exception source: TabularEditor3.Shared
Stack trace:    at TabularEditor.Shared.Services.TableImportService.GetImportSettingsFromSource(VirtualDataSource dataSource)
   at TabularEditor.Shared.Services.VirtualDataSourceService.GetTableImportSettings(VirtualDataSource dataSource)
   at TabularEditor.UI.Dialogs.ImportTableWizard.IsNativeQuerySupported()
   at TabularEditor.UI.Dialogs.ImportTableWizard.pageDataSourceOptions_PageInit(Object sender, EventArgs e)

And Update table schema on existsing table:

Unable to detect schema changes on table '<table_name>': Unknown identifier: '<data_source_name>'

[otykier]

Which version of Tabular Editor 3 are you using?

[ish1mura]

@otykier 3.3.8350.24632

[otykier]

Hi @ish1mura

Apologies for the delayed response. We have looked into this issue, and so far we've found that importing tables from PostgreSQL using either Legacy or Structured data sources with ODBC and the psqlodbc driver works fine - we only need to make sure that "Reduce rows using" is set to LIMIT, for the table preview to work:

Note that this testing was performed on version 3.4.0 - there were some issues with the Import Table Wizard and ODBC data sources in previous versions, which may have impacted you.

Unfortunately, we were not able to test the Npgsql scenario that you described. It would be very helpful if you could provide step-by-step details on how to reach the error shown in your first screenshot. I.e. after installing the Npgsql driver on the development machine, which steps are you taking in the Import Table Wizard within Tabular Editor 3?

Also, it would be useful if you could share the full M query of one of your tables along with the JSON definition of the "pg_master" data source (please blank out any sensitive information such as server names, database names, usernames and passwords). If you are not comfortable sharing these details here, please feel free to send an e-mail to support@tabulareditor.com.

Thank you very much!

[ish1mura]

Hi! We are using NpgSQL because its an official supported PG driver for MS SSAS Tabular Projects. psqlodbc is much slower for reading in our case.

For using NpgSQL you must do: 1 ) Install NpgSQL<=4.0.10 with GAC (https://github.com/npgsql/npgsql/releases?q=4.0.10&expanded=true). It's important to install only this version of NpgSQL. 2 ) Create new Tabular project 3 ) Create PostgreSQL data source:

      {
        "type": "structured",
        "name": "pg_master",
        "maxConnections": 20,
        "connectionDetails": {
          "protocol": "postgresql",
          "address": {
            "server": "pg_master.server",
            "database": "pg_master"
          },
          "authentication": null,
          "query": null
        },
        "options": {
          "commandTimeout": "PT2H"
        },
        "credential": {
          "AuthenticationKind": "UsernamePassword",
          "kind": "PostgreSQL",
          "path": "pg_master.server;pg_master",
          "Username": "user_user",
          "EncryptConnection": true,
          "PrivacySetting": "Public"
        }
      },

4 ) Import new table with wizard - you will get errors from above 5 ) PowerQueryM:

let
    Source = #"pg_master",
    olap_v_discount = Source{[Schema="olap",Item="v_discount"]}[Data],
    #"Renamed Columns" = Table.RenameColumns(olap_v_discount,{{"discount_id", "Акция Код"}, {"discount_name", "Акция"}, {"type_name", "Акция Тип"}})
in
    "Renamed Columns"