Forum Discussion

Venkat_Thota's avatar
Venkat_Thota
Frequent Visitor
1 year ago
Solved

How to Limit rows from Oracle source

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...
Need Help
  • tharunkumarRTK's avatar
    tharunkumarRTK
    1 year ago

    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!

     

Venkat_Thota's avatar
Venkat_Thota
Frequent Visitor
1 year ago

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.

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago
     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's avatar
      Venkat_Thota
      Frequent Visitor
      1 year ago

      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's avatar
    tharunkumarRTK
    Super User
    1 year ago

    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's avatar
      Venkat_Thota
      Frequent Visitor
      1 year ago

      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's avatar
        tharunkumarRTK
        Super User
        1 year ago

        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!