Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I am connecting to oracle data source using a query.
select col1, col2, col3....colN from Table group by col1,col2,col3...colN
I am adding athe end 'fetch first 5000 rows only' to limit no of rows.
Now I wanted to make it as dynamic so I created a parameter in Power Query pLimit rows and added a step Keep First rows with parameter. I gave parameter value as 10,000.
All other sources like sharepoint, networkpath ...etc. working good and limiting to 10,000 rows. But for Oracle its not considering the Keep First rows step. its pulling whole 15M records on desktop and its cauing trouble for me to do fast development.
am I missing something here?
Solved! Go to Solution.
You can follow this synatx then:
let
DEVQUery = "select * from some_table fetch first" & parameterName &"rows only",
PRODQuery = "select * from some_table",
Query = if parameterName = 5000 then DEVQUery else PRODQuery,
Source = Oracle.Database(Server, [Query=Query])
in
Source
From service you can change the parameter value to anything but 5000. then you will be able to to do the full load.
please be informed you need to keep the parameter data type as whole number.
Need a Power BI Consultation? Hire me on Upwork
Connect on LinkedIn
|
@lbendlin Its a OLEDB connection, Not ODBC. Its a SQL query. So query fold is not working.
@tharunkumarRTK - I am trying to do this only but how this can be dynamic.
I put Keep first rows and put condition logic if its 0 then source else kept first row. This helps me in setting the parameter on serivce to 0 and on desktop to some number (50000).
how I can do that in parameter and in sql query. I am trying to add that logic in sql and its not working.
You dont need to add 'keep top rows' step.
Like I mentioned in my previous reply, first hardcode the number of rows in sql query. Once it is loaded in the power bi, open advanced editor and replace the number with your power query parameter. (Please look at my previous answer for example code).
After publishing your semantic model (Power BI dataset) to power bi service, you can open semantic model settings and under parameters you will find the parameter and you can modeify the number there.
Need a Power BI Consultation? Hire me on Upwork
Connect on LinkedIn
|
Sorry...I am not clear on your solution. On PBI Service, I wanted to do a full load, how your logic work on service. By changing number, I can increase the no of rows to load. YOu mean to say I have to give a big number like total no of rows. For example my table is having 30M records then I have to give 30M as the parameter value.
You can follow this synatx then:
let
DEVQUery = "select * from some_table fetch first" & parameterName &"rows only",
PRODQuery = "select * from some_table",
Query = if parameterName = 5000 then DEVQUery else PRODQuery,
Source = Oracle.Database(Server, [Query=Query])
in
Source
From service you can change the parameter value to anything but 5000. then you will be able to to do the full load.
please be informed you need to keep the parameter data type as whole number.
Need a Power BI Consultation? Hire me on Upwork
Connect on LinkedIn
|
Its a SQL query. So query fold is not working.
Huh? It being a SQL type data source is one of the core requirements for query folding.
I wanted to see the Native Query but its disabled. I read in this community only that for Oracle data source, query folding wont support.
I think query folding is not happening in your case. Instead of using 'Keep Top Rows' transformation, Use native query option.
Step 1: Create a parameter and assign the value as 10,000. (Do not forget to select the data type as whole number)
Step 2: Connect to your oracle database and in the advanced tab, put your SQL query and hard code the row limit 10,000.
select * from some_table fetch first 10000 rows only
Step 3: Open advanced edit advanced editor and replace 10000 with the parameter name. After the modification your m code should look similar to
let
Source = Oracle.Database(Server, [Query="select * from some_table fetch first" & parameterName &"rows only"])
in
Source
Need a Power BI Consultation? Hire me on Upwork
Connect on LinkedIn
|
Oracle driver or ODBC driver? Have you checked that the query folds?