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.
Hi,
I have found how to use parameter in query, like this:
let Source = Oracle.Database("SYNCDWP", [Query="SELECT MGROUP,COL2,COL3 #(lf) FROM MY_BIG_TABLE#(lf)WHERE MGROUP = '"&PAR_GROUP&"'", HierarchicalNavigation=true]) in Source
The Power BI replaces the parameter PAR_GROUP with the value and executes the query.
1. First problem is, that for every new parameter value I have to give permission for executing.
2. The second is, that is not bind variable used in Oracle, but constant. In this way, Oracle will parse the query again and again
for every new parameter value and there will be a different execution plan as in the case of bind variable using.
Did I use the parameters in the wrong way?
Is there a way to use real bind variables with Oracle queries?
Where can I find more about the language (and functions) used in Advanced Editor?
Best Regards
Solved! Go to Solution.
@Mottor wrote:
Hi,
I have found how to use parameter in query, like this:
let Source = Oracle.Database("SYNCDWP", [Query="SELECT MGROUP,COL2,COL3 #(lf) FROM MY_BIG_TABLE#(lf)WHERE MGROUP = '"&PAR_GROUP&"'", HierarchicalNavigation=true]) in SourceThe Power BI replaces the parameter PAR_GROUP with the value and executes the query.
1. First problem is, that for every new parameter value I have to give permission for executing.
2. The second is, that is not bind variable used in Oracle, but constant. In this way, Oracle will parse the query again and again
for every new parameter value and there will be a different execution plan as in the case of bind variable using.
Did I use the parameters in the wrong way?
Is there a way to use real bind variables with Oracle queries?
Where can I find more about the language (and functions) used in Advanced Editor?
Best Regards
@Mottor wrote:
Hi,
I have found how to use parameter in query, like this:
let Source = Oracle.Database("SYNCDWP", [Query="SELECT MGROUP,COL2,COL3 #(lf) FROM MY_BIG_TABLE#(lf)WHERE MGROUP = '"&PAR_GROUP&"'", HierarchicalNavigation=true]) in SourceThe Power BI replaces the parameter PAR_GROUP with the value and executes the query.
1. First problem is, that for every new parameter value I have to give permission for executing.
2. The second is, that is not bind variable used in Oracle, but constant. In this way, Oracle will parse the query again and again
for every new parameter value and there will be a different execution plan as in the case of bind variable using.
Did I use the parameters in the wrong way?
Is there a way to use real bind variables with Oracle queries?
Where can I find more about the language (and functions) used in Advanced Editor?
Best Regards
Hi @Eric_Zhang,
1. Thank you
2. The 40 queries which I have are two pages each and have up to 7 parameters. I don't want to post them here. Parsing takes time when query is complex. But is not too much or critical. But when constants are used instead of bind variables the query is seen as new and it is cached with the execution plan. Does not mather. I will use stored procedures, like you suggested. Thank you
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 |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |