source: main/trunk/db/src/main/scripts/mysql/data-dimensions_to_iv.sql @ 13080

Last change on this file since 13080 was 13080, checked in by Garth Braithwaite, 3 years ago

db - more script mods.

File size: 13.6 KB
Line 
1use ibisph;
2
3/* sets the iv dimensions and iv dimension values used.
4        MUST BE RAN after the IVV dimension name and value updates.
5*/
6
7delete from DIMENSION_TO_INDICATOR_VIEW;
8delete from DIMENSION_VALUE_TO_IND_VIEW;
9
10
11/* period dim names.  A view can only have 1 period dimension */
12insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
13select ivv.INDICATOR_VIEW_NAME, ivv.PERIOD_DIMENSION_NAME, 'period' 
14from INDICATOR_VIEW_VALUE ivv
15where not exists 
16        (select dtiv.INDICATOR_VIEW_NAME
17         from DIMENSION_TO_INDICATOR_VIEW dtiv
18         where dtiv.DIMENSION_USAGE = 'period' 
19           and dtiv.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
20        )
21group by ivv.INDICATOR_VIEW_NAME, ivv.PERIOD_DIMENSION_NAME
22;
23
24
25/* Test
261247 total
271101 are active
281039 have period
29
30so the 208 that do not have a period appear to be new and have no values or are
31inactive.
32*/
33select * 
34from INDICATOR_VIEW_VALUE ivv
35where not exists 
36        (select dtiv.INDICATOR_VIEW_NAME
37         from DIMENSION_TO_INDICATOR_VIEW dtiv
38         where dtiv.DIMENSION_USAGE = 'period' 
39           and dtiv.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
40        )
41group by ivv.INDICATOR_VIEW_NAME, ivv.PERIOD_DIMENSION_NAME
42;
43
44
45
46/* add in the IV series and cat dimension IF NOT NM%.  This was split out so the
47        NM check is not really not needed as the SERIES_DIM and CAT_DIM are already
48        mapped properly to the DS_SERIES and DS_CAT NAMEs.  This code also handles 
49        setting up the approp period series and cat dim relationships. 
50        *** Just need to handle NM% different as it is split. ***
51*/
52insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
53select iv.NAME, iv.SERIES_DIMENSION_NAME, 'series' 
54from INDICATOR_VIEW iv
55where iv.DATASET_SERIES_NAME not like 'NM%'
56  and not exists
57        (select dtiv.INDICATOR_VIEW_NAME
58         from DIMENSION_TO_INDICATOR_VIEW dtiv
59         where dtiv.DIMENSION_USAGE = 'series' 
60           and dtiv.INDICATOR_VIEW_NAME = iv.NAME
61           and dtiv.DIMENSION_NAME  = iv.SERIES_DIMENSION_NAME
62        )
63;
64
65insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
66select iv.NAME, iv.CATEGORY_DIMENSION_NAME, 'category' from INDICATOR_VIEW iv
67where iv.DATASET_CATEGORY_NAME not like 'NM%'
68  and not exists 
69        (select dtiv.INDICATOR_VIEW_NAME
70         from DIMENSION_TO_INDICATOR_VIEW dtiv
71         where dtiv.DIMENSION_USAGE = 'category' 
72           and dtiv.INDICATOR_VIEW_NAME = iv.NAME
73           and dtiv.DIMENSION_NAME  = iv.CATEGORY_DIMENSION_NAME
74        )
75;
76
77
78/* Add the split apart NM_% series that were split into US/State/TX. */
79insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
80select iv.NAME, 'State', 'series' 
81from INDICATOR_VIEW iv
82where iv.DATASET_SERIES_NAME like 'NM%'
83  and not exists
84        (select dtiv.INDICATOR_VIEW_NAME
85         from DIMENSION_TO_INDICATOR_VIEW dtiv
86         where dtiv.INDICATOR_VIEW_NAME = iv.NAME
87           and dtiv.DIMENSION_NAME  = 'State'
88           and dtiv.DIMENSION_USAGE = 'series'
89        )
90;
91insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
92select iv.NAME, 'US', 'series' 
93from INDICATOR_VIEW iv
94where iv.DATASET_SERIES_NAME like 'NM%'
95  and not exists
96        (select dtiv.INDICATOR_VIEW_NAME
97         from DIMENSION_TO_INDICATOR_VIEW dtiv
98         where dtiv.INDICATOR_VIEW_NAME = iv.NAME
99           and dtiv.DIMENSION_NAME  = 'US'
100           and dtiv.DIMENSION_USAGE = 'series'
101        )
102;
103
104
105
106/* add the State and US dimension for Geo's and age groups.  Do for category and
107        series.
108*/
109insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
110select iv.NAME, 'State', 'category' 
111from INDICATOR_VIEW iv
112where ((iv.CATEGORY_DIMENSION_NAME like 'Geo%')
113                or (iv.CATEGORY_DIMENSION_NAME in ('Tribe', 'AgeGrp5Survey', 'AgeGrp11', 
114                                'AgeGrp18', 'AgeGrp10MomAge', 'AgeGrp9Suicide', 'AgeGrp3Adult', 
115                                'AgeGrp7', 'AgeGrp7ChildPov', 'AgeGrp7Injury',  'RacEthDOH', 
116                                'AgeGrp8Injury', 'AgeGrp8MomAge'
117                        ))
118           )
119  and not exists
120        (select dtiv.INDICATOR_VIEW_NAME
121         from DIMENSION_TO_INDICATOR_VIEW dtiv
122         where dtiv.INDICATOR_VIEW_NAME = iv.NAME
123           and dtiv.DIMENSION_NAME  = 'State'
124           and dtiv.DIMENSION_USAGE = 'category'
125        )
126;
127insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
128select iv.NAME, 'US', 'category' 
129from INDICATOR_VIEW iv
130where ((iv.CATEGORY_DIMENSION_NAME like 'Geo%')
131                or (iv.CATEGORY_DIMENSION_NAME in ('Tribe', 'AgeGrp5Survey', 'AgeGrp11', 
132                                'AgeGrp18', 'AgeGrp10MomAge', 'AgeGrp9Suicide', 'AgeGrp3Adult', 
133                                'AgeGrp7', 'AgeGrp7ChildPov', 'AgeGrp7Injury',  'RacEthDOH', 
134                                'AgeGrp8Injury', 'AgeGrp8MomAge'
135                        ))
136           )
137  and not exists
138        (select dtiv.INDICATOR_VIEW_NAME
139         from DIMENSION_TO_INDICATOR_VIEW dtiv
140         where dtiv.INDICATOR_VIEW_NAME = iv.NAME
141           and dtiv.DIMENSION_NAME  = 'US'
142           and dtiv.DIMENSION_USAGE = 'category'
143        )
144;
145
146
147insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
148select iv.NAME, 'State', 'series' 
149from INDICATOR_VIEW iv
150where ((iv.SERIES_DIMENSION_NAME like 'Geo%')
151                or (iv.SERIES_DIMENSION_NAME in ('Tribe', 'AgeGrp5Survey', 'AgeGrp11', 
152                                'AgeGrp18', 'AgeGrp10MomAge', 'AgeGrp9Suicide', 'AgeGrp3Adult', 
153                                'AgeGrp7', 'AgeGrp7ChildPov', 'AgeGrp7Injury',  'RacEthDOH', 
154                                'AgeGrp8Injury', 'AgeGrp8MomAge'
155                        ))
156           )
157  and not exists
158        (select dtiv.INDICATOR_VIEW_NAME
159         from DIMENSION_TO_INDICATOR_VIEW dtiv
160         where dtiv.INDICATOR_VIEW_NAME = iv.NAME
161           and dtiv.DIMENSION_NAME  = 'State'
162           and dtiv.DIMENSION_USAGE = 'series'
163        )
164;
165insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
166select iv.NAME, 'US', 'series' 
167from INDICATOR_VIEW iv
168where ((iv.SERIES_DIMENSION_NAME like 'Geo%')
169                or (iv.SERIES_DIMENSION_NAME in ('Tribe', 'AgeGrp5Survey', 'AgeGrp11', 
170                                'AgeGrp18', 'AgeGrp10MomAge', 'AgeGrp9Suicide', 'AgeGrp3Adult', 
171                                'AgeGrp7', 'AgeGrp7ChildPov', 'AgeGrp7Injury',  'RacEthDOH', 
172                                'AgeGrp8Injury', 'AgeGrp8MomAge'
173                        ))
174           )
175  and not exists
176        (select dtiv.INDICATOR_VIEW_NAME
177         from DIMENSION_TO_INDICATOR_VIEW dtiv
178         where dtiv.INDICATOR_VIEW_NAME = iv.NAME
179           and dtiv.DIMENSION_NAME  = 'US'
180           and dtiv.DIMENSION_USAGE = 'series'
181        )
182;
183
184
185
186
187/* set the geocnty if NM_Cnty_US */
188insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
189select iv.NAME, 'GeoCnty', 'series' 
190from INDICATOR_VIEW iv
191where iv.DATASET_SERIES_NAME = 'NM_Cnty_US'
192  and not exists
193        (select dtiv.INDICATOR_VIEW_NAME
194         from DIMENSION_TO_INDICATOR_VIEW dtiv
195         where dtiv.INDICATOR_VIEW_NAME = iv.NAME
196           and dtiv.DIMENSION_NAME  = 'GeoCnty'
197           and dtiv.DIMENSION_USAGE = 'series'
198        )
199;
200insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
201select iv.NAME, 'GeoCnty', 'category' 
202from INDICATOR_VIEW iv
203where iv.DATASET_CATEGORY_NAME = 'NM_Cnty_US'
204  and not exists
205        (select dtiv.INDICATOR_VIEW_NAME
206         from DIMENSION_TO_INDICATOR_VIEW dtiv
207         where dtiv.INDICATOR_VIEW_NAME = iv.NAME
208           and dtiv.DIMENSION_NAME  = 'GeoCnty'
209           and dtiv.DIMENSION_USAGE = 'category'
210        )
211;
212
213
214
215/* checks */
216select NAME, SERIES_DIMENSION_NAME, CATEGORY_DIMENSION_NAME
217from INDICATOR_VIEW
218where ACTIVE_FLAG = 'x'
219  and NAME not in (select distinct INDICATOR_VIEW_NAME from DIMENSION_TO_INDICATOR_VIEW)
220;
221select NAME, SERIES_DIMENSION_NAME, CATEGORY_DIMENSION_NAME
222from INDICATOR_VIEW
223where SERIES_DIMENSION_NAME is null or CATEGORY_DIMENSION_NAME is null
224;
225
226
227select 'Missing Series', NAME, DATASET_CATEGORY_NAME, CATEGORY_DIMENSION_NAME, SERIES_DIMENSION_NAME, DATASET_SERIES_NAME
228from INDICATOR_VIEW iv
229where not exists
230        (select dtiv.INDICATOR_VIEW_NAME
231         from DIMENSION_TO_INDICATOR_VIEW dtiv
232         where dtiv.INDICATOR_VIEW_NAME = iv.NAME
233           and dtiv.DIMENSION_USAGE = 'series'
234        )
235;
236select 'Missing Cat', NAME, DATASET_CATEGORY_NAME, CATEGORY_DIMENSION_NAME, SERIES_DIMENSION_NAME, DATASET_SERIES_NAME
237from INDICATOR_VIEW iv
238where not exists
239        (select dtiv.INDICATOR_VIEW_NAME
240         from DIMENSION_TO_INDICATOR_VIEW dtiv
241         where dtiv.INDICATOR_VIEW_NAME = iv.NAME
242           and dtiv.DIMENSION_USAGE = 'category'
243        )
244;
245select 'Missing Period', NAME, DATASET_CATEGORY_NAME, CATEGORY_DIMENSION_NAME, SERIES_DIMENSION_NAME, DATASET_SERIES_NAME
246from INDICATOR_VIEW iv
247where not exists
248        (select dtiv.INDICATOR_VIEW_NAME
249         from DIMENSION_TO_INDICATOR_VIEW dtiv
250         where dtiv.INDICATOR_VIEW_NAME = iv.NAME
251           and dtiv.DIMENSION_USAGE = 'period'
252        )
253;
254
255
256
257/*
258======================== have an issue with a bunch not having period set - see
259inactive and new IPs that do not appear to have values at this point...
260*/
261
262
263
264/* catch the DIMENSION_TO_INDICATOR_VIEW that do NOT have a period dimension
265        set yet.  First try category dim name being a period and use that.  Next try
266        the series.  If still nothing then default to the YearsCombined.
267*/
268insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
269select NAME, CATEGORY_DIMENSION_NAME, 'period' 
270from INDICATOR_VIEW iv
271where not exists 
272        (select dtiv.INDICATOR_VIEW_NAME
273         from DIMENSION_TO_INDICATOR_VIEW dtiv
274         where dtiv.DIMENSION_USAGE = 'period' 
275           and dtiv.INDICATOR_VIEW_NAME = iv.NAME
276        )
277  and iv.CATEGORY_DIMENSION_NAME in (select NAME from DIMENSION where PERIOD_FLAG = 'x')
278;
279
280insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
281select NAME, SERIES_DIMENSION_NAME, 'period' 
282from INDICATOR_VIEW iv
283where not exists 
284        (select dtiv.INDICATOR_VIEW_NAME
285         from DIMENSION_TO_INDICATOR_VIEW dtiv
286         where dtiv.DIMENSION_USAGE = 'period' 
287           and dtiv.INDICATOR_VIEW_NAME = iv.NAME
288        )
289  and iv.SERIES_DIMENSION_NAME in (select NAME from DIMENSION where PERIOD_FLAG = 'x')
290;
291
292insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
293select NAME, 'YearsCombined', 'period' 
294from INDICATOR_VIEW iv
295where not exists 
296        (select dtiv.INDICATOR_VIEW_NAME
297         from DIMENSION_TO_INDICATOR_VIEW dtiv
298         where dtiv.DIMENSION_USAGE = 'period' 
299           and dtiv.INDICATOR_VIEW_NAME = iv.NAME
300        )
301;
302
303
304
305/* quick test to show which IVs have multiple period usages */
306select IP_NAME
307from (
308                select substring_index(INDICATOR_VIEW_NAME, '.', 1) as IP_NAME, DIMENSION_NAME, count(*) as GB_COUNT
309                from DIMENSION_TO_INDICATOR_VIEW
310                where DIMENSION_USAGE = 'period' 
311                group by IP_NAME, DIMENSION_NAME
312        having count(*) > 1 
313    ) xxx
314group by IP_NAME
315having count(IP_NAME) > 1
316order by 1
317;
318
319/* show basic list of IP's and count of the distinct period DIMs */
320select substring_index(INDICATOR_VIEW_NAME, '.', 1) as IP_NAME, DIMENSION_NAME, count(*) as GB_COUNT
321from DIMENSION_TO_INDICATOR_VIEW
322where DIMENSION_USAGE = 'period' 
323group by IP_NAME, DIMENSION_NAME
324having count(*) > 1 
325order by 1
326;
327
328select *
329from DIMENSION_TO_INDICATOR_VIEW
330where DIMENSION_USAGE = 'period' 
331  and substring_index(INDICATOR_VIEW_NAME, '.', 1) = 'DiabHosp'
332;
333
334
335
336/*
337==> Out of 266 IP's 45 IP's have more than 1 so 266-45 = 221 are good.
338==> Out of 1039 IPV records 314 conflict so 1039-314=725 are single.
339Almost ALL are like:
340AirQualOzone    YearAll
341AirQualOzone    YearsCombined
342*/
343
344
345
346
347
348/* Populate the indicator view dimension values based on the ivv records.  */
349insert into DIMENSION_VALUE_TO_IND_VIEW(INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_VALUE)
350select ivv.INDICATOR_VIEW_NAME, ivv.PERIOD_DIMENSION_NAME, ivv.PERIOD_DIMENSION_VALUE
351from INDICATOR_VIEW_VALUE ivv
352where not exists 
353        (select 1 
354         from DIMENSION_VALUE_TO_IND_VIEW dviv
355         where dviv.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
356           and dviv.DIMENSION_NAME      = ivv.PERIOD_DIMENSION_NAME 
357           and dviv.DIMENSION_VALUE     = ivv.PERIOD_DIMENSION_VALUE
358        )
359group by ivv.INDICATOR_VIEW_NAME, ivv.PERIOD_DIMENSION_NAME, ivv.PERIOD_DIMENSION_VALUE
360;
361
362insert into DIMENSION_VALUE_TO_IND_VIEW(INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_VALUE)
363select ivv.INDICATOR_VIEW_NAME, ivv.DIMENSION_1_NAME, ivv.DIMENSION_1_VALUE
364from INDICATOR_VIEW_VALUE ivv
365where not exists 
366        (select 1
367         from DIMENSION_VALUE_TO_IND_VIEW dviv
368         where dviv.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
369           and dviv.DIMENSION_NAME      = ivv.DIMENSION_1_NAME 
370           and dviv.DIMENSION_VALUE     = ivv.DIMENSION_1_VALUE
371        )
372group by ivv.INDICATOR_VIEW_NAME, ivv.DIMENSION_1_NAME, ivv.DIMENSION_1_VALUE
373;
374
375insert into DIMENSION_VALUE_TO_IND_VIEW(INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_VALUE)
376select ivv.INDICATOR_VIEW_NAME, ivv.DIMENSION_2_NAME, ivv.DIMENSION_2_VALUE
377from INDICATOR_VIEW_VALUE ivv
378where not exists 
379        (select 1 
380         from DIMENSION_VALUE_TO_IND_VIEW dviv
381         where dviv.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
382           and dviv.DIMENSION_NAME      = ivv.DIMENSION_2_NAME 
383           and dviv.DIMENSION_VALUE     = ivv.DIMENSION_2_VALUE
384        )
385group by ivv.INDICATOR_VIEW_NAME, ivv.DIMENSION_2_NAME, ivv.DIMENSION_2_VALUE
386;
387
388
389
390/* do a quick test to make sure that every view has dim values */
391select INDICATOR_VIEW_NAME, DIMENSION_1_NAME, DIMENSION_2_NAME, PERIOD_DIMENSION_NAME
392from INDICATOR_VIEW_VALUE
393where ACTIVE_FLAG = 'x'
394  and INDICATOR_VIEW_NAME not in (select distinct INDICATOR_VIEW_NAME from DIMENSION_VALUE_TO_IND_VIEW)
395;
396
397
398
399
400
401/* set the default sort order based on the dimension value sort order
402   - ???????????????? not sure needed ?????????????
403*/
404update DIMENSION_VALUE_TO_IND_VIEW dvtiv
405        set dvtiv.SORT_ORDER = 
406    (select dv.SORT_ORDER
407         from DIMENSION_VALUE dv
408         where dv.DIMENSION_NAME = dvtiv.DIMENSION_NAME
409           and dv.VALUE = dvtiv.DIMENSION_VALUE
410        )
411where dvtiv.SORT_ORDER is null
412;
413
414/*------------------------------ END OF FILE ------------------------------*/
Note: See TracBrowser for help on using the repository browser.