Backup Session
Tracks the backup history provided by selected monitored services.
Methodology: Kimball Star Schema.
Fact Type: Transactional
Grain: Backup by session event
Backup Session schema
Backup Session table joins
FACT: FACT_BACKUP_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_BACKUP_PRODUCT |
BACKUP_PRODUCT_SK |
BACKUP_PRODUCT_SK |
1:N |
|
DIM_BACKUP_STATUS |
BACKUP_STATUS_SK |
BACKUP_STATUS_SK |
1:N |
|
DIM_START_DATE* |
DATE_SK |
DATE_START_SK |
1:N |
|
DIM_END_DATE* |
DATE_SK |
DATE_END_SK |
1:N |
|
DIM_START_TIME** |
TIME_SK |
TIME_START_SK |
1:N |
|
DIM_END_TIME** |
TIME_SK |
TIME_END_SK |
1:N |
* DIM_START_DATE & DIM_END_DATE are alias tables of DIM_DATE
** DIM_START_TIME & DIM_END_TIME are alias tables of DIM_TIME
Boilerplate for Backup Session table joins
with DIM_START_DATE as
(Select
* from SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_DATE),
DIM_END_DATE as
(Select
* from SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_DATE),
DIM_START_TIME as
(Select
* from SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_TIME),
DIM_END_TIME as
(Select
* from SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_TIME)
Select
-- enter columns here
From SHARED_ANALYTICS_DATA.SHARED_DATA.FACT_BACKUP_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 DIM_START_DATE sd on f.Date_SK = sd.Date_SK
join DIM_END_DATE ed on f.Date_SK = ed.Date_SK
join DIM_START_TIME st on f.Time_SK = st.Time_SK
join DIM_END_TIME et on f.Time_SK = et.Time_SK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_BACKUP_PRODUCT bp on f.Backup_Product_SK = bp.Backup_Product_SK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_BACKUP_STATUS bs on f.Backup_Status_SK = bs.Backup_Status_SK
-- Where clauses here
-- Group By columns here
-- Having clauses here
-- Order by columns here