CUBES CUBES

There are a variety of cubes created for analysing key UEA business processes.

The cubes contain dimensions, which hold contextual information for the analysis, and measures which contain the values to be analysed.

They hold the data in a form optimised for quick analysis and reporting.

The cubes are held within the following Analysis Services databases.

Database Cubes Notes SPOT Groups Having Access

DataWarehouse_A.

Refreshed daily, overnight. Additional refreshes in working day during Board Reporting and Clearing & Confirmation periods.

Cube_Application Details of applications

cis_dw_group_planning_cube,

cis_dw_group_arm_cube,

cis_dw_group_equalities_cube 

Cube_Application_SDRA Holds Sub-Dept Running Average, used during clearing & confirmation as cube_application
Cube_Assessment Details of students' assessments cis_dw_group_lts_cube 
Cube_Award Details of awards

cis_dw_group_equalities_cube,

cis_dw_group_lts_cube,

cis_dw_group_pgr_cube,

cis_dw_group_planning_cube

Cube_Module Module results

cis_dw_group_lts_cube,

cis_dw_group_planning_cube
Cube_Stage Stage-level data

cis_dw_group_lts_cube, 

cis_dw_group_planning_cube 
Cube_Student Student profile and numbers

cis_dw_group_equalities_cube,

cis_dw_group_lts_cube,

cis_dw_group_pgr_cube,

cis_dw_group_planning_cube  

DataWarehouse_B.

Refreshed daily overnight.

Cube_Attendance Attendance info cis_dw_group_lts_cube (Note:  cis_dw_group_lts also used to provide access in the interim, until standard reports are available through Tableau)
Cube_Room_Usage Room booking info cis_dw_group_planning_cube 
Cube_Teaching Which staff teach which lessons cis_dw_group_faculty_cube 
DataWarehouse_C. Refreshed weekly, on Sunday night. Cube_National_Student_Survey NSS Results as cube_student 
Cube_Snapshot_Application Regular snapshots of applicant data, for time-series analysis as cube_application 
Cube_Snapshot_Student Regular snapshots of current student data, for time-series analysis as cube_student 

DataWarehouse_D.

Refreshed daily overnight.

Cube_Data_Quality Records which have data quality issues Those with access to cube_student or cube_application.

DataWarehouse_E.

Refreshed weekly, on Saturday nights.

Cube_Library_Loan Loans taken out from the library cis_dw_group_library_cube
Cube_Library_Item Items in the library cis_dw_group_library_cube

DataWarehouse_F

Refreshed weekly on Sunday nights

Cube_Access_Event Swipe access to the library cis_dw_group_library_cube
DataWarehouse_G Cube_Accommodation_Room Details of student accommodation TBD
DataWarehouse_H Cube_Careers_Activity Student activities linked to Careers Service cis_dw_group_careers - Note: No separate cube group created.

For details of who is in the SPOT Groups please contact the IT Helpdesk.

Refreshing the Cubes

The cubes are refreshed using batches, with one batch per database.   

The cubes remain available during the refresh, but show the data from the previous refresh until the refresh has completed. 

If the refresh fails the cubes will normally remain available, but will hold data from the previous refresh. 

Failure of one batch will normally not affect other batches.

 

Technical Info

The cube fact tables are loaded as specified below. 

 

Cube Fact Table and Load Scripts
Cube_Application

Fact Table: Fact_Application

Load To DW: USP_Load_Fact_Application

Load To Staging: USP_Load_Application_Staging_Table_From_SITS

Cube_Application_SDRA

Fact Table:

Application_Sub_Dept_Running_Avg

(view based upon Fact_Application)

Load as Cube_Application

Cube_Assessment

Fact Table: Fact_Board_Assessment (view based on Fact_Module_Assessment_Result)

To DW: USP_Load_Fact_Module_Assessment_Result

To Staging:

USP_Load_Module_Assessment_Result_Staging_Table_From_SITS

 

Cube_Award

Fact Table: Fact_Award

To DW: USP_Load_Fact_Award

To Staging: USP_Load_Award_Staging_Table_From_SITS

Cube_Module

Fact Table: Fact_Board_Module (view based upon Fact_Module_Result)

To DW: USP_Load_Fact_Module_Result

To Staging: USP_Load_Module_Result_Staging_Table_From_SITS

Cube_Stage

Fact Table: Fact_Stage_Over_Time

To DW: USP_Load_Fact_Stage_Over_Time.

Note: Stage info taken from Fact_Module_Result, no separate load procedure from SITS.

 

Cube_Student

Fact Table: Fact_Student_Academic_Year, (view based mostly upon Fact_Member_Over_Time table).

To DW: USP_Load_Fact_Member_Over_Time

To Staging: USP_Load_Member_Over_Time_Staging_Table_From_SITS

Cube_Attendance

Fact Table: Fact_Student_Attendance (view based upon Fact_Event_Attribute, Fact_Event_Instance).

Note: Fact_Event_Attribute further depends on Fact_Absence

Load To DW: USP_Load_Fact_Event_Attribute, USP_Load_Fact_Event_Instance, USP_Load_Fact_Absence.

Load To Staging: USP_Load_Event_Attribute_Staging_Table_From_SITS, USP_Load_Event_Instance_Staging_Table_From_SITS, USP_Load_Absence_Staging_Table_From_SITS

 

Cube_Room_Usage

Fact Table: Fact_Room_Usage (view based upon same tables as Cube_Attendance).

Load: As For Cube_Attendance (note: Absence info not used) 

Cube_Teaching Fact Table: Fact_Staff_Teaching (view based upon same tables as Cube_Attendance).
Cube_National_Student_Survey

Fact Table: NSS_Results (view based upon DW_Interfaces.dbo.NSS_Base_Data).

Load: Uses Package NSS_Load, followed by procedure USP_Update_NSS_Institution

Cube_Snapshot_Application

Fact Table: Snapshot_Application

Load To DW: USP_Load_Snapshot_Application

Note: No separate load

Cube_Snapshot_Student

Fact Table: Snapshot_Student

Load To DW: USP_Load_Snapshot_Student

Cube_Data_Quality

Fact Table: DQ_Check_Detail

Load To DW: USP_Load_DQ_Check_Detail.

Note: Dynamically loads records views beginning DQ_ in DW database.

Cube_Library_Loan

Fact Table: Fact_Library_Loan (view based upon Fact_Library_Loan_Base)

Load To DW: USP_Load_Fact_Library_Loan

Load To Staging: USP_Load_Library_Loan_Staging_Table_From_Aleph

Cube_Library_Item

Fact Table: Library_Item_Summary (view based upon Fact_Library_Loan)

Load: As For Cube_Library_Loan

Cube_Access_Event

Fact Table: Fact_Access_Event

Load To DW: USP_Load_Fact_Access_Event

Load To Staging: USP_Load_Access_Event_Staging_Table_From_Gallagher

Cube_Accommodation_Room

Fact Table: Accommodation_Room_Status

Load To DW: USP_Load_Fact_Accommodation_Allocation, USP_Load_Fact_Accommodation_Servicing

 

Load To Staging: USP_Load_Accommodation_Allocation_From_Kinetics,

USP_Load_Accommodation_Servicing_From_Kinetics

Cube_Careers_Activity

Fact Table: USP_Load_Fact_Careers_Student_Activity

Load To Staging: USP_Load_Careers_Student_Activity_From_CareerHub