Skip to content

semantic-models.md

Latest commit

 

History

History
173 lines (122 loc) · 6.71 KB

semantic-models.md

File metadata and controls

173 lines (122 loc) · 6.71 KB

Semantic Models

How the Power BI tables, relationships, and calculated columns are constructed for each dashboard.

Ticketing Analytics Model

The ticketing board visualizes ticket operations — SLA compliance, escalation rates, workload distribution, turnaround times.

Data Pipeline

tickets-history.pq          →   Tickets (History)  table     ← raw, may contain duplicates
                                       │
ticket-activities.dax        →   Ticket Activities   table     ← deduplicated, use this in visuals
                                       │
calculated-columns/*.dax     →   14 calculated columns on Tickets (History)

Step 1 — Load: tickets-history.pq

This Power Query file calls fxGetDataViewSet with the Ticket Activities View GUID (fce38263...) to pull ticket data from Matrix42.

Key behavior: Incremental Refresh

Setting Value
Archive window 4 years
Refresh window 6 months
Detect data changes LastChangeDate

Because Power BI merges each incremental partition rather than replacing it, the same ticket can appear multiple times — once per refresh cycle that touched it.

Example: Ticket INC-4567 is modified on Jan 5, Feb 12, and Mar 1. After three monthly refreshes, the Tickets (History) table contains three rows for INC-4567, each with a different LastChangeDate.

The file also injects RefreshTimestampUTC — the UTC time when each row was loaded — for data-freshness auditing.

Step 2 — Deduplicate: ticket-activities.dax

This DAX calculated table eliminates the duplicates:

CALCULATETABLE(
    'Tickets (History)',
    TREATAS(
        SUMMARIZE(
            'Tickets (History)',
            'Tickets (History)'[TicketNumber],    -- group by ticket
            "MaxLastChange", MAX('Tickets (History)'[LastChangeDate])  -- keep newest
        ),
        'Tickets (History)'[TicketNumber],
        'Tickets (History)'[LastChangeDate]
    )
)

Result: From the three INC-4567 rows above, only the Mar 1 version survives. All visuals should reference Ticket Activities, never Tickets (History) directly.

Step 3 — Enrich: 14 Calculated Columns

Organized in calculated-columns/ (one .dax file per column):

Timezone Conversions (UTC → CET/CEST)

Column Source Field When You Need It
ClosedDateLocal ClosedDate "When was this ticket resolved, local time?"
CreatedDateLocal CreatedDate "When was this ticket opened, local time?"
EscalatedDateLocal EscalatedDate "When did the SLA breach happen?"
LastChangeDateLocal LastChangeDate "When was the last update?"
RefreshTimestampLocal RefreshTimestampUTC "When was this data imported?"
SolutionPointLocal SolutionPoint "What's the SLA deadline in local time?"

All six columns use identical DST logic:

CEST (UTC+2):  last Sunday in March 01:00 UTC  →  last Sunday in October 01:00 UTC
CET  (UTC+1):  rest of the year

Formatted Dates

Column Example Purpose
Creation Date "7/15/2025" Slicer-friendly text format

Classification Flags

Column Logic Example
Is Automated Ticket Recipient = "m42_service" Filter out robot-handled work
Is In Work State ∉ {203, 204, 220} Active tickets only
Is Compliant In Work In Work AND not SLA-escalated Green zone: on track
Solution Escalated In Recipient Role Escalated in current team's watch Accountability pinpointing

Display & Navigation Helpers

Column Example Output Purpose
Sys-Entity-Label "INC-12345 - Printer broken" Tooltip/drill-through title
Sys-Link https://m42.../ObjectDetail/... One-click to Matrix42
Sys-Subject "Printer broken" Cleaned subject (strips WG:, AW:, FW:, RE:)

Demand Management Model

The demand board tracks application requests through their lifecycle — submission, expert rating, acceptance, and supplier management.

Data Pipeline

demand_requests_query.pq              →   Demand Requests table
demand_application_ratings_query.pq   →   Demand Application Ratings table
demand_service_requests_query.pq      →   Demand Service Requests table
demand_suppliers_query.pq             →   Demand Suppliers table
demand_requests_2_activities_query.pq →   Activity 2 Demand table (maps tickets ↔ demands)

Table: Demand Requests

Source: fxGetDataQuery with GUID 7f1c002d... (Data Definition on Ud_DemandRequestClass)

Column Expression What It Shows
DemandNr Ud_DemandId Human-readable demand number (e.g., DMD-0042)
Subject Ud_Subject Request title
Step T(SPSCommonClassBase).State Workflow step (numeric)
State SUBQUERY(Ud_DemandRequestStateGroup) Friendly state label (e.g., "Approved")
Accepted SUBQUERY(Ud_DemandRequestDecisionClass) "Accepted" or "Declined"
CreationDate T(SPSCommonClassBase).ValidFrom When the demand was filed
Application T(Ud_DemandApplicationSolutionClass).Ud_Name Requested application name
Supplier ...Ud_Supplier Vendor providing the app
Hosting ...Ud_Hosting Hosting model (Cloud/OnPrem)
AuthenticationMethod ...Ud_AuthenticationMethod SSO / LDAP / Local
IsEntraIDSCIMSupported CASE expression Provisioning support flag

Special logic: If a demand has no CreationDate, the query falls back to the smallest CreationDate from a demand with a larger DemandNr. See demand_requests_query.pq header comment.

Table: Demand Application Ratings

Source: fxGetDataQuery with GUID e0a67c3b... (Data Definition on Ud_DemandApplicationRatingClass)

Each row = one expert's feasibility rating for a demand.

Column What It Shows
DemandId Links back to the rated demand
User The expert who rated
ExpertTeam Which team the expert belongs to
IsFeasible Feasibility verdict
EstimatedEffort Effort estimate
Complexity Complexity rating
DaysUntilRatingWasPerformed SLA metric: how quickly the team responded

Table: Demand Service Requests

Source: fxGetDataQuery with GUID d603843b... (raw SQL joining demands → activities)

Each row = one service request ticket linked to a demand.

Table: Demand Suppliers

Source: fxGetDataQuery with GUID 7f19bbac... (Data Definition on Ud_DemandSupplierClass)

Vendor directory with address, contact, and status information.

Table: Activity 2 Demand

Source: fxGetDataViewSet with GUID ea323eb5... → Data Query fb497591... (Data Definition on SPSActivityClassBase)

Maps ticket numbers to demand IDs by matching Subject LIKE 'Demand DMD%'.