An Post – Customer Analytics using Oracle Analytics Cloud
An Post, is the provider of postal services in Ireland…the Most reputable organisation in Ireland (*RepTrak 2017), and a trusted and well known name to many of us Oracle Users in Ireland and also a long term Oracle customer, with lots of internal experience in both Oracle Technology, Applications and now also Analytics in the Cloud.
An Post has started its journey to the cloud in recent years and now its utilising Oracle Public Cloud as part of it’s Hybrid Big Data and Analytics Solution…
They have been an early adopter of Oracle Analytics Cloud Service (OACS) and they have chosen it, to fit a recently emerging and Innovative business need – which created the requirement for a Customer Experience Management (CXM) project. Introduction
The remit of which was to securely provide both An Post’s customer account managers and also the end customer account managers, with an analytics suite and related KPIs, which they could both utilise, simultaneously, both online and near real time, in order to assist in succinctly and expediently managing any issues in the normal parcels delivery processes, whereby An Post and their customers interact on a daily basis.
The customers are varied and some examples are;
- global names in terms of Parcel delivery who utilise An Post network to deliver in Ireland
- then some other well know online retailers (e.g. Amazon)
The CXM project enabled customers to analyse An Posts quality of service on successfully delivered items, highlight downfalls and identify the status of specific items. It was decided that the solution to best serve this requirement would include Oracle Analytics Cloud Service (OACS) and Database Cloud Service (DBCS) fed by the on-premise Data Warehouse via ODI 12c and secured by Virtual Private Database (VPD). The source data is collated from track and trace data for all registered items.
Recently, An Post has restructured their business to focus upon parcels and packet delivery in an attempt to capture a bigger share of the €600 million a year online shopping market and focus less upon the declining letter business. Before the CXM project was initiated, An Post’s business analysts had been using the track and trace subject area in the existing BI solution for quality of service KPI’s and data analysis. The data from the 11g based track and trace source system was previously being extracted twice daily via ODI 12c to the 12c Data Warehouse hosted on Exadata. Now, after the high demand both within An Post and its customers, data is being loaded to the on-premise Data Warehouse then directly pushed to DBCS near real time.
The Architecture and Implementation is managed by our trusted managed service partner, Vertice.
To enable data to be securely extracted from the Data Warehouse and pushed to the cloud, the An Post security team opened a route through their firewall out to DBCS. Secure Shell (SSH) tunnels were then created to enable the BI team to create a connection between the Data Warehouse, ODI and DBCS. Paired SSH-2_RSA public and private keys were generated which were used during the configuration of the cloud services to create a secure connection between the An Post network and the cloud via the SSH tunnel.
As sensitive data is being provided to customers outside of the business, it is crucial that customers can only view An Post data related to their own business. VPD was configured on DBCS and the RPD to enforce this row-level security. A context is used to set and store the identity of the person logging into OACS. This context is passed to the database via the RPD configuration. This context value is then used by the RDBMS Policy to identify which customers data the user is configured to view. This policy alters the SQL generated by OACS to implement row-level data filtering. A user with no configuration will be unable to view any data. When customers have a query, An Post support employees can ‘Act As’ the customer to view data as the customer would.
Business analysts had been using track and trace data for years which was being refreshed twice daily by ODI packages taking 40 minutes to complete on average. The CXM project required this data to be pushed to the cloud hourly for consumption by customers. As there was a high risk of the existing ELT overrunning and not being able to be refreshed hourly, the existing packages had been split into two separate load plans, one to load the Dimensions once daily and another to load the Fact data hourly. The load plans had been optimised to minimise this risk, the Dimension load plan completes in less than 1 minute with the Fact load plan taking 20 minutes on average, dependent on volumes at runtime.
Data is loaded from to the staging area on Exadata (Oracle 12cR1) where business logic is applied using various functions, views and staging tables. The data is transformed as part of the ELT load into the on-premise Data Warehouse into the STAR schema using the LKM Oracle to Oracle (DBLINK view Target) Knowledge Module to guarantee good performance.
Once the incremental load to the Data Warehouse has completed, the load to DBCS is started. Only the data that has changed since the last extract is required to be pushed to DBCS. As the business logic has been performed on Exadata, no further transformations are required and the delta records are pushed using the LKM Oracle to Oracle Push (DB Link).GLOBAL Knowledge Module.
As the push to DBCS is an extra step in the ELT process, we needed to ensure that any failure in this step would not affect the on-premise data. For example, if the SSH tunnel was closed due to a networking or firewall issue. The load plan had been constructed so that the on-premise loads would continue to run hourly, populating the Data Warehouse even though there was an issue with the push to DBCS. Once issues had been resolved with the DBCS load, it automatically syncs the data using a ‘Last Extract’ variable and a ‘Record Modified On’ column the next time the load plan is run. To ensure the data is in syncd after a recovery, a series of scripts are available to the BI team to perform comparisons efficiently. Documentation is also available to the An Post BI team detailing past failures and the recovery steps. This helps to ensure that both on-premise and OACS users are not left with stale data for an extended period of time.
Sample source extract physical view
Oracle Analytics Cloud Service (OACS)
The VPD policy is applied at the database level and in order to apply row-level data filtering, VPD was also configured in the RPD. This involved enabling the ‘Virtual Private Database’ check box in the database properties, setting a security sensitive USER variable and adding a call in the database connection pools which executes on connection sets the user’s data filter.
When user login to OACS, they are directed to the CXM Dashboard which contains three Dashboard pages, KPI’s Overview, Daily Summary and Delivery Sites Map. The Delivery Sites Map displays parcel delivery information on a map of Ireland which helps to identify the performance of delivery offices and highlights any downfalls in the An Post delivery network. The Customers logo appears on each of the pages, text displaying date ranges selected, a series of prompts and the last data refresh time. Users have the ability to drill on each of the data points provided to view individual items and export data if required.
The CXM dashboard is available both on-premise and CXM and a strict release management process is in place to ensure that any changes are fully tested before releasing to users. Any changes are completed in each of the BI environments, signed off and post-release checks are completed to ensure any changes have been released correctly. Dashboard changes are migrated from OBIEE to OACS while a cut down version of the RPD including only relevant subject areas is deployed.
While the project was in the development phase, caching on the CXM tables had been disabled as the data was being refreshed hourly and users needed to view fresh data. Before the project went live, caching had been enabled to improve user experience even further. Users still view fresh data each time the ELT process has been completed. This was achieved by setting up the S_NQ_EPT event polling table in the RPD. Each time the ELT process completes, a record is entered into the S_NQ_EPT table to identify which tables have been updated so any associated cache is purged.
The CXM project has proved very popular with An Post employees and Customers with a series of enhancements planned for the next phases of the project. There are new requirements to extract more data, for example, parcel recipient contact details so recipients can easily be contacted by customer services. There are new business processes where An Post is aiming to improve delivery rates by attempting to deliver an item three times before providing the customer with a docket to collect their item at their local post office.
Changes are required so this new information can easily be identified if a recipient reports they have not yet received their item. This could be something as simple as there was no answer during the first two delivery attempts.
With all of the new requirements and more data to be processed within the same ELT timeframe, further enhancements are being carried out to ensure refresh times are not affected. In the short term, ODI 12c will still be the tool of choice however with higher demand for data, this is likely to involve Golden Gate and Oracle Golden Gate Cloud Service in the future.
In summary the solution has fulfilled a requirement for An Post, It has been validated as a value add by the “Voice of the Customer” and the respective interaction and feedback received therein. It has also strengthened the An Post business offering in terms of Parcels delivery, where Quality of Service is paramount and this solution not only enables improved process, but improved Quality of Service metrics and KPIs, based on Analytics and Big Data aspects from Oracle technology. Overall it helps enhance the existing processes in place and improves their Parcel business Customer Experience as intended.
