Device Incidents
Tracks incidents or alerts monitored by a service on a device, also known as Device Alerts or the items appearing in the N-central Active Issues interface.
Methodology: Kimball Star Schema
Fact Type: Transactional
Grain: Monitored Service Instance by day/time for a Device
Device Incidents schema
Device Incidents table joins
FACT: FACT_DEVICE_INCIDENT
| Dimension | Dimension key | Fact key | Cardinality |
|---|---|---|---|
|
DIM_TENANT |
TENANT_DK |
TENANT_DK |
1:N |
|
DIM_CUSTOMER |
CUSTOMER_SK |
CUSTOMER_SK |
1:N |
|
DIM_CUSTOMER_LATEST |
CUSTOMER_DK |
CUSTOMER_DK |
1:N |
|
DIM_SITE |
SITE_SK |
SITE_SK |
1:N |
|
DIM_SITE_LATEST |
SITE_DK |
SITE_DK |
1:N |
|
DIM_DEVICE_SUMMARY |
DEVICE_SK |
DEVICE_SK |
1:N |
|
DIM_DEVICE_SUMMARY_LATEST |
DEVICE_DK |
DEVICE_DK |
1:N |
|
DIM_DATE |
DATE_DK |
INCIDENT_DATE_OPENED_DK |
1:N |
|
DIM_TIME |
TIME_SK |
INCIDENT_TIME_OPENED_SK |
1:N |
|
DIM_CLOSED_EARLIEST_DATE |
DATE_DK |
INCIDENT_DATE_CLOSED_EARLIEST_DK |
1:N |
|
DIM_CLOSED_EARLIEST_TIME |
TIME_SK |
INCIDENT_TIME_CLOSED_EARLIEST_SK |
1:N |
|
DIM_CLOSED_LATEST_DATE |
DATE_DK |
INCIDENT_DATE_CLOSED_LATEST_DK |
1:N |
|
DIM_CLOSED_LATEST_TIME |
TIME_SK |
INCIDENT_TIME_CLOSED_LATEST_SK |
1:N |
|
DIM_SERVICE |
SERVICE_INSTANCE_ID |
SERVICE_INSTANCE_ID |
1:N |
DIM_CLOSED_EARLIEST_DATE & DIM_CLOSED_LATEST_DATE are alias tables of DIM_DATE
DIM_CLOSED_EARLIEST_TIME & DIM_CLOSED_LATEST_TIME are alias tables of DIM_TIME
Boilerplate for Device Incidents joins
with DIM_CLOSED_EARLIEST_DATE as
(Select
* from SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_DATE),
DIM_CLOSED_LATEST_DATE as
(Select
* from SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_DATE),
DIM_CLOSED_EARLIEST_TIME as
(Select
* from SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_TIME),
DIM_CLOSED_LATEST_TIME as
(Select
* from SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_TIME)
Select
-- enter columns here
From SHARED_ANALYTICS_DATA.SHARED_DATA.FACT_DEVICE_INCIDENT f
Join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_CUSTOMER c on f.Customer_SK = c.Customer_SK
Join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_CUSTOMER_LATEST cl on f.Customer_DK = cl.Customer_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_SITE s on f.Site_SK = s.Site_SK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_SITE_LATEST sl on f.Site_DK = sl.Site_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_DEVICE_SUMMARY ds on f.Device_DK = ds.Device_SK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_DEVICE_SUMMARY_LATEST dl on f.Device_DK = dl.Device_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_DATE dt on f.INCIDENT_DATE_OPENED_DK = dt.Date_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_TIME t on f.INCIDENT_TIME_OPENED_SK = t.TIME_SK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_CLOSED_EARLIEST_DATE ce on f.INCIDENT_DATE_CLOSED_EARLIEST_DK = ce.Date_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_CLOSED_EARLIEST_TIME cet on f.INCIDENT_TIME_CLOSED_EARLIEST_SK = cet.TIME_SK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_CLOSED_LATEST_DATE cl on f.INCIDENT_DATE_CLOSED_EARLIEST_DK = cl.Date_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_CLOSED_LATEST_TIME ctt on f.INCIDENT_TIME_CLOSED_EARLIEST_SK = clt.TIME_SK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_SERVICE si on f.Service_Instance_id = si.Service_Instance_Id
-- Where clauses here
-- Group By columns here
-- Having clauses here
-- Order by columns here