Understanding the Data Flow Process in Oracle Cloud Infrastructure (OCI) Data Integration

In today's data-driven world businesses need robust solutions to manage, process, and analyze vast amounts of data efficiently. Oracle Cloud Infrastructure (OCI) Data Integration is a powerful service designed to address these needs. With OCI Data Integration, (OCI-DI) organizations can seamlessly integrate data across various sources, load and transform it into meaningful insights, and drive informed decision-making.

OCI-DI offers a streamlined approach to Extracting, Transforming and Loading data across various sources. This involves several steps, each designed to ensure data is accurately extracted, transformed, and loaded into the desired destination.

Let's take a look into the data flow process in OCI-DI.

1. Data Extraction

The data flow process begins with the extraction of data from various sources. This step involves identifying and accessing the relevant data sources, which could include:

- Databases: Oracle databases, third-party databases (such as MySQL, PostgreSQL, SQL Server, Snowflake), and other on-premises or cloud databases.

- SaaS Applications: Data from various Software as a Service (SaaS) applications, such as Oracle Fusion Cloud (BICC and BI Server), Peoplesoft, Salesforce, and others.

- File Storage: Structured or unstructured data stored in files within OCI Object Storage, HDFS, Hive as well as Amazon S3.

OCI-DI offers pre-built connectors that simplify the extraction process, allowing users to seamlessly connect to different data sources and retrieve the necessary data.

2. Data Transformation

Once data is extracted, it undergoes transformation to ensure it is in the correct format and structure for analysis. This stage is crucial for cleansing, enriching, and converting raw data into meaningful insights. Below are the key transformation activities in OCI-DI:

- Data Cleansing: Removing duplicates, applying filters, correcting errors, and ensuring consistency in the data.

- Data Enrichment: Adding additional information or context to the data to make it more useful for analysis.

- Data Aggregation: Summarizing data to generate metrics or insights, such as calculating totals, averages, or other aggregate values.

- Data Mapping: Converting data from one format to another, aligning it with the schema and requirements of the target system.

OCI-DI provides a rich set of transformation tools and SQL-based expressions to perform complex transformations efficiently.

3. Data Loading

The final step in the data flow process is loading the transformed data into the target destination. This could be a data warehouse, data lake, or another storage solution where data can be analyzed and used for decision-making. Below are some commonly used target destinations in OCI-DI:

- OCI Data Warehouse: For storing and analyzing structured data using Oracle Autonomous Data Warehouse (ADW).

- OCI Data Lake: For storing large volumes of structured, semi-structured, and unstructured data using OCI Data Lake.

- Any other on-prem Oracle Database.

- External Systems: Other cloud storage solutions, third-party databases, or on-premises systems.

OCI-DI ensures that data is loaded efficiently and accurately, maintaining data integrity throughout the process.

4. Scheduling and Orchestration

To ensure that data flows are executed regularly and efficiently, OCI-DI provides tools for scheduling and orchestration. This involves:

- Task Scheduling: Setting up schedules for Data Flow Integration Tasks to run at specified times or intervals, ensuring data is updated and synchronized regularly.

- Orchestration using Pipelines: Building Pipelines to coordinate multiple data flows and processes to run in a specific sequence, ensuring dependencies are managed, and workflows are executed smoothly.

5. Monitoring and Management

Effective monitoring and management are crucial for maintaining the reliability and performance of data flows. OCI-DI offers comprehensive monitoring and management capabilities, including:

- Real-Time Monitoring: Tracking the status and performance of data flows in real time, allowing users to identify and address issues promptly.

- Alerts and Notifications: Setting up alerts to notify users of any failures, delays, or anomalies in the data flows, ensuring quick resolution of issues.

- Logging and Auditing: Maintaining detailed logs and audit trails of data flow activities for troubleshooting, compliance, and governance purposes.

Main Components of OCI-DI service:

1. Data Assets:

Data Assets represent the data sources that we want to use in Data Integration data flows, tasks, and pipelines. They help us in connecting to various systems such as BICC, Fusion BI Server, Object Storage, Oracle Cloud Databases such as ADW as well as any external on-prem Oracle databases.

Data Assets for RDBMS:

Data Assets for File and Big Data:

Data Assets for Application:

Data Assets for Others:

2. Data Flows:

A data flow is a visual program representing the flow of data from source data assets, such as a database or flat file, to target data assets, such as a data lake or data warehouse.

Sample Data Flow:

3. Tasks:

A task is a design-time resource that specifies a set of actions to perform on data. Tasks can be of below types:

- Data Loader: Lets us load diverse data into data lakes and data warehouses.

- Integration: Let's us take a data integration flow design and prepare it for running.

- OCI Data Flow: Let's us run an existing application in OCI Data Flow from within OCI-DI

- Pipeline: Let's us run a Pipeline.

- SQL: Let's us run custom SQL from within a pipeline.

- REST: Let's us run a REST API endpoint in a pipeline.

Sample Integration Task:

Task Scheduling:

Once a task is published to an Application in OCI-DI, we run the task manually from the application or create a task schedule to automate the runs.

Sample Task Schedule:

4. Pipelines:

A pipeline is a design-time resource in Data Integration for connecting tasks and activities in one or more sequences or in parallel from start to finish to orchestrate data processing. We can invoke various Data Flow tasks, Data Loader tasks or even SQL and REST Tasks in a Pipeline to meet the requirements of the orchestration/ data integration.

Sample Pipeline:

Conclusion

The Extract-Transform-Load process in Oracle Cloud Infrastructure (OCI) Data Integration is designed to be efficient, scalable, and robust. OCI-DI seamlessly integrates data from various sources, transforming it into meaningful insights, and loading it into the appropriate destination. This empowers organizations to make data-driven decisions. Be it large volumes of data or complex data integration requirements, OCI-DI offers the tools and capabilities needed to streamline the data workflows and unlock the full potential of the data.

Read More

Implementing Geolocation Maps in Oracle APEX

Geolocation Maps have become a crucial part in many applications built in Oracle APEX. Prior to release 21.1, developers had to rely on third-party geolocation map providers and had to obtain API keys and third-party plugins to integrate Geolocation Maps in Oracle Apex.

But since release 21.1, Oracle has introduced native Maps region in Apex. It eliminates the need to obtain third-party plugins, API keys and best of all - it's free.

Map component is based on Oracle Elocation Service. More details can be found at https://maps.oracle.com. 

Map region supports Vector map layers based on OpenStreetMap Position, OpenStreetMap Bright, and OpenStreetMap DarkMatter.

Let's see how to implement this functionality in our Apex App.

- Create a new App in Oracle Apex

- Create a new page

- Create a page item to store GeoLocation details

- Create a Region and set type to Map

- Set the SQL Query in source of Map region to 

SELECT :P4_GEOLOC GEOJSON_COL FROM DUAL

Set Page Items to Submit to P4_GEOLOC

- Now, let's see how we can obtain the current location of the user

- Navigate to Dynamic Actions

- Create a DA at Page Load level

- Set Action to 'Get Current Position'

- Set Return Type to GeoJSON

- Set Item to P4_LATLONG

This will enable our App to acquire device's current location and return Geolocation in GeoJSON format and store it in item P4_LATLONG

About GeoJSON:

GeoJSON is a geospatial data interchange format based on JavaScript Object Notation (JSON).  It defines several types of JSON objects and the manner in which they are combined to represent data about geographic features, their properties, and their spatial extents. Broadly it accommodates 7 different kinds of geometry objects: "Point", "LineSegment", "Polygon", "MultiPoint", "MultiLineString", "MultiPolygon", and "GeometryCollection".

In above example, it will return the data as shown in below example:

{

    "latitude":20.7195718,

    "longitude":-103.4363326,

    "altitude":null,

    "accuracy":16.452,

    "altitudeAccuracy":null,

    "heading":null,

    "speed":null

}

BUT the Map Region in Oracle Apex requires the data in below GeoLocation format:

{

"type": "point",

"coordinates": [

Longitude,

Latitude

]

}

- So we need to Transform the data returned by Get Current Position action into this format.

- Let's create another Dynamic Action named Transform with Action as Execute Server-side code and embed below PLSQL code in it. We will be using json_table function to transform the data in the desired format.

Set Items to submit as P4_LATLONG

Set Items to Return as P4_GEOLOC

P4_GEOLOC is the item we originally used in the Source of Map region

Code:

declare

   lv_geojson varchar2(4000);

begin

 

   with device_json as (

           select :P4_LATLONG json_res 

           from dual )

 

   select '{"type": "Point", "coordinates": ['||json_data.longitude||', '||json_data.latitude||']}' coordinates

       into lv_geojson 

       from device_json,

       json_table(json_res, '$[*]'

          columns (longitude varchar2(100) path '$.longitude',

                   latitude  varchar2(100) path '$.latitude')

       ) as json_data;

 

   :P4_GEOLOC := lv_geojson;

 

end;

- Let's add another Dynamic Action to refresh the map

- Let's run the App and see what we see.

- Here, we can see the Map region shows my current location and it shows I'm somewhere in Seattle area.

- Let's zoom into the map and we'll see my exact location which is near the famous Starbucks Pike Place Market location.

- In above examples, if you notice you will see it says OpenStreetMap in the footnote of the map.

This is because I've selected OpenStreetMap Bright as the Standard in Map Region Attributes

You can experiment using all these options and select the one that suits your use case the best.

Here's the output using Oracle World Map standard

Using Latitude/Longitude instead of GeoJSON:

We saw how we can obtain the geolocation data in GeoJSON format using Get Current Position action.

There's another type supposed by this action and that is Latitude and Longitude values. 

If we want to only obtain these two and do not require other values such as Altitude, Speed etc., then we can use this method. Let's take a look into that.

- Let's create two separate page items one to store Latitude and other for Longitude and make them hidden

- Now, let's navigate back to our Dynamic Action and let's change the Return Type to Latitude and Longitude

- Now, assign P4_LAT and P4_LONG page items as below.

This will return the Latitude and Longitude values of user's current location in these page items.

- Now, let's navigate to Map Region

- Set Source SQL Query as below. This will simply use the Latitude and Longitude values stored in hidden page items.

Note: We need to convert these to NUMBER format before using it in Map Region

SELECT TO_NUMBER(:P4_LAT) P_LAT 

      ,TO_NUMBER(:P4_LONG) P_LONG

FROM DUAL

- Set Page Items to Submit to P4_LAT,P4_LONG

- Navigate to Column Mapping section under Map Layer

- Set Geometry Column Data Type to Longitude/Latitude

- Set the page items corresponding values

- Now, let's assume the user is near Museum of the City of New York while accessing the App, the Map Region will precisely show his location as below -

Read More

Unleashing the power of Artificial Intelligence in Oracle APEX 24.1

Oracle APEX has been a game-changer in the low-code application development  domain, enabling developers to build robust applications rapidly. 

Oracle recently released Apex version 24.1 which is loaded with AI features. With the introduction of the AI Assistant in Oracle APEX in version 24.1, APEX has elevated its capabilities, offering AI support to developers. Let's see what are the features and benefits of the Oracle APEX AI Assistant and how to configure and use it in a step by step tutorial.

What is the Oracle APEX AI Assistant?

The Oracle APEX AI Assistant is an intelligent, context-aware virtual assistant designed to help developers and end-users interact more efficiently with their APEX applications. 

It leverages natural language processing (NLP) and machine learning (ML) to understand user queries and automate routine tasks.

AI Assistant has ability to understand and process natural language queries. Developers and users can interact with the assistant using plain language, asking questions and giving commands to generate queries and/or automate repetitive tasks.

Steps to configure and use the APEX AI Assistant

First we need to configure Generative AI service in our APEX environment:

- Navigate to Workspace Utilities > All Workspace Utilities

- Navigate to Generative AI option

- Create a new service

- Let's select Open AI for the AI Provider

- You can choose Cohere, Open AI or Oracle Generative AI service providers

AI Provider: Open AI

Name: OpenAI

Used by App Builder: Make sure to enable this option

Base URL (auto-populated): https://api.openai.com/v1

Credentials (Create New): Obtain the Open AI API key and paste it here

Note: You can create a free Open AI account and generate an API key under Trial Keys section

- Click Create

- Once created, it should look like this

- With these steps, we have configured the Gen-AI Service in our APEX environment.

APEX AI Assistant Features

Once the Gen-AI Service is configured in APEX environment, below features will be automatically activated in our environment:

Create App Using Generative AI:
This feature facilitates the creation of new application from a natural language user prompt that specifies desired capabilities. This simplifies the development of new applications and allows developers to focus their energy and resources on creating unique, bespoke application features rather than underlying code.

Conversational AI dialogs:
This feature enables one to 'converse' with their application and database in natural language through an out-of-the-box conversational interface that developers can easily add to their applications. As a result, developers can provide a richer in-app experience without the need to build Generative AI or natural language processing components from scratch.

Let's take a look at these features in detail.

Create App Using Generative AI

- Navigate to App Builder and click Create a New App option

- Here, we will notice a new option named 'Create App Using Generative AI'. Select it.

- Let's mention below sentence to Gen-AI Engine to create a new App to manage Projects data.

"Create an app to maintain project milestones, project tasks, projects, project task todos, and project status, that includes pages for a chart, dashboard, interactive report, and faceted search."

- As we can see the Gen-AI Engine in our APEX Environment understood our Natural Language (NLP) prompt and has offered us an option that will create an application called Project Tracker with the pages to offer Project Overview, Dashboard, Projects interactive report and a Faceted Search page.

- Let's click Create Application

- This will take us to a final review page where we can see all above pages are listed. Let's click Create Application

- Once finished, we'll have our application ready for first use

- Let's run it and see how it works.

- Once logged in, we can see our app shows all the features including the pages described in above steps.

Project Overview:

Project Dashboard:

 

Projects List (Interactive Report):

Search Tasks (Faceted Search):

- This way we can leverage Gen-AI service to automatically create an App for us based on our Natural Language instructions.

Conversational AI dialogs

SQL Assistant:

Now, lets take a look at Conversational AI dialogs feature to 'converse' with our database in natural language and build constructs using AI.

 -Let's create a new page in our application

- Select Interactive Report and click next

- Provide name to the IR Report and select Source Type as SQL Query

- Here, we'll notice a new option named APEX Assistant

- This is another AI feature that let's us converse with our database and provide our requirements in Natural Language prompts and APEX Assistant would convert the same into SQL queries

- Let's mention a statement like "create a report based on Employees data" and see if Gen-AI decodes it and builds an Interactive Report query based on Employees and related information.

- As we can see the APEX Assistant understood our Natural Language prompt and has built the query for us. Let's click Insert to use this query.

- Validate the query and we can see the APEX AI Assistant has built a valid query for us.

- Let's run our AI Generated Interactive Report and we can see the query works perfectly.

PL/SQL Assistant

- The APEX AI Assistant is available throughout the App Builder at many places and it doesn't stop at generating SQL queries but we can use it to also generate PL/SQL Code.

- Let's create a Dynamic Action for example purpose

- Change Action to Execute Server-side code

- Under PL/SQL Code box, click Code Editor icon to popup the Code Editor

- As we can see we have APEX Assistant available under PL/SQL as well.

- Let's  mention statement 'anonymous block to maintain project tasks' and see if AI can generate a anonymous block to handle Project Tasks for us.

- As we can see, Gen-AI understood our Natural Language prompt and built an anonymous block for us that helps in performing Insert, Update and Delete operations on Project Tasks related data. We can utilize this as a template and tweak it as needed.

As seen above, the Oracle APEX AI Assistant in version 24.1 is a groundbreaking addition that significantly enhances the capabilities of the APEX platform. By leveraging AI and machine learning, it transforms the way developers interact with applications and databases, making the process more intuitive, efficient, and productive.

Read More