Passing value from Power BI to DENODO Source

[Narukkp]

Hi Team,

Can we pass the input value from power bi to source like how we pass the Parameters in  SSRS. My source is DENODO and it does not have any Advance query option to filter the data from source. In my source we have only read only permission so we are not able to create the multiple views. In my view having millions of records but do not required all those records and we need to fetch the records based on input like today, this month , this week, this year.

So instead of the fetching all milions of records can we pass the input value and fetch the records based on input value from data source ?

[Anonymous]

@Anonymous 

Can you please explain this with a simple example and screenshots if that's a possibility ?

Thank you! 

[Narukkp]

 Hi Experts,

Any one having idea about this please let me know. 

[Anonymous]

Hi,

I know its old thread but the issue is still alive thats why replying here 

i too face the same issue, you can do odbc connector for denodo to get advanced SQL editor and also can pass parameters to denodo but the limitation is storage method remains import. To say, passing parameters in direct query method for denodo seems to be a limitation atleast as per my knowledge 

[kmaurer8]

I'm also having this issue.  Without being able to have a DirectQuery connection to a parameterized view in Denodo I essentially can't use the view or PowerBI for this data.

If someone has an example Advanced Connection where this works that'd be great.  

[Anonymous]

Hi ,

I just noticed you can pass paramters without advanced SQL. Please check on query folding,

Use Denodo Connector ->Direct Mode -> Filter the desired Column in query editor and set parameter as an input to the filter 

This will actually do a query folding and generate a SQL to denodo with where condition. For relational database , you must be able to see native query generated for database but it will greyed out for denodo. It works on the backend 

Please push all calcuclations to denodo view since query folding will fail if you use most M transformations. Try to keep only passing where conditon from Power query

[kmaurer8]

My issue is that I can't even get the schema in the denodo connector because a parameter is required by the denodo view.  Power Query/M language doesn't even let me get to that point.  Was wondering if there was a way around that.  Can't believe something this simple is so difficult.

[Anonymous]

did you tried with optional params in denodo view? if not please do the steps

1. keep the params in base view (  ex: Range1)

2. Create similar params in integration view(Model Tab) and assign default vaules like Range2=currentdate etc.

3.Go to where condition and assign this params to the base view filters (which is a new column in integration view) like Range1= Range2

4.Now you have set optional params , connection to denodo view will not you error now and basically refresh for current date.

5.once you imported supply the where condition in M query using paramters like Range2 equals 'Your Parameter'

6.This will work fine for direct query and Import as well

Thanks,

Jay