Integrations and APIs for Service

Get Involved. Join the Conversation.

Topic

    Charlie Mopps
    RunAnalyticsReport and PHP scripts
    Topic posted June 8, 2015 by Charlie MoppsGold Medal: 3,500+ Points 
    417 Views, 2 Comments
    Title:
    RunAnalyticsReport and PHP scripts
    Content:

    We've a need to query the Transactions table. This table is not visible via the API, as a result we're having to call it using a report. So we're using RunAnalyticsReport. BUT, this table does not have a unique key and Oracle wont add one. So I used a PHP script to create an MD5 hash of the entire row:

    $field = $rows[0][1]->val.$rows[0][2]->val.$rows[0][3]->val.$rows[0][4]->val; $rows[0][0]->val = md5($field);

    etc... this concat's the entire row and then turns it into a hash. It's not perfect but it'll serve our needs. However, when calling the report using RunAnalyticsReport, the PHP doesn't fire. Does anyone know if it's possible to get a php script to fire on a report being called with RunAnalyticsReport? Or am I going to have to pull the entire row down remotely and create the key there?

    Comment

     

    • Allan Schrum

      Custom script execution is not supported by the Connect PHP RunAnalyticsReport API. As you are running the report within a script you'll need to manipulate the data within that script to reflect whatever values you need.

      As far as adding a PK to the transactions table, we are working on that. If you have business reasons to help support this effort, please post them to the Idea Lab. The issue is that this table is huge and significant changes to this type of table can interfere with the upgrade process. We want that process to be as smooth as possible encouraging more customers to join the Auto Upgrade Program.

      We do support some transaction entries via ROQL for some objects (e.g. Contact, Incident). Could you do a direct query using ROQL for your use case?

      Regards,

      -Allan

    • Charlie Mopps

      This is part of our ODBC replacement project. So we're trying to replicate the entire table locally. It's obviously likely that our queries crash at some point during the 100 million+ rows of initial load. So we need a unique key so we won't duplicate data.

      Since there isn't a unique key, and there's no way to get a unique key, we're going to declare the entire row as unique, and if it happens to not be unique we'll just let it error on the unique constraint and toss out the duplicate row (did we really need it anyway?)

      Unfortunately the size of the primary key when we concat the entire row is problematic. So we thought to reduce its size with an MD5 hash. This isn't perfect, a 32 bit hash isn't entirely unique, but it's mathematically unlikely that we'd get a collision.

      Unfortunately, any change you were to make in a future release as a result of something posted to the idea lab isn't going to help us. You're turning off ODBC in November and we're in a time crunch. We have reporting that cannot lose access to the data. If there's no way to add a unique key, or fire a php script to create one, we're going to just have to accept that this is going to be messy and let our reporting folks know that the data is not perfect.