PowerBI Report Builder query parameters

[gvinokur]

Hello people

   Could please someone help? 

I use PowerBI Report Builder with Data source Oracle Database(ODP.NET) Dataset based on Text query. Query is expecting text parameter with multiply values. These values passed in  national character set as in: cust_id IN (:customer_id) is passed as cust_id IN (N'31341234134kwer', N'3898798734asf').  Trouble is Oracle in this case converts cust_id to national character set and so doesn't use index based on cust_id. 

    How can I set PowerBI Report Builder to pass parameters as they are without N at the start so Oracle can use index in query?

[v-easonf-msft]

Hi, @gvinokur 

Not sure what you mean.

Can you share relevant screenshots to explain further?

Best Regards,
Community Support Team _ Eason

[gvinokur]

Hi @v-easonf-msft 

    Thanks for your reply. I attached here screenshots of data source, data set and parameter properties below. When I run report in PowerBI Report Builder it let's me pass many customer_id values. Because it executes long time I ran the following queries on Oracle db using SQL Developer:

select sid, sql_id, serial#, osuser

from SYS.GV_$SESSION

where username = 'user  name used in Datasource connection';

and

SELECT SQL_FULLTEXT

FROM   SYS.GV_$SQL

WHERE  SQL_ID = '4vwzntwmagup9';

SQL_ID value is taken from result given by first query.  It gave me text of SQL statement actually being passed to and run by Oracle.  It's in this SQL statement I saw WHERE condition became 

WHERE cust_id IN (N'31341234134kwer', N'3898798734asf')

So parameter's values have been passed in national character set. This causes Oracle to convert cust_id column value to national character set before checking and so prevents it from using index based on cust_id.

Please let me know if you need any more details.

Best Regards,

gvinokur

   

[keping]

Hi gvinokur.

Have you found a slution for that? We have axactly the same problem and still searching for a fix.

[gvinokur]

Hi keping, no I haven't got solution

[v-easonf-msft]

Hi, @gvinokur 

Please check if below tutorials could help:

Add a multi-value parameter to a paginated report 

All About Multi Value Parameters in Power BI Paginated Report | Tips and Tricks in Paginated Reports 

PAGINATED REPORT (MULTI VALUE PARAMETER) 

Best Regards,
Community Support Team _ Eason

[gvinokur]

Hi @v-easonf-msft

    Thank you for your suggestions. My problem however lies not in building report with multi-value parameter - I've done it as described in videos you linked. The problem is that when report is executing query on Oracle it passes to Oracle text string containing SQL statement. In that statement text parameters are listed with prefix N. Like I stated above: cust_id IN (N'31341234134kwer', N'3898798734asf'). That means parameter values have been given in national character set. Since cust_id is VARCHAR2 it's been converted to NVARCHAR2 before comparing with parameters values. That in turn is stopping Oracle from using index based on cust_id column and consequently causes performance problems.

    I am looking for a way to let PowerBI Report Bulder pass text parameters without N at the beginning of parameter value. It's got to be some Report Builder's configuration settings or at least I hope so...