Posted: April 24th, 2025

Data models

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. 

<h

2

>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

4 3

: 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

4

					FIELD NO.						FIELD NAME
	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

1

3

2

(

)

7

3

– 1

5

4

– 31

)

16

5

4

6

X(4)
4

7

2

.

8

8

9

6

10

X(5)
5

11

)

452

POSITION

PICTURE

LENGTH

FIELD DESCRIPTION / VALUES

RECORD-ID

1 – 3

X(3)

“CHD”

SEQUENCE-NO

4 –

10

9

7

Starts with 0000001

CONTRACT-NO

11

5

X(5)

Contract number of the Plan of Record

FILE-ID

1

6

X(

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.)

PROD-TEST-IND

32 – 35

X(4)

TEST or PROD

AS-OF-YEAR

36 – 39

Identifies “data reported through” year. Format is CCYY.

AS-OF-MONTH

40 – 41

X(2)

Identifies “data reported through” month. Valid values are 01 through

12

DDPS-SYSTEM-DATE

42 – 49

X(8)

‘CCYYMMDD’ = DDPS File creation date.

DDPS-SYSTEM-TIME

50 – 55

X(6)

‘HHMMSS’ = DDPS File creation time.

DDPS-REPORT-ID

56 – 60

DDPS Report identifier (’43COV’)

FILLER

61 – 512

X(

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

2
SEQUENCE-NO

7
Starts with 0000001

3
CONTRACT-NO

X(5)
5

4
FILLER

X(3)
3
SPACES

5
FILE-ID

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

X(4)
4
TEST or PROD

7
AS-OF-YEAR

X(4)
4
Identifies “data reported through” year. Format is CCYY.

8
AS-OF-MONTH

X(2)
2
Identifies “data reported through” month. Valid values are 01 through 12.

9
DDPS-SYSTEM-DATE

X(8)
8
‘CCYYMMDD’ = DDPS File creation date.

10
DDPS-SYSTEM-TIME

X(6)
6
‘HHMMSS’ = DDPS File creation time.

11
DDPS-REPORT-ID

X(5)
5
DDPS Report identifier (’43COV’)

12
FILLER

)

449
SPACES

“SHD”
			4 – 10				9(7)
		11 – 15	Submitting Contract number
	16 – 18
19 – 34
35 – 38
39 – 42
43 – 44
45 – 52
53 – 58
59 – 63
64 – 512	X(	449

&”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

3

1

4
Current CMS Medicare beneficiary identifier

)

20

5

20

6

7

S9(12)V99
14

8

S9(12)V99
14

9

S9(12)V99
14

10

S9(12)V99
14

11

X(5)
5

12

S9(12)V99
14

FILLER

)

372
SPACES

Must start with 0000001

DRUG-COVERAGE-STATUS-CODE

11 – 11

X(1)

Note: only PDEs with a value of ‘C’ (Covered Drug) will be included on this report.

12 – 31

X(

20

The most current CMS Medicare Beneficiary Identifier (MBI).

Last Submitted Medicare beneficiary identifier

32 – 51

X(20)

Beneficiary identifier (HICN or MBI) from the most recent accepted PDE in the DDPS database.

CURRENT-MONTH-GDCB-AMOUNT

52 – 65

S9(12)V99

14

Monthly change in Gross Drug Cost Below the Catastrophic Coverage Threshold

CURRENT-MONTH-GDCA-AMOUNT

66 – 79

Monthly change in Gross Drug Cost Above the Catastrophic Coverage Threshold

CURRENT-MONTH-TOTAL-GROSS-DRUG-COST

80 – 93

Sum of Net Ingredient Cost, Net Dispensing Fee,Net Sales Tax and Net Vaccine Administration Fee.

CURRENT-MONTH-LICS-AMOUNT

94 – 107

Monthly change in Low Income Cost Sharing Amount

CURRENT-MONTH-CPP-AMOUNT

108 – 121

Monthly change in Covered Plan Paid Amount

P2P-CONTRACT

122 – 126

The contract number of the Submitting Contract associated with the P2P reconciliation condition.

CURRENT-MONTH-P2P-AMOUNT

127 – 140

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

141 – 512

X(

372

&”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

2
SEQUENCE-NO
4 – 10
9(7)
7
Starts with 0000001

3
CONTRACT-NO
11 – 15
X(5)
5

4
FILLER
16 – 18
X(3)
3
SPACES

5
DRUG-COVERAGE-STATUS-CODE

X(1)
1
Note: only PDEs with a value of ‘C’ (Covered Drug) will be included on this report.

6

11

7
CURRENT-MONTH-GDCB-AMOUNT

S9(12)V99
14
Monthly change in Gross Drug Cost Below the Catastrophic Coverage Threshold

8
CURRENT-MONTH-GDCA-AMOUNT

S9(12)V99
14
Monthly change in Gross Drug Cost Above the Catastrophic Coverage Threshold

9
CURRENT-MONTH-TOTAL-GROSS-DRUG-COST

S9(12)V99
14

10
CURRENT-MONTH-LICS-AMOUNT

S9(12)V99
14
Monthly change in Low Income Cost Sharing Amount

11
CURRENT-MONTH-CPP-AMOUNT

S9(12)V99
14
Monthly change in Covered Plan Paid Amount

12

8

13

S9(12)V99
14

14
FILLER

)

390
SPACES

“STR”
Same as SHD
19 – 19
	BENEFICIARY-COUNT	20 – 30		9(11)		Count of beneficiaries with utilization in the reporting period.
31 – 44
45 – 58
59 – 72		Sum of Net Ingredient Cost, Net Dispensing Fee, Net Sales Tax and Net Vaccine Administration Fee.
73 – 86
87 – 100
	DET-RECORD-TOTAL	101 – 108		9(8)		Total count of DET records
	CURRENT-MONTH-P2P-AMOUNT-DUE-TO-ALL-SUBMITTING-CONTRACTS	109 – 122	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.
123 – 512	X(	390

&”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

2
SEQUENCE-NO
4 – 10
9(7)
7
Must start with 0000001

3
CONTRACT-NO
11 – 15
X(5)
5

4
DRUG-COVERAGE-STATUS-CODE

X(1)
1
Note: only PDEs with a value of ‘C’ (Covered Drug) will be included on this report.

5
BENEFICIARY-COUNT

9(11)
11
Count of beneficiaries with utilization in the reporting period.

6
FILLER

9
SPACES

7
CURRENT-MONTH-GDCB-AMOUNT

S9(12)V99
14
Monthly change in Gross Drug Cost Below the Catastrophic Coverage Threshold

8
CURRENT-MONTH-GDCA-AMOUNT

S9(12)V99
14
Monthly change in Gross Drug Cost Above the Catastrophic Coverage Threshold

9
CURRENT-MONTH-TOTAL-GROSS-DRUG-COST

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

S9(12)V99
14
Monthly change in Low Income Cost Sharing Amount

11
CURRENT-MONTH-CPP-AMOUNT

S9(12)V99
14
Monthly change in Covered Plan Paid Amount

12
DET-RECORD-TOTAL

9(8)
8
Total count of DET records

13
CURRENT-MONTH-P2P-AMOUNT-DUE-TO-ALL-SUBMITTING-CONTRACTS

S9(12)V99
14

14
FILLER

)

384
SPACES

“CTR”
Must match CHD
16 – 16
17 – 27
28 – 36	X(9)
37 – 50
51 – 64
65 – 78
79 – 92
93 – 106
107 – 114
115 – 128	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.
129 – 512	X(	384

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

<h

2

>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

4

1: 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

4

					FIELD NO.						FIELD NAME
	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

1

3

2

(

)

7

3

– 1

5

4

– 31

)

16

5

4

6

X(4)
4

7

2

.

8

8

9

6

10

X(5)
5

11

)

452

POSITION

PICTURE

LENGTH

FIELD DESCRIPTION / VALUES

RECORD-ID

1 –

3

X(3)

“CHD”

SEQUENCE-NO

4 –

10

9

7

Starts with 0000001

CONTRACT-NO

11

5

X(5)

Submitting Contract number

FILE-ID

1

6

X(

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.)

PROD-TEST-IND

32 – 35

X(4)

TEST or PROD

AS-OF-YEAR

36 – 39

Identifies “data reported through” year. Format is CCYY.

AS-OF-MONTH

40 – 41

X(2)

Identifies “data reported through” month. Valid values are 01 through

12

DDPS-SYSTEM-DATE

42 – 49

X(8)

‘CCYYMMDD’ = DDPS File creation date.

DDPS-SYSTEM-TIME

50 – 55

X(6)

‘HHMMSS’ = DDPS File creation time.

DDPS-REPORT-ID

56 – 60

DDPS Report identifier (’41COV’)

FILLER

61 – 512

X(

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

X(3)
3

2
SEQUENCE-NO

7
Starts with 0000001

3
CONTRACT-NO

X(5)
5

4
FILLER

X(3)
3
SPACES

5
FILE-ID

X(16)
16

6
PROD-TEST-IND

X(4)
4
TEST or PROD

7
AS-OF-YEAR

X(4)
4
Identifies “data reported through” year. Format is CCYY.

8
AS-OF-MONTH

X(2)
2
Identifies “data reported through” month. Valid values are 01 through 12.

9
DDPS-SYSTEM-DATE

X(8)
8
‘CCYYMMDD’ = DDPS File creation date.

10
DDPS-SYSTEM-TIME

X(6)
6
‘HHMMSS’ = DDPS File creation time.

11
DDPS-REPORT-ID

X(5)
5
DDPS Report identifier (’41COV’)

12
FILLER

)

449
SPACES

			1 – 3	“RHD”
			4 – 10				9(7)
		11 – 15	Contract Number of the Plan of Record
	16 – 18
19 – 34	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.)
35 – 38
39 – 42
43 – 44
45 – 52
53 – 58
59 – 63
64 – 512	X(	449

&”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

3

1

4
Current CMS Medicare beneficiary identifier

)

20

5

20

6

X(20)
20

7

8

S9(12)V99
14

9

S9(12)V99
14

10

S9(12)V99
14

11

S9(12)V99
14

12

S9(12)V99
14

13
FILLER

)

357
SPACES

Must start with 0000001

DRUG-COVERAGE-STATUS-CODE

11 – 11

X(1)

Note: Only PDEs with a value of ‘C’ (Covered Drug) will be included on this report.

12 – 31

X(

20

The most current CMS Medicare Beneficiary Identifier (MBI).

Last Submitted Medicare beneficiary identifier

32 – 51

X(20)

Beneficiary identifier (HICN or MBI) from the most recent accepted PDE in the DDPS database.

LAST-SUBMITTED-CARDHOLDER-ID

52 – 71

Plan identification of the enrollee, as reported on the most recent PDE for the benefit year.

CURRENT-MONTH-GDCB-AMOUNT

72 – 85

S9(12)V99

14

Monthly change in Gross Drug Cost Below the Catastrophic Coverage Threshold

CURRENT-MONTH-GDCA-AMOUNT

86 – 99

Monthly change in Gross Drug Cost Above the Catastrophic Coverage Threshold

CURRENT-MONTH-TOTAL-GROSS-DRUG-COST

100 – 1

13

Sum of Net Ingredient Cost, Net Dispensing Fee, Net Sales Tax, and Net Vaccine Administration Fee.

CURRENT-MONTH-LICS-AMOUNT

114 – 127

Monthly change in Low Income Cost Sharing Amount

CURRENT-MONTH-CPP-AMOUNT

128 – 141

Monthly change in Covered Plan Paid Amount

CURRENT-MONTH-P2P-AMOUNT

142 – 155

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.

156 – 512

X(

357

&”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

2
SEQUENCE-NO
4 – 10
9(7)
7
Starts with 0000001

3
CONTRACT-NO
11 – 15
X(5)
5

4
FILLER
16 – 18
X(3)
3
SPACES

5
DRUG-COVERAGE-STATUS-CODE

X(1)
1
Note: Only PDEs with a value of ‘C’ (Covered Drug) will be included on this report.

6

11

7
CURRENT-MONTH-GDCB-AMOUNT

S9(12)V99
14
Monthly change in Gross Drug Cost Below the Catastrophic Coverage Threshold

8
CURRENT-MONTH-GDCA-AMOUNT

S9(12)V99
14
Monthly change in Gross Drug Cost Above the Catastrophic Coverage Threshold

9
CURRENT-MONTH-TOTAL-GROSS-DRUG-COST

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

S9(12)V99
14
Monthly change in Low Income Cost Sharing Amount

11
CURRENT-MONTH-CPP-AMOUNT

S9(12)V99
14
Monthly change in Covered Plan Paid Amount

12

8

13

S9(12)V99
14

14
FILLER

)

390
SPACES

“RTR”
Same as RHD
19 – 19
	BENEFICIARY-COUNT	20 – 30	9(11)		Count of Beneficiaries with utilization in the reporting period
31 – 44
45 – 58
59 – 72
73 – 86
87 – 100
	DET-RECORD-TOTAL	101 – 108		9(8)		Total count of DET records
	CURRENT-MONTH-P2P-AMOUNT-DUE-FROM-ALL-PLANS-OF-RECORD	109 – 122	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.
123 – 512	X(	390

&”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

2
SEQUENCE-NO
4 – 10
9(7)
7
Must start with 0000001

3
CONTRACT-NO
11 – 15
X(5)
5

4
DRUG-COVERAGE-STATUS-CODE

X(1)
1
Note: Only PDEs with a value of ‘C’ (Covered Drug) will be included on this report.

5
BENEFICIARY-COUNT

11
Count of Beneficiaries with utilization in the reporting period

6
FILLER

9
SPACES

7
CURRENT-MONTH-GDCB-AMOUNT

S9(12)V99
14
Monthly change in Gross Drug Cost Below the Catastrophic Coverage Threshold

8
CURRENT-MONTH-GDCA-AMOUNT

S9(12)V99
14
Monthly change in Gross Drug Cost Above the Catastrophic Coverage Threshold

9
CURRENT-MONTH-TOTAL-GROSS-DRUG-COST

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

S9(12)V99
14
Monthly change in Low Income Cost Sharing Amount

11
CURRENT-MONTH-CPP-AMOUNT

S9(12)V99
14
Monthly change in Covered Plan Paid Amount

12
DET-RECORD-TOTAL

9(8)
8
Total count of DET records

13
CURRENT-MONTH-P2P-AMOUNT-DUE-FROM-ALL-PLANS-OF-RECORD

S9(12)V99
14

14
FILLER

)

384
SPACES

“CTR”
Must match CHD
16 – 16
17 – 27	X(11)
28 – 36	X(9)
37 – 50
51 – 64
65 – 78
79 – 92
93 – 106
107 – 114
115 – 128	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.
129 – 512	X(	384

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