Oracle Autonomous Data Warehouse

Oracle Autonomous Data Warehouse is being used by our organization to help our clients get the best out of their data which was earlier analyzed using Microsoft Excel. It is currently being used by one department of the company which caters to an Australian insurance giant. The clients want to better utilize the claims data in order to reduce the pathing cost and increase income from recoveries.

• It has helped generate key insights, reducing the cost of company per claim
• It has helped gather average recovery information from each claim
• It helped streamline the process of data analysis

Jira Software, Atlassian Confluence, Microsoft Powerpoint

A manufacturing company recently asked my company to do a Business Intelligence project to improve and standardize the analysis of its users. Before the project, users used spreadsheets and local databases based on Access to perform management analysis.

For the project, we proposed them an Oracle cloud full-stack architecture based on:

<ul><li>Object storage</li><li>Oracle Autonomous Data Warehouse</li><li>Oracle Analytics cloud</li></ul>After a first as-is analysis, the project steps have been:

<ul><li>Export of data from the company ERP on flat files</li><li>Creation of a staging area layer</li><li>Creation of PL/SQL procedures to import files on staging structures, performing formal checks, cleaning and standardization processes</li><li>Creation of entity-relation models for some Data Marts</li><li>Creation of ETL flows to load data from the staging area on the Data Marts</li><li>Creation of a series of institutional reports, based on Data Marts</li><li>Profiling of users to access to the reporting layer and for free ad hoc analysis</li></ul>The project has recently been deployed and the architecture is currently being used by about fifty users.

• No costs for hardware or server rooms
• Cost reduction, turning off or decreasing resources (RAM or CPUs) for the database when it's not needed
• No costs for maintenance of the database (i.e.: patching or resources monitoring)
• No costs for backups and disaster recovery

The blockchain style for data storage and analysis. We have many different data formats from XML, HTML, JSON and this tool is very useful in organizing and querying all types of data at once. Getting all of our data centralized in one place and then only using one tool to look at all of it is hugely beneficial.

• Faster reports
• Faster queries
• In-house solution

We use ADW in conjunction with the Oracle Blockchain Platform, as it provides an easy way to inspect, analyze, and reuse information stored in a blockchain style. The ADW has basically the same functionality as the Oracle Autonomous Transaction Processing - and they have a massive toolset of useful things!

Let's start with the most engineer-y one: JSON or SQL? It does not matter. Basically, the DB hides the fact if data is in SQL or JSON structure and allows you to easily make queries independently of the actual data structure. This is extremely useful as in our case the Blockchain provides structures in JSON and we needed to digest the information without wanted to dump everything into a strict SQL table! And that works out of the box.

• Oracle Autonomous Data Warehouse is approx 100 times faster spin up than manual setup of an Oracle DB.
• 3h annual maintenance for big version releases (18c to 19c) compared to est. 500h annual maintenance with manual database updates.

We use Oracle Autonomous Data Warehouse for one specific application that store[s] alarm[s] and incidents as an historical database. It address business problem[s] with regard to preventive maintenance that analysts can query more than a billion rows very fast, to identify certain patterns. This historical database was migrated from an older Oracle database into the Autonomous Data Warehouse and now we don't need to think about upgrading the database and having machine/database available. Now the database is always available and if it is not need[ed] for some time, we just stop it, and when there is need to query the data I start it up again.

• Just positive as we wanted to decommission an old application/database and now we have all the historical data - always available and it hardly cost us anything to store it on the Oracle Autonomous Data Warehouse.