Remote Execution by Day

Tracks Task types, Items, Last Run Start Time, task success or failure on a Device to the day using Max (Last Run Start Time) per day.

Methodology: Kimball Star Schema.

Fact Type: Transactional

Grain: Monitored Remote Executions by Device and day

Remote Execution by Day schema

Remote Execution by Day table joins

FACT: FACT_REMOTE_EXECUTION_DAY

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

EVENT_DATE_DK

1:N

DIM_TIME

TIME_SK

EVENT_TIME_DK

1:N

DIM_TASK_STATE

STATE_DK

STATE_DK

1:N

DIM_REMOTE_EXECUTION_TYPE

REMOTE_EXECUTION_TYPE_DK

REMOTE_EXECUTION_TYPE_DK

1:N

DIM_REMOTE_EXECUTION_ITEM

REMOTE_EXECUTION_ITEM_DK

REMOTE_EXECUTION_ITEM_DK

1:N

Boilerplate for Remote Execution by Day joins

Copy
Select
-- enter columns here
From SHARED_ANALYTICS_DATA.SHARED_DATA.FACT_REMOTE_EXECUTION_DAY 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 d on f.Device_SK = d.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.Event_Date_DK = dt.Date_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_TIME dtm on f.Event_Last_Time_SK = dtm.Time_SK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_TASK_STATE si on f.State_DK = si.State_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_REMOTE_EXECUTION_TYPE tp on f.Remote_Execution_Type_DK = tp.Remote_Execution_Type_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_REMOTE_EXECUTION_ITEM ti on f.Remote_Execution_Item_DK = ti.Remote_Execution_Item_DK

-- Where clauses here
-- Group By columns here
-- Having clauses here
-- Order by columns here