Skip to content

data-sources.md

Latest commit

 

History

History
249 lines (178 loc) · 9.19 KB

data-sources.md

File metadata and controls

249 lines (178 loc) · 9.19 KB

Data Sources — Matrix42 Configuration Packages

How data is obtained from Matrix42 using Data Queries and Data View Sets.

How It Works

Power BI doesn't query Matrix42's database directly. Instead, it calls the Matrix42 REST API through two wrapper functions:

fxGetDataQuery(GUID)       →  returns rows defined by a named Data Query
fxGetDataViewSet(GUID)     →  returns rows defined by a named Data View Set

Both wrappers handle authentication (fxGetAuthHeader), pagination, and column filtering automatically.

Data Queries vs Data View Sets

Concept What It Is When To Use
Data Query Standalone query definition — either ASQL on a Data Definition class, or raw SQL Direct data retrieval (most demand queries)
Data View Set A UI widget container that wraps a Data Query and adds filtering/display settings When you want the same data the Matrix42 UI shows (ticket grids)

Example: The "Ticket Activities View" Data View Set (fce38263...) wraps the "Ticket Activities" Data Query (3b08e2aa...). Calling fxGetDataViewSet("fce38263...") in Power Query returns the same column set you'd see in the Matrix42 grid widget.

Package 1 — DemandManagement.Analytics (v1.4)

Installed in Matrix42 as adessoSE_RootITUp.DemandManagement.Analytics.

Data Queries

Demand Request (Full) — 7f1c002d...

Property Value
Schema Class Ud_DemandRequestClass
Type ASQL Data Definition
PQ file demand_requests_query.pq

Columns (19):

Column Name Expression Notes
DemandVersion Ud_DemandVersion
DemandNr Ud_DemandId Human-readable ID
Subject Ud_Subject
Step T(SPSCommonClassBase).State Workflow step number
State SUBQUERY(Ud_DemandRequestStateGroup, ...) Friendly group label
CreationDate T(SPSCommonClassBase).ValidFrom
Requestor Ud_Requestor Person who filed
Accepted SUBQUERY(Ud_DemandRequestDecisionClass, ...) Latest decision
ForwardTarget RequestForwardDecision.Ud_Target Forwarding recipient
FaultyRecipientCompany SUBQUERY(SPSOrgUnitClassBase, ...) OU tree walk
Application T(Ud_DemandApplicationSolutionClass).Ud_Name
ImplementationType ...Ud_ImplementationType
Supplier ...Ud_Supplier
UserGroup ...Ud_UserGroup
AreLicensesRequired ...Ud_AreLicensesRequired
OperationResponsible ...Ud_OperationResponsible
SupportResponsible ...Ud_SupportResponsible
Hosting ...Ud_Hosting
AuthenticationMethod ...Ud_AuthenticationMethod
IsEntraIDSCIMSupported CASE WHEN ... Ud_SCIMSupport ... Computed flag
IsPersonalInformation T(Ud_DemandApplicationDataPrivacyClass).Ud_IsPersonalInformationProcessed
IsConfidentialInformation T(Ud_DemandApplicationInfoSecClass).Ud_IsConfidentialInformationProcessed
DataProcessingCountry T(Ud_DemandApplicationDataPrivacyClass).Ud_DataProcessingCountry

Demand Application Rating (Full) — e0a67c3b...

Property Value
Schema Class Ud_DemandApplicationRatingClass
Type ASQL Data Definition
PQ file demand_application_ratings_query.pq

Columns (7): User, DemandId, ExpertTeam, CreationDate, IsFeasible, EstimatedEffort, Complexity, DaysUntilRatingWasPerformed

Demand Service Requests — d603843b...

Property Value
Type Raw SQL
PQ file demand_service_requests_query.pq

Joins Ud_DemandRequestClass → SPSCommonClassBase → SPSActivityClassBase → SPSActivityTypeServiceRequest to map demands to their linked service requests.

Columns (11): Id, TicketId, DemandId, RecipientId, RecipientName, RecipientRoleId, RecipientRoleName, CreationDate, ClosedDate, ReactionTimeEscalated, SolutionTimeEscalated

Demand Supplier — 7f19bbac...

Property Value
Schema Class Ud_DemandSupplierClass
Type ASQL Data Definition
PQ file demand_suppliers_query.pq

Columns (12): State, IsOfficial, Name, Street, ZIP, City, FederalState, Country, Email, Phone, WebSite, Creator, CreationDate

Activity 2 Demand — 9d1376f7...

Property Value
Schema Class SPSActivityClassBase
Type ASQL Data Definition
Primary Filter Subject LIKE 'Demand DMD%'
PQ file demand_requests_2_activities_query.pq (via Data View Set)

Columns (4): TicketNumber, CreatedDate, DemandId (SUBQUERY), DemandObjectId (SUBQUERY)

Data View Set: ea323eb5... wraps this query for UI display.

Package 2 — PowerBIAnalytics (v1.12)

Installed in Matrix42 as adessoSEPowerBIAnalytics.

Data Queries

Ticket Activities — 42a841de...

Property Value
Schema Class SPSActivityClassBase
Type ASQL Data Definition
PQ file tickets-history.pq (via Data View Set)

Columns (16):

Column Name Expression Notes
TicketNumber TicketNumber e.g., INC-12345
State T(SPSCommonClassBase).State Numeric state
LastChangeDate LastChangeDate Key for dedup + incremental refresh
CreatedDate CreatedDate
ClosedDate ClosedDate
EscalatedDate EscalatedDate
SolutionTimeEscalated SolutionTimeEscalated Boolean
ReactionTimeEscalated ReactionTimeEscalated Boolean
Ud_SolutionTimeEscalatedInRole Ud_SolutionTimeEscalatedInRole Custom field
SLA SLA FK to SLA definition
SolutionPoint SolutionPoint SLA deadline datetime
Category Category Service catalog category
Creator Creator Ticket creator
Initiator Initiator End-user who reported
Recipient Recipient Assigned agent
RecipientRole RecipientRole Assigned team
InitiatorCompany SUBQUERY(SPSOrgUnitClassBase, ...) OU tree walk

Data View Set: fce38263... wraps this query. Schema types: SPSActivityTypeTicket, SPSActivityTypeServiceRequest, SPSActivityTypeIncident.

First Level Support Resolution & Escalation — 8f74f9d3...

Property Value
Type Raw SQL (complex CTEs)
Data cached Yes (60 min)

Analyzes journal entries (SPSActivityClassUnitOfWork) to determine:

Column Logic
IsCreatedForFirstLevelSupport First assigned role is a FLS team
IsFirstLevelSupportResolution Closed by FLS without escalation
IsEscalated At least one non-FLS role in history
IsClosed State = 204

How FLS teams are identified: SPSScRoleClassBase.Ud_SupportRoleType = 10 (using the Ud_SupportRoleType enum seeded by this package).

Single Contact Resolution — 835fa0b5...

Property Value
Type Raw SQL

Logic: A ticket has single contact resolution when it's closed AND only one distinct user interacted with it (via journal actions: replies, notes, status changes).

Columns (3): ID, IsClosed, IsSingleContactResolution

Turnaround Time — 4966c6df...

Property Value
Type Raw SQL (SLA-aware working-hours calculation)

Calculates net business-hours between ticket creation and closure, respecting:

  • SLA time profiles (working hours per weekday)
  • Timezone conversion (UTC → local via SLA's time zone)
  • Calendar table (Ud_CalendarDateSeries) for day-by-day iteration

Columns (5): ID, StartDateLocal, EndDateLocal, NetTurnaroundHours, NetTurnaroundDays

Active Agents — 04f8b5a8...

Property Value
Type Raw SQL

Counts journal interactions per agent per day per role. Used for workload distribution analysis.

Columns (4): Date, UserId, RoleId, JournalInteractions

Seed Data

Object Purpose
Ud_CalendarDateSeries Pre-populated date dimension (used by Turnaround Time query for day iteration)
Ud_SupportRoleType Enum: 10 = First Level Support (used by FLS Resolution query)
SPSScRoleClassBase update Adds Ud_SupportRoleType field to the existing role class

GUID Quick Reference

GUID Name Type Package
7f1c002d-a23c-f011-dfaa-00505685723c Demand Request (Full) Data Query DemandMgmt
e0a67c3b-903f-f011-dfaa-00505685723c Demand Application Rating (Full) Data Query DemandMgmt
d603843b-7b11-c037-0250-08dda1d71e9d Demand Service Requests Data Query DemandMgmt
7f19bbac-b253-c90a-be83-08db83711c5c Demand Supplier Data Query DemandMgmt
9d1376f7-e065-c60b-c161-08de85b755a2 Activity 2 Demand Data Query DemandMgmt
42a841de-e5f4-c654-e632-08ddfc1ad1bc Ticket Activities Data Query PowerBI
8f74f9d3-67fb-c76a-c028-08ddf61b65be FLS Resolution & Escalation Data Query PowerBI
835fa0b5-972f-c9a7-310f-08ddf61b6f66 Single Contact Resolution Data Query PowerBI
4966c6df-83c4-c9e7-6bb3-08ddf61b32a7 Turnaround Time Data Query PowerBI
04f8b5a8-0f42-c9d6-76bf-08de20743e78 Active Agents Data Query PowerBI
ea323eb5-4c9d-cc66-bcf4-08de85b8ca7b Activity 2 Demand View Data View Set DemandMgmt
fce38263-2761-ca82-e3da-08de625ecdb7 Ticket Activities View Data View Set PowerBI