sql-server-docker-basics/sql/08_load_reporting_model.sql at main · DataTideHH/sql-server-docker-basics · GitHub

History

181 lines (164 loc) · 4.91 KB

Raw

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

USE dpa_training;

SET ANSI_NULLS ON;

SET QUOTED_IDENTIFIER ON;

SET ANSI_PADDING ON;

SET ANSI_WARNINGS ON;

SET ARITHABORT ON;

SET CONCAT_NULL_YIELDS_NULL ON;

SET NUMERIC_ROUNDABORT OFF;

SET NOCOUNT ON;

SET XACT_ABORT ON;

IF OBJECT_ID(N'dpa.v_assessment_results', N'V') IS NULL

THROW 51200, 'Missing source view: dpa.v_assessment_results', 1;

IF OBJECT_ID(N'reporting.fact_assessment_result', N'U') IS NULL

THROW 51200, 'Missing reporting model. Run sql/07_create_reporting_model.sql first.', 1;

BEGIN TRY

BEGIN TRANSACTION;

UPDATE target

SET

target.module_code = src.module_code,

target.module_name = src.module_name,

target.topic_area = src.topic_area

FROM reporting.dim_module target

JOIN dpa.learning_modules src

ON src.module_id = target.source_module_id;

INSERT INTO reporting.dim_module (

source_module_id,

module_code,

module_name,

topic_area

)

SELECT

src.module_id,

src.module_code,

src.module_name,

src.topic_area

FROM dpa.learning_modules src

WHERE NOT EXISTS (

SELECT 1

FROM reporting.dim_module target

WHERE target.source_module_id = src.module_id

);

UPDATE target

SET

target.learner_code = src.learner_code,

target.learner_name = src.learner_name

FROM reporting.dim_learner target

JOIN dpa.learners src

ON src.learner_id = target.source_learner_id;

INSERT INTO reporting.dim_learner (

source_learner_id,

learner_code,

learner_name

)

SELECT

src.learner_id,

src.learner_code,

src.learner_name

FROM dpa.learners src

WHERE NOT EXISTS (

SELECT 1

FROM reporting.dim_learner target

WHERE target.source_learner_id = src.learner_id

);

UPDATE target

SET

target.assessment_name = src.assessment_name,

target.assessment_date = src.assessment_date

FROM reporting.dim_assessment target

JOIN dpa.assessments src

ON src.assessment_id = target.source_assessment_id;

INSERT INTO reporting.dim_assessment (

source_assessment_id,

assessment_name,

assessment_date

)

SELECT

src.assessment_id,

src.assessment_name,

src.assessment_date

FROM dpa.assessments src

WHERE NOT EXISTS (

SELECT 1

FROM reporting.dim_assessment target

WHERE target.source_assessment_id = src.assessment_id

);

SELECT

ar.result_id AS source_result_id,

dm.module_key,

dl.learner_key,

da.assessment_key,

vr.score,

vr.max_score,

vr.pass_score

INTO #fact_source

FROM dpa.assessment_results ar

JOIN dpa.v_assessment_results vr

ON vr.result_id = ar.result_id

JOIN dpa.assessments a

ON a.assessment_id = ar.assessment_id

JOIN reporting.dim_module dm

ON dm.source_module_id = a.module_id

JOIN reporting.dim_learner dl

ON dl.source_learner_id = ar.learner_id

JOIN reporting.dim_assessment da

ON da.source_assessment_id = ar.assessment_id;

IF (SELECT COUNT(*) FROM #fact_source)

<> (SELECT COUNT(*) FROM dpa.assessment_results)

BEGIN

THROW 51201, 'Reporting load could not resolve every source result to dimension keys.', 1;

END;

UPDATE target

SET

target.module_key = src.module_key,

target.learner_key = src.learner_key,

target.assessment_key = src.assessment_key,

target.score = src.score,

target.max_score = src.max_score,

target.pass_score = src.pass_score,

target.loaded_at_utc = SYSUTCDATETIME()

FROM reporting.fact_assessment_result target

JOIN #fact_source src

ON src.source_result_id = target.source_result_id;

INSERT INTO reporting.fact_assessment_result (

source_result_id,

module_key,

learner_key,

assessment_key,

score,

max_score,

pass_score

)

SELECT

src.source_result_id,

src.module_key,

src.learner_key,

src.assessment_key,

src.score,

src.max_score,

src.pass_score

FROM #fact_source src

WHERE NOT EXISTS (

SELECT 1

FROM reporting.fact_assessment_result target

WHERE target.source_result_id = src.source_result_id

);

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

IF XACT_STATE() <> 0

ROLLBACK TRANSACTION;

THROW;

END CATCH;

SELECT

(SELECT COUNT(*) FROM reporting.dim_module) AS module_dimension_rows,

(SELECT COUNT(*) FROM reporting.dim_learner) AS learner_dimension_rows,

(SELECT COUNT(*) FROM reporting.dim_assessment) AS assessment_dimension_rows,

(SELECT COUNT(*) FROM reporting.fact_assessment_result) AS fact_rows,

(SELECT COUNT(*) FROM reporting.fact_assessment_result WHERE passed = 1) AS passed_fact_rows,

CAST(1 AS bit) AS reporting_load_completed;

sql-server-docker-basics/sql/08_load_reporting_model.sql at main · DataTideHH/sql-server-docker-basics · GitHub

Expand file tree

Breadcrumbs

08_load_reporting_model.sql

Latest commit

History

Breadcrumbs

08_load_reporting_model.sql

File metadata and controls