# app.py
# Pharma KPI Copilot
# - Auto-loads KPI Glossary Excel from same folder as app.py
# - Reads PDF for KPI definition / formula / notes
# - Fixes Excel mapping so report names show instead of "Not mapped"
# - Displays report / offering values as colored badges
# - Installs openpyxl automatically if missing
import os
import re
import sys
import subprocess
import importlib.util
import unicodedata
from pathlib import Path
from difflib import SequenceMatcher
def ensure_package(package_name: str):
if importlib.util.find_spec(package_name) is None:
print(f"Package '{package_name}' not found. Installing...")
subprocess.check_call([sys.executable, '-m', 'pip', 'install', package_name])
print(f"Package '{package_name}' installed successfully.")
# Required for pandas Excel engine
ensure_package('openpyxl')
import gradio as gr
import pandas as pd
from langchain_community.document_loaders import PyPDFLoader
from langchain_text_splitters import RecursiveCharacterTextSplitter
os.environ['TOKENIZERS_PARALLELISM'] = 'false'
SERVICENOW_INCIDENT_URL = ("https://sanofiservices.service-now.com/onesupport?id=sc_cat_item&sys_id=a5c743d39761b19cbb28fa871153afc3")
PDF_FILE = 'data.pdf'
DEFAULT_KPI_EXCEL = 'CIA Consolidated KPIs_MetricsGovernance (1).xlsx'
REPORT_FLAG_COLUMNS = [
'SFE', 'B360', 'OMNICHANNEL', 'C360', 'E&C', 'AC',
'Field Reporting', 'Content Reporting', 'Above Country', 'Country'
]
EXTRA_INFO_COLUMNS = [
'Placement in Offering', 'Calculated at:', 'Domain', 'Interaction', 'Channels', 'PowerBI Field/Measure'
]
MANUAL_ALIAS_MAP = {
# 'hcp reach in occp': 'HCPs in OCCP',
}
# =========================================================
# 1) TEXT HELPERS
# =========================================================
def fix_pdf_text(text: str) -> str:
if not text:
return ''
text = unicodedata.normalize('NFKC', text)
replacements = {
'fi': 'fi', 'fl': 'fl', '“': '"', '”': '"', '’': "'", '‘': "'", '–': '-', '—': '-', '\u00ad': '',
}
for bad, good in replacements.items():
text = text.replace(bad, good)
text = re.sub(r'(?<=\w)[θΘϑϴƟɵ](?=\w)', 'ti', text)
return text
def normalize_exact(text: str) -> str:
text = fix_pdf_text(text or '').lower().strip()
return re.sub(r'\s+', ' ', text)
def singularize_token(token: str) -> str:
token = token.strip().lower()
if len(token) > 4 and token.endswith('ies'):
return token[:-3] + 'y'
if len(token) > 3 and token.endswith('s') and not token.endswith('ss'):
return token[:-1]
return token
def normalize_loose(text: str) -> str:
text = fix_pdf_text(text or '').lower().strip()
text = text.replace('#', ' ').replace('%', ' ')
text = re.sub(r'[^a-z0-9]+', ' ', text)
text = re.sub(r'\s+', ' ', text).strip()
if not text:
return ''
return ' '.join(singularize_token(tok) for tok in text.split())
def tokenize_loose(text: str):
loose = normalize_loose(text)
return loose.split() if loose else []
STOPWORDS = {
'a', 'an', 'the', 'in', 'of', 'with', 'and', 'or', 'for', 'to', 'by', 'on',
'this', 'that', 'is', 'are', 'was', 'were', 'be', 'been', 'being',
'what', 'how', 'why', 'show', 'give', 'tell', 'me', 'please', 'explain',
'search', 'find', 'calculated', 'computed', 'measured', 'formula', 'mean', 'important',
}
def significant_tokens(text: str):
toks = tokenize_loose(text)
sig = [t for t in toks if t not in STOPWORDS]
return sig if sig else toks
def clean_user_query(text: str) -> str:
text = fix_pdf_text(text or '').strip()
text = re.sub(r'[?]+$', '', text).strip()
patterns = [
r'^what is\s+', r'^what s\s+', r'^show me\s+', r'^give me\s+', r'^tell me\s+',
r'^explain\s+', r'^find\s+', r'^search\s+for\s+', r'^how is\s+', r'^why is\s+',
]
lowered = text.lower()
for pat in patterns:
lowered = re.sub(pat, '', lowered).strip()
return lowered.strip()
def clean_formula_text(text: str) -> str:
text = fix_pdf_text(text or '').lower()
text = re.sub(r'--.*', '', text)
text = re.sub(r'\s+', '', text)
return text
def html_escape(text: str) -> str:
if text is None:
return ''
return (
str(text)
.replace('&', '&')
.replace('<', '<')
.replace('>', '>')
.replace('"', '"')
)
def nl2br(text: str) -> str:
return html_escape(fix_pdf_text(text)).replace('\n', '
')
def is_generic_followup_question(text: str) -> bool:
q = normalize_exact(text)
generic_patterns = [
r'^how is this calculated', r'^how is this computed', r'^how is this measured',
r'^what is the formula', r'^show formula', r'^show the formula', r'^give formula',
r'^why is this important', r'^explain this', r'^what does this mean',
]
return any(re.search(p, q) for p in generic_patterns)
def extract_kpi_name_from_notes(notes_text: str) -> str:
if not notes_text:
return ''
m = re.search(r'\*\*KPI Name:\*\*\s*(.+)', notes_text)
return m.group(1).strip() if m else ''
def resolve_alias(user_query: str):
cleaned = clean_user_query(user_query)
q = normalize_loose(cleaned)
if not q:
return user_query, None, None
alias_map_norm = {normalize_loose(k): v for k, v in MANUAL_ALIAS_MAP.items()}
if q in alias_map_norm:
return alias_map_norm[q], q, alias_map_norm[q]
return cleaned, None, None
# =========================================================
# 2) EXCEL LOADING AND MAPPING
# =========================================================
def is_truthy_excel_value(value):
if pd.isna(value):
return False
return str(value).strip().lower() in {'yes', 'y', 'true', '1', 'x'}
def detect_glossary_header_row(raw_df: pd.DataFrame):
"""Find the real KPI Glossary header row."""
for idx in range(min(len(raw_df), 60)):
row_values = [normalize_exact(str(v)).replace('/', ' ') for v in raw_df.iloc[idx].tolist()]
if 'metrics kpis' in row_values and 'powerbi field measure' in row_values:
return idx
joined = ' | '.join(row_values)
if 'metrics kpis' in joined and ('powerbi field measure' in joined or 'definitions' in joined):
return idx
return None
def build_glossary_dataframe(excel_path: str):
raw = pd.read_excel(excel_path, sheet_name='KPI Glossary', header=None, engine='openpyxl')
header_row = detect_glossary_header_row(raw)
if header_row is None:
return None, None
header = [str(x).strip() for x in raw.iloc[header_row].tolist()]
data = raw.iloc[header_row + 1:].copy().reset_index(drop=True)
data.columns = header
data = data.dropna(how='all')
keep_cols = [str(c).strip() != '' and str(c).strip().lower() != 'nan' for c in data.columns]
data = data.loc[:, keep_cols]
data.columns = [str(c).strip() for c in data.columns]
return data, header_row
def merge_excel_record(a: dict, b: dict):
if not a:
return b
if not b:
return a
merged = {
'kpi_name': a.get('kpi_name') or b.get('kpi_name', ''),
'measure_name': a.get('measure_name') or b.get('measure_name', ''),
'report_sources': sorted(set(a.get('report_sources', [])) | set(b.get('report_sources', []))),
'extra_info': {},
'row_ids': sorted(set(a.get('row_ids', [])) | set(b.get('row_ids', []))),
}
for col in EXTRA_INFO_COLUMNS:
vals = []
for rec in (a, b):
val = rec.get('extra_info', {}).get(col)
if val and val not in vals:
vals.append(val)
if vals:
merged['extra_info'][col] = ' | '.join(vals)
return merged
def add_record_to_mapping(mapping: dict, key: str, record: dict):
if not key:
return
mapping[key] = merge_excel_record(mapping.get(key), record) if key in mapping else record
def load_kpi_excel_mapping(excel_path: str):
if not excel_path or not Path(excel_path).exists():
print(f'Excel not found: {excel_path}')
return {}
try:
df, header_row = build_glossary_dataframe(excel_path)
except Exception as e:
print(f'Could not read KPI Glossary sheet: {e}')
return {}
if df is None or df.empty:
print('Could not detect KPI Glossary header row or data is empty.')
return {}
print(f'KPI Glossary header row detected at: {header_row}')
print(f'KPI Glossary columns detected: {list(df.columns)[:20]}')
kpi_col = 'Metrics/KPIs' if 'Metrics/KPIs' in df.columns else None
measure_col = 'PowerBI Field/Measure' if 'PowerBI Field/Measure' in df.columns else None
if not kpi_col and not measure_col:
print('Metrics/KPIs and PowerBI Field/Measure columns not found.')
return {}
mapping = {}
for idx, row in df.iterrows():
kpi_name = str(row.get(kpi_col, '')).strip() if kpi_col else ''
measure_name = str(row.get(measure_col, '')).strip() if measure_col else ''
if not kpi_name and not measure_name:
continue
report_sources = [col for col in REPORT_FLAG_COLUMNS if col in df.columns and is_truthy_excel_value(row.get(col))]
extra_info = {}
for col in EXTRA_INFO_COLUMNS:
if col in df.columns:
val = row.get(col)
if pd.notna(val) and str(val).strip():
extra_info[col] = str(val).strip()
record = {
'kpi_name': kpi_name,
'measure_name': measure_name,
'report_sources': sorted(set(report_sources)),
'extra_info': extra_info,
'row_ids': [int(idx)],
}
if kpi_name:
add_record_to_mapping(mapping, normalize_loose(kpi_name), record)
if measure_name:
add_record_to_mapping(mapping, normalize_loose(measure_name), record)
print(f'Final mapped KPI keys: {len(mapping)}')
return mapping
def excel_candidate_keys(*texts):
keys = []
for t in texts:
if not t:
continue
k = normalize_loose(t)
if k and k not in keys:
keys.append(k)
return keys
def excel_token_coverage_score(query_key: str, candidate_key: str):
q_tokens = significant_tokens(query_key)
c_tokens = significant_tokens(candidate_key)
if not q_tokens or not c_tokens:
return 0.0, 0
q_set, c_set = set(q_tokens), set(c_tokens)
overlap = q_set & c_set
return len(overlap) / max(len(q_set), 1), len(overlap)
def lookup_kpi_excel_info(kpi_name: str, measure_name: str, excel_mapping: dict, query_text: str = None):
if not excel_mapping:
return None
keys = excel_candidate_keys(query_text, kpi_name, measure_name)
result = None
# exact lookup
for key in keys:
if key in excel_mapping:
result = merge_excel_record(result, excel_mapping[key]) if result else excel_mapping[key]
if result:
return result
# fuzzy fallback
best_key = None
best_ratio = 0.0
for q in keys:
for cand in excel_mapping.keys():
coverage, overlap = excel_token_coverage_score(q, cand)
ratio = SequenceMatcher(None, q, cand).ratio()
if coverage >= 1.0 or ratio >= 0.84 or (overlap >= 2 and ratio >= 0.70):
if ratio > best_ratio:
best_ratio = ratio
best_key = cand
return excel_mapping.get(best_key) if best_key else None
def load_default_excel_if_present():
return load_kpi_excel_mapping(DEFAULT_KPI_EXCEL) if Path(DEFAULT_KPI_EXCEL).exists() else {}
def search_kb_article(query, kb_articles):
q = normalize_loose(query)
if not q:
return None
best_match = None
best_score = 0
for article in kb_articles:
search_text = (
article["meta"] + " " +
article["tags"]
)
candidate = normalize_loose(search_text)
if not candidate:
continue
ratio = SequenceMatcher(
None,
q,
candidate
).ratio()
if q in candidate:
ratio += 0.5
if ratio > best_score:
best_score = ratio
best_match = article
if best_score >= 0.60:
return best_match
return None
KB_EXCEL = "kb_knowledge.xlsx"
def load_kb_articles(excel_path):
if not Path(excel_path).exists():
print(f"KB file not found: {excel_path}")
return []
try:
df = pd.read_excel(
excel_path,
engine="openpyxl"
).fillna("")
print("KB Columns:")
print(df.columns.tolist())
articles = []
for _, row in df.iterrows():
articles.append({
"number": str(row.get("Number", "")).strip(),
"author": str(row.get("Author", "")).strip(),
"description": str(row.get("Short description", "")).strip(),
"meta": str(row.get("Meta", "")).strip(),
"tags": str(row.get("Tags", "")).strip(),
# Replace with actual column name
"link": str(row.get("Article Link", "")).strip()
})
print(f"KB Articles Loaded: {len(articles)}")
return articles
except Exception as e:
print("KB Load Error:", e)
return []
# =========================================================
# 3) PDF LOAD / PARSE
# =========================================================
loader = PyPDFLoader(PDF_FILE)
page_docs = loader.load()
for d in page_docs:
d.page_content = fix_pdf_text(d.page_content)
# ---------------------------------------------------------
# Split PDF into KPI sections instead of character chunks
# ---------------------------------------------------------
chunk_docs = []
for page in page_docs:
text = page.page_content
sections = re.split(
r'\n\s*Name\s*\n',
text,
flags=re.IGNORECASE
)
for section in sections:
section = section.strip()
if not section:
continue
if not section.lower().startswith("name"):
section = "Name\n" + section
new_doc = type(page)(
page_content=section,
metadata=page.metadata.copy()
)
chunk_docs.append(new_doc)
print(f"KPI sections created: {len(chunk_docs)}")
def normalize_lines(text: str):
return [line.strip() for line in fix_pdf_text(text).splitlines() if line.strip()]
def is_metadata_line(line: str) -> bool:
line = normalize_loose(line)
patterns = [
r'^name$', r'^kpi id', r'^measure name', r'^description$', r'^definition$',
r'^business meaning$', r'^category$', r'^owner$', r'^source$', r'^dashboard$', r'^glossary$',
]
return any(re.search(p, line) for p in patterns)
def looks_like_formula_start(line: str) -> bool:
line = fix_pdf_text(line)
low = line.lower().strip()
formula_starts = [
'calculate(', 'sum(', 'count(', 'distinctcount(', 'divide(', 'if(', 'filter(',
'removefilters(', 'all(', 'average(', 'var ', 'return', 'switch(', 'countrows(',
'summarize(', 'lookupvalue(', 'selectedvalue(',
]
if any(fs in low for fs in formula_starts):
return True
if '[' in line and ']' in line:
return True
if '=' in line:
return True
return False
def extract_named_field(lines, labels):
wanted = [normalize_loose(x) for x in labels]
for i, line in enumerate(lines):
if normalize_loose(line) in wanted and i + 1 < len(lines):
return fix_pdf_text(lines[i + 1].strip())
return ''
def extract_label_block(lines, labels):
wanted = [normalize_loose(x) for x in labels]
start_idx = None
for i, line in enumerate(lines):
if normalize_loose(line) in wanted:
start_idx = i + 1
break
if start_idx is None:
return ''
collected = []
for j in range(start_idx, len(lines)):
current = fix_pdf_text(lines[j].strip())
if is_metadata_line(current) and normalize_loose(current) not in wanted:
break
collected.append(current)
return ' '.join(collected).strip()
def extract_formula(lines):
formula_lines = []
in_formula = False
paren_balance = 0
for i, line in enumerate(lines):
line = fix_pdf_text(line.strip())
if not in_formula and looks_like_formula_start(line):
in_formula = True
formula_lines.append(line)
paren_balance += line.count('(') - line.count(')')
continue
if in_formula:
if is_metadata_line(line) and paren_balance <= 0:
break
formula_lines.append(line)
paren_balance += line.count('(') - line.count(')')
if paren_balance <= 0:
next_line = fix_pdf_text(lines[i + 1].strip()) if i + 1 < len(lines) else ''
if next_line and is_metadata_line(next_line):
break
return '\n'.join(formula_lines).strip()
def remove_formula_lines(lines, formula_text):
if not formula_text:
return lines
formula_lines = {fix_pdf_text(x.strip()) for x in formula_text.splitlines() if x.strip()}
return [x for x in lines if fix_pdf_text(x.strip()) not in formula_lines]
def build_business_meaning(audience, kpi_name, measure_name):
base_name = fix_pdf_text(measure_name or kpi_name or 'This KPI')
if audience == 'Leadership':
return f"{base_name} helps leadership monitor performance and coverage trends for decision-making."
if audience == 'Analytics User':
return f"{base_name} is used in reporting and should be interpreted with source logic, filters, and exclusions."
return f"{base_name} helps business users understand what is being tracked and why it matters."
def parse_doc_entry(doc, audience, match_info=None, forced_kpi_name=None, excel_mapping=None, query_text=None):
context = fix_pdf_text(doc.page_content)
lines = normalize_lines(context)
formula = extract_formula(lines)
non_formula_lines = remove_formula_lines(lines, formula)
kpi_name = extract_named_field(non_formula_lines, ['Name'])
kpi_id = extract_named_field(non_formula_lines, ['KPI ID from KPI Glossary', 'KPI ID'])
measure_name = extract_named_field(non_formula_lines, ['Measure name in the PBI', 'Measure Name'])
if forced_kpi_name and (not kpi_name or normalize_loose(kpi_name) == 'not found'):
kpi_name = forced_kpi_name
definition = extract_label_block(non_formula_lines, ['Description', 'Definition'])
if not definition:
heur = []
for line in non_formula_lines:
low = line.lower()
if any(x in low for x in ['number of', 'count of', 'unique', '%', 'percent', 'rate of', 'ratio of', 'calculated as']):
heur.append(fix_pdf_text(line))
definition = ' '.join(heur[:3]).strip() or 'Definition not found clearly in the source extract.'
if not formula:
formula = 'Formula not found in source extract.'
excel_info = lookup_kpi_excel_info(kpi_name, measure_name, excel_mapping or {}, query_text=query_text)
report_sources = excel_info.get('report_sources', []) if excel_info else []
extra_excel_info = excel_info.get('extra_info', {}) if excel_info else {}
matched_rows = excel_info.get('row_ids', []) if excel_info else []
notes = []
if kpi_name:
notes.append(f"**KPI Name:** {fix_pdf_text(kpi_name)}")
if kpi_id:
notes.append(f"**KPI ID:** {fix_pdf_text(kpi_id)}")
if measure_name:
notes.append(f"**Power BI Measure:** {fix_pdf_text(measure_name)}")
if report_sources:
notes.append(f"**Report / Offering Presence (Yes columns):** {', '.join(report_sources)}")
if matched_rows:
notes.append(f"**Matched Excel Row Count:** {len(matched_rows)}")
if extra_excel_info.get('Placement in Offering'):
notes.append(f"**Placement in Offering:** {extra_excel_info['Placement in Offering']}")
if extra_excel_info.get('Calculated at:'):
notes.append(f"**Calculated at:** {extra_excel_info['Calculated at:']}")
if extra_excel_info.get('Domain'):
notes.append(f"**Domain:** {extra_excel_info['Domain']}")
if extra_excel_info.get('Interaction'):
notes.append(f"**Interaction:** {extra_excel_info['Interaction']}")
if extra_excel_info.get('Channels'):
notes.append(f"**Channels:** {extra_excel_info['Channels']}")
if doc.metadata.get('page') is not None:
notes.append(f"**Page:** {doc.metadata['page'] + 1}")
if match_info:
notes.append(f"**Primary Search Match:** {match_info}")
return {
'doc': doc,
'page': doc.metadata.get('page'),
'context': context,
'kpi_name': fix_pdf_text(kpi_name) or 'Not found',
'kpi_id': fix_pdf_text(kpi_id) or 'Not found',
'measure_name': fix_pdf_text(measure_name) or 'Not found',
'definition': fix_pdf_text(definition),
'business': build_business_meaning(audience, kpi_name, measure_name),
'formula': fix_pdf_text(formula),
'notes': '\n\n'.join(notes) if notes else 'No additional notes found.',
'report_sources': report_sources,
'excel_info': extra_excel_info,
}
PARSED_CHUNKS = [parse_doc_entry(doc, 'Business User') for doc in chunk_docs]
def recommend_kpis(user_query, top_n=5):
q = normalize_loose(user_query)
scores = []
for entry in PARSED_CHUNKS:
search_text = " ".join([
entry.get("kpi_name", ""),
entry.get("definition", ""),
entry.get("business", ""),
entry.get("measure_name", "")
])
candidate = normalize_loose(search_text)
ratio = SequenceMatcher(
None,
q,
candidate
).ratio()
scores.append((ratio, entry))
scores.sort(
key=lambda x: x[0],
reverse=True
)
return [x[1] for x in scores[:top_n]]
def entry_key(entry):
return (
normalize_exact(entry['kpi_name']),
normalize_exact(entry['measure_name']),
normalize_exact(entry['context'][:300]),
)
def build_indices(entries):
kpi_exact_index, measure_exact_index, kpi_loose_index, measure_loose_index = {}, {}, {}, {}
seen = set()
for entry in entries:
key = entry_key(entry)
if key in seen:
continue
seen.add(key)
nk_exact = normalize_exact(entry['kpi_name'])
nm_exact = normalize_exact(entry['measure_name'])
nk_loose = normalize_loose(entry['kpi_name'])
nm_loose = normalize_loose(entry['measure_name'])
if nk_exact and nk_exact != 'not found':
kpi_exact_index.setdefault(nk_exact, []).append(entry)
if nm_exact and nm_exact != 'not found':
measure_exact_index.setdefault(nm_exact, []).append(entry)
if nk_loose and nk_loose != 'not found':
kpi_loose_index.setdefault(nk_loose, []).append(entry)
if nm_loose and nm_loose != 'not found':
measure_loose_index.setdefault(nm_loose, []).append(entry)
return kpi_exact_index, measure_exact_index, kpi_loose_index, measure_loose_index
EXACT_KPI_INDEX, EXACT_MEASURE_INDEX, LOOSE_KPI_INDEX, LOOSE_MEASURE_INDEX = build_indices(PARSED_CHUNKS)
ALL_LOOSE_KPI_NAMES = sorted(LOOSE_KPI_INDEX.keys())
ALL_LOOSE_MEASURE_NAMES = sorted(LOOSE_MEASURE_INDEX.keys())
def token_overlap_score(query_text: str, candidate_text: str):
q_tokens = significant_tokens(query_text)
c_tokens = significant_tokens(candidate_text)
if not q_tokens or not c_tokens:
return 0.0, 0, 0
q_set, c_set = set(q_tokens), set(c_tokens)
overlap = q_set & c_set
coverage = len(overlap) / max(len(q_set), 1)
return coverage, len(overlap), len(c_set)
def find_best_exact_like_name(query_text: str):
q_exact = normalize_exact(query_text)
q_loose = normalize_loose(query_text)
if not q_loose:
return None, None
if q_exact in EXACT_KPI_INDEX:
return 'kpi_exact', q_exact
if q_exact in EXACT_MEASURE_INDEX:
return 'measure_exact', q_exact
if q_loose in LOOSE_KPI_INDEX:
return 'kpi_loose', q_loose
if q_loose in LOOSE_MEASURE_INDEX:
return 'measure_loose', q_loose
best, best_score = None, -1.0
for name in ALL_LOOSE_KPI_NAMES:
coverage, overlap_count, candidate_size = token_overlap_score(q_loose, name)
if coverage == 1.0 and overlap_count >= 2:
score = overlap_count * 10 - max(candidate_size - overlap_count, 0)
if score > best_score:
best_score, best = score, ('kpi_loose', name)
for name in ALL_LOOSE_MEASURE_NAMES:
coverage, overlap_count, candidate_size = token_overlap_score(q_loose, name)
if coverage == 1.0 and overlap_count >= 2:
score = overlap_count * 10 - max(candidate_size - overlap_count, 0)
if score > best_score:
best_score, best = score, ('measure_loose', name)
return best if best else (None, None)
def doc_contains_exact_text(doc, search_text: str) -> bool:
return normalize_loose(search_text) in normalize_loose(doc.page_content)
# =========================================================
# 4) SEARCH
# =========================================================
def choose_primary_entry(query: str, audience: str, excel_mapping=None):
cleaned_query = clean_user_query(query)
if not cleaned_query:
return None, None
resolved_query, _, canonical_term = resolve_alias(query)
effective_query = canonical_term if canonical_term else resolved_query
match_type, canonical_name = find_best_exact_like_name(effective_query)
if match_type == 'kpi_exact':
chosen = EXACT_KPI_INDEX[canonical_name][0]
return parse_doc_entry(chosen['doc'], audience, match_info='Exact KPI name match', excel_mapping=excel_mapping, query_text=effective_query), 100.0
if match_type == 'measure_exact':
chosen = EXACT_MEASURE_INDEX[canonical_name][0]
return parse_doc_entry(chosen['doc'], audience, match_info='Exact PBI measure match', excel_mapping=excel_mapping, query_text=effective_query), 95.0
if match_type == 'kpi_loose':
chosen = LOOSE_KPI_INDEX[canonical_name][0]
return parse_doc_entry(chosen['doc'], audience, match_info='Normalized KPI name match', excel_mapping=excel_mapping, query_text=effective_query), 90.0
if match_type == 'measure_loose':
chosen = LOOSE_MEASURE_INDEX[canonical_name][0]
return parse_doc_entry(chosen['doc'], audience, match_info='Normalized PBI measure match', excel_mapping=excel_mapping, query_text=effective_query), 88.0
raw_chunk_hits = [doc for doc in chunk_docs if doc_contains_exact_text(doc, effective_query)]
if raw_chunk_hits:
chosen_doc = raw_chunk_hits[0]
return parse_doc_entry(chosen_doc, audience, match_info='Exact raw text found in PDF chunk', forced_kpi_name=effective_query, excel_mapping=excel_mapping, query_text=effective_query), 75.0
raw_page_hits = [doc for doc in page_docs if doc_contains_exact_text(doc, effective_query)]
if raw_page_hits:
chosen_doc = raw_page_hits[0]
return parse_doc_entry(chosen_doc, audience, match_info='Exact raw text found in PDF page', forced_kpi_name=effective_query, excel_mapping=excel_mapping, query_text=effective_query), 70.0
return None, None
def find_second_same_occurrence(primary_entry, audience: str, excel_mapping=None):
target_name_loose = normalize_loose(primary_entry['kpi_name'])
if not target_name_loose or target_name_loose == 'not found':
return None
primary_context = normalize_exact(primary_entry['context'][:400])
if target_name_loose in LOOSE_KPI_INDEX:
candidates = [e for e in LOOSE_KPI_INDEX[target_name_loose] if normalize_exact(e['context'][:400]) != primary_context]
if candidates:
candidates.sort(key=lambda e: (e['page'] if e['page'] is not None else 99999))
return parse_doc_entry(candidates[0]['doc'], audience, excel_mapping=excel_mapping, query_text=primary_entry['kpi_name'])
for doc in chunk_docs:
if target_name_loose in normalize_loose(doc.page_content) and normalize_exact(doc.page_content[:400]) != primary_context:
return parse_doc_entry(doc, audience, forced_kpi_name=primary_entry['kpi_name'], excel_mapping=excel_mapping, query_text=primary_entry['kpi_name'])
for doc in page_docs:
if target_name_loose in normalize_loose(doc.page_content) and normalize_exact(doc.page_content[:400]) != primary_context:
return parse_doc_entry(doc, audience, forced_kpi_name=primary_entry['kpi_name'], excel_mapping=excel_mapping, query_text=primary_entry['kpi_name'])
return None
# =========================================================
# 5) UI HELPERS
# =========================================================
def compare_same(value1, value2, formula=False):
return clean_formula_text(value1) == clean_formula_text(value2) if formula else normalize_loose(value1) == normalize_loose(value2)
def render_badges(sources):
if not sources:
return "Not mapped"
colors = ['info', 'success', 'warning', 'neutral']
icons = {
"Above Country": "🌍",
"Country": "🏢",
"SFE": "📈",
"B360": "📊",
"OMNICHANNEL": "📱",
"C360": "🔄"
}
pills = []
for i, src in enumerate(sources):
color = colors[i % len(colors)]
label = f"{icons.get(src,'📌')} {src}"
pills.append(
f"{html_escape(label)}"
)
return ''.join(
f"{p}
"
for p in pills
)
def field_diff_html(left_text, right_text, formula=False):
left_text = fix_pdf_text(left_text or '')
right_text = fix_pdf_text(right_text or '')
if compare_same(left_text, right_text, formula=formula):
return "No difference. Both occurrences match for this field.
"
left_lines = [ln for ln in left_text.splitlines() if ln.strip()] or ['Not found']
right_lines = [ln for ln in right_text.splitlines() if ln.strip()] or ['Not found']
removed = [x for x in left_lines if x not in right_lines]
added = [x for x in right_lines if x not in left_lines]
removed_html = ''.join(f"{html_escape(line)}" for line in removed[:12]) or 'No unique lines found.'
added_html = ''.join(f"{html_escape(line)}" for line in added[:12]) or 'No unique lines found.'
return f"""
"""
def build_summary_cards(entry1, entry2=None, retrieval_score=None):
#page1 = f"Page {entry1['page'] + 1}" if entry1 and entry1['page'] is not None else 'Page not found'
report_badges = render_badges(entry1.get('report_sources', []))
cards = [
f"KPI Name
{html_escape(entry1['kpi_name'])}
PBI Measure
{html_escape(entry1['measure_name'])}
Report / Offering
{report_badges}
" + ''.join(cards) + "
"
def build_side_by_side_comparison(entry1, entry2):
if not entry1 and not entry2:
return "No relevant KPI entry found.
"
if entry1 and not entry2:
page_text = f"Page {entry1['page'] + 1}" if entry1['page'] is not None else 'Unknown page'
kpi_text = html_escape(entry1['kpi_name'])
return f"Primary result shown for {kpi_text} ({html_escape(page_text)}). No second occurrence with the exact same KPI name was found.
{html_escape(label)}
{'SAME' if status == 'same' else 'DIFFERENT'}
Occurrence 1
{nl2br(left_val)}
Occurrence 2
{nl2br(right_val)}
{diff_panel}
""")
return f"""
{html_escape(overall_text)}
Occurrence 1
{html_escape(page1)}
{html_escape(entry1['kpi_name'])}
Occurrence 2
{html_escape(page2)}
{html_escape(entry2['kpi_name'])}
{''.join(rows)}
"""
def on_satisfaction_change(choice):
if choice == "Yes":
return (
gr.update(visible=True), # rating_row
gr.update(visible=False), # followup_row
gr.update(visible=False), # still_not_satisfied_row
gr.update(value="", visible=False), # incident_html
gr.update(
value="Please rate the information from 1 to 5.",
visible=True
)
)
elif choice == "No":
html = """
"""
return (
gr.update(visible=False), # rating_row
gr.update(visible=False), # followup_row
gr.update(visible=False), # still_not_satisfied_row
gr.update(value=html, visible=True), # incident_html
gr.update(
value="Please use the ServiceNow link below to raise a support request.",
visible=True
)
)
return (
gr.update(visible=False),
gr.update(visible=False),
gr.update(visible=False),
gr.update(value="", visible=False),
gr.update(value="", visible=False),
)
# =========================================================
# 6) FEEDBACK FLOW
# =========================================================
def run_search_and_prepare_feedback(question, excel_mapping):
results = get_answer(question, "User",excel_mapping=excel_mapping)
current_kpi_name = ''
if isinstance(results, tuple) and len(results) >= 5:
current_kpi_name = ""
return results + (
current_kpi_name,
gr.update(visible=True), gr.update(value=None, visible=True),
gr.update(visible=False), gr.update(value=None), gr.update(value='', visible=False),
gr.update(visible=False), gr.update(value=''), gr.update(visible=False), gr.update(value=None),
gr.update(value='', visible=False), gr.update(value='', visible=False),
)
def clear_feedback_only():
return (
gr.update(visible=False), gr.update(value=None, visible=False),
gr.update(visible=False), gr.update(value=None), gr.update(value='', visible=False),
gr.update(visible=False), gr.update(value=''), gr.update(visible=False), gr.update(value=None),
gr.update(value='', visible=False), gr.update(value='', visible=False),
)
def submit_rating(rating):
if rating is None:
return gr.update(value='Please select a rating from 1 to 5.', visible=True)
return gr.update(value=f"Thanks for the feedback. You rated the definition **{rating}/5**.", visible=True)
def run_followup_search(followup_question, current_kpi_name, excel_mapping):
if not followup_question or not followup_question.strip():
return (
gr.update(), gr.update(), gr.update(), gr.update(), gr.update(), gr.update(),
gr.update(value=current_kpi_name), gr.update(visible=True), gr.update(value='No', visible=True),
gr.update(visible=False), gr.update(value=None), gr.update(value='', visible=False),
gr.update(visible=True), gr.update(value=''), gr.update(visible=True), gr.update(value=None),
gr.update(value='Please type a follow-up question before submitting.', visible=True), gr.update(value='', visible=False),
)
effective_followup = current_kpi_name if current_kpi_name and is_generic_followup_question(followup_question) else followup_question
used_context = effective_followup != followup_question
results = get_answer(effective_followup, "User", excel_mapping=excel_mapping)
new_current_kpi = current_kpi_name or ''
if isinstance(results, tuple) and len(results) >= 5:
extracted = extract_kpi_name_from_notes(results[4] or '')
if extracted:
new_current_kpi = extracted
helper_message = 'If you are still not satisfied, choose below to raise an incident.'
if used_context and current_kpi_name:
helper_message = f"Used KPI context from the previous result: **{current_kpi_name}**. If you are still not satisfied, choose below to raise an incident."
return results + (
new_current_kpi, gr.update(visible=True), gr.update(value='No', visible=True),
gr.update(visible=False), gr.update(value=None), gr.update(value='', visible=False),
gr.update(visible=True), gr.update(value=followup_question), gr.update(visible=True), gr.update(value=None),
gr.update(value=helper_message, visible=True), gr.update(value='', visible=False),
)
def build_chat_html(question, entry, kb_article=None):
reports = ", ".join(entry.get("report_sources", []))
if not reports:
reports = "Not Mapped"
kb_html = ""
if kb_article:
kb_html = f"""
📚 Related KB Article
KB Number: {kb_article['number']}
Author: {kb_article['author']}
Description: {kb_article['description']}
{kb_article['link']}
Open ServiceNow Article
"""
return f"""
{question}
KPI Name
{entry['kpi_name']}
Definition
{entry['definition']}
Business Meaning
{entry['business']}
Formula
{entry['formula']}
{kb_html}
Was the definition satisfactory?
"""
def on_still_not_satisfied_change(choice):
if choice == 'Yes':
html = f""
return gr.update(value=html, visible=True), gr.update(value='You selected to raise an incident for further support.', visible=True)
if choice == 'No':
return gr.update(value='', visible=False), gr.update(value='Glad the follow-up helped.', visible=True)
return gr.update(value='', visible=False), gr.update(value='', visible=False)
# =========================================================
# 7) MAIN ANSWER
# =========================================================
def get_answer(question, audience, excel_mapping=None):
if not question or not question.strip():
return (
'Ask a KPI question to see the summary cards.
',
'Please enter a KPI question.',
'',
'',
'',
'No comparison available.
'
)
primary_entry, best_score = choose_primary_entry(
question,
audience,
excel_mapping=excel_mapping
)
if primary_entry is None:
recommendations = recommend_kpis(question)
suggestion_text = "\n".join(
f"- {r['kpi_name']}"
for r in recommendations[:5]
)
workbook_note = (
DEFAULT_KPI_EXCEL
if Path(DEFAULT_KPI_EXCEL).exists()
else f"{DEFAULT_KPI_EXCEL} not found next to the app file"
)
return (
'No KPI found.
',
'No KPI found for the searched text.',
'',
'',
f"**Search Tried:** `{fix_pdf_text(clean_user_query(question))}`\n\n"
f"**Excel Auto-load:** {workbook_note}",
'No comparison available.
'
)
# -----------------------
# Search KB Articles
# -----------------------
kb_article = search_kb_article(
question,
KB_ARTICLES
)
second_entry = find_second_same_occurrence(
primary_entry,
audience,
excel_mapping=excel_mapping
)
summary_html = build_summary_cards(
primary_entry,
second_entry,
retrieval_score=best_score
)
comparison_html = build_side_by_side_comparison(
primary_entry,
second_entry
)
chat_html = build_chat_html(
question,
primary_entry,
kb_article
)
return (
summary_html,
chat_html,
)
def clear_all(default_mapping):
return (
'', 'Business User', 'Ask a KPI question to see the summary cards.
',
'', '', '', '', 'Comparison results will appear here.
',
default_mapping, '', *clear_feedback_only(),
)
def dax_to_sql(dax_text):
if not dax_text or not dax_text.strip():
return "Please enter a DAX formula."
dax = dax_text.strip()
if "=" in dax:
dax = dax.split("=", 1)[1].strip()
dax_upper = dax.upper()
# SUM(Table[Column])
match = re.search(
r"SUM\s*\(\s*([A-Za-z0-9_]+)\[(.*?)\]\s*\)",
dax,
re.IGNORECASE
)
if match:
table_name = match.group(1)
column_name = match.group(2)
return f"""
SELECT
SUM({column_name}) AS KPI_VALUE
FROM {table_name};
"""
# AVERAGE(Table[Column])
match = re.search(
r"AVERAGE\s*\(\s*([A-Za-z0-9_]+)\[(.*?)\]\s*\)",
dax,
re.IGNORECASE
)
if match:
table_name = match.group(1)
column_name = match.group(2)
return f"""
SELECT
AVG({column_name}) AS KPI_VALUE
FROM {table_name};
"""
# COUNT(Table[Column])
match = re.search(
r"COUNT\s*\(\s*([A-Za-z0-9_]+)\[(.*?)\]\s*\)",
dax,
re.IGNORECASE
)
if match:
table_name = match.group(1)
column_name = match.group(2)
return f"""
SELECT
COUNT({column_name}) AS KPI_VALUE
FROM {table_name};
"""
# DISTINCTCOUNT(Table[Column])
match = re.search(
r"DISTINCTCOUNT\s*\(\s*([A-Za-z0-9_]+)\[(.*?)\]\s*\)",
dax,
re.IGNORECASE
)
if match:
table_name = match.group(1)
column_name = match.group(2)
return f"""
SELECT
COUNT(DISTINCT {column_name}) AS KPI_VALUE
FROM {table_name};
"""
# DIVIDE([Measure1],[Measure2])
if "DIVIDE(" in dax_upper:
measures = re.findall(
r"\[(.*?)\]",
dax
)
if len(measures) >= 2:
numerator = measures[0]
denominator = measures[1]
return f"""
SELECT
CAST({numerator} AS FLOAT)
/
NULLIF({denominator},0)
AS KPI_VALUE;
"""
# COUNTROWS
if "COUNTROWS(" in dax_upper:
return """
SELECT
COUNT(*) AS KPI_VALUE
FROM YourTable;
"""
if "CALCULATE(" in dax_upper:
return f"""
-- CALCULATE detected
-- Original DAX:
{dax}
-- Additional filter context may need manual conversion.
"""
return f"""
-- Conversion not supported yet
Original DAX:
{dax}
"""
# =========================================================
# ROLE BASED TAB VISIBILITY
# =========================================================
def update_role_view(role):
if role == "Business":
# Show Formula and DAX tabs
return (
gr.update(visible=True), # Formula Tab
gr.update(visible=True) # DAX Tab
)
# User role
return (
gr.update(visible=False), # Formula Tab
gr.update(visible=False) # DAX Tab
)
# =========================================================
# 8) UI
# =========================================================
CUSTOM_CSS = """
"""
DEFAULT_MAPPING = load_default_excel_if_present()
KB_ARTICLES = load_kb_articles(KB_EXCEL)
with gr.Blocks() as demo:
gr.HTML(CUSTOM_CSS)
gr.HTML("""
""")
with gr.Row():
with gr.Column(scale=4, elem_classes=['panel']):
question = gr.Textbox(label='Ask KPI question', placeholder='e.g. OCCP Interactions', lines=2)
#excel_status = gr.Markdown(DEFAULT_STATUS)
submit_btn = gr.Button('Submit', variant='primary')
clear_btn = gr.Button('Clear')
with gr.Column(scale=8, elem_classes=['panel']):
summary_cards = gr.HTML('Ask a KPI question to see the summary cards.
')
chat_response = gr.HTML('Conversation will appear here.
')
excel_mapping_state = gr.State(DEFAULT_MAPPING)
current_kpi_state = gr.State('')
with gr.Group(visible=False) as feedback_panel:
satisfied_choice = gr.Radio(choices=['Yes', 'No'], label='Was the information satisfactory?', visible=True)
with gr.Row(visible=False) as rating_row:
rating_value = gr.Radio(choices=['1', '2', '3', '4', '5'], label='Rate the definition (1 to 5)')
rating_submit_btn = gr.Button('Submit Rating')
rating_status = gr.Markdown(visible=False)
with gr.Column(visible=False) as followup_row:
followup_question = gr.Textbox(label='Ask more', placeholder='Please ask your follow-up question here', lines=3)
followup_submit_btn = gr.Button('Ask More', variant='primary')
with gr.Row(visible=False) as still_not_satisfied_row:
still_not_satisfied_choice = gr.Radio(choices=['Yes', 'No'], label='Still not satisfied after the follow-up?')
feedback_status = gr.Markdown(visible=False)
incident_html = gr.HTML(visible=False)
submit_btn.click(
fn=run_search_and_prepare_feedback,
inputs=[question, excel_mapping_state],
outputs=[
summary_cards,chat_response,
current_kpi_state,
feedback_panel, satisfied_choice, rating_row, rating_value,
rating_status, followup_row, followup_question,
still_not_satisfied_row, still_not_satisfied_choice,
feedback_status, incident_html,
],
)
satisfied_choice.change(fn=on_satisfaction_change, inputs=[satisfied_choice], outputs=[rating_row, followup_row, still_not_satisfied_row, incident_html, feedback_status])
rating_submit_btn.click(fn=submit_rating, inputs=[rating_value], outputs=[rating_status])
followup_submit_btn.click(
fn=run_followup_search,
inputs=[followup_question, current_kpi_state, excel_mapping_state],
outputs=[
summary_cards,chat_response,
current_kpi_state,
feedback_panel, satisfied_choice, rating_row, rating_value,
rating_status, followup_row, followup_question,
still_not_satisfied_row, still_not_satisfied_choice,
feedback_status, incident_html,
],
)
still_not_satisfied_choice.change(fn=on_still_not_satisfied_change, inputs=[still_not_satisfied_choice], outputs=[incident_html, feedback_status])
clear_btn.click(
fn=clear_all,
inputs=[excel_mapping_state],
outputs=[
question,summary_cards,
excel_mapping_state, current_kpi_state,
feedback_panel, satisfied_choice, rating_row, rating_value,
rating_status, followup_row, followup_question,
still_not_satisfied_row, still_not_satisfied_choice,
feedback_status, incident_html,
],
)
demo.launch() 