source: main/trunk/db/src/main/scripts/mysql/migrate/10_data-dataset.sql @ 25095

/*
        The point of this table is to create a mapping between the IV/IVV tables
        and the new DATASET table.  This sql script builds a temp table so that the 
        unique combinations of IPV names, dimension names, data sources, measure 
        name(s) are captured.  This requires a cleaned up and standardized DS to IV 
        sort order (see below).  The DS name is then built and updated where the 
        actual DS record can then reference this table based on that record's dims
        to determine the approp dataset to be put into.
        
        This table once created and initially populated could be exported to Excel 
        where Kim/Lois can then make decisions for data clean up.  It could then be 
        imported and the balance of the scripts ran which are based off of this TMP 
        table.

        DATASET DATASOURCE NOTES: 
        Datasets are based on a singular numerator datasource.  Numerator data 
        sources are determined by the DATA_SOURCE_TO_INDICATOR_VIEW's SORT_ORDER.
        Lois standardized/updated version 2.3 to have all SO=3's to be the US 
        dataset.  SO=1 is the standard numerator for non US data.  SO=2 is the denom
        source for the SO=1 numerator.  As such here's the rules:
        1) GeoCountry & SO3 should all be inserted straight up.
        2) Insert the SO3 special split usages like:
                'ACA_Pre_Post', 'BRFSS_Wgt', 'SexStateUS', 'Girls15-17_18-19', 'DiagPriAny'.
                This is needed so that an actual dataset with the approp measure name
                and data source name will be created.  The data-dataset_record script 
                will handle/create the approp split records.
        3) All SO 1 should be processed straight up.  

        NOTES: The special SO3 splits is semi handled.  For example BRFSS_Wgt is a 
        special split usage.  The IP has a SO3 with CDC_BRFSS and a SO1 with a data 
        source of BRFSS.  So the script that populates will only need to test for
        specific dimenison values of say 1&2 or 3&4 and insert into the approp ds.

        See previous 3/14/2018 commits for other notes and approaches.  
*/

/* quick test to see what DS to IV SORT_ORDER=3 records are missing */
select ivv.INDICATOR_VIEW_NAME
from INDICATOR_VIEW_VALUE ivv
where ivv.VALUE is not null
  and ivv.VALUE not like '*%'
  and ivv.VALUE > ''
  and ivv.ACTIVE_FLAG = 'x'
  and ((DIMENSION_1_NAME = 'GeoCountry') or (DIMENSION_2_NAME = 'GeoCountry'))
  and ivv.INDICATOR_VIEW_NAME not in 
        (
                select dstiv.INDICATOR_VIEW_NAME  
                from DATA_SOURCE_TO_INDICATOR_VIEW dstiv
                where dstiv.SORT_ORDER = 3
        )
group by ivv.INDICATOR_VIEW_NAME
;


drop table if exists TMP_INDICATOR_VIEW_DATASET;

create table TMP_INDICATOR_VIEW_DATASET(
  INDICATOR_NAME                  varchar  (100),
  INDICATOR_VIEW_NAME             varchar  (200),
  INDICATOR_VIEW_ACTIVE_FLAG      varchar    (1),
  MEASURE_NAME                    varchar  (100),
  NUMERATOR_DATA_SOURCE_NAME      varchar  (100),
  DATA_SOURCE_SORT_ORDER          int             default null,
  PERIOD_DIMENSION_NAME           varchar  (100),
  DIMENSION_1_NAME                varchar  (100),
  DIMENSION_2_NAME                varchar  (100),
  DATASET_NAME                    varchar  (200)
);


delete from TMP_INDICATOR_VIEW_DATASET;


/* do some basic tests to make sure all IPVs have a data source */
select count(*) from INDICATOR_VIEW;
select count(*) from DATA_SOURCE_TO_INDICATOR_VIEW;

select NAME, ACTIVE_FLAG 
from INDICATOR_VIEW
where NAME not in (select INDICATOR_VIEW_NAME from DATA_SOURCE_TO_INDICATOR_VIEW)
;

select iv.NAME, iv.ACTIVE_FLAG, count(ivv.INDICATOR_VIEW_NAME)
from INDICATOR_VIEW iv, INDICATOR_VIEW_VALUE ivv
where iv.NAME not in (select INDICATOR_VIEW_NAME from DATA_SOURCE_TO_INDICATOR_VIEW)
  and ivv.INDICATOR_VIEW_NAME = iv.NAME 
;

select * from DATA_SOURCE_TO_INDICATOR_VIEW 
where INDICATOR_VIEW_NAME not in 
        (select NAME from INDICATOR_VIEW)
;



/* Insert all the SO1. */
insert into TMP_INDICATOR_VIEW_DATASET (
                INDICATOR_NAME, INDICATOR_VIEW_NAME, 
                NUMERATOR_DATA_SOURCE_NAME, DATA_SOURCE_SORT_ORDER,
                PERIOD_DIMENSION_NAME, DIMENSION_1_NAME, DIMENSION_2_NAME
        )
select  substring_index(ivv.INDICATOR_VIEW_NAME, '.', 1), ivv.INDICATOR_VIEW_NAME, 
        dstiv.DATA_SOURCE_NAME, dstiv.SORT_ORDER,
        ivv.PERIOD_DIMENSION_NAME, ivv.DIMENSION_1_NAME, ifnull(ivv.DIMENSION_2_NAME, 'NA')
from INDICATOR_VIEW_VALUE ivv, DATA_SOURCE_TO_INDICATOR_VIEW dstiv
where dstiv.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
  and dstiv.SORT_ORDER = 1
group by 2, 3, 4, 5, 6, 7
;

/* quick test to see what DS_TO_IV is not used */
select distinct INDICATOR_VIEW_NAME
from TMP_INDICATOR_VIEW_DATASET 
where INDICATOR_VIEW_NAME not in 
        (select INDICATOR_VIEW_NAME 
         from DATA_SOURCE_TO_INDICATOR_VIEW
        )
;



/* Insert the US SO3 entries */
insert into TMP_INDICATOR_VIEW_DATASET (
                INDICATOR_NAME, INDICATOR_VIEW_NAME, 
                NUMERATOR_DATA_SOURCE_NAME, DATA_SOURCE_SORT_ORDER,
                PERIOD_DIMENSION_NAME, DIMENSION_1_NAME, DIMENSION_2_NAME
        )
select  substring_index(ivv.INDICATOR_VIEW_NAME, '.', 1), ivv.INDICATOR_VIEW_NAME, 
        dstiv.DATA_SOURCE_NAME, dstiv.SORT_ORDER,
        ivv.PERIOD_DIMENSION_NAME, ivv.DIMENSION_1_NAME, ifnull(ivv.DIMENSION_2_NAME, 'NA')
from INDICATOR_VIEW_VALUE ivv, DATA_SOURCE_TO_INDICATOR_VIEW dstiv
where dstiv.INDICATOR_VIEW_NAME = ivv.INDICATOR_VIEW_NAME
  and dstiv.SORT_ORDER = 3
group by 2, 3, 4, 5, 6, 7
;


/*  The SO3 insert is very specific to only include those records that have a 
        US related dimension.  Inserting the blanket SO1 results in some dup US type
        entries as the insert keys off of the view name, dataset, SO, and DIM 1 & 2.  
        Thus need to delete US SO1 if there's a US SO3 record.  Also need to delete
        any NON US SO3's from the SO1 *IF* the data source name matches.  Otherwise
        it needs to stay as those non US SO3's are the split use entries.
        
        Note that the "select" and "delete" counts should match.
*/
select *
from TMP_INDICATOR_VIEW_DATASET 
where DATA_SOURCE_SORT_ORDER = 1
  and INDICATOR_VIEW_NAME in 
        (select INDICATOR_VIEW_NAME 
         from TMP_INDICATOR_VIEW_DATASET 
         where DATA_SOURCE_SORT_ORDER = 3
        )
order by INDICATOR_VIEW_NAME, NUMERATOR_DATA_SOURCE_NAME, DIMENSION_1_NAME,  DIMENSION_2_NAME
;

delete 
from TMP_INDICATOR_VIEW_DATASET 
where DATA_SOURCE_SORT_ORDER = 1
  and INDICATOR_VIEW_NAME in 
        (select t1.INDICATOR_VIEW_NAME from 
                (select INDICATOR_VIEW_NAME 
                 from TMP_INDICATOR_VIEW_DATASET 
                 where DATA_SOURCE_SORT_ORDER = 3
                ) t1
        )
;



/* Now process for the special SO3 uses - shouldn't be any - if some delete */
select *
from TMP_INDICATOR_VIEW_DATASET tivd
where DATA_SOURCE_SORT_ORDER = 1
  and INDICATOR_VIEW_NAME in 
        (select tivd2.INDICATOR_VIEW_NAME 
         from TMP_INDICATOR_VIEW_DATASET tivd2
         where tivd2.DATA_SOURCE_SORT_ORDER = 3
           and tivd2.NUMERATOR_DATA_SOURCE_NAME = tivd.NUMERATOR_DATA_SOURCE_NAME
        )
order by INDICATOR_VIEW_NAME, NUMERATOR_DATA_SOURCE_NAME, DIMENSION_1_NAME,  DIMENSION_2_NAME
;



/* Finally, should not have any of these but test/remove all SO3's that only have 1 US SO1 */
select *
from TMP_INDICATOR_VIEW_DATASET tivd
where tivd.DATA_SOURCE_SORT_ORDER = 3
  and 1 =  
        (select count(*) 
         from TMP_INDICATOR_VIEW_DATASET t
         where t.DATA_SOURCE_SORT_ORDER = 1
           and t.INDICATOR_VIEW_NAME = tivd.INDICATOR_VIEW_NAME
        )
;

/* code that can be modified if the above exists...
delete
from TMP_INDICATOR_VIEW_DATASET 
where DATA_SOURCE_SORT_ORDER = 3
  and INDICATOR_VIEW_NAME =  
        (select t.INDICATOR_VIEW_NAME from 
                (select INDICATOR_VIEW_NAME
                 from TMP_INDICATOR_VIEW_DATASET 
                 where DATA_SOURCE_SORT_ORDER = 1
                   and ((DIMENSION_1_NAME = 'GeoCountry') or (DIMENSION_2_NAME = 'GeoCountry'))
                 group by INDICATOR_VIEW_NAME
                 having count(INDICATOR_VIEW_NAME) > 1
                ) t
        )
;
*/


/* set the measaure name, active flag, and update the IP name - just in case */
update TMP_INDICATOR_VIEW_DATASET tmp
        inner join 
                (select NAME, INDICATOR_NAME, MEASURE_NAME, ACTIVE_FLAG from INDICATOR_VIEW) iv 
                on iv.NAME = tmp.INDICATOR_VIEW_NAME
set tmp.INDICATOR_VIEW_ACTIVE_FLAG = iv.ACTIVE_FLAG,
        tmp.MEASURE_NAME               = iv.MEASURE_NAME,
        tmp.INDICATOR_NAME             = iv.INDICATOR_NAME
;

/* set the dataset name */
update TMP_INDICATOR_VIEW_DATASET 
set DATASET_NAME = concat(INDICATOR_NAME, '.', MEASURE_NAME, '.', NUMERATOR_DATA_SOURCE_NAME)
where DATASET_NAME is null
;

select count(*) from TMP_INDICATOR_VIEW_DATASET;
select * from TMP_INDICATOR_VIEW_DATASET where DATASET_NAME is null;




delete from DATASET;

insert into DATASET (
        NAME, INDICATOR_NAME, 
        TITLE,
        NUMERATOR, DENOMINATOR, DATA_NOTE,  
        MEASURE_NAME, SORT_ORDER, ACTIVE_FLAG, MODIFIED_DATE)
select 
        tivd.DATASET_NAME, tivd.INDICATOR_NAME, 
        concat('TITLE: ', tivd.MEASURE_NAME, ', ', tivd.NUMERATOR_DATA_SOURCE_NAME),
        i.NUMERATOR, i.DENOMINATOR, substring(i.DATA_NOTE, 1, 1000), 
        tivd.MEASURE_NAME, iv.SORT_ORDER, 'x', iv.MODIFIED_DATE
from INDICATOR i, INDICATOR_VIEW iv, TMP_INDICATOR_VIEW_DATASET tivd
where i.NAME  = tivd.INDICATOR_NAME
  and iv.NAME = tivd.INDICATOR_VIEW_NAME
group by tivd.DATASET_NAME
;

select INDICATOR_NAME, DATASET_NAME, count(DATASET_NAME)
from TMP_INDICATOR_VIEW_DATASET 
group by DATASET_NAME, INDICATOR_NAME
;

/* update any null dataset data notes via the IPV */
update DATASET ds set DATA_NOTE = 
        (select iv.DATA_NOTE 
         from INDICATOR_VIEW iv, TMP_INDICATOR_VIEW_DATASET tivd
         where iv.NAME = tivd.INDICATOR_VIEW_NAME
           and tivd.DATASET_NAME = ds.NAME
           and iv.DATA_NOTE is not null
         limit 1
        )
where ds.DATA_NOTE is null;

/* update the title and desc via the DS and MEASURE */
update DATASET d set TITLE = 
        ifnull( substr(
                (select concat(ds.NAME, ', ', m.TITLE)
                 from INDICATOR_VIEW iv, TMP_INDICATOR_VIEW_DATASET tivd, MEASURE m, DATA_SOURCE ds
                 where iv.NAME = tivd.INDICATOR_VIEW_NAME
                   and d.NAME  = tivd.DATASET_NAME 
                   and ds.NAME = tivd.NUMERATOR_DATA_SOURCE_NAME
                   and m.NAME  = tivd.MEASURE_NAME
                 limit 1
                ), 1, 100)
        , '')
;
update DATASET d set DESCRIPTION = 
        (select concat('Dataset of ', m.TITLE, ' for ', ds.TITLE)
         from INDICATOR_VIEW iv, TMP_INDICATOR_VIEW_DATASET tivd, MEASURE m, DATA_SOURCE ds
         where iv.NAME = tivd.INDICATOR_VIEW_NAME
           and d.NAME  = tivd.DATASET_NAME 
           and ds.NAME = tivd.NUMERATOR_DATA_SOURCE_NAME
           and m.NAME  = tivd.MEASURE_NAME
         limit 1
        )
;




/* Insert the DATA_SOURCE_TO_DATASET records.
        Indicator view to data source sort order which specifies 1=numerator vs 2=denomn 3=US 
        1 only:  1 = numer, 1 = denom 
        1, 2:    1 = numer, 2 = denom
        1, 2, 3: 1 = NM numer, 2 = NM denom  ,,  3 = US numer, US denom
        1, 3:    1 = NM numer, 1 = NM denom  ,,  3 = US numer, US denom
        3 only:  3 = US numer, 3 = US denom 
*/
/* The numerator data source in the temp table is ALWAYS the numerator DS. */
delete from DATA_SOURCE_TO_DATASET;

insert into DATA_SOURCE_TO_DATASET (DATASET_NAME, DATA_SOURCE_NAME, DATA_SOURCE_USAGE)
select DATASET_NAME, NUMERATOR_DATA_SOURCE_NAME, 'numerator'
from TMP_INDICATOR_VIEW_DATASET 
group by DATASET_NAME, NUMERATOR_DATA_SOURCE_NAME
;

/* As a general catch all set the denom datasource to the numerator DS.  This 
        is accurate for both only 1, or only 1&3 e.g. no SO2.
*/
insert into DATA_SOURCE_TO_DATASET (DATASET_NAME, DATA_SOURCE_NAME, DATA_SOURCE_USAGE)
select DATASET_NAME, NUMERATOR_DATA_SOURCE_NAME, 'denominator'
from TMP_INDICATOR_VIEW_DATASET 
group by DATASET_NAME, NUMERATOR_DATA_SOURCE_NAME
;

/* Need to update the denominator dataset datasource usage when there is a 
        specific denom DS (sort order 2).   Note the use of ifnull to set it to
        itself if no SO2 record exists (e.g. don't set it to null).

        NOTES: the ds to IV has around 500 SO2's.  However, many of those IV's are
        consolidated into fewer datasets.  So update of dataset to SO2 ==> 117
        which is inline with the ratio of IVs to DSs.

        The select returns approx 135 rows of SO2 for the assoc IPs.  The update
        results in approx 150 rows updated.
*/
select *
from DATA_SOURCE_TO_INDICATOR_VIEW 
where SORT_ORDER = 2
group by substring_index(INDICATOR_VIEW_NAME, '.', 1), DATA_SOURCE_NAME
;

update DATA_SOURCE_TO_DATASET dstd
set DATA_SOURCE_NAME = ifnull(
        (select dstiv.DATA_SOURCE_NAME
         from DATA_SOURCE_TO_INDICATOR_VIEW dstiv, TMP_INDICATOR_VIEW_DATASET tivd
         where tivd.DATASET_NAME = dstd.DATASET_NAME
           and tivd.DATA_SOURCE_SORT_ORDER = 1
           and dstiv.INDICATOR_VIEW_NAME = tivd.INDICATOR_VIEW_NAME
           and dstiv.SORT_ORDER = 2
         group by tivd.DATASET_NAME, dstiv.DATA_SOURCE_NAME
         limit 1
        ), DATA_SOURCE_NAME)  /* use 'garth' to test which ones do NOT have a SO of 2 of the 322 exactly 1/2 do not.... */
where DATA_SOURCE_USAGE = 'denominator'
;




/* DATASET to IV */
delete from DATASET_TO_INDICATOR_VIEW;

insert into DATASET_TO_INDICATOR_VIEW(DATASET_NAME, INDICATOR_VIEW_NAME)
select DATASET_NAME, INDICATOR_VIEW_NAME
from TMP_INDICATOR_VIEW_DATASET 
where DATASET_NAME        is not null
  and INDICATOR_VIEW_NAME is not null
group by INDICATOR_VIEW_NAME, DATASET_NAME 
;



/* create the DIMENSION to DATASET records based on the dataset's records.  Prior
        to 10/5/16 these records were based on the view.
        
        5/5/2017, changed logic to use the tmp table and not join on the IVV.
*/
delete from DIMENSION_TO_DATASET;

insert into DIMENSION_TO_DATASET(DATASET_NAME, DIMENSION_NAME, DIMENSION_USAGE)
select tivd.DATASET_NAME, tivd.PERIOD_DIMENSION_NAME, 'period' 
from TMP_INDICATOR_VIEW_DATASET tivd
group by 1, 2
;

insert into DIMENSION_TO_DATASET(DATASET_NAME, DIMENSION_NAME, DIMENSION_USAGE)
select tivd.DATASET_NAME, tivd.DIMENSION_1_NAME, 'dimension1' 
from TMP_INDICATOR_VIEW_DATASET tivd 
where tivd.DIMENSION_1_NAME is not null
group by 1, 2
;

insert into DIMENSION_TO_DATASET(DATASET_NAME, DIMENSION_NAME, DIMENSION_USAGE)
select tivd.DATASET_NAME, tivd.DIMENSION_2_NAME, 'dimension2' 
from TMP_INDICATOR_VIEW_DATASET tivd 
where tivd.DIMENSION_2_NAME is not null
  and not exists 
         (select 1
           from DIMENSION_TO_DATASET dtd2
           where dtd2.DIMENSION_USAGE = 'dimension1'
                 and dtd2.DIMENSION_NAME  = tivd.DIMENSION_2_NAME
                 and dtd2.DATASET_NAME    = tivd.DATASET_NAME
         )
group by 1, 2
;



/* check - make sure no dupicate dim1/dim2 usages. */
select * from DIMENSION_TO_DATASET dtd1
where dtd1.DIMENSION_USAGE = 'dimension2'
  and exists 
         (select 1
           from DIMENSION_TO_DATASET dtd2
           where dtd2.DIMENSION_USAGE = 'dimension1'
                 and dtd2.DIMENSION_NAME  = dtd1.DIMENSION_NAME
                 and dtd2.DATASET_NAME    = dtd1.DATASET_NAME
         )
;

delete from DIMENSION_TO_DATASET
where DIMENSION_USAGE = 'dimension2'
  and (DATASET_NAME, DIMENSION_NAME) in  
        (select dtd.DATASET_NAME, dtd.DIMENSION_NAME
         from (select DATASET_NAME, DIMENSION_NAME
                   from DIMENSION_TO_DATASET
                   where DIMENSION_USAGE = 'dimension1'
                  ) as dtd
        )
;


/* clean out the NA dims */
delete from DIMENSION_TO_DATASET where DIMENSION_NAME = 'NA';



/* Set the dataset's DATA_ISSUE to the associated IP.DATA_NOTE field.  This is 
        done via the DATASOURCE_TO_IV and the IP.DEFAULT_INDICATOR_VIEW_NAME
        and the SORT_ORDER being 1 (which is the numerator data source).
*/
update DATA_SOURCE ds
set ds.DATA_ISSUES = 
        (select i.DATA_NOTE 
         from INDICATOR i 
         where i.DATA_NOTE is not null
           and i.DEFAULT_INDICATOR_VIEW_NAME = 
                (select dstiv.INDICATOR_VIEW_NAME
                 from DATA_SOURCE_TO_INDICATOR_VIEW dstiv
                 where dstiv.DATA_SOURCE_NAME = ds.NAME
                   and dstiv.SORT_ORDER = 1
                 group by dstiv.DATA_SOURCE_NAME, dstiv.INDICATOR_VIEW_NAME
                 limit 1
                )
        ) 
where ds.DATA_ISSUES is null
;

update DATA_SOURCE ds
set ds.DATA_ISSUES = 
        (select i.DATA_NOTE 
         from INDICATOR i 
         where i.DATA_NOTE is not null
           and i.DEFAULT_INDICATOR_VIEW_NAME = 
                (select dstiv.INDICATOR_VIEW_NAME
                 from DATA_SOURCE_TO_INDICATOR_VIEW dstiv
                 where dstiv.DATA_SOURCE_NAME = ds.NAME
                 group by dstiv.DATA_SOURCE_NAME, dstiv.INDICATOR_VIEW_NAME
                 limit 1
                )
        ) 
where ds.DATA_ISSUES is null
;

update DATA_SOURCE ds
set ds.DATA_ISSUES = 
        (select i.DATA_NOTE 
         from INDICATOR i, INDICATOR_VIEW iv, DATA_SOURCE_TO_INDICATOR_VIEW dstiv
         where i.DATA_NOTE is not null
           and i.NAME = iv.INDICATOR_NAME
           and iv.NAME = dstiv.INDICATOR_VIEW_NAME
           and dstiv.DATA_SOURCE_NAME = ds.NAME
         group by dstiv.DATA_SOURCE_NAME, dstiv.INDICATOR_VIEW_NAME
         limit 1
        ) 
where ds.DATA_ISSUES is null
;
/*------------------------------ END OF FILE ------------------------------*/