Replicate and Integrate Retail Merchandizing Data Access Schema (DAS) in Near Real-Time

Leverage data access schema (DAS) and Oracle Cloud Infrastructure GoldenGate to create a near-real-time data replica for common business use cases, such as real-time shelf replenishment and indirect tax reporting at the transaction level.

Oracle Retail Merchandising Cloud Services (RMS) is the leading software as a service (SaaS) application for retailers. Data access schema (DAS) is a read-only schema that allows third-party applications to access a subset of the RMS source data that has been replicated to a new database schema in a one-way replication. The target environment can be in the cloud or on-premises and can also host custom applications.

RMS does not require DAS, however, you may want to install DAS for the following reasons:

  • Data is available across the value chain and merchandising data is accessed in near real time
  • Innovation is faster because one-way data replication to a target database ensures there is no impact on core RMS operations
  • Out of box integration with Merchandise Financial Planning (MFP) PoViewer micro application

Oracle Cloud Infrastructure (OCI) offers a comprehensive portfolio of cloud services for retailers to accelerate their cloud investments, continue to innovate for the customer, and to set the course for long-term growth and resilience.

Architecture

The data access schema (DAS) and Oracle Cloud Infrastructure GoldenGate replication solution enables you to use the data in the read-only DAS replica as the basis for downstream services.

Merchandising solutions presents DAS to customer who'd like to have the best of both breeds: the fully managed SaaS solution and the flexibility to innovate with data-driven metrics. DAS is a read-only schema in the merchandising database. GoldenGate replicates the data to an instance of the DAS database in Oracle Cloud Infrastructure (OCI). This is a one-way replication from the merchandising database in the merchandising services. You can use the target replica DAS database as-is for simple SQL queries or expose it through REST API as web services. In addition:
  • Custom applications can leverage the data replica that resides in a transactional database, such as an Autonomous Database or a standalone database in OCI
  • The data in the DAS replica can be integrated into Oracle Autonomous Data Warehouse and managed by the data lakehouse
  • The integrated data can be used to train AI/ML models to provide retail business insight by using AI/ML inference, to facilitate process improvements by using Oracle Digital Assistant (ODA), and to allow the business user to create additional reports in Oracle Analytics Cloud

This following diagram demonstrates the data flow and user access to the extended retailer data ecosystem.



retail-das-oci-flow-oracle.zip

DAS is replicated from the Oracle Autonomous Database in Oracle Retail Merchandising SaaS Cloud to an Oracle Autonomous Database in OCI. Two GoldenGate services are required to replicate the schema. The GoldenGate Marketplace instance is deployed in Oracle Retail Merchandising Cloud Services. It replicates the data to the Oracle Cloud Infrastructure GoldenGate instance deployed in the OCI tenancy by using the internet. You can use Oracle Cloud Infrastructure GoldenGate or you can deploy and manage your own GoldenGate service on Oracle Cloud Infrastructure Compute. Because DAS replica in the OCI Autonomous Database is read-only, custom applications that combine data require a separate read-write schema or a secondary database.

The diagram below shows that the target DAS database replica resides in an Oracle Autonomous Database. Alternatively, you can use Oracle Database Cloud Service, Oracle Exadata Database Service, or manage your own Oracle Database on Oracle Cloud Infrastructure Compute as the target database.

You can deploy custom apps in the same VCN in OCI, such as custom extension apps, custom cross-reference apps, customer reports, customer services, and Oracle REST Data Services. These apps can integrate with legacy applications in the cloud or on-premises. Customer on-premises applications and users can access these custom applications and the DAS database by using site-to-site VPN or FastConnect to connect to OCI. Internet users can access custom applications through an internet gateway with a web application firewall (WAF) provided for added security. All API integrations are managed using API Gateway, including inbound API services to the applications in the Oracle Retail Merchandising SaaS Cloud, and to on-premises and third-party application integrations.

Customer IT administrators access the OCI tenancy through the private network connected to a bastion host. From the bastion host, an administrator can manage the OCI resources directly.

You can set up high availability (HA) and disaster recovery (DR) by using Autonomous Data Guard (not shown in the diagram). The HA configuration uses the Autonomous Data Guard local replication to create a local, standby Autonomous Database. The DAS schema is replicated to a separate availability domain in the same OCI region. You can set up the geographic DR in a different OCI region and use Autonomous Data Guard remote replication to synchronize the DR database.

The following diagram illustrates this reference architecture.



retail-das-oci-arch-oracle.zip

The architecture has the following components:

  • Region

    An Oracle Cloud Infrastructure region is a localized geographic area that contains one or more data centers, called availability domains. Regions are independent of other regions, and vast distances can separate them (across countries or even continents).

  • Availability domain

    Availability domains are standalone, independent data centers within a region. The physical resources in each availability domain are isolated from the resources in the other availability domains, which provides fault tolerance. Availability domains don’t share infrastructure such as power or cooling, or the internal availability domain network. So, a failure at one availability domain is unlikely to affect the other availability domains in the region.

  • Virtual cloud network (VCN) and subnets

    A VCN is a customizable, software-defined network that you set up in an Oracle Cloud Infrastructure region. Like traditional data center networks, VCNs give you complete control over your network environment. A VCN can have multiple non-overlapping CIDR blocks that you can change after you create the VCN. You can segment a VCN into subnets, which can be scoped to a region or to an availability domain. Each subnet consists of a contiguous range of addresses that don't overlap with the other subnets in the VCN. You can change the size of a subnet after creation. A subnet can be public or private.

  • Security list

    For each subnet, you can create security rules that specify the source, destination, and type of traffic that must be allowed in and out of the subnet.

  • Route table

    Virtual route tables contain rules to route traffic from subnets to destinations outside a VCN, typically through gateways.

  • Service gateway

    The service gateway provides access from a VCN to other services, such as Oracle Cloud Infrastructure Object Storage. The traffic from the VCN to the Oracle service travels over the Oracle network fabric and never traverses the internet.

  • Internet gateway

    The internet gateway allows traffic between the public subnets in a VCN and the public internet.

  • API Gateway

    Oracle API Gateway enables you to publish APIs with private endpoints that are accessible from within your network, and which you can expose to the public internet if required. The endpoints support API validation, request and response transformation, CORS, authentication and authorization, and request limiting.

  • Dynamic routing gateway (DRG)

    The DRG is a virtual router that provides a path for private network traffic between VCNs in the same region, between a VCN and a network outside the region, such as a VCN in another Oracle Cloud Infrastructure region, an on-premises network, or a network in another cloud provider.

  • Site-to-Site VPN

    Site-to-Site VPN provides IPSec VPN connectivity between your on-premises network and VCNs in Oracle Cloud Infrastructure. The IPSec protocol suite encrypts IP traffic before the packets are transferred from the source to the destination and decrypts the traffic when it arrives.

  • Web Application Firewall (WAF)

    Oracle Cloud Infrastructure Web Application Firewall (WAF) is a payment card industry (PCI) compliant, regional-based and edge enforcement service that is attached to an enforcement point, such as a load balancer or a web application domain name. WAF protects applications from malicious and unwanted internet traffic. WAF can protect any internet facing endpoint, providing consistent rule enforcement across a customer's applications.

  • Load balancer

    The Oracle Cloud Infrastructure Load Balancing service provides automated traffic distribution from a single entry point to multiple servers in the back end.

  • Bastion host

    The bastion host is a compute instance that serves as a secure, controlled entry point to the topology from outside the cloud. The bastion host is provisioned typically in a demilitarized zone (DMZ). It enables you to protect sensitive resources by placing them in private networks that can't be accessed directly from outside the cloud. The topology has a single, known entry point that you can monitor and audit regularly. So, you can avoid exposing the more sensitive components of the topology without compromising access to them.

  • Autonomous Database

    Oracle Cloud Infrastructure Autonomous Database is a fully managed, preconfigured database environments that you can use for transaction processing and data warehousing workloads. You do not need to configure or manage any hardware, or install any software. Oracle Cloud Infrastructure handles creating the database, as well as backing up, patching, upgrading, and tuning the database.

  • Data Access Schema (DAS)

    Data access schema (DAS) is a way for certain tables in the merchandizing production database to be replicated to an on-premises or hosted environment to provide you with more direct access to your production data in order to build extensions, integration, custom reporting, and so on. DAS uses Oracle Cloud Infrastructure GoldenGate, a comprehensive software package for real-time data integration and replication in heterogeneous IT environments. If you purchased the subscriber license for using GoldenGate as part of your merchandising subscription, and then install and configure your target environment, you can specify which of the tables available for replication you want replicated to your target database. All tables in DAS are accessed by using database views. Views are used to ensure that, even if a column is dropped from a base table or is no longer used, the view continues to include all columns so that any integrations or other extensions built using the data will not fail, though they may need to be altered to remain functionally correct.

  • GoldenGate

    Oracle Cloud Infrastructure GoldenGate is a fully managed service that allows data ingestion from sources residing on premises or in any cloud, leveraging the GoldenGate CDC technology for a non intrusive and efficient capture of data and delivery to Oracle Autonomous Data Warehouse in real time and at scale in order to make relevant information available to consumers as quickly as possible.

Recommendations

Your requirements might differ from the architecture described here. Use the following recommendations as a starting point.

  • VCN

    When you create a VCN, determine the number of CIDR blocks required and the size of each block based on the number of resources that you plan to attach to subnets in the VCN. Use CIDR blocks that are within the standard private IP address space.

    Select CIDR blocks that don't overlap with any other network (in Oracle Cloud Infrastructure, your on-premises data center, or another cloud provider) to which you intend to set up private connections.

    After you create a VCN, you can change, add, and remove its CIDR blocks.

    When you design the subnets, consider your traffic flow and security requirements. Attach all the resources within a specific tier or role to the same subnet, which can serve as a security boundary.

  • Load balancer bandwidth

    While creating the load balancer, you can either select a predefined shape that provides a fixed bandwidth, or specify a custom (flexible) shape where you set a bandwidth range and let the service scale the bandwidth automatically based on traffic patterns. With either approach, you can change the shape at any time after creating the load balancer.

Considerations

Consider the following points when deploying this reference architecture.

Managed Services

Consider using Oracle managed services such as Autonomous Database, Autonomous Data Guard and Oracle Cloud Infrastructure GoldenGate.

A managed service provides specific functionality without requiring you to perform maintenance tasks related to optimizing performance, availability, scaling, security, or upgrading. With a managed service, you can focus on delivering features for your customers instead of worrying about the complexity of operations.

Acknowledgments

  • Authors: Wei Han
  • Contributors: Robert Lies, Nicole Champion