|
|
||||||
|
|
||||||
User Defined Variables
| Name | Value | Expression | Description |
2016-03-04 |
This is a configuration setting in SSIS_Configurations that is set to the date that the package is being executed on. |
||
EnterpriseDW |
|||
N/A |
If the Enterprise Data Warehouse resides on a different server from the DWStaging and DWoperations databases, a linked server must be defined and the name populated into the Var_EDWLinkedServername. If it resides on the same server, this setting must be set to ?N/A?. This is a configuration setting from SSIS_Configurations. |
||
System.Object |
|||
System.Object |
|||
System.Object |
This contains the Active Terms returned from a SQL select statement. |
||
System.Object |
This contains the Active Terms returned from a SQL select statement. |
||
Y |
This SSIS_Configuration Setting will determine if a Full Load will be done or not. |
||
7 |
|||
DELETE
FROM [EnterpriseDW].dbo.FactEnrollmentSnapshot
WHERE EnrollmentSnapshotSK NOT IN
(
SELECT fes.EnrollmentSnapshotSK
FROM [EnterpriseDW].dbo.FactEnrollmentSnapshot fes
INNER JOIN [EnterpriseDW].dbo.DimRelativeDay drd
ON (drd.RelativeDaySK = fes.RelativeDaySK)
INNER JOIN dbo.FactEnrollmentSnapshot_TermRelativeDays festrd
ON (
festrd.Term = drd.Term
AND
festrd.RelativeDayNumber = drd.RelativeDayNumber
)
)
|
"
DELETE
FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.FactEnrollmentSnapshot
WHERE EnrollmentSnapshotSK NOT IN
(
SELECT fes.EnrollmentSnapshotSK
FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.FactEnrollmentSnapshot fes
INNER JOIN " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.DimRelativeDay drd
ON (drd.RelativeDaySK = fes.RelativeDaySK)
INNER JOIN dbo.FactEnrollmentSnapshot_TermRelativeDays festrd
ON (
festrd.Term = drd.Term
AND
festrd.RelativeDayNumber = drd.RelativeDayNumber
)
)
"
|
||
UPDATE [EnterpriseDW].dbo.FactEnrollmentSnapshot
SET StudentSK = obs_std.NewStudentSK
FROM [EnterpriseDW].dbo.FactEnrollmentSnapshot fct
INNER JOIN [EnterpriseDW].dbo.DimStudent ds
ON (fct.StudentSK = ds.StudentSK)
INNER JOIN dbo.Obsolete_DimStudent obs_std
ON (obs_std.OldStudentSK = ds.StudentSK)
WHERE ds.RowIsCurrent = 'Y';
|
"
UPDATE " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) +"[" + @[User::EDWDBName] + "].dbo.FactEnrollmentSnapshot
SET StudentSK = obs_std.NewStudentSK
FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.FactEnrollmentSnapshot fct
INNER JOIN " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.DimStudent ds
ON (fct.StudentSK = ds.StudentSK)
INNER JOIN dbo.Obsolete_DimStudent obs_std
ON (obs_std.OldStudentSK = ds.StudentSK)
WHERE ds.RowIsCurrent = 'Y';
"
|
||
UPDATE [EnterpriseDW].dbo.FactEnrollmentSnapshot
SET StudentTermSK = NEW_d_StdTrm.StudentTermSK
FROM [EnterpriseDW].dbo.FactEnrollmentSnapshot f_enract
INNER JOIN [EnterpriseDW].dbo.DimStudentTerm OLD_d_StdTrm
ON (OLD_d_StdTrm.StudentTermSK = f_enract.StudentTermSK)
INNER JOIN dbo.Obsolete_DimStudent obs_DimStd
ON (obs_DimStd.OldStudentIdAK = OLD_d_StdTrm.StudentId)
INNER JOIN [EnterpriseDW].dbo.DimStudentTerm NEW_d_StdTrm
ON (
NEW_d_StdTrm.StudentId = obs_DimStd.NewStudentIdAK AND
NEW_d_StdTrm.Term = OLD_d_StdTrm.Term
)
WHERE OLD_d_StdTrm.RowIsCurrent = 'Y'
AND NEW_d_StdTrm.RowIsCurrent = 'Y';
|
"UPDATE " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) +"[" + @[User::EDWDBName] +"].dbo.FactEnrollmentSnapshot
SET StudentTermSK = NEW_d_StdTrm.StudentTermSK
FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) +"[" + @[User::EDWDBName] +"].dbo.FactEnrollmentSnapshot f_enract
INNER JOIN " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] +"].dbo.DimStudentTerm OLD_d_StdTrm
ON (OLD_d_StdTrm.StudentTermSK = f_enract.StudentTermSK)
INNER JOIN dbo.Obsolete_DimStudent obs_DimStd
ON (obs_DimStd.OldStudentIdAK = OLD_d_StdTrm.StudentId)
INNER JOIN " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] +"].dbo.DimStudentTerm NEW_d_StdTrm
ON (
NEW_d_StdTrm.StudentId = obs_DimStd.NewStudentIdAK AND
NEW_d_StdTrm.Term = OLD_d_StdTrm.Term
)
WHERE OLD_d_StdTrm.RowIsCurrent = 'Y'
AND NEW_d_Std |
||
/*
Query provides list of Terms
where the Term and RelativeDayNumber does not
already exist in the FactEnrollmentSnapshot
fact table.
*/
SELECT DISTINCT
fct_enrll_ss_rd.Term
FROM dbo.FactEnrollmentSnapshot_TermRelativeDays fct_enrll_ss_rd
LEFT OUTER JOIN
(
--This section produces a distinct list of Terms and RelativeDayNumbers
--that exist in data warehouse FactEnrollmentSnapshot.
SELECT DISTINCT dim_rd.Term, dim_rd.RelativeDayNumber
FROM [EnterpriseDW].dbo.DimRelativeDay dim_rd
INNER JOIN [EnterpriseDW].dbo.FactEnrollmentSnapshot fct_enrll_ss
ON (fct_enrll_ss.RelativeDaySK = dim_rd.RelativeDaySK)
) AS Fact_Term_DayNum
ON
(
Fact_Term_DayNum.Term = fct_enrll_ss_rd.Term
AND
Fact_Term_DayNum.RelativeDayNumber = fct_enrll_ss_rd.RelativeDayNumber
)
WHERE Fact_Term_DayNum.Term IS NULL |
|||
/*
Query provides list of Terms and RelativeDayDate
where the Term and RelativeDayNumber does not
already exist in the FactEnrollmentSnapshot
fact table.
*/
SELECT
fct_enrll_ss_rd.Term,
--RelativeDayNumber,
cast(fct_enrll_ss_rd.RelativeDayDate AS VARCHAR(10)) AS RelativeDayDate
FROM dbo.FactEnrollmentSnapshot_TermRelativeDays fct_enrll_ss_rd
LEFT OUTER JOIN
(
--This section produces a distinct list of Terms and RelativeDayNumbers
--that exist in data warehouse FactEnrollmentSnapshot.
SELECT DISTINCT dim_rd.Term, dim_rd.RelativeDayNumber
FROM [EnterpriseDW].dbo.DimRelativeDay dim_rd
INNER JOIN [EnterpriseDW].dbo.FactEnrollmentSnapshot fct_enrll_ss
ON (fct_enrll_ss.RelativeDaySK = dim_rd.RelativeDaySK)
) AS Fact_Term_DayNum
ON
(
Fact_Term_DayNum.Term = fct_enrll_ss_rd.Term
AND
Fact_Term_DayNum.RelativeDayNumber = fct_enrll_ss_rd.RelativeDayNumber
)
WHERE Fact_Term_DayNum.Term IS NULL AND fct_enrll_ss_rd.Term = '' |
"
/*
Query provides list of Terms and RelativeDayDate
where the Term and RelativeDayNumber does not
already exist in the FactEnrollmentSnapshot
fact table.
*/
SELECT
fct_enrll_ss_rd.Term,
--RelativeDayNumber,
cast(fct_enrll_ss_rd.RelativeDayDate AS VARCHAR(10)) AS RelativeDayDate
FROM dbo.FactEnrollmentSnapshot_TermRelativeDays fct_enrll_ss_rd
LEFT OUTER JOIN
(
--This section produces a distinct list of Terms and RelativeDayNumbers
--that exist in data warehouse FactEnrollmentSnapshot.
SELECT DISTINCT dim_rd.Term, dim_rd.RelativeDayNumber
FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.DimRelativeDay dim_rd
INNER JOIN " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.FactEnrollmentSnapshot fct_enrll_ss
ON (fct_enrll_ss.RelativeDaySK = dim_rd.RelativeDaySK)
) AS Fact_Term_DayNum
ON
(
Fact_Term_DayNum.Term = fct_enrll_ |
||
/*
Query provides list of Terms and RelativeDayDate
where the Term and RelativeDayNumber does not
already exist in the FactEnrollmentSnapshot
fact table.
*/
SELECT
fct_enrll_ss_rd.Term,
--RelativeDayNumber,
cast(fct_enrll_ss_rd.RelativeDayDate AS VARCHAR(10)) AS RelativeDayDate
FROM dbo.FactEnrollmentSnapshot_TermRelativeDays fct_enrll_ss_rd
LEFT OUTER JOIN
(
--This section produces a distinct list of Terms and RelativeDayNumbers
--that exist in data warehouse FactEnrollmentSnapshot.
SELECT DISTINCT dim_rd.Term, dim_rd.RelativeDayNumber
FROM [EnterpriseDW].dbo.DimRelativeDay dim_rd
INNER JOIN [EnterpriseDW].dbo.FactEnrollmentSnapshot fct_enrll_ss
ON (fct_enrll_ss.RelativeDaySK = dim_rd.RelativeDaySK)
) AS Fact_Term_DayNum
ON
(
Fact_Term_DayNum.Term = fct_enrll_ss_rd.Term
AND
Fact_Term_DayNum.RelativeDayNumber = fct_enrll_ss_rd.RelativeDayNumber
)
WHERE Fact_Term_DayNum.Term IS NULL AND fct_enrll_ss_rd.Term = '' |
"
/*
Query provides list of Terms and RelativeDayDate
where the Term and RelativeDayNumber does not
already exist in the FactEnrollmentSnapshot
fact table.
*/
SELECT
fct_enrll_ss_rd.Term,
--RelativeDayNumber,
cast(fct_enrll_ss_rd.RelativeDayDate AS VARCHAR(10)) AS RelativeDayDate
FROM dbo.FactEnrollmentSnapshot_TermRelativeDays fct_enrll_ss_rd
LEFT OUTER JOIN
(
--This section produces a distinct list of Terms and RelativeDayNumbers
--that exist in data warehouse FactEnrollmentSnapshot.
SELECT DISTINCT dim_rd.Term, dim_rd.RelativeDayNumber
FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.DimRelativeDay dim_rd
INNER JOIN " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.FactEnrollmentSnapshot fct_enrll_ss
ON (fct_enrll_ss.RelativeDaySK = dim_rd.RelativeDaySK)
) AS Fact_Term_DayNum
ON
(
Fact_Term_DayNum.Term = fct_enrll_ |
||
System Variables
| Name | Value | Expression | Description |
| Powered by BI Documenter |
