4

I am trying to figure out a workaround for refreshing dataset with Custom functions over Power BI service. My query looks like the following:

Data source for _op_kiekis

let
    Source = Loginai_File,
    #"Invoked Custom Function" = Table.AddColumn(Source, "Q_Sybase_2_Op_Kiekis", each Q_Sybase_2_Op_Kiekis([Source], [IP], [PORT], [DB_name]))

in
    #"Invoked Custom Function"

Data source for _eil_sk

let
    Source = Loginai_File,
    #"Invoked Custom Function" = Table.AddColumn(Source, "Q_Sybase_1_Eil_Sk_2", each Q_Sybase_1_Eil_Sk_2([Source], [IP], [PORT], [DB_name]))
in
    #"Invoked Custom Function"

Loginai_File

let
    Source = Excel.Workbook(File.Contents(PathToLoginai & Loginai_File_Name), null, true),
    dbs = Source{[Name="Sheet1"]}[Data],

in
    dbs

Q_Sybase_2_Op_Kiekis

(Name, strSource, Ip , Port, dBase ) =>

let

    Source = Sybase.Database(Ip & ":" & Number.ToText(Port), dBase, [Query="select [_].[Count] from [DBA].[dbs] [_] where [_].[DID_DAT] >= '" & sFilterDate & "' order by [DID_DAT]"])
in
    Source

Q_Sybase_1_Eil_Sk_2

(Name, strSource, Ip , Port, dBase ) =>

let

    Source = Sybase.Database(Ip & ":" & Number.ToText(Port), dBase, [Query="select [_].[Count] from [DBA].[dbs2] [_] where [_].[DID_DAT] >= '" & sFilterDate & "' order by [DID_DAT]"])
in
    Source

This works fine on Power BI desktop. However, I am getting following error on Power BI service: enter image description here

Dmitrij Holkin
  • 1,995
  • 3
  • 39
  • 86
  • Are you using a Gateway to connect to the database? If not, this might be the issue why it works in Desktop but not in Service. – VBA Pete Aug 20 '20 at 07:21
  • ofcourse i use gateway, it think problem with auth to database, as in Desktop version i put credential manually, but PBI service cant do that – Dmitrij Holkin Aug 20 '20 at 08:20
  • both of the problematic functions use `Loginai_File` which references a file stored locally on your pc. If you replace that step with a static value does it allow you to refresh? – Stachu Aug 26 '20 at 08:37
  • where is your excelfile located? and does your gateway have access to it? to provide creds for your gateway you can input them under the "manage gateway" section of the pbi service – Nikolaj Klitlund Børty Aug 26 '20 at 12:13
  • Stachu - NO @Nikolaj Klitlund Børty, Localy, Gateway have access, problems with auth, as multiple databases accessed, and each need provide credentials by gateway, i cant manage gateway, as i have > 100 databases in Loginai_File, which has different credentials – Dmitrij Holkin Aug 26 '20 at 12:23
  • @DmitrijHolkin So, if I understand correctly, you have it set up so that the custom function will select the proper credentials for a connection from the excel file? And those selected credentials are then used to connect to the required data source? – Mistella Aug 26 '20 at 12:49
  • @Mistella Not exactly, from excel file it take only IP, port number and database name, as i cant find a way put also username and password for each database. So in desktop version PBI dont ask me for credentials (username and password) only once, probably what now i using same credentials for all databases – Dmitrij Holkin Aug 26 '20 at 17:57
  • @DmitrijHolkin You may want to try creating a basic report in the Power Bi Service to verify that the service is able to get data from the Sybase Database. From the error message you included, it's possible the issue is with the Sybase connection through the service. – Mistella Aug 28 '20 at 14:03
  • service is able to get data from the Sybase Database, already created a basic report in the Power Bi Service and it work fine – Dmitrij Holkin Sep 01 '20 at 19:32

1 Answers1

0

You need a SQL Server wrapper: PowerBI doesnt play nicely with Sybase but you have a work around that will fully support refreshes where Sybase is actually the back-end datastore:

  1. Setup SQL Server and add it to your Datagateway
  2. On your SQL Server add a linked Server Connection to your Sybase Servers from your new SQL Server. https://www.sqlservercentral.com/articles/connect-to-sybase-with-a-linked-server
  3. In PowerBI connect as a SQL Server data source and access the linked servers you setup on your SQL Server to get to the Sybase data.
vvvv4d
  • 3,881
  • 1
  • 14
  • 18
  • So how to put different credentials for each database? – Dmitrij Holkin Sep 09 '20 at 12:51
  • 1
    @DmitrijHolkin you 'd set the credentials the connection when you add it to the data gateway. On the data gateway you'll just be adding a connection to the SQL Server that has the linked servers. On the SQL Server you'll have several different linked server connections to wherever you have your Sybase server+dbs. When you setup the data gateway and the linked servers, both are separate tasks, in both though, you are required to set credentials, a username and password. – vvvv4d Sep 09 '20 at 20:22
  • it is too complicated, and i need create linked server for each sybase db, i have it over 100 – Dmitrij Holkin Sep 17 '20 at 05:37