Extracting Data from BICS / Apex via RESTful Webservice

Introduction

BI Cloud Service (BICS) has an API to load data into the underlying database (more details here), but reports created in BICS can not be made available and called as web-services to extract the data.  This article will document a method by which RESTful web-services can be created in Apex and exposed to replicate a BICS report, and provide access in the underlying BICS Schema Service Database.

 

Main Article

RESTful services can be created from the Service Console of the Schema Service Database.  Select the ‘SQL Workshop’:

Oracle_Application_Express

and then the ‘RESTful Services’ option:

SQL_Workshop

Create a new RESTful Service.

For this example, use the URL Prefix ‘bicsdemo’ and create a ‘GET’ handler which will respond by selecting all the records from the demo_orders table based on the SQL statement of ‘select * from demo_orders‘, as shown below.

Create_RESTful_Services_Module

Once created, edit the ‘GET’ handler that was just created, and select the ‘Test’ button beneath the source to confirm that the JSON formatted data will be returned as expected.

Resource_Handler

 

The RESTful webservice is now ready to be called.  This can be tested using cURL.

The following syntax will call the ‘orders’ RESTful webservice, and return the data in JSON format (changing the username / password and BICS instance details as appropriate).

Be sure to append a ‘/’ character at the end of the string.

curl -u testuser@oracle.com:Welcome1! -k https://businessintelltrialXXXXdb-usoracletrialABCDE.db.us2.oraclecloudapps.com/apex/bicsdemo/orders/

 

This simple example showed how to extract data from a specific table, but it may be necessary to use some more complex SQL and include filters to limit the data returned.

In the future it should be possible to call a BICS report as a web-service.  Until that functionality is available, an alternative option is to replicate the report by re-creating the SQL statement that BICS generates.

The dashboard below is from the Sample App Dashboard within BICS.

The section highlighted uses the filters shown on the left hand side of the screen for Year, Region and Product, and returns data on Product Revenue Performance.

 

Dashboard

 

With a BICS user with Administrator rights, the dashboard can be edited and that report can be analyzed to get a better idea of the logic.

Using the ‘Administration’ option, and then ‘Manage Sessions’ – the SQL that the BI Server runs can be located and examined.

 

Untitled

 

With a little work, and some testing within the SQL Workshop, the SQL statement can be recreated.  In this case the following SQL statement was reversed engineered to mimic the BICS report.

select
extract(year from c.time_bill_dt) as year,
sum(c.revenue) as revenue,
sum (c.cost_fixed + c.cost_variable) as cost,
sum(1) as orders,
p.product,
g.region,
o.channel_name
from cloud_f_cost c
join cloud_d_products p on p.prod_item_key=c.prod_item_key
join cloud_d_geography g on g.addr_key = c.addr_key
join cloud_d_orders o on o.order_key = c.order_key
group by
extract(year from c.time_bill_dt),
p.product,
g.region,
o.channel_name

 

This SQL can then be used to create a new RESTful ‘GET’ method.

This time a parameter for ‘region’ will be created, which is included as a filter in the select statement.

Resource_Handler

This parameter can be passed in the call of the RESTful service.  From cURL the syntax to filter the result set to the ‘APAC’ region would look like this.

curl -u testuser@oracle.com:Welcome1! -k https://businessintelltrialXXXXdb-usoracletrialABCDE.db.us2.oraclecloudapps.com/apex/bicsdemo/orders/APAC

Multiple parameters can be created in this manner to filter the dataset further and minimize the data returned.

 

Summary

This article demonstrated how the Apex RESTful services can be used to replicate BICS reports and allow data to be extracted from the BICS Schema Service Database via a web-service call.

 

Further Reading

Using RESTful Services:

https://docs.oracle.com/cd/E37097_01/doc.42/e35128/restful_svc.htm#AEUTL445

Cloud Specific Documentation:

http://docs.oracle.com/cloud/latest/dbcs_schema/CSDBU/GUID-FA6FC371-064A-467C-A10D-EC995A2A4DB0.htm#CSDBU195

White Paper:

https://cloud.oracle.com/_downloads/WhitePaper_Database_7/RESTful+Web+Services+for+the+Oracle+Database+Cloud+Service.pdf

Sample:

All Schema Service instances come with a sample RESTful service that you can review (called oracle.example.hr).

Comments

  1. vijayalakshmi says:

    What will be the URI template format when we use more than one parameter?
    eg. if i want to pass region as well as channel value ==> /bicsdemo/orders/{region}?

    Are there any examples for using PUT and POST methods?

    • I didn’t test this, so you’d need to do some research within the URL syntax for Apex. I *believe* it’s probably possible, and seem to remember reading that using a comma separated list worked that matched the parameters.

      One other idea if you can’t get it to work. Make the single parameter a string – and then pass your multiple parameters in that single string separated by a character – say ‘#’ – and then just parse that out in the SQL.

      So you could have ‘London#Green’ for instance as the value you pass – and then in the SQL separate that out and filter on your 2 parameter values in the select statement as ‘London’ and ‘Green’.

Add Your Comment