How to Limit rows from Oracle source

[Venkat_Thota]

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?

[Venkat_Thota]

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

[tharunkumarRTK]

@Venkat_Thota 

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

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[Venkat_Thota]

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.

[tharunkumarRTK · Accepted solution]

@Venkat_Thota 

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

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[lbendlin]

 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.

[Venkat_Thota]

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. 

[tharunkumarRTK]

@Venkat_Thota 

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

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[lbendlin]

Oracle driver or ODBC driver?  Have you checked that the query folds?