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

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

db, java, xslt - cleaned up the dims to iv sql. Based inserts on actual IVV records because had duplicates. Also based on active records which does not quite line up with production NM data but it appears to check out.

File size: 9.2 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;
8
9select count(*) from INDICATOR_VIEW;
10select count(*) from INDICATOR_VIEW where ACTIVE_FLAG = 'x';
11select count(*) from INDICATOR_VIEW where not exists (select 1 from INDICATOR_VIEW_VALUE where INDICATOR_VIEW_NAME = NAME);
12select count(*) from INDICATOR_VIEW where ACTIVE_FLAG = 'x' and not exists (select 1 from INDICATOR_VIEW_VALUE where INDICATOR_VIEW_NAME = NAME);
13select count(*) from INDICATOR_VIEW where ACTIVE_FLAG = 'x' and not exists (select 1 from INDICATOR_VIEW_VALUE where INDICATOR_VIEW_NAME = NAME and ACTIVE_FLAG = 'x');
14
15/*
161203 views
171153 active views
18126 views that don't have values  ==> 1203 - 126 = 1087
19126 active views that don't have any values
20126 active views that don't have active values ==> 1153-126 = 1027
21
221077 period records inserted. 126 don't have a period associated with it.
23cat records inserted.
24series records inserted.
25*/
26
27/* period dim names.  Had a bunch of other checks and inserts but this one gets
28        all - as long as the IVV records have been populated correctly.  See commits
29        prior to 4/6/2017 for other versions.
30*/
31insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
32select ivv.INDICATOR_VIEW_NAME, ivv.PERIOD_DIMENSION_NAME, 'period' 
33from INDICATOR_VIEW_VALUE ivv
34where not exists 
35        (select dtiv.INDICATOR_VIEW_NAME
36         from DIMENSION_TO_INDICATOR_VIEW dtiv
37         where dtiv.DIMENSION_USAGE = 'period' 
38           and dtiv.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
39        )
40group by ivv.INDICATOR_VIEW_NAME, ivv.PERIOD_DIMENSION_NAME
41;
42
43/* Test that all views have a period dimension.  They're all active but no IVVs... */
44select NAME, ACTIVE_FLAG
45from INDICATOR_VIEW iv
46where not exists 
47        (select 1
48         from DIMENSION_TO_INDICATOR_VIEW dtiv
49         where dtiv.DIMENSION_USAGE = 'period' 
50           and dtiv.INDICATOR_VIEW_NAME = iv.NAME
51        )
52;
53
54/* set a default to years combined for those IVs that don't have any IVV records to draw from. */
55insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
56select NAME, 'YearsCombined', 'period' 
57from INDICATOR_VIEW iv
58where not exists 
59        (select 1 
60         from DIMENSION_TO_INDICATOR_VIEW dtiv
61         where dtiv.DIMENSION_USAGE = 'period' 
62           and dtiv.INDICATOR_VIEW_NAME = iv.NAME
63        )
64;
65
66
67
68/* Do the same thing for categories.  1870 rows created. */
69insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
70select ivv.INDICATOR_VIEW_NAME, ivv.DIMENSION_1_NAME, 'category' 
71from INDICATOR_VIEW_VALUE ivv
72where not exists 
73        (select dtiv.INDICATOR_VIEW_NAME
74         from DIMENSION_TO_INDICATOR_VIEW dtiv
75         where dtiv.DIMENSION_USAGE = 'category' 
76           and dtiv.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
77        )
78group by ivv.INDICATOR_VIEW_NAME, ivv.DIMENSION_1_NAME
79;
80
81select NAME, ACTIVE_FLAG, CATEGORY_DIMENSION_NAME
82from INDICATOR_VIEW iv
83where not exists 
84        (select 1
85         from DIMENSION_TO_INDICATOR_VIEW dtiv
86         where dtiv.DIMENSION_USAGE = 'category' 
87           and dtiv.INDICATOR_VIEW_NAME = iv.NAME
88        )
89;
90
91insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
92select NAME, CATEGORY_DIMENSION_NAME, 'category' 
93from INDICATOR_VIEW iv
94where not exists 
95        (select 1
96         from DIMENSION_TO_INDICATOR_VIEW dtiv
97         where dtiv.DIMENSION_USAGE = 'category' 
98           and dtiv.INDICATOR_VIEW_NAME = iv.NAME
99        )
100;
101
102
103
104/* Do the same thing for series 1122 rows */
105insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
106select ivv.INDICATOR_VIEW_NAME, ivv.DIMENSION_2_NAME, 'series' 
107from INDICATOR_VIEW_VALUE ivv
108where not exists 
109        (select dtiv.INDICATOR_VIEW_NAME
110         from DIMENSION_TO_INDICATOR_VIEW dtiv
111         where dtiv.DIMENSION_USAGE = 'series' 
112           and dtiv.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
113        )
114group by ivv.INDICATOR_VIEW_NAME, ivv.DIMENSION_2_NAME
115;
116
117select NAME, ACTIVE_FLAG, SERIES_DIMENSION_NAME
118from INDICATOR_VIEW iv
119where not exists 
120        (select 1
121         from DIMENSION_TO_INDICATOR_VIEW dtiv
122         where dtiv.DIMENSION_USAGE = 'series' 
123           and dtiv.INDICATOR_VIEW_NAME = iv.NAME
124        )
125;
126
127insert into DIMENSION_TO_INDICATOR_VIEW (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
128select NAME, SERIES_DIMENSION_NAME, 'series' 
129from INDICATOR_VIEW iv
130where not exists 
131        (select 1
132         from DIMENSION_TO_INDICATOR_VIEW dtiv
133         where dtiv.DIMENSION_USAGE = 'series' 
134           and dtiv.INDICATOR_VIEW_NAME = iv.NAME
135        )
136;
137
138
139
140/* Do some cleanup on the DIMENSION_NAMEs that are NOT dimension names.  This can
141        get crazy but since all the values are for views that didn't have any values
142        simply set series to NONE.
143*/
144update DIMENSION_TO_INDICATOR_VIEW set DIMENSION_NAME = 'None'
145where  DIMENSION_USAGE = 'series'
146  and  DIMENSION_NAME not in (select NAME from DIMENSION)
147;
148
149/* quick test to make sure they're all covered */
150select * from DIMENSION_TO_INDICATOR_VIEW
151where DIMENSION_NAME not in (select NAME from DIMENSION)
152;
153
154/* Quick test to show which IVs have multiple period usages.  This is
155        technically possible and might be acceptable but for IVV this really should
156        never be the case.  This typically happens with orphaned IVV records.
157*/
158select INDICATOR_VIEW_NAME, DIMENSION_NAME, count(*) as MULT_PERIOD_COUNT
159from DIMENSION_TO_INDICATOR_VIEW
160where DIMENSION_USAGE = 'period' 
161group by INDICATOR_VIEW_NAME, DIMENSION_NAME
162having count(*) > 1 
163;
164
165
166
167
168
169/*
170==> Out of 266 IP's 45 IP's have more than 1 so 266-45 = 221 are good.
171==> Out of 1039 IPV records 314 conflict so 1039-314=725 are single.
172Almost ALL are like:
173AirQualOzone    YearAll
174AirQualOzone    YearsCombined
175*/
176
177delete from DIMENSION_VALUE_TO_IND_VIEW;
178
179
180/* Populate the indicator view dimension values based on the ivv records.  */
181insert into DIMENSION_VALUE_TO_IND_VIEW(INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_VALUE)
182select ivv.INDICATOR_VIEW_NAME, ivv.PERIOD_DIMENSION_NAME, ivv.PERIOD_DIMENSION_VALUE
183from INDICATOR_VIEW_VALUE ivv
184where ACTIVE_FLAG = 'x'
185  and not exists 
186        (select 1 
187         from DIMENSION_VALUE_TO_IND_VIEW dviv
188         where dviv.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
189           and dviv.DIMENSION_NAME      = ivv.PERIOD_DIMENSION_NAME 
190           and dviv.DIMENSION_VALUE     = ivv.PERIOD_DIMENSION_VALUE
191        )
192group by ivv.INDICATOR_VIEW_NAME, ivv.PERIOD_DIMENSION_NAME, ivv.PERIOD_DIMENSION_VALUE
193;
194
195insert into DIMENSION_VALUE_TO_IND_VIEW(INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_VALUE)
196select ivv.INDICATOR_VIEW_NAME, ivv.DIMENSION_1_NAME, ivv.DIMENSION_1_VALUE
197from INDICATOR_VIEW_VALUE ivv
198where ACTIVE_FLAG = 'x'
199  and not exists 
200        (select 1
201         from DIMENSION_VALUE_TO_IND_VIEW dviv
202         where dviv.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
203           and dviv.DIMENSION_NAME      = ivv.DIMENSION_1_NAME 
204           and dviv.DIMENSION_VALUE     = ivv.DIMENSION_1_VALUE
205        )
206group by ivv.INDICATOR_VIEW_NAME, ivv.DIMENSION_1_NAME, ivv.DIMENSION_1_VALUE
207;
208
209insert into DIMENSION_VALUE_TO_IND_VIEW(INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_VALUE)
210select ivv.INDICATOR_VIEW_NAME, ivv.DIMENSION_2_NAME, ivv.DIMENSION_2_VALUE
211from INDICATOR_VIEW_VALUE ivv
212where ACTIVE_FLAG = 'x'
213  and not exists 
214        (select 1 
215         from DIMENSION_VALUE_TO_IND_VIEW dviv
216         where dviv.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
217           and dviv.DIMENSION_NAME      = ivv.DIMENSION_2_NAME 
218           and dviv.DIMENSION_VALUE     = ivv.DIMENSION_2_VALUE
219        )
220group by ivv.INDICATOR_VIEW_NAME, ivv.DIMENSION_2_NAME, ivv.DIMENSION_2_VALUE
221;
222
223
224
225/* do some cleanup - get rid of any dim values that are not dim values.
226        Appears that most are series that have a State and/or US which was removed.
227        Example RacEthDOH has both state and US which are now split out.
228*/
229select * from DIMENSION_VALUE_TO_IND_VIEW
230where not exists
231        (select 1 
232         from DIMENSION_VALUE dv
233         where dv.ACTIVE_FLAG = 'x'
234           and dv.VALUE          = DIMENSION_VALUE_TO_IND_VIEW.DIMENSION_VALUE
235           and dv.DIMENSION_NAME = DIMENSION_VALUE_TO_IND_VIEW.DIMENSION_NAME
236        )
237order by INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_VALUE
238;
239
240delete from DIMENSION_VALUE_TO_IND_VIEW
241where not exists
242        (select 1 
243         from DIMENSION_VALUE dv
244         where dv.ACTIVE_FLAG = 'x'
245           and dv.VALUE          = DIMENSION_VALUE_TO_IND_VIEW.DIMENSION_VALUE
246           and dv.DIMENSION_NAME = DIMENSION_VALUE_TO_IND_VIEW.DIMENSION_NAME
247        )
248;
249
250
251/* do a quick test to make sure that every view has dim values */
252select INDICATOR_VIEW_NAME, DIMENSION_1_NAME, DIMENSION_2_NAME, PERIOD_DIMENSION_NAME
253from INDICATOR_VIEW_VALUE
254where ACTIVE_FLAG = 'x'
255  and INDICATOR_VIEW_NAME not in (select distinct INDICATOR_VIEW_NAME from DIMENSION_VALUE_TO_IND_VIEW)
256;
257
258
259
260
261
262/* set the default sort order based on the dimension value sort order
263   - ???????????????? not sure needed ?????????????
264*/
265update DIMENSION_VALUE_TO_IND_VIEW dvtiv
266        set dvtiv.SORT_ORDER = 
267    (select dv.SORT_ORDER
268         from DIMENSION_VALUE dv
269         where dv.DIMENSION_NAME = dvtiv.DIMENSION_NAME
270           and dv.VALUE = dvtiv.DIMENSION_VALUE
271        )
272where dvtiv.SORT_ORDER is null
273;
274
275/*------------------------------ END OF FILE ------------------------------*/
Note: See TracBrowser for help on using the repository browser.