...
...
Summary
Values contains the measured values and are essentially a triangulation of the reporting entity, the measure being reported and the date period. The key comprises comprises entity_values.measure_id, entity_values.entity_id, entity_values.date_start and and entity_values.date_end and must be unique. Footnotesfootnotes_values uses this composite key as a foreign key to enforce data integrity. Measure and Entity IDs must be created first before an entity_value can be added. Deletion from entity_values cascades to footnotes_values.
Fieldsfield type attributes notes example entity_values.measure_id int(11) NOT NULL from measures 12345 entity_values.entity_id bigint(20) unsigned NOT NULL from entities 123456 entity_values.date_start date NOT NULL YYYY-MM-DD 2008-01-01 entity_values.date_end date NOT NULL YYYY-MM-DD 2008-12-31 entity_values.source_id int(11) NOT NULL DEFAULT '0' from Value sources 12 entity_values.is_comparator tinyint(1) NOT NULL DEFAULT '0' Values can either be (0) reported or (1) calculated by aggregating multiple entities into comparator values, for example state average or average of the top quartile. If the value is a comparator value this field is set to "1"1 0 entity_values.is_private tinyint(1) NOT NULL DEFAULT '0' Values can either be (0) public domain or (1) private property. If the value is private this field is set to "1" 0 entity_values.value decimal(16,5) DEFAULT NULL This records the value of the measure for the entity in question. 0.5 entity_values.unit enum('percent','per 1000','per 100000','cases','ratio','days','hours','patients','dollars','na','minutes','people','square miles','years','households','per 100','per 10000','micrograms') NOT NULL DEFAULT 'na' The unit of measurement that the value represents percent entity_values.numerator decimal(16,5) DEFAULT NULL Used for fractions, percentages, ratios etc. Signifies the number to be divided by the denominator. 1 entity_values.denominator decimal(16,5) DEFAULT NULL 0Used for fractions, percentages, ratios etc. Signifies the number the numerator should be divided by. 2
entity_values.letter_grade varchar(3) DEFAULT NULL Letter grades to be used in report cards. B entity_values.gpa decimal(16,5) DEFAULT NULL This is used to store the mathematical value of the letter_grade. For instance, a B is equal to a gpa of 3.0 3.0 entity_values.sig_var_state enum('-1','0','1') DEFAULT NULL Subject to Minimum sample the value is tested for statistically significant variation from the state average.- -1 denotes a negative finding, always expressed as "worse than".
- 0 denotes no statistically significant variation was found.
- 1 denotes a positive finding, always expressed as "better than".
- 2 is reserved
For more information about this field and the methodology used please read Statistical significance.
-1 entity_values.sig_var_nation enum('-1','0','1') DEFAULT NULL Subject to Minimum sample the value is tested for statistically significant variation from the national average.- -1 denotes a negative finding, always expressed as "worse than".
- 0 denotes no statistically significant variation was found.
- 1 denotes a positive finding, always expressed as "better than".
- 2 is reserved
For more information about this field and the methodology used please read Statistical significance.
0 entity_values.sig_change enum('-1','0','1') DEFAULT NULL Subject to Minimum sample the value is tested for statistically significant improvement from the previous measurement point.- -1 denotes a negative finding, always expressed as "worse than".
- 0 denotes no statistical significant variation was found.
- 1 denotes a positive finding, always expressed as "better than".
- 2 is reserved
For more information about this field and the methodology used please read Statistical significance.
1 entity_values.std_dev_from_state decimal (16,5) DEFAULT NULL standard deviation from state value 1.96 entity_values.std_dev_from_nation decimal (16,5) DEFAULT NULL standard deviation from national value 2.21 entity_values.std_dev decimal (16,5) DEFAULT NULL standard deviation for the measure set 5.12 entity_values.lower_ci decimal (16,5) DEFAULT NULL lower confidence interval for statistical analysis 1.234 entity_values.upper_ci decimal(16,5) DEFAULT NULL upper confidence interval for statistical analysis
12.34 entity_values.percentile_nation smallint(3) DEFAULT NULL Subject to Minimum sample the value is percentile ranked against all eligible values (of the same entity type) in the national set. For more information about this field and the methodology used please read Percentile ranking. Not all values are ranked. 12 entity_values.percentile_state smallint(3) DEFAULT NULL Subject to Minimum sample the value is percentile ranked against all eligible values (of the same entity type) in the relevant state set. For more information about this field and the methodology used please read Percentile ranking. Not all values are ranked. 34 entity_values.rank_place_nation smallint(6) DEFAULT NULL Subject to Minimum sample the value is competitively ranked against all eligible values (of the same entity type) in the national set. For more information about this field and the methodology used please read Competitive ranking. 123 entity_values.rank_denominator_nation smallint(6) DEFAULT NULL If the value was competitively ranked, this field holds the denominator of the ranked set. 1234 entity_values.rank_place_state smallint(6) DEFAULT NULL Subject to Minimum sample the value is competitively ranked against all eligible values (of the same entity type) in the relevant state set. For more information about this field and the methodology used please read Competitive ranking. 12 entity_values.rank_denominator_state smallint(6) DEFAULT NULL If the value was competitively ranked intra-state, this field holds the denominator of the ranked (state) set. 123
| field | type | attributes | notes | example |
|---|---|---|---|---|
| entity_values.measure_id | int(11) | NOT NULL | from measures | 12345 |
| entity_values.entity_id | bigint(20) | unsigned NOT NULL | from entities | 123456 |
| entity_values.date_start | date | NOT NULL | YYYY-MM-DD | 2008-01-01 |
| entity_values.date_end | date | NOT NULL | YYYY-MM-DD | 2008-12-31 |
| entity_values.source_id | int(11) | NOT NULL DEFAULT '0' | from Value sources | 12 |
| entity_values.is_comparator | tinyint(1) | NOT NULL DEFAULT '0' | Values can either be (0) reported or (1) calculated by aggregating multiple entities into comparator values, for example state average or average of the top quartile. If the value is a comparator value this field is set to "1"1 | 0 |
| entity_values.is_private | tinyint(1) | NOT NULL DEFAULT '0' | Values can either be (0) public domain or (1) private property. If the value is private this field is set to "1" | 0 |
| entity_values.value | decimal(16,5) | DEFAULT NULL | This records the value of the measure for the entity in question. | 0.5 |
| entity_values.unit | enum('percent','per 1000','per 100000','cases','ratio','days','hours','patients','dollars','na','minutes','people','square miles','years','households','per 100','per 10000','micrograms') | NOT NULL DEFAULT 'na' | The unit of measurement that the value represents | percent |
| entity_values.numerator | decimal(16,5) | DEFAULT NULL | Used for fractions, percentages, ratios etc. Signifies the number to be divided by the denominator. | 1 |
| entity_values.denominator | decimal(16,5) | DEFAULT NULL | 0Used for fractions, percentages, ratios etc. Signifies the number the numerator should be divided by. | 2 |
| entity_values.letter_grade | varchar(3) | DEFAULT NULL | Letter grades to be used in report cards. | B |
| entity_values.gpa | decimal(16,5) | DEFAULT NULL | This is used to store the mathematical value of the letter_grade. For instance, a B is equal to a gpa of 3.0 | 3.0 |
| entity_values.sig_var_state | enum('-1','0','1') | DEFAULT NULL | Subject to Minimum sample the value is tested for statistically significant variation from the state average.
For more information about this field and the methodology used please read Statistical significance. | -1 |
| entity_values.sig_var_nation | enum('-1','0','1') | DEFAULT NULL | Subject to Minimum sample the value is tested for statistically significant variation from the national average.
For more information about this field and the methodology used please read Statistical significance. | 0 |
| entity_values.sig_change | enum('-1','0','1') | DEFAULT NULL | Subject to Minimum sample the value is tested for statistically significant improvement from the previous measurement point.
For more information about this field and the methodology used please read Statistical significance. | 1 |
| entity_values.std_dev_from_state | decimal (16,5) | DEFAULT NULL | standard deviation from state value | 1.96 |
| entity_values.std_dev_from_nation | decimal (16,5) | DEFAULT NULL | standard deviation from national value | 2.21 |
| entity_values.std_dev | decimal (16,5) | DEFAULT NULL | standard deviation for the measure set | 5.12 |
| entity_values.lower_ci | decimal (16,5) | DEFAULT NULL | lower confidence interval for statistical analysis | 1.234 |
| entity_values.upper_ci | decimal(16,5) | DEFAULT NULL | upper confidence interval for statistical analysis | 12.34 |
| entity_values.percentile_nation | smallint(3) | DEFAULT NULL | Subject to Minimum sample the value is percentile ranked against all eligible values (of the same entity type) in the national set. For more information about this field and the methodology used please read Percentile ranking. Not all values are ranked. | 12 |
| entity_values.percentile_state | smallint(3) | DEFAULT NULL | Subject to Minimum sample the value is percentile ranked against all eligible values (of the same entity type) in the relevant state set. For more information about this field and the methodology used please read Percentile ranking. Not all values are ranked. | 34 |
| entity_values.rank_place_nation | smallint(6) | DEFAULT NULL | Subject to Minimum sample the value is competitively ranked against all eligible values (of the same entity type) in the national set. For more information about this field and the methodology used please read Competitive ranking. | 123 |
| entity_values.rank_denominator_nation | smallint(6) | DEFAULT NULL | If the value was competitively ranked, this field holds the denominator of the ranked set. | 1234 |
| entity_values.rank_place_state | smallint(6) | DEFAULT NULL | Subject to Minimum sample the value is competitively ranked against all eligible values (of the same entity type) in the relevant state set. For more information about this field and the methodology used please read Competitive ranking. | 12 |
| entity_values.rank_denominator_state | smallint(6) | DEFAULT NULL | If the value was competitively ranked intra-state, this field holds the denominator of the ranked (state) set. | 123 |
...
entity_values.created timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' The timestamp of row INSERT 2015-01-01 12:00:00
| entity_values.created | timestamp | NOT NULL DEFAULT '0000-00-00 00:00:00' | The timestamp of row INSERT | 2015-01-01 12:00:00 |
...
entity_values.modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE The timestamp of latest row UPDATE 2015-01-01 12:00:00 entity_values.batch_id varchar(64) DEFAULT NULL Denotes the data run the row was a part of CMS20151234
PRIMARY KEY `entity_values_id` (`measure_id`,`entity_id`,`date_start`,`date_end`),
CONSTRAINT `entity_values_ibfk_1` FOREIGN KEY (`measure_id`) REFERENCES `measures` (`measure_id`) ON DELETE CASCADE,
CONSTRAINT `entity_values_ibfk_2` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE CASCADE
| entity_values.modified | timestamp | NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE | The timestamp of latest row UPDATE | 2015-01-01 12:00:00 |
| entity_values.batch_id | varchar(64) | DEFAULT NULL | Denotes the data run the row was a part of | CMS20151234 |
CONSTRAINT `entity_values_ibfk_1` FOREIGN KEY (`measure_id`) REFERENCES `measures` (`measure_id`) ON DELETE CASCADE,
CONSTRAINT `entity_values_ibfk_2` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE CASCADE