Competency Center Solutions Skill
Overview
Provides specialized knowledge for competency center and business intelligence engagements including data architecture design, KPI framework development, dashboard creation, and analytics operating model establishment.
KPI Framework Development
KPI Definition Template
KPI Name: [Name]
Business Owner: [Role]
Definition: [Clear, unambiguous description]
Formula: [Numerator / Denominator]
Unit: [%, $, #, etc.]
Frequency: [Daily/Weekly/Monthly/Quarterly]
Data Source: [System(s)]
Target: [Value and rationale]
Threshold: Red < [X] < Yellow < [Y] < Green
Trend Direction: Higher is Better / Lower is Better
Related KPIs: [Dependencies]
KPI Categories Framework
Strategic KPIs
├── Lagging indicators
├── Outcome-focused
└── Board/Executive level
Operational KPIs
├── Leading indicators
├── Process-focused
└── Management level
Diagnostic KPIs
├── Root cause indicators
├── Drill-down metrics
└── Analyst level
Balanced Scorecard Alignment
| Perspective | Focus | Example KPIs |
|---|
| Financial | Shareholder value | Revenue, Margin, ROIC |
| Customer | Value proposition | NPS, Retention, CSAT |
| Process | Operational excellence | Cycle time, Quality, Cost |
| Learning | Innovation capability | Training, Patents, Engagement |
KPI Hierarchy (Driver Tree)
┌─────────────┐
│ Revenue │
└──────┬──────┘
┌───────┴───────┐
┌────┴────┐ ┌────┴────┐
│ Volume │ │ Price │
└────┬────┘ └────┬────┘
┌──────┼──────┐ ┌────┴────┐
│Customers│Units│ │Mix│Rate│
└──────────────┘ └─────────┘
Dashboard Design Principles
Information Hierarchy
Dashboard Layout (Z-Pattern Reading):
┌────────────────────────────────────┐
│ HEADLINE KPIs (1-3 key metrics) │ ← First glance
├────────────────────────────────────┤
│ TRENDS │ COMPARISONS │ ← Context
├────────────────────────────────────┤
│ DETAIL TABLES / DRILL-DOWN │ ← Analysis
└────────────────────────────────────┘
Visual Selection Guide
| Data Type | Best Visual | Avoid |
|---|
| Single metric | Card/KPI tile | Pie chart |
| Trend over time | Line chart | Bar chart |
| Category comparison | Bar chart | Pie chart (>5 items) |
| Part-to-whole | Stacked bar, treemap | 3D pie |
| Distribution | Histogram, box plot | Line chart |
| Correlation | Scatter plot | Line chart |
| Geographic | Map | Table |
Dashboard Checklist
Color Usage Guidelines
Semantic Colors:
├── Green: Good/Positive/On track
├── Yellow/Amber: Warning/Caution
├── Red: Bad/Negative/Critical
├── Blue: Neutral/Informational
└── Gray: Secondary/Disabled
Avoid:
├── More than 5 colors per visual
├── Color as only differentiator (accessibility)
├── Bright/saturated colors for large areas
└── Red-green only (colorblind users)
Data Architecture Patterns
Data Warehouse Architecture (Kimball)
Source Systems → Staging → Data Warehouse → Data Marts → BI Tools
↓
Dimension Tables ←→ Fact Tables
(Who, What, Where) (Measures)
Dimensional Modeling
Fact Table Design:
fact_sales
├── date_key (FK)
├── customer_key (FK)
├── product_key (FK)
├── store_key (FK)
├── quantity (Measure)
├── revenue (Measure)
├── cost (Measure)
└── profit (Measure)
Dimension Table Design:
dim_customer
├── customer_key (PK)
├── customer_id (Natural key)
├── customer_name
├── segment
├── region
├── effective_date
├── end_date
└── is_current (SCD Type 2)
Modern Data Architecture (Lakehouse)
┌──────────────────────────────────────────┐
│ Bronze Layer │
│ (Raw data, as-is ingestion) │
├──────────────────────────────────────────┤
│ Silver Layer │
│ (Cleaned, conformed, validated) │
├──────────────────────────────────────────┤
│ Gold Layer │
│ (Business-ready, aggregated, modeled) │
└──────────────────────────────────────────┘
Data Quality Framework
Data Quality Dimensions
| Dimension | Definition | Measurement |
|---|
| Accuracy | Data correctly represents reality | Error rate |
| Completeness | All required data present | % populated |
| Consistency | Same data across systems | Match rate |
| Timeliness | Data current and available | Latency |
| Validity | Data conforms to rules | Validation pass rate |
| Uniqueness | No duplicate records | Duplicate rate |
Data Quality Scorecard
Overall Score: [85%]
By Dimension:
├── Accuracy: 90%
├── Completeness: 85%
├── Consistency: 80%
├── Timeliness: 95%
├── Validity: 88%
└── Uniqueness: 92%
By Critical Data Element:
├── Customer ID: 99%
├── Revenue: 95%
├── Date: 100%
└── Product Code: 88%
Data Quality Rules Template
| Rule ID | Data Element | Rule Type | Rule Definition | Threshold |
|---|
| DQ-001 | Email | Format | Valid email pattern | 100% |
| DQ-002 | Revenue | Range | > 0 and < $10M | 99.9% |
| DQ-003 | Date | Completeness | Not null | 100% |
COE Operating Model
COE Structure Options
Centralized COE:
├── Single team serves entire organization
├── Consistent standards and governance
├── Resource efficiency
└── May create bottleneck
Federated COE:
├── Central standards, distributed execution
├── Business unit analysts embedded
├── Flexibility with governance
└── Balance of control and agility
Hybrid COE:
├── Core team for platform/standards
├── Embedded analysts for business
├── Community of practice
└── Most common model
COE Service Catalog
| Service | Description | SLA |
|---|
| Dashboard Development | New dashboard creation | 2-4 weeks |
| Report Request | Standard report creation | 1 week |
| Ad-hoc Analysis | One-time analysis | 3-5 days |
| Data Integration | New source onboarding | 4-6 weeks |
| Training | BI tool training | Scheduled |
| Support | Break-fix, questions | 24-48 hours |
COE Roles
| Role | Responsibilities |
|---|
| COE Lead | Strategy, stakeholder management |
| Data Architect | Data modeling, platform design |
| BI Developer | Dashboard/report development |
| Data Engineer | ETL, data pipelines |
| Data Analyst | Analysis, insights, support |
| Data Steward | Quality, governance, definitions |
Platform Selection
BI Platform Comparison Matrix
| Criterion | Power BI | Tableau | Qlik | Looker |
|---|
| Visualization | Good | Excellent | Good | Good |
| Self-Service | Excellent | Good | Good | Fair |
| Governance | Good | Good | Excellent | Excellent |
| Embedded | Good | Good | Good | Excellent |
| Cost | Low | High | Medium | High |
| Learning Curve | Low | Medium | Medium | High |
Data Platform Comparison
| Criterion | Snowflake | Databricks | BigQuery | Redshift |
|---|
| Ease of Use | Excellent | Good | Excellent | Good |
| Performance | Excellent | Excellent | Excellent | Good |
| ML Integration | Good | Excellent | Good | Good |
| Cost Model | Usage | Compute | Usage | Provisioned |
| Data Sharing | Excellent | Good | Good | Fair |
TCO Calculation Template
Total Cost of Ownership (5 Year):
Year 0 (Implementation):
├── License/Subscription: $X
├── Implementation Services: $X
├── Infrastructure: $X
├── Training: $X
└── Change Management: $X
Annual (Years 1-5):
├── License/Subscription: $X
├── Infrastructure/Hosting: $X
├── Maintenance/Support: $X
├── Internal Resources: $X
└── Enhancements: $X
Total TCO: $XXX
Cost per User: $XXX
Industry-Specific Metrics
Healthcare
| Metric | Formula |
|---|
| ALOS | Total Patient Days / Discharges |
| CMI | Sum(DRG Weights) / Discharges |
| Readmission Rate | Readmissions / Total Admits |
| Bed Utilization | Occupied Beds / Available Beds |
| HCAHPS | Survey score composite |
Education
| Metric | Formula |
|---|
| Retention Rate | Returning Students / Prior Enrollment |
| Graduation Rate | Graduates / Starting Cohort |
| Net Tuition Revenue | Gross Tuition - Institutional Aid |
| Discount Rate | Institutional Aid / Gross Tuition |
| Yield | Enrolled / Admitted |
Financial Services
| Metric | Formula |
|---|
| Cost-to-Income | Operating Costs / Operating Income |
| NIM | (Interest Income - Interest Expense) / Assets |
| NPL Ratio | Non-Performing Loans / Total Loans |
| CAC | Sales & Marketing / New Customers |
| LTV | Average Revenue per Customer × Lifetime |
Manufacturing
| Metric | Formula |
|---|
| OEE | Availability × Performance × Quality |
| First Pass Yield | Good Units / Total Units |
| Scrap Rate | Scrapped Units / Total Units |
| On-Time Delivery | On-Time Orders / Total Orders |
| Inventory Turns | COGS / Average Inventory |
Implementation Approach
Phase 1: Foundation (4-6 weeks)
- Requirements gathering
- Architecture design
- Platform selection/setup
- Governance framework
Phase 2: Build (6-10 weeks)
- Data integration
- Data modeling
- Dashboard development
- Security configuration
Phase 3: Deploy (2-4 weeks)
- User acceptance testing
- Training delivery
- Go-live support
- Documentation
Phase 4: Sustain (Ongoing)
- Adoption monitoring
- Continuous improvement
- New use cases
- Capability building
References
See references/ folder for:
- KPI templates by industry
- Dashboard examples
- Data model patterns
- Platform comparison details