source: main/trunk/db/src/main/scripts/mysql/migrate/6_data-iv_dimensions.sql @ 25052

Last change on this file since 25052 was 25052, checked in by GarthBraithwaite_STG, 4 months ago

db - renamed script files to match updated steps. Minor tweaks.

File size: 5.9 KB
Line 
1/* Sets the IV dimension names based on Kim's temp tables.  This script also
2        sets the period dim and map dim names.  The series and cat dim names are set
3        to provide an initial starting point for downstream migration scripts as
4        most DSC and DSS to new DIMs will match.  These values also provide a quick
5        xref when doing a basic visual check but these values are not 100% and can
6        not be relied upon because some DSS and/or DSC are split apart into multiple
7        dimensions (like GeoCnty old => GeoCnty, State and US dimensions).
8*/
9
10select * from TMP_DSC23_DIM;
11select * from TMP_DSS23_DIM;
12select * from TMP_IPV_CONSTANT_DIM_NAME;
13
14
15/* Most of these will be correct but some are split into several new dims which
16        can not be captured.  These values are used as the first attempt at setting
17        the dim to view usage.  These values are also used for visual inspection
18        but should NOT be solely relied upon for other use.
19
20        *** IMPORATANT: These values are used later in this script for determinine
21        the view's period dim.  IVs can only have ONE PERIOD DIMENSION.  As such
22        can NOT split period dims like the year and target year = 2020.1 etc.
23*/
24update INDICATOR_VIEW set CATEGORY_DIMENSION_NAME = 
25        (select distinct(DIMENSION_NAME)
26         from TMP_DSC23_DIM
27         where DSC_NAME = DATASET_CATEGORY_NAME
28         group by DIMENSION_NAME
29         order by count(DIMENSION_NAME) desc
30     limit 1
31        )
32where CATEGORY_DIMENSION_NAME is null
33;
34update INDICATOR_VIEW set SERIES_DIMENSION_NAME = 
35        (select DIMENSION_NAME
36         from TMP_DSS23_DIM
37         where DSS_NAME = DATASET_SERIES_NAME
38         group by DIMENSION_NAME
39         order by count(DIMENSION_NAME) desc
40     limit 1
41        )
42where SERIES_DIMENSION_NAME is null
43;
44update INDICATOR_VIEW set CONSTANT_DIMENSION_NAME = 
45        (select DIMENSION_NAME
46         from TMP_IPV_CONSTANT_DIM_NAME
47         where INDICATOR_VIEW_NAME = NAME
48        )
49where CONSTANT_DIMENSION_NAME is null
50;
51
52
53/* clean up some invalid entries. */
54update INDICATOR_VIEW set SERIES_DIMENSION_NAME = null   
55where SERIES_DIMENSION_NAME = CATEGORY_DIMENSION_NAME
56;
57
58/* test */
59select * from INDICATOR_VIEW where (DATASET_CATEGORY_NAME is not null) and CATEGORY_DIMENSION_NAME is null;
60select * from INDICATOR_VIEW where (DATASET_SERIES_NAME   is not null) and (DATASET_SERIES_NAME != 'None') and SERIES_DIMENSION_NAME is null;
61
62
63
64/* set the map dimension name */
65update INDICATOR_VIEW
66set MAP_DIMENSION_NAME = 
67        (select DIMENSION_NAME
68         from TMP_DSC23_DIM
69         where DSC_NAME = DATASET_CATEGORY_NAME
70           and DIMENSION_NAME in (select NAME from DIMENSION where GEOGRAPHY_FLAG = 'x')
71         group by DIMENSION_NAME
72         order by count(DIMENSION_NAME) desc
73     limit 1
74        )
75where MAP_DIMENSION_NAME is null
76  and MAP_NAME != 'None' 
77;
78
79update INDICATOR_VIEW
80set MAP_DIMENSION_NAME = 
81        (select DIMENSION_NAME
82         from TMP_DSS23_DIM
83         where DSS_NAME = DATASET_SERIES_NAME
84           and DIMENSION_NAME in (select NAME from DIMENSION where GEOGRAPHY_FLAG = 'x')
85         group by DIMENSION_NAME
86         order by count(DIMENSION_NAME) desc
87     limit 1
88        )
89where MAP_DIMENSION_NAME is null
90  and MAP_NAME != 'None' 
91;
92
93/* test */
94select * from INDICATOR_VIEW where (MAP_DIMENSION_NAME is null) and (MAP_NAME is not null) and (MAP_NAME != 'None') and (MAP_NAME != 'DEFAULT');
95
96
97
98/* Set the default period dim name.  Note that the above code inserted the
99        highest count dim name as the DSC and DSS.  Periods do not split and must
100        be the same so the cat or series if a period will all be the same so no
101        need to join to the tmp table and check that the dim is a period. 
102
103        period dim name rules:
104        - if cat or series use that (NOTE cat and ser dims only set if 1 dim matches).
105        - if default/null then try the CONSTANT_DIM_NAME as a period and if so use
106        - if default/null then check the period_title to match a DV.title and use
107        - else default...
108*/
109update INDICATOR_VIEW iv set PERIOD_DIMENSION_NAME = 'YearDefault';
110
111/* Set period dim name based on the series and categories. */
112update INDICATOR_VIEW 
113set PERIOD_DIMENSION_NAME = CATEGORY_DIMENSION_NAME
114where CATEGORY_DIMENSION_NAME in (select NAME from DIMENSION where PERIOD_FLAG='x')
115;
116update INDICATOR_VIEW 
117set PERIOD_DIMENSION_NAME = SERIES_DIMENSION_NAME
118where SERIES_DIMENSION_NAME in (select NAME from DIMENSION where PERIOD_FLAG='x')
119  and PERIOD_DIMENSION_NAME = 'YearDefault'
120;
121update INDICATOR_VIEW 
122set PERIOD_DIMENSION_NAME = CONSTANT_DIMENSION_NAME
123where CONSTANT_DIMENSION_NAME in (select NAME from DIMENSION where PERIOD_FLAG='x')
124  and PERIOD_DIMENSION_NAME = 'YearDefault'
125;
126
127/* if default set to an associated constant dim from the temp mapping table */
128/* another way to do it without needing the temp constand dim name column in IV
129update INDICATOR_VIEW 
130set PERIOD_DIMENSION_NAME =
131        ifnull
132        (
133                (select CONSTANT_DIMENSION_NAME
134                 from TMP_IPV_CONSTANT_DIM_NAME
135                 where INCIATOR_VIEW_NAME = NAME
136                   and CONSTANT_DIMENSION_NAME in (select NAME from DIMENSION where PERIOD_FLAG='x')
137                )
138                , PERIOD_DIMENSION_NAME = 'YearDefault'
139        )
140where PERIOD_DIMENSION_NAME = 'YearDefault'
141;
142*/
143
144/* Update YearDefault PDN to a more specific value based on the IV.PERIOD_TITLE
145        matching a dim value/title.
146*/
147update INDICATOR_VIEW iv
148set PERIOD_DIMENSION_NAME = 
149        ifnull
150        (
151                (select dv.DIMENSION_NAME
152                 from DIMENSION_VALUE dv, DIMENSION d
153                 where ((dv.VALUE = iv.PERIOD_TITLE) or (dv.TITLE = iv.PERIOD_TITLE))
154                   and d.NAME = dv.DIMENSION_NAME
155                   and d.PERIOD_FLAG = 'x'
156                 limit 1
157                )
158                ,PERIOD_DIMENSION_NAME
159        )
160where PERIOD_DIMENSION_NAME = 'YearDefault'
161;
162
163
164
165/* spot test */
166select * from INDICATOR_VIEW where PERIOD_DIMENSION_NAME = 'YearDefault' order by NAME;
167
168select * from INDICATOR_VIEW where NAME
169in ('AgeSpecBirthRate.REYear1517', 'AlzDeath.RESex', 'EPHTNeonatalMort.Cause',
170'Formaldehyde.County', 'GeneralHealth.Year', 'TuberTherapy.Year', 'UnintInjDeath.CODRE'
171);
172
173/*------------------------------ END OF FILE ------------------------------*/
Note: See TracBrowser for help on using the repository browser.