Contact person: Power BI KPI Dashboard Development

Phone:Show

Email:Show

Location: Cairo, Egypt

Budget: Recommended by industry experts

Time to start: As soon as possible

Project description:
"What we’re building
A Power BI Control Tower delivered in phases. Phase 1 is a full Supplier Tower page that monitors supplier feeds and drives actions via alerts and an action log. It must be production-ready, scalable, and easy to bind to our live OLAP/SSAS model later.
Source & refresh model
* Source: OLAP/SSAS model (owner-managed).

* Mode: Import (no DirectQuery).

* Refresh cadence (Service):
* Hourly for Real-Time fact (last 1–7 days, hourly partitions).

* Daily for History fact (last 30–60+ days, daily partitions).

* Incremental Refresh (IR):
* Two facts, two policies:
* Fact_SupplierRT (hourly): Store last 7 days, Refresh last 1 day.

* Fact_SupplierHistory (daily): Store last 395 days, Refresh last 7 days.

* Use RangeStart/RangeEnd DateTime parameters and fold the date filter in Power Query where possible.

* Gateway: Owner manages gateway & credentials. Freelancer does report/DAX layer only.

Who does what (RACI)
* Owner (you): Data connections, gateway, dataset publishing, enabling IR & scheduled refresh, final alert hookups.

* Freelancer: PBIX report layer—DAX measures, visuals, bookmarks/field parameters, drill-through, alert flags & logic, theme, and layout. Provides optional Power Automate templates (no credentials).


Phase 1 – Supplier Tower (scope to deliver now)
Visual zones & interactions
* KPI Strip (18 cards) with micro-sparklines & WoW deltas
Requests, Availability %, Error %, Avg Resp (s), Bookings, Booking Fail %, L2B (weekly), Cached %, Utilization %, Searches per Booking, TTV (placeholder), Enabled (3h), Disabled (3h), Non-converting pairs (6h), Volume Spikes (4h), Active Clients, Strategic Suppliers, Date Range Text.

* Acceleration Meters (3 gauges): Availability %, Error %, Avg Resp (s) with thresholds.

* Live Alerts Table (6h window): Time, Alert, Entity (Supplier or Client→Supplier), Value, Trigger, Severity (color), Status, Action → link to Action Log.

* Supplier Leaderboard: Credential/Supplier, Requests, Avail %, Error %, Avg Resp, Bookings, Booking Fail %, Searches per Booking (sortable; respects grain).

* Stage Funnel: Availability → Cancellation → Booking → Cancel (with drop-offs).

* Response-time Intervals by Stage: Buckets (<1s, 1–2s, 2–3s, >3s) + impacted client & suggested action.

* Client→Supplier Conversion: Requests, Bookings, L2B, Resp, Error %, Avail %.

* Error Breakdown by Stage: Supplier, Stage, ErrorType, % of stage errors.

* Strategic Suppliers — Tracking List: Weekly actions + Pre↔Post deltas (TTV/Bookings/Avail/Resp/Error). Drill-through to a Supplier Dossier (recent alerts, actions, notes).

* Grain switch (global): Hourly / Daily / Weekly / Monthly via field parameters or bookmarks.

Alert logic (initial triggers)
* High Error % by stage: Avail > 30%, Cancellation > 10%, Booking > 1%.

* High Response: Avg > 1.5s for ≥ 2 hours.

* Non-converting pair: Requests ≥ N and Bookings = 0 within last 6h (client→supplier).

* Enabled→Disabled: Had traffic in last 24h, none in last 3–4h.

* Volume spike: +50% vs rolling 4h baseline.

Acceptance tests (for sign-off)
1. KPI totals match control exports for same filters/dates.

2. Alerts fire on seeded test rows; clicking filters the page and opens the Supplier Dossier.

3. Leaderboard sorting works across all grains.

4. Strategic Suppliers table shows Pre↔Post KPIs for a test action window.

5. Theme, spacing, and responsiveness match the supplied mockup.


Data model the report expects (names can be masked)
Facts
* Fact_SupplierRT (hourly): DateTime (hour), Credential, Customer, Market/City, RequestType, Requests, HotelResults, HotelsCached, Errors, ErrorType, AvgResponseSec, MaxResponseSec, Bookings, BookingFails.

* Fact_SupplierHistory (daily): Same columns, daily grain.

Dims
* Dim_Calendar (marked as Date)

* Dim_Credential (Credential, SupplierDisplay, Channel, Mode, Tier, Owner)

* Dim_Customer (Customer, AgencyGroup, Market/City, Country)

* Dim_RequestType (Availability/Cancellation/Reservation/Cancel)

* Dim_ErrorType (Mapping/NoResults, SupplierGeneral, Timeouts, BookingFailures)

Owner-managed helper tables (can be CSV/SharePoint)
* Thresholds (editable): metric, stage, defaultLimit, severity.

* ActionLog: Time, AlertType, Entity, Owner, Status, ActionTaken, Pre/Post KPIs, Notes.


Incremental Refresh (cookbook the freelancer must set up in PBIX)
1. Create parameters (Power BI Desktop → Manage Parameters):
* RangeStart (Date/Time), RangeEnd (Date/Time).

2. Apply date filters in Power Query for each fact:
* Filter on the fact’s Date/DateTime column using RangeStart <= Date < RangeEnd.

* Ensure the filter folds to the source if possible (freelancer should design queries to be fold-friendly).

3. Enable IR (Model view → table → Incremental refresh):
* Fact_SupplierRT: Store 7 days, Refresh 1 day; (optional) detect changes by DateTime.

* Fact_SupplierHistory: Store 395 days, Refresh 7 days; detect changes by Date.

4. Testing IR:
* First publish with a small seed (e.g., 7 days RT + 60 days History).

* Validate refresh partitions in Service; confirm hourly/daily schedules.

(Owner will actually publish & schedule. Freelancer must deliver PBIX configured for IR.)

DAX (minimum set the freelancer must deliver)
* [Requests], [Hotel Results], [Availability %] = DIVIDE([Hotel Results],[Requests])

* [Errors], [Error %] = DIVIDE([Errors],[Requests])

* [Bookings], [Booking Fails], [Booking Fail %] = DIVIDE([Booking Fails],[Bookings]+[Booking Fails])

* [L2B %] = DIVIDE([Bookings],[Requests])

* [Avg Response (s)], [Max Response (s)]

* [Cached %] = DIVIDE([HotelsCached],[Requests])

* [Searches per Booking] = DIVIDE([Requests],[Bookings])

* [Enabled (3h)] / [Disabled (3h)] (distinct credentials by 3h window)

* [Non-converting pairs (6h)], [Volume Spikes (4h)]

* [TTV] (placeholder until commercial table arrives)

* [Date Range Text] (for dynamic card titles)

* WoW deltas for key KPIs


Materials the freelancer will receive (after shortlisting)
* Interactive mockup (HTML) showing layout, interactions, and example values.

* Field-binding checklist (visual ↔ measure/dimension).

* Sample data (CSV) with masked names but real columns.

* Theme JSON (below) to match the “future vibe” look.

* (Optionally later) A PBIX stub with tables, relationships, slicers, and placeholders.



Base (Phase 1 — Supplier Tower)
* Full report layer (all visuals above), DAX, alerts logic, drill-through, grain switch, theming.

Add-ons (quote separately)
* Power Automate templates (Teams/Email; trigger on alert flags).

* PBIX stub creation (relationships, slicers, placeholders) if we don’t provide one.

* Incremental Refresh advisory (partition ranges, detect changes strategy).

* Advanced visuals (Deneb/Vega gauges/sparklines).

* Accessibility & performance pass (query reduction, visual interactions, bookmarks tidy-up).

Revision policy
* 1 revision round after we bind to live cubes (small name tweaks, formatting).

* Further scope changes (new visuals or logic) billed separately." (client-provided description)