Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gckcmc
Resolver I
Resolver I

ODBC connection to Denodo with a mandatory parameter isn't working

Ok datanuts, I'm scratching my head.  I've been googling everywhere to answer what I assumed to be an easy novice question but no luck...so looking for help.

 

I have an ODBC-based connection to a denodo server.  That server has many services, and I can connect to them without issue.  My problem is that one of them has a mandatory parameter it requires a value for, and for the life of me I can't figure out how to provide it.

 

once I open the advanced editor, I have something that looks like this:

let
Source = Denodo.Contents("DenodoODBC", null),
worker_Database = Source{[Name="worker",Kind="Database"]}[Data],
worker_Schema = worker_Database{[Name="worker",Kind="Schema"]}[Data],
TheOneIWantToSee_View = worker_Schema{[Name="TheOneIWantToSee",Kind="View"]}[Data]
in
TheOneIWantToSee_View

 

I've tried using a WHERE in here, and I can't find any syntax documentation on being able to provide that in the view row.

 

I've tried going to the database properties, and adding in an SQL line, and also connection properties for this mandatory field, but nothing is working.

 

any suggestions?

1 ACCEPTED SOLUTION

so I eventually figured this out.  it was a syntax and quoting issue with VDL based language, as well as me trying to do a direct native query vs. using a linked server syntax (openquery style), which has lots of google'd reference syntax.  What I used, in case anyone else searches, is:

 

= Odbc.Query("dsn=DenodoODBC", "SELECT * FROM ""MyTable"" WHERE ""FieldN"" > 0")

View solution in original post

6 REPLIES 6
v-xicai
Community Support
Community Support

Hi @gckcmc ,

 

You can do this by using the table function Table.SelectRows in Power Query. Please refer to the linked Microsoft documentation. Reference: https://community.denodo.com/answers/question/details?questionId=9060g0000000BkrAAE&title=Required+P....

 

If you still have this issue for Power BI, you'd better create a support ticket in Power BI Support to get further help.

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi Amy,

 

Thanks.  I would usually use Table.SelectRows as a subsequent step after getting an initial table from the Source.  In this case, I can't filter after getting the source because it fails in the source.  The referenced link you provided to denodo community answers effectively the same thing, with very similar source to mine.  e.g.:

(just referencing that code in the link since it's the same problem as mine):

 

let
Quelle = Denodo.Contents(“DenodoODBCa”, null),
shopfloor_Database = Quelle{[Name=”shopfloor”,Kind=”Database”]}[Data],
shopfloor_Schema = shopfloor_Database{[Name=”shopfloor”,Kind=”Schema”]}[Data],
my_data_View = shopfloor_Schema{[Name=”my_data”,Kind=”View”]}[Data]
in
my_data_View

 

This is effectively the Navigation step post source connection to the database.  (which, without mandatory parameters, would yield a beginning table).

 

Are you suggesting to avoid the navigation step, and go directly to a Table.SelectRows in lieu of, or are you suggesting adding a Table.SelectRows in the code above? (which would act as the "WHERE") (like this:)

 

let
Quelle = Denodo.Contents(“DenodoODBCa”, null),
shopfloor_Database = Quelle{[Name=”shopfloor”,Kind=”Database”]}[Data],
shopfloor_Schema = shopfloor_Database{[Name=”shopfloor”,Kind=”Schema”]}[Data],
my_data_View = shopfloor_Schema{[Name=”my_data”,Kind=”View”]}[Data],

Table.SelectRows(my_data_View, each [someVariable] > 2
in
my_data_View

 

if so, I don't think that works....

Oops...realized why that wouldn't work...updated to:

 

let
Quelle = Denodo.Contents(“DenodoODBCa”, null),
shopfloor_Database = Quelle{[Name=”shopfloor”,Kind=”Database”]}[Data],
shopfloor_Schema = shopfloor_Database{[Name=”shopfloor”,Kind=”Schema”]}[Data],
my_data_View = shopfloor_Schema{[Name=”my_data”,Kind=”View”]}[Data],

FooBar = Table.SelectRows(my_data_View, each [someVariable] > 2
in
FooBar

 

ok so that passes syntax, but still gives me the same error that the "someVariable" effectively isn't being recognized as (I think) the my_data_View executes before the Select rows, and still wants to see the mandatory variable before it gets to the FooBar line.

 

Is there a way to put the Table.SelectRows as part of the Schema row, to pass in as a variable?

 

so I eventually figured this out.  it was a syntax and quoting issue with VDL based language, as well as me trying to do a direct native query vs. using a linked server syntax (openquery style), which has lots of google'd reference syntax.  What I used, in case anyone else searches, is:

 

= Odbc.Query("dsn=DenodoODBC", "SELECT * FROM ""MyTable"" WHERE ""FieldN"" > 0")

Anonymous
Not applicable

Hi there,

I am late here but worth discussing it

I am currently in the same scenerio now , use of ODBC connector instead of a Denodo connector would work here to pass parameters but wouldnt that make your query to Import instead of Direct ? I couldnt see an option to select storage type while using ODBC and i do not see a advance box for Denodo connector to enter custom query.

 

I think there is a limiation of using direct query and parameter together, please clarify 

 

Thanks

gckcmc
Resolver I
Resolver I

Any connection gurus out there?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.