Forum Discussion

gvinokur's avatar
gvinokur
Regular Visitor
3 years ago

PowerBI Report Builder query parameters

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 value...
Need Help
gvinokur's avatar
gvinokur
Regular Visitor
3 years ago

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's avatar
keping
New Member
3 years ago

Hi gvinokur.

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

  • gvinokur's avatar
    gvinokur
    Regular Visitor
    3 years ago

    Hi keping, no I haven't got solution

      • gvinokur's avatar
        gvinokur
        Regular Visitor
        3 years ago

        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...