Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Hi, @gvinokur
Not sure what you mean.
Can you share relevant screenshots to explain further?
Best Regards,
Community Support Team _ Eason
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
Hi gvinokur.
Have you found a slution for that? We have axactly the same problem and still searching for a fix.
Hi keping, no I haven't got solution
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
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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
8 | |
5 | |
4 | |
2 | |
2 |