IM 310 Week 4 Designing a Data Warehouse Schema Example

A Star for Every Visit: Designing a Warehouse Schema for a Veterinary Group's Reporting

[Student Name]

University of Phoenix

IM/310: Data Analytics & Modeling

Week 4 Assignment

[Instructor Name]

[Date]

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

Weeks 1 through 3 designed an architecture, data models and normalized tables for the Lone Star clinics. Normalized tables suit daily operations, recording each visit and invoice accurately. They suit reporting less well: answering a question about revenue by clinic, month and service requires joining many tables and is slow for casual users. This paper designs the warehouse schema for the central data store recommended in Week 1.

Why a Separate Warehouse Design

Kimball and Ross (2013) argue that analytical systems should be designed around business processes, such as orders or visits, using dimensional models that are simple for users to understand and fast to query. They contrast this with normalized operational designs, which optimize updates. Inmon (2005) favors first building an integrated, normalized enterprise warehouse from which departmental data marts are derived. For a group of Lone Star's size, with one analyst and owners who want answers quickly, Kimball's dimensional approach, built process by process with shared dimensions, offers results sooner.

The Business Questions

The owners' questions drive the design. Where is dental revenue per visit highest? How does revenue per patient differ by species and age group? Do new veterinarians generate less revenue per visit in their first year? How many visits come from clients who use more than one clinic? Which months have the most vaccine visits?

Choosing the Process and Grain

The first process to model is patient visits, the source of about 90 percent of revenue. The grain, the level of detail of each fact row, is one service line on one visit: for example, one dental cleaning performed by one veterinarian on one dog on one date. This fine grain allows any summary, by visit, day, clinic or service, and avoids losing detail.

Facts

The visit line fact table holds measures that can be added up: quantity, list price, discount, net revenue and cost of supplies used. It also holds foreign keys to each dimension and the visit number, kept as a reference without its own dimension.

Dimensions

Date: one row per day, with day of week, month, quarter, year, holiday flags and season.

Clinic: name, city, region, size, acquisition date and the practice system it used.

Patient: species, breed, sex, age group, weight category and home clinic.

Client: city, ZIP code, years as client and number of pets, with names and contact details kept out of the warehouse to protect privacy.

Service: the common service code from Week 2, category such as dental, vaccine or surgery and standard price.

Staff: role, veterinarian or technician, hire date and tenure band.

Golfarelli et al. (1998) proposed the dimensional fact model as a conceptual way to represent facts, measures, dimensions and hierarchies for warehouse design. Lone Star's dimensions include hierarchies, such as date to month to quarter and service to category, that let users drill down from summaries to detail.

In the warehouse, a dental cleaning is no longer a code in one of three systems; it is one row with seven ways to look at it.

Handling Changes

Some dimension attributes change. A pet's age group and weight category change as it grows; a clinic may be renamed or remodeled; a staff member moves from technician to veterinarian after graduating. For age and weight, the warehouse keeps history, adding a new patient row with effective dates when the category changes, so past visits remain linked to the pet's category at the time. For clinic names, the current name overwrites the old one, since owners want reports under current names.

Conformed Dimensions and a Second Fact Table

Wellness plans are a second process. A plan fact table, with one row per plan per month, holds the monthly fee, services used and remaining value. It shares the date, clinic, patient and client dimensions with the visit fact table. Because these dimensions are conformed, defined once and used by both, the owners can compare plan members' visit revenue with non-members' using the same definitions.

Star or Snowflake?

A snowflake schema would normalize the dimensions, for example splitting service category into its own table linked to the service dimension. That saves a little storage but adds joins and makes the model harder for owners and managers to use in reporting tools. With dimensions this small, a few hundred services and 14 clinics, the star schema's simplicity is worth more than the storage saved, so the design keeps each dimension as a single wide table.

Loading the Warehouse

The integration layer from Week 1 extracts data nightly, maps codes and identifiers using the models from Weeks 2 and 3 and loads the dimension tables first, then the facts. Each load is checked for row counts and totals against the source systems.

Testing With Questions

Dental revenue per visit by clinic requires one join of the fact table with the service and clinic dimensions, filtered to dental. Revenue by species and age group requires a join with the patient dimension. New veterinarians' revenue requires the staff dimension with tenure band. Each query runs in seconds on a year of data, about 1.1 million visit lines, even on a laptop.

Watson and Wixom's Lesson

Watson and Wixom (2007) noted that most of the effort and cost in business intelligence goes into getting data into the warehouse. Lone Star's experience fits: designing the star schema took two weeks; building the integration that feeds it will take most of the six-month project.

What Comes Next

Later fact tables can be added on the same dimensions: inventory movements, appointment bookings and staff schedules. Each will reuse date, clinic and staff, so the group's reporting grows without redefining what a clinic or a month means.

Conclusion

A star schema built around patient visits, with a fine grain, clear facts and conformed dimensions, gives Lone Star's owners fast, simple answers to their main questions. Handling changing attributes deliberately and adding a wellness plan fact table on shared dimensions sets up the analytics models in Week 5.

References

Golfarelli, M., Maio, D., & Rizzi, S. (1998). The dimensional fact model: A conceptual model for data warehouses. International Journal of Cooperative Information Systems, 7(2-3), 215-247. https://doi.org/10.1142/S0218843098000118

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