X

Best Practices from Oracle Development's A‑Team

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

 

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).

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha