Integrating Oracle APEX (18.2) with Microsoft Power BI (Oracle RESTful Data Services)
A lot has been spoken about Business Intelligence and the tools that surround this. Another tool that is well known 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 the two be combined?
Essentially, Power BI be used to graphically display data generated in Oracle APEX and bring value to a company’s business through its data?
The answer is clear: RESTful Services in Oracle APEX.
Note: The 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
In the future, you will be able to see how to create modules in Oracle APEX through REST Data Services to perform the integration of this application with Microsoft Power BI.
To illustrate the example, a sales order table will be simulated so that this data can be made available to Power BI. Then the following model will be available:
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 the data, follow these steps:
- In Oracle APEX, in the SQL Workshop menu select the RESTful Services
- When you use RESTful Services for the first time, the message “Schema not registered with ORDS” will appear. To register, click the Register Schema with ORDS.
- A window will open so you can provide some ORDS Schema attributes. Define RESTful Access as enabled and enter an alias for your Schema. You can also opt for the installation of an example service and authorization to access the service. Click on Save Schema Attributes and then 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 this example, the Schema alias is apxservices.
- Now, you need to create the module that will contain the parameters and methods of accessing your data. To do this, within RESTful Services, go to Modules and click Create Module. Some fields will appear to define the module settings. Define a Module Name = learning and Base Path = /apxlr/. To conclude, click on Create Module.
Base Path will compose the URL that will be generated for access to the Web Service.
- After creating the module, you will need to create a template. Inside the created module, in Resource Templates, click Create Template. In the fields that will be made available, it is defined for the Template URI that will be used to access specific features of the database. In this case, the Sales Order data is returned and therefore he name “orderinfo/” is defined. 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, creation was done in Create Template.
As can be seen below, the URI Template and the bind variable – if included – will also compose your URL.
- To finish, you will create the method and the query to bring the data that you want. To do this, within the template that you just created, click Create Handler in the Resource Handlers In the fields that will appear, select the GET method and the source type Query, the return format will be JSON. In Source, select the data from the VW_ORDERS view, which contains the sales order data. To complete the creation of the Handler, click Create Handler.
|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, RESTful service will be complete. The resulting URL that will give access to the data is now:
Integration with Power BI
To integrate your application with power BI you will follow the following steps:
- In Power BI, on the Home tab, click Get Data and select Web.
2. In the window that will open, enter your URL and click OK.
- After clicking OK, you 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, you will have your data in a table.
Your data will now be available on Power BI and can be used as desired. In the example below, there are a total of sales orders per month, separated by Status.
In the example, Power BI, you are able to see how easy it is to integrate the applications developed in Oracle APEX with other applications and/or solutions.
While demonstrating integration with Power BI, when possible, it is recommended to use 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.
Extract from: www.lrayner.com