Legacy Migration to AWS Redshift Strategy and Source System Inventory

Project Overview and Data Strategy

• Project Goal: The primary objective is to transition from a decentralized, legacy reporting environment to a centralized, high-performance AWS Redshift data warehouse. This platform is intended to serve as a "steering compass" for executive leadership, providing real-time metrics and a unified view across various pharmacy operating companies.

• Current Data Inventory:

  • The organization manages over $300$ legacy reports.

  • These reports are distributed across multiple disconnected SQL servers.

  • Active operational data accounts for roughly $500\,GB$.

  • Historical archives were initially estimated at $600\,GB$ but clarified to be over $300\,GB$.

• The Warehouse Roadmap:

  • The strategy involves creating a "repeatable template" for operating companies to use during raw data ingestion.

  • This design aims to isolate data streaming per company initially but merge them at the final reporting layer to facilitate consolidated oversight.

  • Key priority: Ensuring data authenticity and identifying high-priority applications among the "top 10" used by the company.

Core Library of Source Systems

• Primary Systems (The Hub):

  • Framework LTC (Long-Term Care): A third-party system that acts as the core of the pharmacy operations. It holds order details and entire process data.

  • Framework ECM: Integrated with Framework LTC for content management.

  • Sage / ACPAC (Sage Jackpacks): The financial and management accounting backbone.

• System Interconnectivity:

  • Data flow is largely unidirectional; most peripheral systems pull from Framework LTC rather than feeding data back into it.

  • Framework LTC serves as the system of record for submitting claims through the switch and receiving "promise to pay" notifications.

• External and Third-Party Data Integrations:

  • MedSpan: A built-in repository within Framework LTC that receives monthly file updates.

  • NetSmart: A third-party application utilized for clinical data, CMS reporting, and inbound/outbound submissions.

  • Physician Portal: A source system for external interactions.

  • Online Fax Server: Existing system, though rarely queried for reporting given the data eventually reaches ECM.

  • SaaS Platforms: Includes NetSmart, physician portal, RGL, and a cloud-based LMS system (the latter two are not currently in scope for reporting).

Reporting and Business Intelligence (BI) Layer

• Current Infrastructure: Reports are currently managed through SAP BusinessObjects (BO).

• SAP BI Details:

  • Version: $4.2$.

  • Tooling: Uses Web Intelligence (WebI) as the primary interface for reporting. Crystal Reports is not currently used.

  • Semantic Layer: The system utilizes a Universe to contain data connections, objects, transformations, formulas, and filters.

• Server Architecture:

  • There are $8$ dedicated servers for BusinessObjects.

  • Dev Environment: $4$ servers.

  • Production Environment: $4$ servers.

• Data Consolidation Strategy:

  • There are roughly $310$ reports emerging from Framework, ACPAC, and ECM.

  • Many reports are likely duplicates or variations (e.g., monthly vs. quarterly versions); the actual unique count may be closer to $220$.

  • The goal is to identify the "minimum set of data" required for these reports to create optimized data marts in Redshift.

Technical Methodology and Logistics

• Audit Data Store (ADS) and SDK:

  • Rather than manual analysis of $300+$ reports, the team plans to query the Audit Data Store or use an SDK (Software Development Kit) / API to programmatically extract the metadata (headers, SQL providers, universes, and table usage).

  • This will identify exactly which of the hundreds of on-premise tables are actually utilized for reporting.

• Data Inbound/Outbound Logic:

  • The team must map both "Data In" (collection from sources/external files) and "Data Out" (consumers of reports) to design the centerpiece warehouse effectively.

  • This includes identifying folder structures used by developers (like the late Fred) for batch files and local executors.

• Access Requirements: Rich will provide Adil with access to the Dev Box to explore the BI environment programmatically.

Questions & Discussion

• Satya/Rich/Adil Exchange:

  • Question: Are we receiving data from outside sources not captured in application data?

  • Response: MedSpan is the main external monthly file. Claims and NCPDP interfaces are internal to the Framework database.

  • Question: How is data currently ingested into SQL servers? Can we reuse existing processes for AWS?

  • Response: Decisions will be made case-by-case; the priority is identifying whether data originates from an application or a manual batch process.

• Post-Meeting Discussion (Adil & Gaurav):

  • Gaurav advised recording all sessions because the technical details regarding servers and architectures are too complex to recall from memory.

  • They discussed the technical nature of SAP BO versus Tableau/Power BI. Gaurav noted that SAP BO functions as a reporting tool that generates SQL, and identifying the "pinpoint" tables used in its objects will allow direct movement of that data to AWS servers.

  • They plan to coordinate finding a resource familiar with the SAP BI metadata layer to speed up the extraction of data definitions.

Next Steps

• Immediate Tasks:

  • Secure access to the SAP BI 4.2 Dev server.

  • Determine if a programmatic query via SDK/API can export report metadata.

  • Review the list of $310$ reports to identify the "Golden Sources" of data.

  • Map out the "Data In" vs. "Data Out" liners to ensure no critical touchpoints are missed.

• Long-term Collaboration: Adil will be introduced to data architects from the "PaaS side" to align strategies and improve solutions for the warehouse migration.