BI Cloud Service (BICS) has an API to load data into the underlying database, 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.
Step 1: We can create the RESTful services from the Service console of the schema service database. Here we click on the SQL workshop then click on the RESTful Services.
Step 2: Now we create a new RESTful Service by clicking on the “Create>” in RESTful Services tab.
Step 4: For this example, use the URL Prefix ‘bicsdemo’ and create a ‘GET’ handler which will respond by selecting all the records from the DEMO_CUSTOMERS table based on the SQL statement of ‘select * from DEMO_CUSTOMERS‘, as shown below. You can give the URI prefix and URI Template, the module name. In the Resource handler set it to GET method and the format to csv if you want your extracted data to be in the csv format. Then click on the create module present on the above right corner highlighted.
Step 5: After the module is created, click on the module and then edit the GET handler.
Under the source tab you can test code by clicking on the Test button highlighted. Then your csv file is downloaded to your computer.
Step 6: You can check the downloaded file in your downloads. And then you can open it and compare it with data present in the Data Modeler of the Cloud Service.
The data from the downloaded csv file and the data from the data modeler of the cloud service.