Lately we can see how much has been spoken of Business Intelligence, the famous BI, and the tools that encompass this world. One of them, well known by signal, is Microsoft's Power BI. Thinking about this and how Oracle APEX has been prominent in the development of web and mobile applications, how could we combine the best of the two? I mean, how could we use Power BI to graphically display data generated in Oracle APEX and bring value to a company's business through its data?
The answer to this question seems very clear: RESTful Services in Oracle APEX.
Note: My intention is not to speak of the capabilities of Power BI, but of the ability to integrate Oracle APEX with other systems, creating Web Service Modules applied to the REST model.
What is Oracle REST Data Services (ORDS)?
According to Oracle: The Oracle REST Data Service aims to facilitate the development of REST interfaces to relational data in the Oracle Database, the Oracle Database 12c JSON Document Store, and the Oracle NoSQL Database. Using ORDS, we easily create HTTP (S) methods (GET, POST, PUT, DELETE, etc.)
Note: ORDS is included with both Oracle Database and Oracle SQL Developer installs.
Integrating Oracle APEX with Power BI
From now on, we'll see how to create modules in Oracle APEX through REST Data Services to perform the integration of our application with Microsoft Power BI.
To illustrate our example, we will simulate a sales order table so that we can make this data available to Power BI. Then we will have the following model:
Table Creation Scripts
create table product
(
product_id number generated as identity nocache,
name varchar2(45) not null unique,
price number(11,2) not null,
constraint pk_product primary key (product_id)
); create table customer
(
customer_id number generated as identity nocache,
name varchar2(45) not null,
age number not null,
constraint pk_customer primary key (customer_id)
);create table status_order
(
status_order_id number generated as identity nocache,
status varchar2(30) not null unique,
status_code number not null unique,
constraint pk_status_order primary key (status_order_id)
);create table customer_order
(
order_id number generated as identity nocache,
customer_id number not null,
product_id number not null,
qty_product number not null,
total number(11,2) not null,
order_date date not null,
status_id number not null,
constraint pk_order primary key (order_id),
constraint fk_customer_order foreign key (cutomer_id) references customer (customer_id),
constraint fk_product_order foreign key (product_id) references product (product_id),
constraint fk_status_order foreign key (status_id) references status_order (status_order_id)
);Creating RESTful Services in APEX
To create the REST service with the method of accessing our data, we will follow the following steps:
1. In Oracle APEX, in the SQL Workshop menu we select the RESTful Services option.
2. When we use RESTful Services for the first time, the message "Schema not registered with ORDS" will appear. To register, click the Register Schema with ORDS.
3. A window will open so we can provide some ORDS Schema attributes. We define RESTful Access as enabled and enter an alias for our Schema. We can also opt for the installation of an example service and authorization to access our service. We click on Save Schema Attributes and then, our Schema will be registered with ORDS.
For security reasons and as a good practice, it is recommended not to define the Schema alias as the official Schema name in the database. In our example, the Schema alias is apxservices.
4. Now, we need to create the module that will contain the parameters and methods of accessing our data. To do this, within RESTful Services, go to Modules and click Create Module. Some fields will appear to define the module settings. We define a Module Name = apex.learning and Base Path = /apxlr/. To conclude, click on Create Module.
Base Path will compose the URL that will be generated for access to our Web Service.
5. After creating our module, we will create a template. Inside the created module, in Resource Templates, click Create Template. In the fields that will be made available, we define for the Template URI that will be used to access specific features of our database. In our case, we will return the Sales Order data and therefore define the name "orderinfo/". The URI Template can also include a bind variable such as "orderinfo/:id", for example. The priorities we leave as zero (0) and the HTTP Entity Tag Type as "Secure Hash". To conclude, we created in Create Template.
As we can see below, the URI Template and the bind variable - if included - will also compose our URL.
6. To finish, we will create the method and the query to bring the data that we want. To do this, within the template that we just created, click Create Handler in the Resource Handlers region. In the fields that will appear, we select the GET method and the source type Query, the return format will be JSON. In Source, we will select the data from the VW_ORDERS view, which contains the sales order data. To complete the creation of the Handler, click Create Handler.
View VW_ORDERS
CREATE OR REPLACE VIEW "VW_ORDERS" ("ORDER_ID", "CUSTOMER", "PRODUCT", "PRICE", "QTY_PRODUCT", "TOTAL", "ORDER_DATE", "STATUS") AS
select a.order_id,
b.name as customer,
c.name as product,
c.price,
a.qty_product,
a.total,
a.order_date,
d.status
from customer_order a join customer b on (a.customer_id = b.customer_id)
join product c on (a.product_id = c.product_id)
join status_order d on (a.status_id = d.status_order_id)Once this is done, our RESTful service will be complete. The resulting URL that will give access to the data is now:
Integration with Power BI
To integrate our application with power BI we will follow the following steps:
1. In Power BI, on the Home tab, click Get Data and select Web.
2. In the window that will open, we enter our URL and click OK.
3. After clicking OK, we will see that Power BI returns the data as a list containing several objects of type Record. To display the data as in a table, on the Transform tab, click Convert To Table and everything will be fine.
After the transformation, we will have our data in a table.
Our data is now available on Power BI and can be used as desired. In the example below, we have a total of sales orders per month, separated by Status.
Conclusion
We were able to see how easy it is to integrate the applications developed in Oracle APEX with other applications and/or solutions. In our example, Power BI.
While demonstrating integration with Power BI, when possible, I recommend using the Oracle APEX graphics , which use an Oracle JET based engine, which, in addition to being a native APEX solution, is quite powerful and useful for analysis and data management, as demonstrated in the example below.
In practice...
To see the Sales Order data and the charts used in this example, go to the demo application below.
Demo Application
Link: Learning APEX App
User (default): demo
Password: demo