License Management Dashboard for Call Centre IT

Business problem

The IT team ran a recurring joint review every month to cross-reference license assignments against department headcounts: who held what, which departments had excess seats they never used, and which were under-provisioned. The process was entirely manual (spreadsheets, email chains, and a half-day meeting) and it consumed IT's time without adding any analytical depth. There was also no visibility into actual usage, so procurement decisions were made on gut feel.

Data available

• Active Directory / license assignment export (per user, per product)
• Department org chart and headcount from HR system
• Product usage logs (last-login, activity counts) where available
• License costs per SKU for financial impact calculation

Solution implemented

1. 1Built a Power Query pipeline that ingests AD exports and joins them to the org chart so every license is tagged with department, role tier, and cost centre.
2. 2Designed a semantic model with two fact tables (one for assignments, one for usage events) linked to shared Department, User, and Product dimensions.
3. 3Dashboard shows three views: real-time assignment map (who holds what), department variance (over/under vs. headcount target), and a usage heatmap (licensed but never used in 90 days).
4. 4Added drill-through so IT can select a department, see every individual license, flag reclaims, and export a clean handover list. No spreadsheet needed.
5. 5Threshold alerts on the dashboard surface departments exceeding budgeted seats by more than 10%, triggering an automated Power Automate notification to the IT lead.

Impact

What I learned

Recurring manual reviews are a sign of missing infrastructure, not missing discipline. One governed dataset removes the meeting, surfaces the exceptions automatically, and moves IT from reviewer to decision-enabler.