Skip to content

data-flow.md

Latest commit

 

History

History
253 lines (209 loc) · 11.6 KB

data-flow.md

File metadata and controls

253 lines (209 loc) · 11.6 KB

Data Flow — Matrix42 to Power BI Visuals

End-to-end: from Matrix42's database schema through Data Queries, Power Query transformations, DAX enrichment, and into report visuals.

The Big Picture

graph TD
    subgraph "Matrix42 Database"
        DB_ACT["SPSActivityClassBase<br/>(Tickets, Incidents, Service Requests)"]
        DB_DEM["Ud_DemandRequestClass<br/>(Demand Requests)"]
        DB_RAT["Ud_DemandApplicationRatingClass<br/>(Expert Ratings)"]
        DB_SUP["Ud_DemandSupplierClass<br/>(Suppliers)"]
        DB_CAL["Ud_CalendarDateSeries<br/>(Calendar Dimension)"]
        DB_JRN["SPSActivityClassUnitOfWork<br/>(Journal Entries)"]
    end

    subgraph "Matrix42 Data Queries"
        DQ_TA["Ticket Activities<br/><i>ASQL, 16 columns</i>"]
        DQ_FLS["FLS Resolution<br/><i>SQL, 5 columns</i>"]
        DQ_SCR["Single Contact Resolution<br/><i>SQL, 3 columns</i>"]
        DQ_TAT["Turnaround Time<br/><i>SQL, 5 columns</i>"]
        DQ_AA["Active Agents<br/><i>SQL, 4 columns</i>"]
        DQ_DR["Demand Request Full<br/><i>ASQL, 19 columns</i>"]
        DQ_DAR["Demand App Rating<br/><i>ASQL, 7 columns</i>"]
        DQ_DSR["Demand Service Req<br/><i>SQL, 11 columns</i>"]
        DQ_DS["Demand Supplier<br/><i>ASQL, 12 columns</i>"]
        DQ_A2D["Activity 2 Demand<br/><i>ASQL, 4 columns</i>"]
    end

    subgraph "Data View Sets"
        DVS_TA["Ticket Activities View<br/><code>fce38263...</code>"]
        DVS_A2D["Activity 2 Demand View<br/><code>ea323eb5...</code>"]
    end

    subgraph "Power Query"
        PQ_TH["tickets-history.pq<br/>+ Incremental Refresh<br/>+ RefreshTimestampUTC"]
        PQ_DR["demand_requests_query.pq<br/>+ CreationDate fallback"]
        PQ_DAR["demand_app_ratings_query.pq"]
        PQ_DSR["demand_service_requests_query.pq"]
        PQ_DS["demand_suppliers_query.pq"]
        PQ_A2D["demand_requests_2_activities_query.pq"]
    end

    subgraph "DAX Layer"
        DAX_TA["ticket-activities.dax<br/><i>Deduplication table</i>"]
        DAX_CC["14 Calculated Columns<br/><i>Timezone, flags, helpers</i>"]
    end

    subgraph "Power BI Reports"
        RPT_AN["Ticketing Analytics<br/>Dashboard"]
        RPT_DM["Demand Management<br/>Dashboard"]
    end

    DB_ACT --> DQ_TA & DQ_FLS & DQ_SCR & DQ_A2D
    DB_ACT & DB_CAL --> DQ_TAT
    DB_JRN --> DQ_FLS & DQ_SCR & DQ_AA
    DB_DEM --> DQ_DR & DQ_DSR
    DB_RAT --> DQ_DAR
    DB_SUP --> DQ_DS

    DQ_TA --> DVS_TA --> PQ_TH
    DQ_A2D --> DVS_A2D --> PQ_A2D
    DQ_DR --> PQ_DR
    DQ_DAR --> PQ_DAR
    DQ_DSR --> PQ_DSR
    DQ_DS --> PQ_DS

    PQ_TH --> DAX_TA --> DAX_CC --> RPT_AN
    DQ_FLS & DQ_SCR & DQ_TAT & DQ_AA --> RPT_AN

    PQ_DR & PQ_DAR & PQ_DSR & PQ_DS & PQ_A2D --> RPT_DM
Loading

Ticketing Analytics — Detailed Flow

Stage 1: Matrix42 → Data Queries

┌─────────────────────────────────┐
│ SPSActivityClassBase            │   The source table holding all tickets,
│  ├─ TicketNumber                │   incidents, and service requests.
│  ├─ CreatedDate, ClosedDate     │
│  ├─ LastChangeDate              │   ← Key for dedup + incremental refresh
│  ├─ State, Priority, SLA        │
│  ├─ Initiator, Recipient        │
│  └─ RecipientRole, Category     │
└────────────────┬────────────────┘
                 │
    ┌────────────┼──────────────────────────────────────┐
    ▼            ▼                                      ▼
 Ticket      FLS Resolution          Turnaround Time
 Activities  & Escalation            (SLA-aware hours)
 (ASQL)      (SQL + CTEs)            (SQL + Calendar)
 16 cols     5 computed flags        5 cols

Stage 2: Data Query → Power Query

Data View Set "fce38263..."
        │
        ▼
   fxGetDataViewSet()          ← Power Query calls Matrix42 REST API
        │
        ├── Filter: LastChangeDate ≥ RangeStart    ← Incremental refresh
        ├── Filter: LastChangeDate < RangeEnd
        │
        ├── + Column: RefreshTimestampUTC           ← Audit: when was this loaded?
        │
        └── → Tickets (History)  table              ← May contain duplicates!

Why duplicates? Incremental refresh merges new data into existing partitions. If ticket INC-4567 is modified across two refresh windows, both snapshots survive. This is by design — deduplication happens in DAX.

Stage 3: Power Query → DAX Deduplication

Tickets (History)  ──►  ticket-activities.dax  ──►  Ticket Activities
  3 rows for INC-4567       GROUP BY TicketNumber       1 row for INC-4567
  (Jan, Feb, Mar)           KEEP MAX(LastChangeDate)    (Mar — latest)

Stage 4: DAX Calculated Columns

Ticket Activities (deduplicated)
    │
    ├── Timezone: ClosedDateLocal, CreatedDateLocal, ... (6 columns)
    │   Example: 2025-07-15 14:00 UTC → 16:00 CEST
    │
    ├── Flags: Is In Work, Is Automated Ticket, ... (4 columns)
    │   Example: State=200 → Is In Work = TRUE
    │
    └── Display: Sys-Entity-Label, Sys-Link, Sys-Subject (3 columns)
        Example: "INC-12345 - Printer broken"

Stage 5: Report Visuals

The Ticketing Analytics dashboard combines data from multiple queries into a unified view:

┌─────────────────────────────────────────────────────────────────┐
│                    Ticketing Analytics Board                    │
├──────────────────┬──────────────────┬──────────────────────────┤
│ Ticket Volume    │ SLA Compliance   │ Workload Distribution    │
│                  │                  │                          │
│ Source:          │ Source:          │ Source:                   │
│ Ticket           │ Ticket           │ Active Agents            │
│ Activities       │ Activities       │ (04f8b5a8...)            │
│ + Is In Work     │ + Is Compliant   │                          │
│ + CreatedDate    │   In Work        │ JournalInteractions      │
│   Local          │ + SolutionTime   │ per Date per RoleId      │
│                  │   Escalated      │                          │
├──────────────────┼──────────────────┼──────────────────────────┤
│ FLS Resolution   │ Turnaround Time  │ Single Contact           │
│ Rate             │                  │ Resolution Rate          │
│                  │                  │                          │
│ Source:          │ Source:          │ Source:                   │
│ FLS Resolution   │ Turnaround Time  │ Single Contact           │
│ (8f74f9d3...)    │ (4966c6df...)    │ Resolution               │
│                  │                  │ (835fa0b5...)            │
│ IsFirstLevel     │ NetTurnaround    │                          │
│ SupportResolution│ Hours/Days       │ IsSingleContact          │
│                  │                  │ Resolution               │
└──────────────────┴──────────────────┴──────────────────────────┘

Demand Management — Detailed Flow

Stage 1: Matrix42 → Power Query (Direct)

Unlike ticketing, demand queries are called directly via fxGetDataQuery (no Data View Set wrapper needed for most tables):

                Matrix42
                   │
    ┌──────────────┼──────────────────────────────┐
    ▼              ▼              ▼               ▼
 Demand         Demand App     Demand          Demand
 Request        Rating         Service Req     Supplier
 (Full)         (Full)         (SQL joins      (Full)
 7f1c002d...    e0a67c3b...    d603843b...     7f19bbac...
    │              │              │               │
    ▼              ▼              ▼               ▼
 demand_        demand_app_   demand_service  demand_
 requests_      ratings_      _requests_      suppliers_
 query.pq       query.pq      query.pq        query.pq

Special: demand_requests_2_activities_query.pq uses the Data View Set ea323eb5... to map ticket numbers → demand IDs.

Stage 2: Power Query Transformations

The demand PQ files perform lightweight transformations:

File Transformation
demand_requests_query.pq CreationDate fallback: if null, uses smallest CreationDate from demand with higher DemandNr. Type re-assertion to datetimezone.
demand_requests_2_activities_query.pq Maps demands to their earliest-created linked activity
Others Column selection and type casting only

Stage 3: Report Visuals

┌─────────────────────────────────────────────────────────────────┐
│                  Demand Management Board                        │
├──────────────────┬──────────────────┬──────────────────────────┤
│ Demand Pipeline  │ Expert Ratings   │ Supplier Overview        │
│                  │                  │                          │
│ Source:          │ Source:          │ Source:                   │
│ Demand Requests  │ Demand App       │ Demand Suppliers         │
│ + State          │ Ratings          │ + IsOfficial             │
│ + Accepted       │ + IsFeasible     │ + Country                │
│ + CreationDate   │ + Complexity     │ + FederalState           │
│                  │ + Days Until     │                          │
│                  │   Rating         │                          │
├──────────────────┴──────────────────┴──────────────────────────┤
│ Service Requests Linked to Demands                             │
│                                                                │
│ Source: Demand Service Requests + Activity 2 Demand             │
│ + TicketId, RecipientName, ReactionTimeEscalated               │
└────────────────────────────────────────────────────────────────┘

Shared Infrastructure

Authentication

fxGetAuthHeader.pq
    │
    ├── Reads Matrix42 base URL and API token from Power Query parameters
    ├── Returns Authorization header for REST API calls
    │
    └── Used by: fxGetDataQuery.pq, fxGetDataViewSet.pq

API Wrappers

Function Endpoint Pagination
fxGetDataQuery(GUID) /api/data/query/{GUID} Automatic
fxGetDataViewSet(GUID, [RangeStart], [RangeEnd]) /api/data/viewset/{GUID} Automatic + incremental params