Business Intelligence Network - The Vision for BI and Beyond

Quality Assurance Framework Lowers Total Cost of Ownership

Published: June 6, 2006

A technically sound business intelligence and data warehouse architecture component addresses issues and challenges facing pharmaceutical and biotechnology companies.

This is the first article in a series that outlines the inherent value for implementing a quality assurance framework (QAF) within a business intelligence (BI) and data warehousing (DW) environment. It’s a proven, technically sound BI and DW architecture component that addresses several issues and challenges facing pharmaceutical and biotechnology companies today and, most importantly, provides a lower total cost of ownership option with respect to 21 CFR Part 11.

After providing a high-level overview of the framework within this article, we will outline its practical application within business intelligence and data warehousing environments as well as the significant value being realized within the pharmaceutical and biotechnology industries.

Industry-Wide Challenge
Conversion Services International, Inc. performs approximately 35 business intelligence and data warehousing strategy engagements on an annual basis. Within the scope of these engagements, we define business, information and technology needs; confirm critical success factors; assess the strengths and weaknesses of the BI and DW environment by benchmarking it against our best practices; and then deliver strategically aligned and experience-based recommendations to advance business objectives and maximize the return on investment (ROI) with business intelligence and data warehousing technology.

Through our experience, we have found that most companies in the pharmaceutical and biotechnology industries are facing very similar issues and challenges with respect to their business intelligence and data warehousing environments. These challenges include:  

• Executive management is questioning the value of their BI and DW investment because a large percentage of the investments are not increasing operational efficiencies, creating business value or achieving internal user satisfaction.
  
• Escalating total cost of ownership
  
• Lower than expected user adoption rates
  
• Data quality deficiencies, which are impeding the ability to consistently provide accurate, reliable and trusted information for decision-making purposes
  
• Compliance with the FDA’s 21 CFR Part 11

Within 78% of these strategy engagements, implementing the quality assurance framework has been the number one experienced-based recommendation made to clients. It not only addresses the noted issues and challenges in the most efficient and cost-effective way, but it also has become the foundation on which to build a best practice business intelligence and data warehousing program.

Quality Assurance Framework (QAF)
QAF consists of three functional components: metadata management, process management and reporting, and analytics (outlined in more detail below). QAF leverages existing information technology investments and seamlessly integrates with enterprise business intelligence suites (EBIS), data warehousing tools and technologies, enterprise schedulers, corporate and EBIS portals, database management systems and application development technologies. It is constructed using an experience-based design principle known as generic, reusable functional processes that allows software and software wrappers to be developed once – at their most atomic level – to facilitate reuse.

Figure 1: Quality Assurance Framework

Metadata Management
The metadata management component establishes a secure, Web-enabled, centralized collection point for searching, accessing, publishing and administrating all relevant metadata and documentation, including:

• Project-related documentation
• Project management
• Work products and deliverables
• Validation master summary report
  
  
• Source system documentation
• Process flows
• Data models
• User guides
• System administration guides
• Functional and technical specifications
• Systems and operational management guides
• Support team contacts
• Business groups and users
• Application, data and technical environments
• Service level agreements 
  
  
• Business intelligence and data warehousing  documentation
• Technical  architecture
• Data architecture
• Technical  environments
• Technical specifications
• Service level agreements
• Support organization and contacts
• Standards and procedures: development, configuration, administration, and performance monitoring and tuning
• System management procedures: configuration management, security management, backup procedures, archive procedures, change control management, system maintenance and problem logs, and business recovery
  
  
• End-user training
• End-user training materials
• Schedules, course curriculum and locations for both informal and formal education workshops
  
  
• End-user support
• Support repositories containing tips, techniques and frequently asked questions (FAQs)
• End-user support operation covering the roles and responsibilities of a multitiered support infrastructure (e.g., tier 1, 2 & 3), engineered support processes, policies and procedures, and governance for service level objectives
• Support logs identifying deficiencies in service and software delivery used to manage the end-user support operation with log counts, responsiveness, expended effort and target areas for refined training
  
  
• Business intelligence and data warehousing program
• Comprehensive internal marketing program promoting the content, capabilities and business value derived from the business intelligence and data warehouse investment
• A road map communicating short- and long-term strategic objectives, as well as plans to reach those objectives; alignment with business strategy; current and planned project initiatives; diagrams that depict current and future architecture state(s); deployed tools and technologies; and content covering integrated data sources, latency requirements, developed subject areas and deployed solutions
• Post-implementation reports that measure data warehouse performance against project success criteria (such as service level metrics, business impact metrics and business process performance metrics) and assess overall satisfaction with software and service delivery
• Customer satisfaction survey results
• Steering committee reports
  

Metadata management is implemented under an EBIS or corporate portal.

Process Management
The process management component audits the execution of schedules, jobs and the technology-dependent functional processes that ensure the end-to-end integration and distribution of data within a business intelligence and data warehouse architecture. The audit trail is rich and comprehensive, and captures vital information about an executed functional process including start and end times, estimated and actual elapsed times, source record counts, source reject record counts, target record counts, read throughput (bytes/sec) and write throughput (rows/sec). The information is stored in a secure, underlying database that is leveraged by the reporting and analytics component of the QAF.  

The process management component is implemented on the BI and DW infrastructure and seamlessly integrates with enterprise schedulers, workflow management systems, operating system scripting languages, database management systems, etc.

Figure 2: Sample Deployment

Reporting and Analytics
The reporting and analytics component delivers a series of Web-enabled, analytical views for in-line insight to the operation, processing and state of data quality within a business intelligence and data warehousing environment. As a result, business intelligence and data warehousing professionals are well-positioned to enhance performance, adoption rate, end-user satisfaction and data quality, thereby maximizing ROI and lowering the total cost of ownership.

The reporting and analytics component is comprised of four subcomponents: BI and DW dashboard, performance and tuning, audits and controls, and data quality.

BI and DW Dashboard
BI and DW dashboard is the barometer for monitoring and measuring service levels against key performance indicators (KPIs), as well as communicating the organizational performance and capabilities for managing a business intelligence and data warehousing investment.

There are a vast number of KPIs from which to choose; however, at a minimum, a BI and DW program should consider:

1. Availability
   Percentage of time the environment is available within the defined hours of operation for access with uninterrupted service.
   
   
2. Timeliness
   Percentage of time the environment successfully completes scheduled batch processes on time.
   
   
3. Adoption
   Percentage of registered users utilizing the environment on a daily, weekly, monthly, quarterly and annual basis, along with prior period comparisons.
   
   
4. Response Time
   Percentage of time the environment delivers information or data within the defined response-time requirement for small, medium and large requests.
   
   
5. End-User Support Response Time
   Percentage of timely responses by the BI/DW support team for each classification of tier 2 end-user support requests.
   
   
6. End-User Satisfaction
   Percentage of registered end users who perceive the environment is providing adequate service and value.
   
   
7. Software Quality
   Percentage of recorded deviations: software to total business requirements, software to technical specifications, software to executed test plans.
   
   
8. Data Quality Management
   Number of executed data verification and validation checks along with the percentage of data quality exceptions.
   
   
9. Data Quality Governance
   The assessment of data quality by several “By Items” addressing completeness, consistency, domain conformance, attribute rule integrity, structure integrity, reasonableness, format and patterns, and date/times and numeric.

Figure 3 illustrates a V-model for governing data quality metrics.

Figure 3: V- Model for Governing Data Quality Metrics

 

Performance and Tuning
Performance and tuning enables the monitoring of overall performance of BI and DW architecture components and the identification of current and potential bottlenecks with technology-dependent functional processes. Special attention is always given to metrics such as the number of records processed, elapsed time and throughput because these values typically outline processing trends to guide performance remediation and capacity planning.

Audit and Controls
Audit and controls independently report all inbound and outbound activity on the BI and DW platform, as well as the controls of in-stream validation checks to ensure the consistency, accuracy and reliability of end-to-end data integration and distribution.

Data Quality
Data quality reporting addresses four areas: source system, cross-system, downstream integration and subject area verification. This closed-loop feedback process enables data owners, data custodians and BI/DW professionals to monitor and continually improve data quality performance over time. Summary and detail reports accelerate the identification and remediation of data quality issues.

The reporting and analytics component is implemented under the EBIS or corporate portal and on the business intelligence and data warehouse infrastructure.

Next Article Overview
The next article in the series will discuss the QAF business value and practical application within BI and DW environments when complying with the FDA’s 21 CFR Part 11: Controls for Electronic Records and Signatures. This rule was a major achievement for the pharmaceutical industry as it satisfied their desire to become paperless. However, it generated a whole new series of regulatory requirements for the computerized systems that create, store, manipulate, report or transmit the electronic data – and an escalating total cost of ownership. Statistics have shown the costs for applicable business intelligence and data warehousing initiatives have increased by as much as 55%.