Antivirus Threats

Monitors the malware threats and resolution outcome on a device using AV Defender.

Methodology: Kimball Star Schema.

Fact Type: Transactional

Grain: An individual malware threat detected on a device.

Antivirus Threats schema

Antivirus Threats table joins

FACT: FACT_ANTIVIRUS_THREAT

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	THREAT_DATE_DK	1:N
DIM_TIME	TIME_SK	THREAT_TIME_SK	1:N
DIM_ANTIVIRUS_THREAT	ANTIVIRUS_THREAT_DK	ANTIVIRUS_THREAT_DK	1:N
DIM_ANTIVIRUS_OUTCOME	ANTIVIRUS_OUTCOME_DK	ANTIVIRUS_OUTCOME_DK	1:N
DIM_ANTIVIRUS_ACTION	ANTIVIRUS_ACTION_DK	ANTIVIRUS_ACTION_DK	1:N

Boilerplate for Antivirus Threats joins

Copy

Select
-- enter columns here
From SHARED_ANALYTICS_DATA.SHARED_DATA.FACT_ANTIVIRUS_THREAT 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.Threat_Date_DK = dt.Date_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_TIME t on f.Threat_Time_SK = t.Time_SK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_ANTIVIRUS_THREAT at on f.Antivirus_Threat_DK = at.Antivirus_Threat_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_ANTIVIRUS_OUTCOME ao on f.Antivirus_Outcome_DK = ao.Antivirus_Outcome_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_ANTIVIRUS_ACTION aa on f.Antivirus_Action_DK = aa.Antivirus_Action_DK
-- Where clauses here
-- Group By columns here
-- Having clauses here
-- Order by columns here