14 Sep 2017 OBIEE Data API
OBIEE Data API is a REST API using Node.JS to consume OBIEE 11g web services. The REST API identifies the call from external application using OAuth. The application helps to build a non-direct connection using rest calls to the data source rather than using a direct JDBC/ODBC connection.
We came across a situation where we needed to expose the data from OBIEE seamlessly to our mobile application in an agile way without tightly coupling the data layers with the mobile presentation layer. Using the OBIEE Data API will ensure the application server only connects to the data source via REST calls and then communicates to the users, as shown in the diagram below.
Figure 1: Data flow architecture
1. Motivation
Below are the key factors that drove the development of the OBIEE Data API:
1.1. How to get data from multiple silos for the enterprise mobile application.
The Enterprise mobile application required data from multiple data sources, including OBIEE reports and Oracle and SQL databases of internal applications. We wanted to limit the configuration of new data connections to a single source to simplify the data flow to the mobile application.
All the complexity surrounding data source definitions, database drivers, and multi-silo network access should be the responsibility solely of the data API and should not be tied to the data reading application
1.2. How to conceal the direct connection details to data sources.
As per the security audit recommendations we received, we needed to minimize the exposure of the direct connection to the databases from multiple applications and to conceal the actual connection details.
2. How does the Data API work?
When a predefined data endpoint is called from the external (mobile) application, the API checks if the called endpoint exists in the metadata dictionary of defined Data API entities. If it finds a suitable match, then the API establishes a connection to the respective data source and requests to execute the logical SQL statement defined in the entity. The response received from the OBIEE server is rearranged into a predefined JSON format and sends back the final response to the external requestor.
Let’s see how the Data entity is defined and configured in the API.
2.1. Defining Entities
Data API entities are the defined SQL statements to be executed against a known data source connection. They include what parameters are to be used and how the flat JSON response is reshaped, as per the user requirements.
We can create data source connections to connect the OBIEE server as shown below.
Figure 2: Define data source connection
The below screen describes how we define a new entity or data endpoint.
Every data entity has a unique name, a data source connection, a query to be executed, a list of parameters it can receive and process, and the configuration necessary to transform the raw data to the required JSON output.
Figure 3: Define data entity
2.2. How to connect to the OBIEE server using the API to retrieve data.
Data API operations predominately first attempt to establish a connection to the data retrieving system and then initiate the execution of a query to retrieve raw data.
From the OBIEE web services, we are using the SAWSessionService logon method to establish the connection. The response received from the logon method contains the session ID or token required to call further methods to fetch data from OBIEE instance. Once the connection is established, the executeSQLQuery method of XMLViewService is called by passing the logical query saved in the entity, along with the session ID needed to retrieve the raw data. The data formatter module of the API will reformat the received XML data to the required JSON format. A sample output of the data is shown below.
Figure 4: Sample Output
3. Is the Data endpoint secure?
Yes, Security mechanism within the API will ensure that the API call is from a trustworthy source. This is achieved through a token authentication method. Prior to the data endpoint call, the authenticate method has to be called to obtain a token. This token is sent as a header message along with the data end point call to the REST API server.
Conclusion: Applicability (Mobile Application, Web Applications…)
Commonly, enterprise data is hosted on a large number of systems. It is commonly hard to get access to them and quickly build new solutions, which need data from multiple silos. If you implement a RESTFUL API solution, which has access to multiple silo’s, you can expose the data in multiple silo’s to a large number of applications and their servers in a more agile way. Implementing REST API to expose enterprise data from multiple silo’s is a great way of setting your data free to any type of applications including mobile, web, embedded components, etc.
The OBIEE Data API initiative has now been enhanced to retrieve data from Oracle and SQL server databases using the oracledb and mssql npm packages, respectively. The REST API application services can be further extended to connect to mongodb, cloud web services, essbase cube, etc.
Figure 5: Solution road map
Click here if you would you like to know more about how to use the OBIEE Data API.
