Integrations and APIs for Service

Get Involved. Join the Conversation.

Topic

    Charlie Mopps
    API to Report "Limit" not working as expected
    Topic posted June 12, 2015 by Charlie MoppsGold Medal: 3,500+ Points 
    133 Views, 5 Comments
    Title:
    API to Report "Limit" not working as expected
    Content:

    We're trying to export data from some large tables, like transactions.
     Basically the Object model doesn't work for dozens of reasons so we have to use the RunAnalyticsReport API call to call a report we've written in the application to retrieve the data.

    Unfortunately we're running into major problems with some of the tables. For example, "Transactions"...

    It doesn't have a unique Key or Single index for us to sort on.
    We can sort on date, but that's not unique at all.
    The API's row limit is 10,000
    But we have no way to limit the report itself to 10,000 records because there is no unique id.
    We can limit it by date, but we have no idea what date range to use.

    I've tried a number of ways to limit the report using "Record Limit" but this isn't a record limit at all. The function seems almost useless as it retrieves the data and then just truncates what was returned. So if you run a query that exceeds the reports limit, it errors out.
     

    Does anyone know how to put a true "limit" on a report?
    Does anyone know how to iterate through these large tables that don't have unique fields?
    Yes, we've asked to have a unique field added, Oracle wont do it.

    Comment

     

    • Darsen Prasad

      Jerry - 

      Report limit is applied after querying the records. So if you query tries to pull large number records it will fail even if you set low limits.

      Another ways is sort and pull data based on unique key, as you said transaction table doesn't have a unique key we won't be able to do that also.

      Can you check whether the product 'Progress Direct' supports querying data from transaction table? It is a replacement for ODBC connection to OSC.

      - Darz

       

    • Charlie Mopps

      Unfortunately I've personally tested Progress Direct offering... and no, it would definitely not help with this situation.

    • venkata balla

      Hi Jerry,

          We have the same requirement to get the data from transactions table.

          We have created the analytics report with 3 filters

          1. Created > specific time (05/03/2014 10.39 AM)

          2. Created < specific time (05/03/2014 10.40 AM)

          3. Table (tbl) = Queries

          From our code we are setting these filters dynamically and fetching the data, As we have very huge data i am setting the time intervals with the difference of just 1 minute.

          So in our application we always check what is the count of records returned, if it returns exactly 10,000 then we further split the times into smaller intervals(say for example 30 seconds) and bring the data, this way we don't miss any data.

         Hope this helps.

      Thank you

      Venkata

    • Charlie Mopps

      My problem is that when it hits 10,000 records, the report throws a join error and doesn't return any data. Further, I have over 10 years of data. Returning that in 1min increments isn't going to work. We're only allowed so many API calls per our contract. I'm not sure how valuable 10yrs worth of transaction data is so I'm currently trying to get the time frame requirements reduced.

    • Darsen Prasad

      Jerry,

      If you need to import 10 years of data, then I recommend getting a one time data export from Oracle.

      Please refer this document, for more details. After that, you can try following the logic explained by Venkata.

      - Darz