IM 310 Week 1 Introduction to Data Architecture Example

Fourteen Clinics, Six Systems: Mapping the Data Architecture of a Texas Veterinary Group

[Student Name]

University of Phoenix

IM/310: Data Analytics & Modeling

Week 1 Assignment

[Instructor Name]

[Date]

Lone Star Pet Health, its clinics, systems and figures are composites written for a model paper.

Lone Star Pet Health, a composite company, owns 14 veterinary clinics across central Texas, from Austin to Waco. It began as a single practice in Round Rock in 2009 and grew by buying independent clinics, eight of them in the past four years. It employs about 60 veterinarians and 280 support staff and handles about 190,000 patient visits a year. The owners want to grow services such as dental care and wellness plans and to know which clinics perform best and why. They cannot easily answer those questions. This paper examines why, starting with the group's data architecture.

What Data Architecture Means

Data architecture is the blueprint for how an organization's data are collected, stored, integrated, governed and used. It includes the systems that create data, the methods that move and combine data, the places data are stored, the models that define what data mean and the tools people use to reach them. Watson and Wixom (2007) describe business intelligence as a process with two parts: getting data in, through integration into a warehouse, and getting data out, through reports, analysis and dashboards, and they note that getting data in is usually the harder, more expensive part. Lone Star has plenty of data but no architecture for getting it in.

Inventory of Systems

Each system was examined for what it holds, who owns it and how its data leave it.

Practice management. Three different practice management systems run the clinics: one at six original clinics, another at five acquired clinics and a third at three clinics bought last year. Each records clients, pets, appointments, treatments, prescriptions and invoices, but with different field names and service codes.

Laboratory. An outside reference laboratory's portal holds test results, linked to patients by a clinic-assigned number.

Online booking. A booking tool on the website takes appointment requests and sends them by email to clinics.

Accounting. A cloud accounting package receives daily revenue totals entered by each clinic manager.

Payroll and scheduling. A human resources system holds staff schedules and pay.

Spreadsheets. Clinic managers keep spreadsheets for inventory, wellness plan members and marketing campaigns.

How Data Move

Almost all data movement is manual. Each night, clinic managers export a revenue summary from their practice system and type the totals into accounting. Once a month, the operations director asks each clinic to export visit data to spreadsheets, which an analyst combines by hand over about four days. Online booking requests are retyped into the practice systems. Lab results are viewed in the portal and sometimes copied into patient records.

Problems

Duplication and inconsistency. The same client may appear in three systems with different spellings and addresses, especially families who visit more than one clinic. Dental cleanings are coded "DENT-CLN" in one system, "Dental Prophy" in another and as two separate codes in the third, so counts across clinics are unreliable.

No single place to analyze. Because data sit in separate systems, every cross-clinic question requires manual exports, and the monthly combined spreadsheet is three to four weeks old by the time anyone sees it.

Errors from re-entry. Retyped revenue totals in accounting differed from practice system totals by more than 1 percent in 11 of 14 clinics last quarter.

Limited access and security. Spreadsheets with client data are emailed between managers, a privacy risk.

The owners asked a simple question about dental revenue and learned it would take an analyst about a week to answer it.

Architecture Options

Inmon (2005) described the data warehouse as a subject-oriented, integrated, time-variant and nonvolatile collection of data in support of management decisions, built centrally from operational systems. Kimball and Ross (2013) described a dimensional approach that builds the warehouse around business processes, such as visits and invoices, with conformed dimensions, such as client, pet and clinic, shared across them. Both approaches separate operational systems, which run daily work, from an analytical store designed for reporting.

A simpler alternative would be to replace all three practice systems with one. That would reduce inconsistency but cost about $600,000 and disrupt every clinic, and it would not integrate lab, booking and accounting data on its own.

Recommended Target Design

The recommended target has four layers. Sources remain the existing systems for now. An integration layer extracts data nightly from each practice system, the lab portal, booking and accounting through their export functions or interfaces, maps codes to common definitions and matches clients across systems. A central data store, a cloud data warehouse, holds integrated data organized around visits, invoices, patients, clients and clinics. An access layer provides dashboards for owners and clinic managers and secure access for analysts. Spreadsheets with client data will be retired.

What the Design Will Allow

With the target design, owners could compare dental revenue per visit across clinics the morning after, track wellness plan renewals across the group and see which marketing campaigns bring new clients. Clinic managers would stop retyping revenue. The operations director would stop spending four days a month combining spreadsheets.

Cost and Timing

The integration layer and cloud data store are estimated at about $85,000 to build and $30,000 a year to run, far less than replacing the practice systems. Work would start with the six original clinics, whose system is best documented, then add the others over six months. Consolidating practice systems can be reconsidered later, once the group sees which system works best.

Ownership and Security

Each source system will have a named data owner. Access to client and patient data will be based on role, and client data will be protected under the group's privacy policy. The operations director will own the data store and its definitions.

Conclusion

Lone Star's growth by acquisition left it with six systems, manual data movement and no shared definitions. A layered architecture with an integration layer, a central data store and access tools would let the group answer basic management questions without rebuilding every clinic's software. The weeks ahead will design the data models, relational schemas and warehouse structures this architecture needs.

References

Inmon, W. H. (2005). Building the data warehouse (4th ed.). Wiley.

Kimball, R., & Ross, M. (2013). The data warehouse toolkit: The definitive guide to dimensional modeling (3rd ed.). Wiley.

Watson, H. J., & Wixom, B. H. (2007). The current state of business intelligence. Computer, 40(9), 96-99. https://doi.org/10.1109/MC.2007.331