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.
RESTful services can be created from the Service Console of the Schema Service Database. Select the 'SQL Workshop':
and then the 'RESTful Services' option:
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.
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.
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 email@example.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.
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.
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.
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,
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
extract(year from c.time_bill_dt),
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.
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 firstname.lastname@example.org: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.
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.
All Schema Service instances come with a sample RESTful service that you can review (called oracle.example.hr).