PowerBI DirectQuery (extension) for Firebird ODBC - there is chance to build such extension

[alexand...@gmail.com]

Hi!

I can connect from Power BI Desktop to Firebird 2.1 UTF 8 Dialect 3 database using the data import feature. That is fine.

But I need to establish DirectQuery access. It is not possible to do this out-of-box with usual ODBC driver, but there are some hints that it is possible to build extension for every ODBC driver to enable DirectQuery access:

https://github.com/Microsoft/DataConnectors/blob/master/docs/m-extensions.md#next-steps

https://docs.microsoft.com/en-us/power-query/odbc

My question - has someone in the Firebird community done/tried to do this for Firebird ODBC? I prefer solution for Firebird 2.1, but any informaion and hints are welcome.

I have found that some commercial ODBC-DirectQuery drivers have already been built by Progress company:

https://www.progress.com/connectors#data-connectors

https://www.progress.com/odbc

But they have no drivers/connectors neither for Interbase nor for Firebird.

btw There is alredy one thread on Power BI in this forum https://groups.google.com/g/firebird-support/c/_-Ly0ipTXKk/m/b2_VuT4PAgAJ and there is suggestion that Firebird ODBC works badly with Power BI, however it is just suggestion and other participants don't corroborate it and more likely say the opposite.

So - if there are issues even with basic (imported) Firebird ODBC access from Power BI it would be nice to hear either.

Thanks in advance!

Alex

[Dimitry Sibiryakov]

alexand...@gmail.com wrote 06.09.2021 11:02:
> It is not possible to do this out-of-box with usual ODBC driver,

It is possible AFAIK.

> there is suggestion that Firebird ODBC works badly with Power BI

Actually it is PowerBI who works badly. The way it uses ODBC is completely
ridiculous and crazy.

--
WBR, SD.

[alexand...@gmail.com]

I would be greate, but I am afraid that some coding is necessary. When I choose from Power BI 'Get Data - Other - ODBC - Connect' then the dialog does not have 2 options for 'Data Connectivity Mode' ('Import', 'DirectQuery'), so, the ODBC dialog assumes that there is no choice of 'Data Connectivity Mode' and the 'Import' mode is the only mode supported.

So, I am no reading articles about M languge and I am trying to understand how hard it is to created connector for Firebird ODBC.

p.s. There is ADO.NET driver etc. and there is such driver for Firebird, but I don't see how to use it from Power BI, I guess, Power BI does not provide anything special for ADO.NET.

[Dimitry Sibiryakov]

alexand...@gmail.com wrote 06.09.2021 11:45:
> I would be greate, but I am afraid that some coding is necessary.

Yes, but AFAICS it is coding on PowerBI side, not ODBC side and is required
only for ODBC drivers and DBMS that doesn't support SQL standard (or better say
are different from MS SQL).

--
WBR, SD.

[alexand...@gmail.com]

I just wanted to document my experience so far:

1) I installed Visual Studio 2019 community edition, I used minimal install - with Python and Node.js development, no further features;

2) I downloaded source code https://github.com/microsoft/DataConnectors/tree/master/samples/ODBC/SqlODBC from https://github.com/microsoft/DataConnectors

3) I opened this code as new solution and immediatly build in Visual Studio. This created SqlODBC.mez

4) In Power BI Desktop 'Options and Settings - Options - Global - Security - Data Extensions' I selected '(Not Recommended) Allow any extension to load without validation or warning'

5) In 'My Documents' I have created directory '\Power BI Desktop\Custom Connectors' and I have copied SqlODBC.mez there.

6) I could see my know connect after restart of Power BI Desktop and there was dialog for entering the 'server' and possibility to select 'DirectQuery' 'Data Connectivity mode'. I provided the usual Firebird connection string, but then the error message appeared:

Unable to connect

We encountered an error while trying to connect.

Details: "The 'Driver' property with value '{....}' doesn't correspond to an installed ODBC driver"

So, now I am digging futher, how to handle it.

Here is Microsoft documentatio which suggest the create of connect/extension of existing ODBC driver:

https://docs.microsoft.com/en-us/power-query/odbc

If there are some suggestions or experience how to advance all this, it would be nice to hear.

[Dimitry Sibiryakov]

alexand...@gmail.com wrote 06.09.2021 15:49:
> Details: "The 'Driver' property with value '{....}' doesn't correspond to an
> installed ODBC driver"

You must enter full Firebird ODBC driver name (which is "Firebird ODBC
driver") as seen in ODBC Administrator utility here instead of dots.

--
WBR, SD.

[alexand...@gmail.com]

OK, I have corrected the code in SqlODBC.pq to:

// The name of your ODBC driver.
//
Config_DriverName = "Firebird/InterBase(r) driver";

Now I was able to arrive at the dialog where the username/password should be entered.

Before that I was required to enter ODBC connection string which I provided as:

// The name of your ODBC driver.
//
User=SYSDBA;Password=masterkey;Database=D:\DB\ERP.FDB;DataSource=192.168.1.3;Port=3050;Dialect=3;Charset=UTF8;Role=;Connection lifetime=15;Pooling=true;MinPoolSize=0;MaxPoolSize=50;Packet Size=8192;ServerType=0;Initial Catalog=test;

But, sadly, this (any many other attempts and variants) caused the error message:

Unable to connect

We encountered an error while trying to connect.

Details: "ODBC: ERROR [08004] [ODBC Firebird Driver]connection lost to database

ERROR [01S00] [ODBC Firebird Driver]Invalid connection string attribute

ERROR [01S00] [ODBC Firebird Driver]Invalid connection string attribute"

The message is quite generic and currently no known and rational path forward is known... The error message is coming from ODBC or other layers and it is not from the M Language code (Visual Studio project) in which the connector is being developed.

Alex

[alexand...@gmail.com]

As I said, I am base my code on https://github.com/microsoft/DataConnectors/tree/master/samples/ODBC/SqlODBC and it is the code of MS SQL ODBC driver.

This function (from https://github.com/microsoft/DataConnectors/blob/master/samples/ODBC/SqlODBC/SqlODBC.pq):
[DataSource.Kind="SqlODBC", Publish="SqlODBC.Publish"]

shared SqlODBC.Contents = (server as text, optional options as record) =>

is used by the connector to decipher the ODBC connection string into variables that are submitted to the underlying ODBC driver. Maybe this function decipher ODBC string in MSSQL style and puts data into MSSQL style variables and not into Firebird style ODBC connection string parameters. And that is why the error happend.

So, unfortunately, such connector development is almost as hard as ODBC driver development and require almost all the same knowledge...

[Dimitry Sibiryakov]

alexand...@gmail.com wrote 07.09.2021 8:06:
> But, sadly, this (any many other attempts and variants) caused the error message:
>
> Unable to connect
> We encountered an error while trying to connect.
>
> Details: "ODBC: ERROR [08004] [ODBC Firebird Driver]connection lost to database
>
> ERROR [01S00] [ODBC Firebird Driver]Invalid connection string attribute

List of recognized connection attributes is listed in ODBC driver's
documentation and it includes neither "Port" nor "Datasource" (and a half of
others as well).

--
WBR, SD.