Remote Access
Remote access sessions initiated by a MSP technician on an end user Device e.g. a Take Control session
Methodology: Kimball Star Schema.
Fact Type: Transactional
Grain: Session events on a given Device
Remote Access schema
Remote Access table joins
FACT: FACT_REMOTE_ACCESS_SESSION
| 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_CREATE_DATE* |
DATE_DK |
CREATE_DATE_DK |
1:N |
|
DIM_TERMINATED_DATE* |
DATE_DK |
TERMINATED_DATE_DK |
1:N |
|
DIM_CREATE_TIME* |
TIME_SK |
CREATE_TIME_DK |
1:N |
|
DIM_TERMINATED_TIME* |
TIME_SK |
TERMINATED_TIME_DK |
1:N |
|
DIM_TECHNICIAN |
USER_DK |
USER_DK |
1:N |
|
DIM_REMOTE_ACCESS_CONNECTION |
CONNECTION_DK |
CONNECTION_DK |
1:N |
DIM_CREATE_DATE & DIM_TERMINATED_DATE are alias tables of DIM_DATE
DIM_CREATE_TIME & DIM_TERMINATED_TIME are alias tables of DIM_TIME
Boilerplate for Remote Access
with DIM_CREATE_DATE as
(Select
* from SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_DATE),
DIM_TERMINATED_DATE as
(Select
* fromSHARED_ANALYTICS_DATA.SHARED_DATA.DIM_DATE),
DIM_CREATE_TIME as
(Select
* from SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_TIME),
DIM_TERMINATED_TIME as
(Select
* from SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_TIME)
Select
-- enter columns here
From SHARED_ANALYTICS_DATA.SHARED_DATA.FACT_REMOTE_ACCESS_SESSION 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_TENANT t on f.Tenant_DK = t.Tenant_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_TECHNICIAN tc on f.User_DK = tc.User_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_REMOTE_ACCESS_CONNECTION ac on f.Connection_DK = ac.Connection_DK
join DIM_CREATE_DATE cd on f.Create_Date_DK = cd.Date_DK
join DIM_TERMINATED_DATE td on f.Terminated_Date_DK = td.Date_DK
join DIM_CREATE_TIME ct on f.Create_Time_DK = ct.Time_SK
join DIM_TERMINATED_TIME tt on f.Terminated_Time_DK = tt.Time_SK
-- Where clauses here
-- Group By columns here
-- Having clauses here
-- Order by columns here