Competency Center Solutions Skill

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

• Purpose clearly defined (monitor/analyze/explore)
• Target audience identified
• Maximum 7 metrics per view
• Visual hierarchy established
• Color used meaningfully (not decoratively)
• Drill-down paths defined
• Filters intuitive and minimal
• Mobile view considered
• Performance optimized (<3 sec load)
• Accessibility compliance checked

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