Stability Study Orchestration & Analytics
Sapnity consolidated fragmented stability studies into a governed Power Platform hub — orchestrating pulls, LIMS results, expiry decisions and reporting through a single stability data model and real-time Power BI analytics.
1. Business Problem — Stability in Silos
The client ran dozens of stability studies per year across products, markets and storage conditions — but the process was spread across Excel files, LIMS exports and email threads.
- Protocols authored in Word and stored in shared drives.
- Pull schedules managed separately in spreadsheets by site coordinators.
- LIMS results exported and manually pasted into summary templates.
- Expiry / shelf-life decisions tracked in PowerPoint and email chains.
- Little ability to compare stability performance across products or sites.
When regulatory inspections asked for a complete stability story for a given product, QA and R&D had to stitch evidence from multiple systems with tight timelines.
2. Sapnity’s Mandate
Leadership asked Sapnity to:
- Create a governed stability hub without replacing the existing LIMS.
- Orchestrate pulls, results and decisions in a single lifecycle.
- Give QA, R&D and Regulatory a shared, real-time view of stability studies.
- Standardize how protocols, conditions and time points are captured.
- Enable Power BI analytics for shelf-life, trends and “what-if” scenarios.
The goal: move from “spreadsheet-driven coordination” to a data-driven, inspection-ready stability platform.
3. Before — How Stability Actually Ran Day-to-Day
On paper, the process was well defined. In reality, it looked like this:
- R&D drafted protocols and emailed PDFs to site coordinators.
- Each site created its own Excel sheet for pull schedules and sample IDs.
- LIMS handled testing but not the end-to-end study context.
- Study status lived in people’s inboxes and meeting notes.
- Trend analysis was done ad-hoc in Excel whenever regulators or leadership asked.
Every stability manager had a different “truth” — and nobody could see the global picture without days of manual consolidation.
4. After — Sapnity Stability Hub
Sapnity introduced a reusable stability orchestration pattern on Power Platform, designed to sit between LIMS, ERP and QA/R&D teams.
R&D, QA & Site Teams
Stability scientists, QA leads and site coordinators working across markets.
Stability Protocol & Study App
Model-driven Power App for protocols, conditions, products and time points.
Pull Scheduler & Task Engine
Power Automate engine that generates pulls, reminders and sample IDs per site.
Dataverse Stability Model
Studies, pulls, results, decisions, deviations and audit trail in one data model.
LIMS & QC Systems
Test results linked back to specific studies, conditions and time points.
SAP / ERP & Regulatory Data
Batch and market context for expiry decisions and labeling implications.
Power BI Stability Cockpit
Expiry trends, out-of-trend alerts and portfolio-level stability insights.
The pattern above is now used as the reference blueprint for all new products entering the client’s stability program.
5. Implementation Story — From Spreadsheets to Stability Hub
Phase 1 — Discovery & Study Mapping
- Catalogued existing studies, conditions and protocols across three major sites.
- Analyzed how Excel trackers, LIMS exports and SAP data were currently used.
- Defined a harmonized stability lifecycle from protocol approval to expiry decision.
Phase 2 — Stability Data Model
- Designed Dataverse tables for products, studies, conditions, pulls, results and decisions.
- Introduced master data for storage conditions, testing panels and time points.
- Set up row-level security for site, region and product responsibility.
Phase 3 — Protocol & Study App
- Built a model-driven app for R&D and QA to create and approve protocols.
- Allowed cloning of studies for line extensions and new markets.
- Enabled structured capture of justifications and regulatory references.
Phase 4 — Pull Scheduler & LIMS Integration
- Generated pulls automatically based on protocol time points and conditions.
- Sent reminders and tasks to site coordinators ahead of pull windows.
- Linked LIMS results back to the correct study, sample and time point.
Phase 5 — Decision Recording & Analytics
- Standardized how expiry and shelf-life decisions are recorded and justified.
- Built Power BI dashboards for trend analysis, OOT/OOS stability events and portfolio health.
- Made stability history available on demand for inspections and submissions.
6. Technical Architecture — Layered View
7. Outcomes & KPIs
| KPI | Before | After Sapnity |
|---|---|---|
| Time to compile stability package for inspection | 1–2 weeks of manual collation | 1–2 days with ready-made views & exports |
| On-time pulls across all sites | ~80% (manual tracking) | > 98% via scheduled tasks and reminders |
| Global visibility across studies | Fragmented reports by site | Single Power BI cockpit for portfolio stability |
| Effort to trend and visualize results | Ad-hoc Excel each time | Automated trend charts by product, condition, time point |
| Risk of missing critical stability signals | High (manual monitoring) | Reduced with centralized model and alerts |
8. Sapnity Differentiators
- Stability-Ready Blueprint: We brought a pre-defined pattern for studies, pulls, results and decisions, not a blank canvas.
- LIMS-Aware Architecture: The solution respects LIMS as the analytical source of truth while adding orchestration and context on Power Platform.
- Regulatory Storytelling: Data model and views were designed around the questions inspectors actually ask.
- Reuse for Future Products: New products and markets can be onboarded largely via configuration, not new code.
- Balanced Governance: R&D, QA and IT all have clearly defined roles in how stability processes can evolve.
With Sapnity, stability moved from a series of local trackers to a single, governed platform that leadership and regulators can trust.