0 Comments

Need to design Erwin and one one excel, data models. 
1. star schema

2. Normalised

3. denotmalised

i have attached two reports and I will need you to design the data models for  report A and report B.

the attachments talk about the file layout for report a and report b. So we will need to design a data models. 

record identifiers

Record Indicator Record Definition Notes
CHD Contract of Record level file header Occurs once per Contract of Record for each one on file
SHD Submitting Contract level file header Occurs once per Submitting Contract for each one on file
DET Detail records for the report Occurs 1 to many times per SHD record
STR Submitting Contract level file trailer Occurs once per each SHD on the file
CTR Contract of Record level file trailer Occurs once per each CHD on the file
REPORT 43: COV only

&"Times New Roman,Regular"&8&F &A &"Times New Roman,Regular"&8Page &P of &N &"Times New Roman,Regular"&8&D &T

DET Sort Order

FIELD NO. FIELD NAME
4 Current CMS Medicare beneficiary identifier

&"Times New Roman,Regular"&8&F &A &"Times New Roman,Regular"&8Page &P of &N &"Times New Roman,Regular"&8&D &T

CHD (contract of record hdr)

FIELD NO. FIELD NAME POSITION PICTURE LENGTH FIELD DESCRIPTION / VALUES
1 RECORD-ID 1 – 3 X(3) 3 "CHD"
2 SEQUENCE-NO 4 – 10 9(7) 7 Starts with 0000001
3 CONTRACT-NO 11 – 15 X(5) 5 Contract number of the Plan of Record
4 FILE-ID 16 – 31 X(16) 16 43COVCCYY### (Where 43 = Due To Submitting Contracts – Current Month report COV indicates the drug coverage status being reported on (covered drugs only) CCYY indicates the benefit year ### indicates sequential versions of this file. The 4 right-most positions are populated with spaces.)
5 PROD-TEST-IND 32 – 35 X(4) 4 TEST or PROD
6 AS-OF-YEAR 36 – 39 X(4) 4 Identifies "data reported through" year. Format is CCYY.
7 AS-OF-MONTH 40 – 41 X(2) 2 Identifies "data reported through" month. Valid values are 01 through 12.
8 DDPS-SYSTEM-DATE 42 – 49 X(8) 8 ‘CCYYMMDD’ = DDPS File creation date.
9 DDPS-SYSTEM-TIME 50 – 55 X(6) 6 ‘HHMMSS’ = DDPS File creation time.
10 DDPS-REPORT-ID 56 – 60 X(5) 5 DDPS Report identifier ('43COV')
11 FILLER 61 – 512 X(452) 452 SPACES

&"Times New Roman,Regular"&8&F &A &"Times New Roman,Regular"&8Page &P of &N &"Times New Roman,Regular"&8&D &T

SHD (submitting contract hdr)

FIELD NO. FIELD NAME POSITION PICTURE LENGTH FIELD DESCRIPTION / VALUES
1 RECORD-ID 1 – 3 X(3) 3 "SHD"
2 SEQUENCE-NO 4 – 10 9(7) 7 Starts with 0000001
3 CONTRACT-NO 11 – 15 X(5) 5 Submitting Contract number
4 FILLER 16 – 18 X(3) 3 SPACES
5 FILE-ID 19 – 34 X(16) 16 43COVCCYY### (Where 43 = Due To Submitting Contracts – Current Month report COV indicates the drug coverage status being reported on (covered drugs only) CCYY indicates the benefit year ### indicates sequential versions of this file. The 4 right-most positions are populated with spaces.)
6 PROD-TEST-IND 35 – 38 X(4) 4 TEST or PROD
7 AS-OF-YEAR 39 – 42 X(4) 4 Identifies "data reported through" year. Format is CCYY.
8 AS-OF-MONTH 43 – 44 X(2) 2 Identifies "data reported through" month. Valid values are 01 through 12.
9 DDPS-SYSTEM-DATE 45 – 52 X(8) 8 ‘CCYYMMDD’ = DDPS File creation date.
10 DDPS-SYSTEM-TIME 53 – 58 X(6) 6 ‘HHMMSS’ = DDPS File creation time.
11 DDPS-REPORT-ID 59 – 63 X(5) 5 DDPS Report identifier ('43COV')
12 FILLER 64 – 512 X(449) 449 SPACES

&"Times New Roman,Regular"&8&F &A &"Times New Roman,Regular"&8Page &P of &N &"Times New Roman,Regular"&8&D &T

DET

FIELD NO. FIELD NAME POSITION PICTURE LENGTH FIELD DESCRIPTION / VALUES
1 RECORD-ID 1 – 3 X(3) 3 DET
2 SEQUENCE-NO 4 – 10 9(7) 7 Must start with 0000001
3 DRUG-COVERAGE-STATUS-CODE 11 – 11 X(1) 1 Note: only PDEs with a value of 'C' (Covered Drug) will be included on this report.
4 Current CMS Medicare beneficiary identifier 12 – 31 X(20) 20 The most current CMS Medicare Beneficiary Identifier (MBI).
5 Last Submitted Medicare beneficiary identifier 32 – 51 X(20) 20 Beneficiary identifier (HICN or MBI) from the most recent accepted PDE in the DDPS database.
6 CURRENT-MONTH-GDCB-AMOUNT 52 – 65 S9(12)V99 14 Monthly change in Gross Drug Cost Below the Catastrophic Coverage Threshold
7 CURRENT-MONTH-GDCA-AMOUNT 66 – 79 S9(12)V99 14 Monthly change in Gross Drug Cost Above the Catastrophic Coverage Threshold
8 CURRENT-MONTH-TOTAL-GROSS-DRUG-COST 80 – 93 S9(12)V99 14 Sum of Net Ingredient Cost, Net Dispensing Fee,Net Sales Tax and Net Vaccine Administration Fee.
9 CURRENT-MONTH-LICS-AMOUNT 94 – 107 S9(12)V99 14 Monthly change in Low Income Cost Sharing Amount
10 CURRENT-MONTH-CPP-AMOUNT 108 – 121 S9(12)V99 14 Monthly change in Covered Plan Paid Amount
11 P2P-CONTRACT 122 – 126 X(5) 5 The contract number of the Submitting Contract associated with the P2P reconciliation condition.
12 CURRENT-MONTH-P2P-AMOUNT 127 – 140 S9(12)V99 14 The amount related to the Plan-to-Plan reconciliation condition. This amount represents the amount "due-to" this Submitting Contract from this Contract of Record for this beneficiary. This field is the sum of the LICS Amount and CPP Amount.
13 FILLER 141 – 512 X(372) 372 SPACES

&"Times New Roman,Regular"&8&F &A &"Times New Roman,Regular"&8Page &P of &N &"Times New Roman,Regular"&8&D &T

STR (submitting contract trlr)

FIELD NO. FIELD NAME POSITION PICTURE LENGTH FIELD DESCRIPTION / VALUES
1 RECORD-ID 1 – 3 X(3) 3 "STR"
2 SEQUENCE-NO 4 – 10 9(7) 7 Starts with 0000001
3 CONTRACT-NO 11 – 15 X(5) 5 Same as SHD
4 FILLER 16 – 18 X(3) 3 SPACES
5 DRUG-COVERAGE-STATUS-CODE 19 – 19 X(1) 1 Note: only PDEs with a value of 'C' (Covered Drug) will be included on this report.
6 BENEFICIARY-COUNT 20 – 30 9(11) 11 Count of beneficiaries with utilization in the reporting period.
7 CURRENT-MONTH-GDCB-AMOUNT 31 – 44 S9(12)V99 14 Monthly change in Gross Drug Cost Below the Catastrophic Coverage Threshold
8 CURRENT-MONTH-GDCA-AMOUNT 45 – 58 S9(12)V99 14 Monthly change in Gross Drug Cost Above the Catastrophic Coverage Threshold
9 CURRENT-MONTH-TOTAL-GROSS-DRUG-COST 59 – 72 S9(12)V99 14 Sum of Net Ingredient Cost, Net Dispensing Fee, Net Sales Tax and Net Vaccine Administration Fee.
10 CURRENT-MONTH-LICS-AMOUNT 73 – 86 S9(12)V99 14 Monthly change in Low Income Cost Sharing Amount
11 CURRENT-MONTH-CPP-AMOUNT 87 – 100 S9(12)V99 14 Monthly change in Covered Plan Paid Amount
12 DET-RECORD-TOTAL 101 – 108 9(8) 8 Total count of DET records
13 CURRENT-MONTH-P2P-AMOUNT-DUE-TO-ALL-SUBMITTING-CONTRACTS 109 – 122 S9(12)V99 14 The amount related to the Plan-to-Plan reconciliation condition. This amount represents the amount "due-to" this Submitting Contract from this Contract of Record. This field is the sum of the LICS Amount and CPP Amount.
14 FILLER 123 – 512 X(390) 390 SPACES

&"Times New Roman,Regular"&8&F &A &"Times New Roman,Regular"&8Page &P of &N &"Times New Roman,Regular"&8&D &T

CTR (contract of record trlr)

FIELD NO. FIELD NAME POSITION PICTURE LENGTH FIELD DESCRIPTION / VALUES
1 RECORD-ID 1 – 3 X(3) 3 "CTR"
2 SEQUENCE-NO 4 – 10 9(7) 7 Must start with 0000001
3 CONTRACT-NO 11 – 15 X(5) 5 Must match CHD
4 DRUG-COVERAGE-STATUS-CODE 16 – 16 X(1) 1 Note: only PDEs with a value of 'C' (Covered Drug) will be included on this report.
5 BENEFICIARY-COUNT 17 – 27 9(11) 11 Count of beneficiaries with utilization in the reporting period.
6 FILLER 28 – 36 X(9) 9 SPACES
7 CURRENT-MONTH-GDCB-AMOUNT 37 – 50 S9(12)V99 14 Monthly change in Gross Drug Cost Below the Catastrophic Coverage Threshold
8 CURRENT-MONTH-GDCA-AMOUNT 51 – 64 S9(12)V99 14 Monthly change in Gross Drug Cost Above the Catastrophic Coverage Threshold
9 CURRENT-MONTH-TOTAL-GROSS-DRUG-COST 65 – 78 S9(12)V99 14 Sum of Net Ingredient Cost, Net Dispensing Fee, Net Sales Tax and Net Vaccine Administration Fee.
10 CURRENT-MONTH-LICS-AMOUNT 79 – 92 S9(12)V99 14 Monthly change in Low Income Cost Sharing Amount
11 CURRENT-MONTH-CPP-AMOUNT 93 – 106 S9(12)V99 14 Monthly change in Covered Plan Paid Amount
12 DET-RECORD-TOTAL 107 – 114 9(8) 8 Total count of DET records
13 CURRENT-MONTH-P2P-AMOUNT-DUE-TO-ALL-SUBMITTING-CONTRACTS 115 – 128 S9(12)V99 14 The amount related to the Plan-to-Plan reconciliation condition. This amount represents the amount "due-to" all Submitting Contracts from this Contract of Record. This field is the sum of the LICS Amount and CPP Amount.
14 FILLER 129 – 512 X(384) 384 SPACES

&"Times New Roman,Regular"&8&F &A &"Times New Roman,Regular"&8Page &P of &N &"Times New Roman,Regular"&8&D &T

,

record identifiers

Record Indicator Record Definition Notes
CHD Submitter Contract level file header Occurs once per Submitting Contract for each one on file
RHD Contract of Record level file header Occurs once per Contract of Record for each one on file
DET Detail records for the report Occurs 1 to many times per RHD record
RTR Contract of Record level file trailer Occurs once per each RHD on the file
CTR Submitter Contract level file trailer Occurs once per each CHD on the file
REPORT 41: COV only

&"Times New Roman,Regular"&8&F &A &"Times New Roman,Regular"&8Page &P of &N &"Times New Roman,Regular"&8&D &T

DET Sort Order

FIELD NO. FIELD NAME
4 Current CMS Medicare beneficiary identifier

&"Times New Roman,Regular"&8&F &A &"Times New Roman,Regular"&8Page &P of &N &"Times New Roman,Regular"&8&D &T

CHD (submitting contract hdr)

FIELD NO. FIELD NAME POSITION PICTURE LENGTH FIELD DESCRIPTION / VALUES
1 RECORD-ID 1 – 3 X(3) 3 "CHD"
2 SEQUENCE-NO 4 – 10 9(7) 7 Starts with 0000001
3 CONTRACT-NO 11 – 15 X(5) 5 Submitting Contract number
4 FILE-ID 16 – 31 X(16) 16 41COVCCYY### (Where 41 = Due from Contracts of Record – Current Month Report COV indicates the drug coverage status being reported on (covered drugs only) CCYY indicates the benefit year ### indicates sequential versions of this file. The 4 right-most positions are populated with spaces.)
5 PROD-TEST-IND 32 – 35 X(4) 4 TEST or PROD
6 AS-OF-YEAR 36 – 39 X(4) 4 Identifies "data reported through" year. Format is CCYY.
7 AS-OF-MONTH 40 – 41 X(2) 2 Identifies "data reported through" month. Valid values are 01 through 12.
8 DDPS-SYSTEM-DATE 42 – 49 X(8) 8 ‘CCYYMMDD’ = DDPS File creation date.
9 DDPS-SYSTEM-TIME 50 – 55 X(6) 6 ‘HHMMSS’ = DDPS File creation time.
10 DDPS-REPORT-ID 56 – 60 X(5) 5 DDPS Report identifier ('41COV')
11 FILLER 61 – 512 X(452) 452 SPACES

&"Times New Roman,Regular"&8&F &A &"Times New Roman,Regular"&8Page &P of &N &"Times New Roman,Regular"&8&D &T

RHD (contract of record hdr)

FIELD NO. FIELD NAME POSITION PICTURE LENGTH FIELD DESCRIPTION / VALUES
1 RECORD-ID 1 – 3 X(3) 3 "RHD"
2 SEQUENCE-NO 4 – 10 9(7) 7 Starts with 0000001
3 CONTRACT-NO 11 – 15 X(5) 5 Contract Number of the Plan of Record
4 FILLER 16 – 18 X(3) 3 SPACES
5 FILE-ID 19 – 34 X(16) 16 41COVCCYY### (Where 41 = Due from Contracts of Record – Current Month Report COV indicates the drug coverage status being reported on (covered drugs only) CCYY indicates the benefit year ### indicates sequential versions of this file. The 4 right-most positions are populated with spaces.)
6 PROD-TEST-IND 35 – 38 X(4) 4 TEST or PROD
7 AS-OF-YEAR 39 – 42 X(4) 4 Identifies "data reported through" year. Format is CCYY.
8 AS-OF-MONTH 43 – 44 X(2) 2 Identifies "data reported through" month. Valid values are 01 through 12.
9 DDPS-SYSTEM-DATE 45 – 52 X(8) 8 ‘CCYYMMDD’ = DDPS File creation date.
10 DDPS-SYSTEM-TIME 53 – 58 X(6) 6 ‘HHMMSS’ = DDPS File creation time.
11 DDPS-REPORT-ID 59 – 63 X(5) 5 DDPS Report identifier ('41COV')
12 FILLER 64 – 512 X(449) 449 SPACES

&"Times New Roman,Regular"&8&F &A &"Times New Roman,Regular"&8Page &P of &N &"Times New Roman,Regular"&8&D &T

DET

FIELD NO. FIELD NAME POSITION PICTURE LENGTH FIELD DESCRIPTION / VALUES
1 RECORD-ID 1 – 3 X(3) 3 DET
2 SEQUENCE-NO 4 – 10 9(7) 7 Must start with 0000001
3 DRUG-COVERAGE-STATUS-CODE 11 – 11 X(1) 1 Note: Only PDEs with a value of 'C' (Covered Drug) will be included on this report.
4 Current CMS Medicare beneficiary identifier 12 – 31 X(20) 20 The most current CMS Medicare Beneficiary Identifier (MBI).
5 Last Submitted Medicare beneficiary identifier 32 – 51 X(20) 20 Beneficiary identifier (HICN or MBI) from the most recent accepted PDE in the DDPS database.
6 LAST-SUBMITTED-CARDHOLDER-ID 52 – 71 X(20) 20 Plan identification of the enrollee, as reported on the most recent PDE for the benefit year.
7 CURRENT-MONTH-GDCB-AMOUNT 72 – 85 S9(12)V99 14 Monthly change in Gross Drug Cost Below the Catastrophic Coverage Threshold
8 CURRENT-MONTH-GDCA-AMOUNT 86 – 99 S9(12)V99 14 Monthly change in Gross Drug Cost Above the Catastrophic Coverage Threshold
9 CURRENT-MONTH-TOTAL-GROSS-DRUG-COST 100 – 113 S9(12)V99 14 Sum of Net Ingredient Cost, Net Dispensing Fee, Net Sales Tax, and Net Vaccine Administration Fee.
10 CURRENT-MONTH-LICS-AMOUNT 114 – 127 S9(12)V99 14 Monthly change in Low Income Cost Sharing Amount
11 CURRENT-MONTH-CPP-AMOUNT 128 – 141 S9(12)V99 14 Monthly change in Covered Plan Paid Amount
12 CURRENT-MONTH-P2P-AMOUNT 142 – 155 S9(12)V99 14 The amount related to the P2P reconciliation condition. This amount represents the amount "due-from" the Plan of Record. This field is the sum of the LICS Amount and CPP Amount.
13 FILLER 156 – 512 X(357) 357 SPACES

&"Times New Roman,Regular"&8&F &A &"Times New Roman,Regular"&8Page &P of &N &"Times New Roman,Regular"&8&D &T

RTR (contract of record trlr)

FIELD NO. FIELD NAME POSITION PICTURE LENGTH FIELD DESCRIPTION / VALUES
1 RECORD-ID 1 – 3 X(3) 3 "RTR"
2 SEQUENCE-NO 4 – 10 9(7) 7 Starts with 0000001
3 CONTRACT-NO 11 – 15 X(5) 5 Same as RHD
4 FILLER 16 – 18 X(3) 3 SPACES
5 DRUG-COVERAGE-STATUS-CODE 19 – 19 X(1) 1 Note: Only PDEs with a value of 'C' (Covered Drug) will be included on this report.
6 BENEFICIARY-COUNT 20 – 30 9(11) 11 Count of Beneficiaries with utilization in the reporting period
7 CURRENT-MONTH-GDCB-AMOUNT 31 – 44 S9(12)V99 14 Monthly change in Gross Drug Cost Below the Catastrophic Coverage Threshold
8 CURRENT-MONTH-GDCA-AMOUNT 45 – 58 S9(12)V99 14 Monthly change in Gross Drug Cost Above the Catastrophic Coverage Threshold
9 CURRENT-MONTH-TOTAL-GROSS-DRUG-COST 59 – 72 S9(12)V99 14 Sum of Net Ingredient Cost, Net Dispensing Fee, Net Sales Tax, and Net Vaccine Administration Fee.
10 CURRENT-MONTH-LICS-AMOUNT 73 – 86 S9(12)V99 14 Monthly change in Low Income Cost Sharing Amount
11 CURRENT-MONTH-CPP-AMOUNT 87 – 100 S9(12)V99 14 Monthly change in Covered Plan Paid Amount
12 DET-RECORD-TOTAL 101 – 108 9(8) 8 Total count of DET records
13 CURRENT-MONTH-P2P-AMOUNT-DUE-FROM-ALL-PLANS-OF-RECORD 109 – 122 S9(12)V99 14 The amount related to the Plan-to-Plan reconciliation condition. This amount represents the amount "due-from" this Contract of Record to this Submitting Contract. This field is the sum of the LICS Amount and CPP Amount.
14 FILLER 123 – 512 X(390) 390 SPACES

&"Times New Roman,Regular"&8&F &A &"Times New Roman,Regular"&8Page &P of &N &"Times New Roman,Regular"&8&D &T

CTR (submitting contract trlr)

FIELD NO. FIELD NAME POSITION PICTURE LENGTH FIELD DESCRIPTION / VALUES
1 RECORD-ID 1 – 3 X(3) 3 "CTR"
2 SEQUENCE-NO 4 – 10 9(7) 7 Must start with 0000001
3 CONTRACT-NO 11 – 15 X(5) 5 Must match CHD
4 DRUG-COVERAGE-STATUS-CODE 16 – 16 X(1) 1 Note: Only PDEs with a value of 'C' (Covered Drug) will be included on this report.
5 BENEFICIARY-COUNT 17 – 27 X(11) 11 Count of Beneficiaries with utilization in the reporting period
6 FILLER 28 – 36 X(9) 9 SPACES
7 CURRENT-MONTH-GDCB-AMOUNT 37 – 50 S9(12)V99 14 Monthly change in Gross Drug Cost Below the Catastrophic Coverage Threshold
8 CURRENT-MONTH-GDCA-AMOUNT 51 – 64 S9(12)V99 14 Monthly change in Gross Drug Cost Above the Catastrophic Coverage Threshold
9 CURRENT-MONTH-TOTAL-GROSS-DRUG-COST 65 – 78 S9(12)V99 14 Sum of Net Ingredient Cost, Net Dispensing Fee, Net Sales Tax, and Net Vaccine Administration Fee.
10 CURRENT-MONTH-LICS-AMOUNT 79 – 92 S9(12)V99 14 Monthly change in Low Income Cost Sharing Amount
11 CURRENT-MONTH-CPP-AMOUNT 93 – 106 S9(12)V99 14 Monthly change in Covered Plan Paid Amount
12 DET-RECORD-TOTAL 107 – 114 9(8) 8 Total count of DET records
13 CURRENT-MONTH-P2P-AMOUNT-DUE-FROM-ALL-PLANS-OF-RECORD 115 – 128 S9(12)V99 14 The amount related to the Plan-to-Plan reconciliation condition. This amount represents the amount "due-from" all Contracts of Record to this Submitting Contract. This field is the sum of the LICS Amount and CPP Amount.
14 FILLER 129 – 512 X(384) 384 SPACES

&"Times New Roman,Regular"&8&F &A &"Times New Roman,Regular"&8Page &P of &N &"Times New Roman,Regular"&8&D &T

Order Solution Now

Categories: