4 Replies Latest reply: May 21, 2013 1:33 AM by Kedar Gupte RSS

    Plotting Trellis chart using ORE script..results in no data

    Kedar Gupte
      Hi,

      I'm intending to plot trellis charts in OBIEE using ORE. The sql script results in no data. Since trellis chart requires library(lattice) to be included, is there a method to include it or other workaround ?
      R script used is as follows
      begin
      sys.rqScriptDrop('HCM_TO_TRELLIS');
      sys.rqScriptCreate('HCM_TO_TRELLIS',
      'function(dat,plant,switchcase){
      ore.sync()
      ore.attach()
      > # Based on parameter passed either plot scatter or trellis
      >
      > if(switchcase=="single"){
      **Keeping it short: code to plot a scatter chart and return PNG as output**
      }else{
      **Keeping it short: code to plot Trellis chart and return PNG as output**
      }
      }
      ');
      end;
      ****sql to extract
      select id, image
      from table(rqTableEval(
      cursor(select * from HCM_SCATTER where JOB='Crane Operator'),
      cursor(select 1 "ore.connect", 'Mumbai' as "plant", 'trellis' as "switchcase" from dual),
      'PNG',
      'HCM_TO_TRELLIS'))
      ** Else part contains the trellis chart which results in no data.

      Edited by: Kedar Gupte on May 20, 2013 7:18 AM

      Edited by: Kedar Gupte on May 20, 2013 11:47 AM
        • 1. Re: Plotting Trellis chart using ORE script..results in no data
          Mark Hornick-Oracle
          Hi Kedar,

          Invoke library(lattice) within the R script. See the following example:

          R> ore.doEval(function(){Depth <- equal.count(quakes$depth, number=8, overlap=.1)
          +                                xyplot(lat ~ long | Depth, data = quakes) })
          Error in .oci.GetQuery(conn, statement, data = data, prefetch = prefetch, :
          ORA-20000: RQuery error
          Error in function () : could not find function "equal.count"
          ORA-06512: at "RQSYS.RQEVALIMPL", line 104
          ORA-06512: at "RQSYS.RQEVALIMPL", line 101

          R> ?equal.count ## from lattice package
          R> ore.doEval(function(){ *library(lattice)*
          + Depth <- equal.count(quakes$depth, number=8, overlap=.1)
          + xyplot(lat ~ long | Depth, data = quakes) })

          In my environment, this second returned the graph in the R client. I often find it useful to invoke the R script first using the R API (ore.*Apply) before invoking it through SQL.

          Mark

          Edited by: mhornick on May 20, 2013 7:49 AM
          • 2. Re: Plotting Trellis chart using ORE script..results in no data
            Kedar Gupte
            Hi Mark,

            The script runs perfectly fine on R client, the 'lattice' library gets included when using ore.doEval on R Client (mentioned lattice, rqconnect etc in RProfile.site).
            I've to use rqScript ( R Server ) stored as a procedure on oracle 11g and execute using the select sql used in the physical table of the RPD.
            The connection details for it are available from connection pool of the physical layer .
            Final goal is to get this trellis from 'PNG' format to OBIEE dashboard.

            Here in the above R script if I include library("lattice", lib.loc="C:/Program Files/R/R-2.15.2/library")
            even then it gives no result. Result is retrieved for normal scatter chart, fails for trellis alone.
            Could you please help me out in including this lattice library in rqScript stored in the DB, or any other workaround to help achieve it ?

            Thanks you for support,
            Kedar
            • 3. Re: Plotting Trellis chart using ORE script..results in no data
              Mark Hornick-Oracle
              Hi Kedar,

              One other detail is that you need to include the graphics call within a "print" call. Try the following:

              # R
              ore.scriptCreate("lattice.test2",
              function(){library(lattice)
              Depth <- equal.count(quakes$depth, number=8, overlap=.1)
              print(xyplot(lat ~ long | Depth, data = quakes))})
              -- SQL
              select *
              from table(rqEval(NULL,
              'PNG',
              'lattice.test2'));

              Regards,
              Mark
              • 4. Re: Plotting Trellis chart using ORE script..results in no data
                Kedar Gupte
                Thanks Mark,
                print option worked like magic :)