To all the subject matter experts working with preceding loads,
Below is an example from my load script in its current state. It has been recommended to optimize this script by removing the resident loads and doing a preceding load. There is a Original Coverage Key and a Current Coverage Key in the fact table. I thought resident load would be the most simplistic way to link Coverage key to the appropriate key in the fact. Any suggestions on what this would look like if I was to do a preceding load?
I read other blogs that emphasize on the benefit and I have seen some examples. But, I have not seen an example where there is multiple tables being loaded, and also if there is no calculation being added to the next level. Your input is appreciated. Thanks
Coverage:
LOAD
[%Coverage Key]
, [Subscriber Group Number]
, [Subscriber Policy Number]
, [Benefit Plan Name]
, [Payor Financial Class]
, [Payor Name]
;
SQL
Select
CoverageKey AS [%Coverage Key]
, SubscriberGroupNumber AS [Subscriber Group Number]
, SubscriberNumber AS [Subscriber Policy Number]
, BenefitPlanName AS [Benefit Plan Name]
, CASE WHEN PayorFinancialClass = '*Not Applicable'
THEN 'Self-pay'
ELSE PayorFinancialClass
END AS [Payor Financial Class]
, CASE WHEN PayorName = '*Not Applicable'
THEN 'Self-pay'
ELSE PayorName
END AS [Payor Name]
from SecureAccess.CoverageDim
;
[Original Payor Coverage Temp]:
LOAD
[%Coverage Key] AS [%Original Coverage Key]
, [Subscriber Group Number] AS [Original Subscriber Group Number]
, [Subscriber Policy Number] AS [Original Subscriber Policy Number]
, [Benefit Plan Name] AS [Original Benefit Plan Name]
, [Payor Financial Class] AS [Original Payor Financial Class]
, [Payor Name] AS [Original Payor Name]
Resident [Coverage]
;
[Original Payor Coverage]:
LOAD
[%Original Coverage Key]
, [Original Subscriber Group Number]
, [Original Subscriber Policy Number]
, [Original Benefit Plan Name]
, [Original Payor Financial Class]
, ApplyMap('Map_OriginalPayorFinancialClass_OriginalPayorFinancialClassID', [Original Payor Financial Class], '*Unspecified') AS [Original Payor Financial Class ID]
, ApplyMap('Map_OriginalPayorFinancialClass_OriginalPayorFinancialClassGroup', [Original Payor Financial Class], '*Unspecified') AS [Original Payor Financial Class Group]
, ApplyMap('Map_OriginalPayorFinancialClass_OriginalPayorFinancialClassSuperGroup', [Original Payor Financial Class], '*Unspecified') AS [Original Payor Financial Class Super Group]
, ApplyMap('Map_OriginalPayorFinancialClass_OriginalPayorFinancialClassDivision', [Original Payor Financial Class], '*Unspecified') AS [Original Payor Financial Class Division]
, [Original Payor Name]
Resident [Original Payor Coverage Temp]
;
[Current Payor Coverage Temp]:
LOAD
[%Coverage Key] AS [%Epic Current Coverage Key]
, [Subscriber Group Number] AS [Current Subscriber Group Number]
, [Subscriber Policy Number] AS [Current Subscriber Policy Number]
, [Benefit Plan Name] AS [Current Benefit Plan Name]
, [Payor Financial Class] AS [Current Payor Financial Class]
, [Payor Name] AS [Current Payor Name]
Resident [Coverage]
;
[Current Payor Coverage]:
LOAD
[%Epic Current Coverage Key]
, [Current Subscriber Group Number]
, [Current Subscriber Policy Number]
, [Current Benefit Plan Name]
, [Current Payor Financial Class]
, ApplyMap('Map_CurrentPayorFinancialClass_CurrentPayorFinancialClassID', [Current Payor Financial Class], '*Unspecified') AS [Current Payor Financial Class ID]
, ApplyMap('Map_CurrentPayorFinancialClass_CurrentPayorFinancialClassGroup', [Current Payor Financial Class], '*Unspecified') AS [Current Payor Financial Class Group]
, ApplyMap('Map_CurrentPayorFinancialClass_CurrentPayorFinancialClassSuperGroup', [Current Payor Financial Class], '*Unspecified') AS [Current Payor Financial Class Super Group]
, ApplyMap('Map_CurrentPayorFinancialClass_CurrentPayorFinancialClassDivision', [Current Payor Financial Class], '*Unspecified') AS [Current Payor Financial Class Division]
, [Current Payor Name]
Resident [Current Payor Coverage Temp]
;
DROP TABLES [Coverage],[Original Payor Coverage Temp],[Current Payor Coverage Temp];
