Oracle APEX - replacing OAS/OAC/OBI write-back

In today's fast-paced business environment, organisations need powerful and flexible tools to make informed decisions quickly. Oracle Analytics (OAC/OAS), once Business Intelligence (OBI), has long been a reliable choice for business intelligence and analytics.

However, as technology evolves, so do the requirements of businesses. One such requirement is the ability to seamlessly write-back data for analysis and decision-making. While Oracle Analytics stack provides write-back functionality, it has its limitations.

In this blog post, we'll explore the limitations of write-back screens in Oracle Analytics and discuss how migrating to Oracle APEX can unlock advanced write-back capabilities.

The Limitations of Write-Back

Write-back functionality in Oracle Analytics is a valuable feature that allows users to input and modify data directly within reports and dashboards. However, it comes with certain limitations that can hinder its usability and flexibility, especially for organisations with complex data requirements. Let's delve into some of these limitations:

• Limited Data Validation: write-back capabilities lack robust data validation mechanisms. This means that users can input data without adequate validation checks, potentially leading to errors and inconsistencies in the data.
• Limited Select List Support: write-back screens have limited support for dynamic select lists, making it challenging to provide users with predefined choices for data input. This can result in data entry errors and inconsistencies.
• Limited Flexibility: Customising the look and feel of write-back screens can be challenging. Organisations often require a more flexible and user-friendly interface for data input.
• Security Concerns: write-back screens may pose security concerns when used for sensitive data. It may not offer fine-grained control over who can access and modify data, potentially exposing data to unauthorised users.
• Scalability Issues: As data volumes grow, write-back functionality may encounter performance and scalability issues, leading to delays in data input and analysis.

Oracle APEX as an alternative

As you may be aware, Oracle APEX is a low-code development platform and a cost-free feature included with the Oracle Database. APEX enables enterprises to rapidly, effortlessly, and securely deploy web applications that allow users to interact with data.

In this regard, it provides a robust solution to overcome the limitations of write-back in Oracle Analytics. Here's how migrating to APEX can address these challenges and provide advanced write-back capabilities.

Forms and Reports

The heading of this section may appear somewhat dated, given the mention of two enduring Oracle technologies that have played a significant role in the field of data manipulation for quite some time – Oracle Forms and Oracle Reports. The fact is that Oracle APEX serves as a natural replacement for those products. Therefore, it's not surprising that it offers robust support for web forms and various types of reports.

Similar to the first example mentioned above, which is likely the most common one in write-backs, Oracle APEX features the Interactive Grid. This spreadsheet-like report allows users to interact with data quickly and effectively. The grid is a highly flexible and powerful component that, out of the box, declaratively supports the insertion, updating, and deletion of data from a table. With a bit of customisation, it can support even more functionalities.

This component prioritises productivity and proves especially useful when there is a need to edit multiple child records simultaneously. Consider the scenario of creating an invoice where the sold items are organised in a detail table. An Interactive Grid in APEX would handle that table seamlessly. Editing a lookup table, for instance, could be another good example of the usage of this component.

While the grid is advantageous in many scenarios, it may not be suitable for all types of components. For example, when dealing with tables containing a larger number of columns, the grid may struggle to fit well on the screen. Additionally, the user experience for certain item types might not be as smooth as in a standard form. Take a rich text editor, for instance, which requires an extra click to trigger the item in a secondary window within the application, making it more complicated to use.

In cases where mixed screen sizes are involved or a higher level of flexibility is required in terms of the user interface, APEX provides a Form region as an alternative. The primary purpose of a form is to edit one record at a time.

Enhanced Data Validation

APEX allows you to implement robust data validation using declarative features and custom PL/SQL logic. This ensures that data entered through write-back screens meets predefined criteria, reducing errors and maintaining data integrity.

Implementing custom server-side validation enables you to provide users with a friendly error message, rather than a constraint violation error that may not convey as much information to the user.

For instance, you can create one or many validations for any item in your form.

APEX offers a wide range of validation types and ultimately allows you to execute any SQL or PL/SQL expression, enabling you to create validation code tailored to your specific needs.

Allowing you various ways of displaying your custom error message to the users, either inline with the item as shown below, in an error message at the top, or both.

In addition to Server-Side validations, APEX enables you to create client-side validations using dynamic actions – the declarative way of incorporating JavaScript into your application. Client-side validations, in an equal manner, can validate your items and prevent the submission of erroneous data to the database. However, they come with two main advantages: ensuring a smoother user experience and avoiding unnecessary database interactions for issues that can be addressed on the client side.

Dynamic Select Lists

While select lists are available in Write-backs, they have limitations not only in terms of UI/UX but also in the way values are managed. The values in the list itself need to be hardcoded by the developer, and the same value will be used as a return value.

On the contrary, APEX offers extensive support for both static and dynamic select lists. A static list of values operates similarly to write-back ones, with the inclusion of a return value column. This feature allows you to save a normalised value to the column while displaying a user-friendly name to enhance the user experience. When it comes to dynamic lists, APEX provides a diverse range of flexible solutions for your data. This includes reading from a local table declaratively or constructing your list based on a dynamic query executed on a remote server through REST Enabled SQL or a JSON string from any REST API available.

Flexible UI Customisation

Oracle APEX provides a highly customisable user interface, empowering you to design write-back screens that seamlessly align with your organisation's branding and usability standards. In contrast to Write-backs in Oracle Analytics, APEX offers complete control over the layout, appearance, and behaviour of your applications. Besides being fully responsive by design, the Universal Theme included with APEX can be effortlessly customised using a well-known tool called Theme Roller, allowing for easy adjustments with just a few clicks.

In terms of form items, APEX provides a diverse range of options, including checkboxes, radio buttons, normal select lists, and pop-up lists of values, to name just a few. Each of these options comes with a variety of declaratively customisable attributes, and you also have the option to further customise them with a bit of JavaScript code if needed.

As mentioned in the previous section, validations can be applied to any of the aforementioned components or to the entire form submission.

Robust Security Model

The versatility offered by Oracle's Low Code framework extends to security as well. Out of the box, APEX supports various authentication technologies commonly used in web development, such as OpenID Connect, Database Authentication, LDAP, etc. In just a couple of minutes, you can implement authentication with major OAuth providers, delegating security to a trusted third party, simplifying your code, and playfully passing on the security risk to someone else! 😄

If you need to know more about the topic, you can check our previous blog post here.

The security capabilities extend beyond authentication methods; out of the box, authorisation is also effectively handled. You can rely on different methods to check user roles, ranging from APEX default Access Control to IDCS or LDAP Groups or any other logic that can be implemented in SQL or PL/SQL. Once your authorisation schemas are in place, applying them to any application component—whether a page, item, button, report, report column, process, or any other element— is just a click away.

Once again, for more in-depth information on this topic, feel free to read our detailed post here.

Scalability and Performance

In terms of architecture, APEX is integrated directly into a database schema. Consequently, all application logic is executed within the database, and only the operation's output, usually in the form of HTML code, is transmitted to the client. This setup ensures that performance and scalability are not a concern, as long as your queries run efficiently, the application will follow suit.

Clients can efficiently interact with your application when paired with Oracle REST Data Services, commonly known as ORDS. ORDS establishes a connection pool utilised and shared among all clients, ensuring effectiveness and transparency for users.

Conclusion

Oracle Analytics has long been a trusted tool for business intelligence and reporting. However, its limitations in terms of write-back functionality can impede organisations' ability to effectively capture and analyse data for decision-making. By migrating to Oracle APEX, organisations can unlock advanced write-back capabilities, including enhanced data validation, dynamic select lists, flexible UI customisation, robust security, and improved scalability.

In today's data-driven world, having the ability to seamlessly capture and modify data within your reporting and analytics applications is crucial. Oracle APEX empowers organisations to achieve this goal while addressing the shortcomings of write-back screens.

It's a strategic move that can enhance data accuracy, user experience, and overall business agility.

Find out more about our APEX Consultancy Services.