IDI Data Dictionary: Child, Youth and Family data
June 2015 edition
Crown copyright © This work is licensed under the Creative Commons Attribution 3.0 New Zealand licence. You are free to copy, distribute, and adapt the work, as long as you attribute the work to Statistics NZ and abide by the other licence terms. Please note you may not use any departmental or governmental emblem, logo, or coat of arms in any way that infringes any provision of the Flags, Emblems, and Names Protection Act 1981. Use the wording ‘Statistics New Zealand’ in your attribution, not the Statistics NZ logo. Liability While all care and diligence has been used in processing, analysing, and extracting data and information in this publication, Statistics New Zealand gives no warranty it is error free and will not be liable for any loss or damage suffered by the use directly, or indirectly, of the information in this publication. Citation Statistics New Zealand (2015). IDI Data Dictionary: Child, Youth and Family data (June 2015 edition). Available from www.stats.govt.nz. ISSN 2423-0952 (online) Published in June 2015 by Statistics New Zealand Tatauranga Aotearoa Wellington, New Zealand Contact Statistics New Zealand Information Centre:
[email protected] Phone toll-free 0508 525 525 Phone international +64 4 931 4600 www.stats.govt.nz
Contents 1 Purpose of this data dictionary ....................................................................................5 2 Background ....................................................................................................................6 3 About the Child, Youth and Family data .....................................................................7 Coverage .........................................................................................................................7 Methodology ....................................................................................................................7 Quality information ...........................................................................................................7 Privacy, security, or confidentiality issues .......................................................................7 List of datasets.................................................................................................................8 4 Data dictionary for CYF abuse findings details table ................................................9 Dataset description ..........................................................................................................9 Dataset variables .............................................................................................................9 5 Data dictionary for CYF abuse findings events table ..............................................12 Dataset description ........................................................................................................12 Dataset variables ...........................................................................................................12 6 Data dictionary for CYF intakes table........................................................................17 Dataset description ........................................................................................................17 Dataset variables ...........................................................................................................17 7 Data dictionary for CYF intakes events table ...........................................................20 Dataset description ........................................................................................................20 Dataset variables ...........................................................................................................20 8 Data dictionary for CYF details about placement events table ..............................25 Dataset description ........................................................................................................25 Dataset variables ...........................................................................................................25 9 Data dictionary for CYF placement events table......................................................27 Dataset description ........................................................................................................27 Dataset variables ...........................................................................................................27 10 Data dictionary for CYF identity cluster table ..........................................................32 Dataset description ........................................................................................................32 Dataset variables ...........................................................................................................32 11 Data dictionary for sociocultural characteristics of a person table ......................36 Dataset description ........................................................................................................36 Dataset variables ...........................................................................................................36 Ethnicity classification ....................................................................................................43 12 Data dictionary for date information and ‘from dates’ of events table ..................44 3
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
Dataset description ........................................................................................................44 Dataset variables ...........................................................................................................44 Unknown, not applicable, and invalid records ...............................................................47 13 Data dictionary for date information and ‘to dates’ of events table ......................48 Dataset description ........................................................................................................48 Dataset variables ...........................................................................................................48 Appendix ...........................................................................................................................51
4
1 Purpose of this data dictionary IDI Data Dictionary: Child, Youth and Family data (June 2015 edition) documents the content of the Child, Youth and Family (CYF) datasets the Ministry of Social Development (MSD) provides to Statistics New Zealand to use in the Integrated Data Infrastructure (IDI). This dictionary gives information on the variables contained in the datasets from 1991 – including technical information and descriptions. Use this data dictionary if you are interested in understanding and accessing CYF data in the IDI.
5
2 Background The Ministry of Social Development (MSD) provides Child, Youth and Family (CYF) data to Statistics NZ for integration into the IDI. The CYF datasets contain data about a child or young person (CYP), where: it is believed that a CYP is being or is likely to be harmed, ill-treated, abused, neglected, or deprived; it is believed that a CYP is alleged to have committed an offence; a concern is raised about the CYP’s behaviour or insecurity of care. This CYP is captured in the dataset where this concern is raised or report made to either CYF, the Police (other enforcement agency), Youth Court or Family Court. The datasets currently include facts about identities (ie person identities, identity characteristics, and relationships) and they capture these facts over time. They also capture information about the events in the life of a person at a specific time or over a specific period. These events can be one of the following: proceedings actions measures trials procedures dealings occasions happenings. The dataset is operational and is used by MSD to process business operations.
6
3 About the Child, Youth and Family data Coverage Reference period start: 30 September 1991 Reference period end: ongoing. Geographic coverage: New Zealand excluding Waiheke Island and the Chatham Islands. Target population: total New Zealand population. Observed population: people who have had any interaction with MSD Analysis unit: person, event, time, and cost.
Methodology Type of data: administrative data capture. Data collector: MSD captures the data from their operational systems. Frequency of data collection: MSD systems update data on a daily basis. Data in the IDI is refreshed regularly, approximately every three months, to include the most up-todate data available. Typically the data is extracted with a two-month lag to account for changes made by MSD process operations.
Quality information Editing: data cleansing has been done on the data where applicable according to MSD internal business rules. Other quality issues: known data issues, edits, or anomalies are detailed within relevant dataset sections in this document.
Privacy, security, or confidentiality issues The CYF tables that are accessible to researchers do not contain any name or address information to identify an individual. All researchers who have access to the CYF data have had their research proposals assessed using Statistics NZ’s microdata access protocols and only approved researchers who have been granted access by Statistics NZ and the Ministry of Social Development may view the CYF data. Read Statistics NZ’s microdata access protocols. All outputs produced from CYF data must be aggregated and counts suppressed if the underlying unrounded count is fewer than 6.
7
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
List of datasets CYF_ABUSE_DETAILS CYF_ABUSE_EVENT CYF_INTAKES_DETAILS CYF_INTAKES_EVENT CYF_PLACEMENTS_DETAILS CYF_PLACEMENTS_EVENT CYF_IDENTITY_CLUSTER CYF_SOCIOCULTURAL_CHARACTERISTICS CYF_FROM_DATE CYF_TO_DATE
8
4 Data dictionary for CYF abuse findings details table Dataset description Contents of dataset: holds the abuse findings details pertaining to the event data (CYF_ABUSE_DETAILS).
Dataset variables Variable group: cyf_abuse_details
9
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition
Source agency variable name
snz_uid *
Integer
A global unique identifier created by Statistics NZ. There is a snz_uid for each distinct identity in the IDI. This identifier is changed and reassigned each refresh.
snz_msd_uid
Integer
A local unique identifier derived by Statistics NZ from the source agency’s unique identifier(s). This identifier will remain the same for an identity across refreshes. Where Statistics NZ receives more information during a subsequent refresh that indicates that two or more identities represent the same identity, the identifier may change. The snz_msd_uid represents a distinct identity in the MSD benefits, CYF, and MSD data for student loans and Working For Families.
Integer
A unique identifier created by Statistics NZ for each distinct event. This variable is the primary key for each event table. Combining it with cyf_abe_event_from_datetime makes a row of information unique.
composite_event_id
Integer
A unique identifier created by Statistics NZ for each distinct perpetrator.
perp_system_prsn_id_ value
snz_composite_event_ uid
snz_perp_prsn_uid
Y
10
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition
Source agency variable name
snz_org_unit_uid
Integer
A unique identifier created by Statistics NZ for each distinct organisation unit identified by MSD.
organisation_unit_cod e
cyf_abd_extracted_dat etime*
Date time eg yyyy-mm-dd hh:mm:ss.fff
The date and time at which data was extracted from MSD systems. Note: mandatory field in MSD’s operational systems.
data_extracted_dateti me
cyf_abd_business_are a_type_code
Varchar (20)
Business area eg CNP = Care and Protection, YJU = Youth Justice
business_area_type_c ode
cyf_abd_social_work_ phase_id_nbr
Integer
Social work phases under which social work is conducted.
social_work_phase_id
business_area_type_co de
* Mandatory
11
Dictionary of Child, Youth and Family data in the Integrated Data Infrastructure
5 Data dictionary for CYF abuse findings events table Last updated on: 19 February 2015
Dataset description Contents of dataset: holds details pertaining to CYF abuse findings events (CYF_ABUSE_EVENT). An abuse finding event records the assessment that a social worker makes about whether or not a client has suffered abuse. This is a point-in-time event and so the cyf_abe_event_from_datetime and cyf_abe_event_to_datetime will be the same. This is the ‘assessment date’ of the related assessment. Historically, this date is entered by the social worker on the narrative held on the assessment record, but now it is the ‘date created’ in the same phase. There is one event for every combination of client, perpetrator, and abuse type. There will often be multiple abuse findings event records for a client because there may be multiple notifications for a client, each requiring an investigation. The same client may have more than one type of abuse within the same period (eg physically and sexually abused). Similarly, a client may have the same type of abuse more than once for the same notification, as a result of more than one perpetrator subjecting the client to the same abuse. For example, a child is neglected by both parents. Findings are recorded as past involvement types in PSTINVTP_CODE.
Past involvement types code SEX
Past involvement types
PHY
Physically abused by
EMO
Emotionally abused by
NEG
Neglected by
SHS
Self harm / suicidal
BRD
Behavioural / relationship difficulties
NTF
Not found
Sexually abused by
Dataset variables Variable group: cyf_abuse_event
12
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition A global unique identifier created by Statistics NZ. There is a snz_uid for each distinct identity in the IDI. This identifier is changed and reassigned each refresh.
Source agency variable name
snz_uid*
Integer
snz_msd_uid
Integer
A local unique identifier derived by Statistics NZ from the source agency’s unique identifier(s). This identifier will remain the same for an identity across refreshes. Where Statistics NZ receives more information during a subsequent refresh that indicates that two or more identities represent the same identity, the identifier may change. The snz_msd_uid represents a distinct identity in the MSD benefits, CYF, and MSD data for student loans and Working For Families.
current_unique_identit y_id
Integer
A unique identifier created by Statistics NZ for each distinct event. This variable is the primary key for each event table. Combining it with cyf_abe_event_from_datetime makes a row of information unique.
composite_event_id
snz_composite_event_ uid
Y
13
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition
Source agency variable name
snz_systm_prsn_uid
Integer
An identifier created by Statistics NZ based on the system person ID from MSD systems.
system_prsn_id_value
cyf_abe_event_type_wi d_nbr
Tinyint Numeric
The value will be ‘12’ for an abuse findings event.
event_type_wid
cyf_abe_source_uk_va r1_text
Varchar (50)
(Unique key variable 1 value) For this abuse findings table, this is the assessment_id. See Appendix for more information.
source_table_uk_var1 _value
cyf_abe_source_uk_va r2_text
Varchar (50)
(Unique key variable 2 value) For this abuse findings table, this is the assessment_finding_type_code. See Appendix for more information.
source_table_uk_var2 _value
snz_uk_var3_uid
Integer
Encrypted unique key variable 3 value. Contains the third variable in the event which makes up the unique identifying key to the event. In this abuse findings table this is the perpetrator_person_id Note: this variable is the same variable as snz_perp_prsn_uid (cyf_abuse_details).
source_table_uk_var3 _value
source_table_uk_var2_ name
14
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition
Source agency variable name
cyf_abe_source_uk_va r4_text
Integer
This variable will not contain any data.
source_table_uk_var4 _value
cyf_abe_event_from_d atetime
Datetime yyyy-mmddThh:mm:ss.f fffff
Event from date and time.
event_from_datetime
cyf_abe_event_to_date time
Datetime yyyy-mmddThh:mm:ss.f fffff
Event to date and time.
event_to_datetime
cyf_abe_event_from_d ate_wid_date
Date yyyy-mm-dd
Event from date. This variable can be used to join with the cyf_from_date table.
event_from_date_wid
cyf_abe_event_to_date _wid_date
Date yyyy-mm-dd
Event to date, this variable can be used to join with the cyf_to_date table.
event_to_date_wid
cyf_abe_number_of_da ys_nbr
Integer
Number of days in the event so far. Abuse events are a point-in-time event so cyf_abe_event_from_datetime and cyf_abe_event_to_datetime will be the same and this variable will always be equal to 1.
number_of_days
15
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition
Source agency variable name
cyf_abe_direct_daily_n ett_amt
Decimal (9,3)
Direct daily nett cost
direct_daily_nett_cost
cyf_abe_direct_daily_gr oss_amt
Decimal (9,3)
Direct daily gross cost
direct_daily_gross_co st
cyf_abe_indirect_daily_ nett_amt
Decimal (9,3)
Indirect daily nett cost
indirect_daily_nett_co st
cyf_abe_indirect_daily_ gross_amt
Decimal (9,3)
Indirect daily gross cost
indirect_daily_gross_c ost
cyf_abe_count_nbr
tinyint
Count
count
cyf_abe_extracted_dat etime
Datetime yyyy-mmddThh:mm:ss.f fffff
Data extraction datetime
data_extracted_dateti me
* Mandatory
16
Dictionary of Child, Youth and Family data in the Integrated Data Infrastructure
6 Data dictionary for CYF intakes table Last updated on: 19 February 2015
Dataset description Contents of dataset: holds the details pertaining to the intake event (CYF_INTAKES_DETAILS). A care and protection client intake event occurs when a person who believes a child or young person (CYP) is being (or is likely to be) harmed, ill-treated, abused, neglected, or deprived, reports the matter to CYF or the Police. CYF also receive reports when there are concerns regarding a child or young person's behaviour, or insecurity of care. A youth justice client intake event occurs when a child or young person is alleged to have committed an offence and the matter is referred by the Police (or other enforcement agency), Youth Court, or Family Court. Where a child or young person appears before the court, they may also be placed in the custody of CYF following arrest. The client intake event start date is the incident date of the notification to CYF and the end date is the end date of the client role in the intake phase.
Dataset variables Variable group: cyf_intakes_details
17
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name snz_composite_event_ui d
Primary key Y
Format
Classification name
Variable definition A unique identifier created by Statistics NZ for each distinct event. This variable is the primary key for each event table.
Integer
Source agency variable name composite_event_i d
cyf_ind_extracted_dateti me
Date time yyyy-mm-dd hh:mm:ss.fff
The date and time at which data was extracted from MSD.
data_extracted_dat etime
cyf_ind_social_work_ph ase_id_nbr
Integer
Social work phase. Social work phases under which social work is conducted.
social_work_phase _id
cyf_ind_rollfwd_sw_phas e_id_nbr
Integer
Rollforward social work phase ID is the phase ID into which the event is being rolled into.
rollforward_sw_pha se_id
cyf_ind_fgc_referral_ind
Varchar (3)
Family group conference indicator eg Y, N
source_fgc_referral _yn
cyf_ind_cnp_notification _ind
Varchar (3)
Care and protection notification e.g Y, N
cnp_notification_yn
cyf_ind_business_area_t ype_code
Varchar (20)
business_area_type_ code
Business area
business_area_typ e_code
cyf_ind_intake_type_cod e
Varchar (60)
intake_type_code
Intake type code
intake_type_code
cyf_ind_final_urgency_ty pe_code
Varchar (20)
final_urgency_type_c ode
Final urgency type code
final_urgency_type _code
18
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition
cyf_ind_final_outcome_a ctn_code
Varchar (20)
Final_outcome_action Final outcome action code _type_code eg FAR, NFA, ADD
cyf_ind_final_outcome_t ype_code
Varchar (20)
final_outcome_type_c ode
cyf_ind_crnt_org_unit_ui d
Source agency variable name final_outcome_acti on_type_code
Final outcome type code eg CRT, FAR, FARCFA
final_outcome_type _code
Integer
Current organisation unit uid. A unique identifier created by Statistics NZ for each distinct current organisation unit identified by MSD.
crnt_organisation_ unit_code
cyf_ind_rcvd_org_unit_ uid
Integer
Received organisation unit uid. A unique identifier created by Statistics NZ for each distinct current organisation unit identified by MSD.
rcvd_organisation_ unit_code
cyf_ind_refrd_org_unit_ uid
Integer
Referred organisation unit uid. A unique identifier created by Statistics NZ for each distinct current organisation unit identified by MSD.
refrd_organisation_ unit_code
cyf_ind_intake_ref_statu s_code
Varchar (20)
Intake referral status code
intake_referral_stat us_code
cyf_ind_contact_method _code
Varchar (20)
contact_method_cod e
Contact method code
contact_method_c ode
cyf_ind_notifier_role_typ e_code
Varchar (20)
notifier_role_type_co de
Notifier role type code
notifier_role_type_ code
19
Dictionary of Child, Youth and Family data in the Integrated Data Infrastructure
7 Data dictionary for CYF intakes events table Last updated on: 19 February 2015
Dataset description Contents of dataset: holds client intake event for the integrated person view (CYF_INTAKES_EVENT). A care and protection client intake event occurs when a person who believes a child or young person (CYP) is being (or is likely to be) harmed, ill-treated, abused, neglected, or deprived, reports the matter to CYF or the Police. CYF also receive reports when there are concerns regarding a child or young person's behaviour, or insecurity of care. A youth justice client intake event occurs when a child or young person is alleged to have committed an offence and the matter is referred by the Police (or other enforcement agency), Youth Court, or Family Court. Where a child or young person appears before the court, they may also be placed in the custody of CYF following arrest. The client intake event start date is the incident date of the notification to CYF, and the end date is the dnd date of the client role in the intake phase.
Dataset variables Variable group: cyf_intake_event
20
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition
snz_uid*
Integer
A global unique identifier created by Statistics NZ. There is a snz_uid for each distinct identity in the IDI. This identifier is changed and reassigned each refresh.
snz_msd_uid
Integer
A local unique identifier derived by Statistics NZ from the source agency’s unique identifier(s). This identifier will remain the same for an identity across refreshes. Where Statistics NZ receives more information during a subsequent refresh that indicates that two or more identities represent the same identity, the identifier may change. The snz_msd_uid represents a distinct identity in the MSD benefits, CYF, and MSD data for student loans and Working For Families.
Tinyint
Source agency variable name
current_unique_identit y_id
event_type_wid
cyf_ine_event_type_wi d_nbr
21
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
snz_composite_event_ uid
Y
Classification name
Variable definition
Source agency variable name
Integer
A unique identifier created by Statistics NZ for each distinct event. This variable is the primary key for each event table. Combining it with cyf_abe_event_from_datetime makes a row of information unique.
composite_event_id
snz_systm_prsn_uid
Integer
An identifier created by Statistics NZ based on the system person ID from MSD systems.
system_prsn_id_value
cyf_ ine _source_uk_var1_text
Varchar (50)
(Unique key variable 1 value) For this intakes table, this is the notification_record_id. See Appendix for more information.
source_table_uk_var1 _value
cyf_ ine _source_uk_var2_text
Varchar (50)
This variable will not contain any data.
source_table_uk_var2 _value
cyf_ ine _source_uk_var3_text
Varchar (50)
This variable will not contain any data.
source_table_uk_var3 _value
cyf_ ine _source_uk_var4_text
Varchar (50)
This variable will not contain any data.
source_table_uk_var4 _value
22
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition
Source agency variable name
cyf_ ine _event_from_datetime
Datetime yyyy-mmddThh:mm:ss.f fffff
Event from date and time
event_from_datetime
cyf_ ine _event_to_datetime
Datetime yyyy-mmddThh:mm:ss.f fffff
Event to date and time
event_to_datetime
cyf_ ine _event_from_date_wid _date
Date yyyy-mm-dd
Event from date, this variable can be used to join with the cyf_from_date table.
event_from_date_wid
cyf_ ine _event_to_date_wid_d ate
Date yyyy-mm-dd
Event to date, this variable can be used to join with the cyf_to_date table.
event_to_date_wid
cyf_ ine _number_of_days_nbr
Integer
Number of days in the event so far.
number_of_days
cyf_ ine _direct_daily_nett_amt
Decimal (9,3)
Direct daily nett cost
direct_daily_nett_cost
cyf_ ine _direct_daily_gross_a mt
Decimal (9,3)
Direct daily gross cost
direct_daily_gross_co st
23
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition
Source agency variable name
cyf_ ine _indirect_daily_nett_a mt
Decimal (9,3)
Indirect daily nett cost
indirect_daily_nett_co st
cyf_ ine _indirect_daily_gross_a mt
Decimal (9,3)
Indirect daily gross cost
indirect_daily_gross_c ost
cyf_ ine _count_nbr
tinyint
Count
count
cyf_ ine _extracted_datetime
Datetime yyyy-mmddThh:mm:ss.f fffff
Data extraction datetime
data_extracted_dateti me
* Mandatory
24
Dictionary of Child, Youth and Family data in the Integrated Data Infrastructure
8 Data dictionary for CYF details about placement events table Last updated on: 19 February 2015
Dataset description Contents of dataset: holds the details pertaining to the placement event (CYF_PLACEMENTS_DETAILS).
Dataset variables Variable group: cyf_placements_details
25
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name snz_composite_event_ uid
Primary key Y
Format
Classification name
Variable definition A unique identifier created by Statistics NZ for each distinct event. This variable is the primary key for each event table.
Integer
Source agency variable name composite_event_i d
cyf_pld_extracted_datet ime
Date time yyyy-mm-dd hh:mm:ss.fff
The date and time at which data was extracted from MSD.
data_extracted_dat etime
cyf_pld_open_suspensi on_date
Date yyyy-mm-dd
Latest open suspension date
open_suspension_ date
cyf_pld_placement_typ e_code
Varchar (20)
placement_type_code
Placement type code
placement_type_co de
cyf_pld_output_class_c ode
Varchar (20)
output_class_code
Output class code
output_class_code
snz_org_unit_uid
Integer
A unique identifier created by Statistics NZ for each distinct current organisation unit identified by MSD.
organisation_unit_c ode
cyf_pld_business_area _type_code
Varchar (20)
Business area
business_area_typ e_code
cyf_pld_social_work_ph ase_id_nbr
Integer
Social work phases under which social work is conducted.
social_work_phase _id
cyf_pld_open_suspensi on_ind
Varchar (1)
Suspension flag eg Y, N
open_suspension_ yn
cyf_pld_full_time_ind
Varchar (1)
Placement full-time flag eg Y, N
full_time_yn
business_area_type_co de
26
Dictionary of Child, Youth and Family data in the Integrated Data Infrastructure
9 Data dictionary for CYF placement events table Last updated on: 19 February 2015
Dataset description Contents of dataset: holds CYF placement event for the integrated client person/cost/time event table (CYF_PLACEMENTS_EVENT). A placement event occurs when a placement record is created for a client. Some placement records for a given client may overlap. An example of this is where a placement is in force but then a respite placement (perhaps for a few days) occurs for the same client, who then returns to the original placement after the respite placement.
Dataset variables Variable group: cyf_placements_event
27
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
snz_uid*
Integer
snz_msd_uid
Integer
cyf_ple_event_type_ wid_nbr
Tinyint
snz_composite_event _uid
Y
Classification name
Variable definition A global unique identifier created by Statistics NZ. There is a snz_uid for each distinct identity in the IDI. This identifier is changed and reassigned each refresh. A local unique identifier derived by Statistics NZ from the source agency’s unique identifier(s). This identifier will remain the same for an identity across refreshes. Where Statistics NZ receives more information during a subsequent refresh that indicates that two or more identities represent the same identity, the identifier may change. The snz_msd_uid represents a distinct identity in the MSD benefits, CYF, and MSD data for student loans and Working For Families.
Source agency variable name
current_unique_identit y_id
event_type_wid
Integer
A unique identifier created by Statistics NZ for each distinct event. This variable is the primary key for each event table. Combining it with cyf_abe_event_from_datetime makes a row of information unique.
28
composite_event_id
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition
Source agency variable name
snz_systm_prsn_uid
Integer
An identifier created by Statistics NZ based on the system person ID from MSD systems.
system_prsn_id_value
cyf_ple_source_uk_v ar1_text
Varchar (50)
(Unique key variable 1 value) For this placements table it is the placement_id. See Appendix for more information.
source_table_uk_var1 _value
cyf_ ple _source_uk_var2_tex t
Varchar (50)
This variable will not contain any data.
source_table_uk_var2 _value
cyf_ ple _source_uk_var3_tex t
Varchar (50)
This variable will not contain any data.
source_table_uk_var3 _value
cyf_ ple _source_uk_var4_tex t
Varchar (50)
This variable will not contain any data.
source_table_uk_var4 _value
cyf_ ple _event_from_datetim e
Datetime yyyy-mmddThh:mm:ss.f fffff
Event from date and time
event_from_datetime
29
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition
Source agency variable name
cyf_ ple_event_to_datetim e
Datetime yyyy-mmddThh:mm:ss.f fffff
Event to date and time
event_to_datetime
cyf_ ple_event_from_date _wid_date
Date yyyy-mm-dd
Event from date, this variable can be used to join with the cyf_from_date table.
event_from_date_wid
cyf_ ple_event_to_date_wi d_date
Date yyyy-mm-dd
Event to date, this variable can be used to join with the cyf_to_date table.
event_to_date_wid
cyf_ ple_number_of_days _nbr
Integer
Number of days in the event so far.
number_of_days
cyf_ ple_direct_daily_nett_ amt
Decimal (9,3)
Direct daily nett cost
direct_daily_nett_cost
cyf_ ple_direct_daily_gros s_amt
Decimal (9,3)
Direct daily gross cost
direct_daily_gross_co st
cyf_ ple_indirect_daily_net t_amt
Decimal (9,3)
Indirect daily nett cost
indirect_daily_nett_co st
30
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition
Source agency variable name
cyf_ ple_indirect_daily_gro ss_amt
Decimal (9,3)
Indirect daily gross cost
indirect_daily_gross_c ost
cyf_ ple_count_nbr
Tinyint
Count
count
cyf_ ple_extracted_dateti me
Datetime yyyy-mmddThh:mm:ss.f fffff
Data extraction datetime
data_extracted_dateti me
* Mandatory
31
Dictionary of Child, Youth and Family data in the Integrated Data Infrastructure
10 Data dictionary for CYF identity cluster table Last updated on: 19 February 2015
Dataset description Contents of dataset: holds the matched cluster groups and the distinctive characteristics for a person: gender, date of birth, given names, last name, and date of death (CYF_IDENTITY_CLUSTER). This table can be used for any CYF event to join to any other CYF event information for a ‘person’.
Dataset variables Variable group: cyf_identity_cluster
32
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition
snz_uid*
Integer
A global unique identifier created by Statistics NZ. There is a snz_uid for each distinct identity in the IDI. This identifier is changed and reassigned each refresh.
snz_msd_uid
Integer
A local unique identifier derived by Statistics NZ from the source agency’s unique identifier(s). This identifier will remain the same for an identity across refreshes. Where Statistics NZ receives more information during a subsequent refresh that indicates that two or more identities represent the same identity, the identifier may change. The snz_msd_uid represents a distinct identity in the MSD benefits, CYF, and MSD data for student loans and Working For Families. This is based on the current_unique_identity_id, which is assigned to a distinct person. Where a person’s identity record has been matched against other identity records, this source agency variable will contain the final identity ID to which this identity record belongs at a point in time. Therefore, records that had been identified by MSD as belonging to the same person will have the same snz_msd_uid. This snz_msd_uid will stay the same for a person across refreshes except where cross-referencing during a subsequent refresh has indicated that two records are the same person and then the uid may change.
33
Source agency variable name
current_unique_id entity_id
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition
Source agency variable name
snz_systm_ prsn_uid
Integer
The source system identity for each person.
system_prsn_id_v alue
snz_crnt_systm_prsn _uid
Integer
The current source system identity for each person. If a person’s identity has been matched against other identities, this column will contain the final identity to which this identity record belongs to at a point in time.
current_system_pr sn_id_value
snz_from_msd_uid
Varchar (20)
Contains the ID that is distinct to each record, ie records that have been identified as the official and alias identity of a person will have different snz_from_msd_uid (because their unique_identity_ids are different).
unique_identity_id
cyf_idc_identity_type_ text
Char (20)
Identity role type within the MSD system eg official, alias, duplicate, not applicable
identity_type
cyf_idc_role_type_tex t
Varchar (20)
Role type eg other, client
role_type
cyf_idc_source_syste m_code
Varchar (50)
The MSD source system where the identity originated eg C0YS
source_system_co de
cyf_idc_systm_persn_ id_var_text
Varchar (50)
The MSD source system information eg PERSON_ID
system_prsn_id_v ar_name
cyf_idc_sex_code
Varchar (100)
The sex of the person ie male = 1, female = 2, unknown
gender
34
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition
Source agency variable name
cyf_idc_birth_month_ nbr
Tinyint
The birth month of the person
dob
cyf_idc_birth_year_nb r
Smallint
The birth year of the person
dob
cyf_idc_death_month _nbr
Tinyint
The month of death
dod
cyf_idc_death_year_n br
Smallint
The year of death
dod
cyf_idc_extracted_dat etime
Datetime yyyy-mmddThh:mm: ss.ffffff
Data extraction datetime
* Mandatory
35
Dictionary of Child, Youth and Family data in the Integrated Data Infrastructure
11 Data dictionary for sociocultural characteristics of a person table Last updated on: 19 February 2015
Dataset description Contents of dataset: holds the sociocultural characteristics of a person (CYF_SOCIOCULTURAL_CHARACTERISTICS). Characteristics have been standardised across MSD systems and the number of characteristics are expanded as the need for them arises.
Dataset variables Variable group: cyf_sociocultural_characteristics
36
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition
snz_uid*
Integer
A global unique identifier created by Statistics NZ. There is a snz_uid for each distinct identity in the IDI. This identifier is changed and reassigned each refresh.
snz_msd_uid
Integer
A local unique identifier derived by Statistics NZ from the source agency’s unique identifier(s). This identifier will remain the same for an identity across refreshes. Where Statistics NZ receives more information during a subsequent refresh that indicates that two or more identities represent the same identity, the identifier may change. The snz_msd_uid represents a distinct identity in the MSD benefits, CYF, and MSD data for student loans and Working For Families.
Source agency variable name
unique_identity_id
For this table, the source agency variable used to create snz_msd_uid was the unique_identity_id (and not the current_unique_identity_id), as only the records where unique_identity_id = current_unique_identity_id (in identity_cluster table) were populated in the sociocultural table. cyf_soc_ethnic_1_co de
Varchar (20)
The primary ethnicity recorded for the person in the CYF data.
37
ethnicity_1_code
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition
Source agency variable name
cyf_soc_ethnic_2_co de
Varchar (20)
The 2nd ethnicity code recorded for the person identity in the CYF data.
ethnicity_2_code
cyf_soc_ethnic_3_co de
Varchar (20)
The 3rd ethnicity code recorded for the person identity in the CYF data.
ethnicity_3_code
cyf_soc_ethnic_4_co de
Varchar (20)
The 4th ethnicity code recorded for the person identity in the CYF data.
ethnicity_4_code
cyf_soc_ethnic_5_co de
Varchar (20))
The 5th ethnicity code recorded for the person identity in the CYF data.
ethnicity_5_code
cyf_soc_ethnic_6_co de
Varchar (20)
The 6th ethnicity code recorded for the person identity in the CYF data.
ethnicity_6_code
cyf_soc_ethnic_asian _ind
Varchar (1)
Yes/no flag for whether the person is part of the Asian ethnicity category. eg Y , N
ethnicity_asian_yn
cyf_soc_ethnic_europ ean_ind
Varchar (1)
Yes/no flag for whether the person is part of the European ethnicity category. eg Y , N
ethnicity_europea n_yn
cyf_soc_ethnic_maori _ind
Varchar (1)
Yes/no flag for Statistics NZ level 1 classification for Māori ethnicity category, where one of the six ethnicity codes will begin with a 2 eg Y , N
ethnicity_maori_yn
38
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition
Source agency variable name
cyf_soc_ethnic_mela a_ind
Varchar (1)
Yes/no flag for Statistics NZ level 1 classification for Middle Eastern / Latin American / African (MELAA) ethnicity category, where one of the six ethnicity codes will begin with a 5 eg Y , N
ethnicity_melaa_y n
cyf_soc_ethnic_other _ind
Varchar (1)
Yes/no flag for Statistics NZ level 1 classification for Other ethnicity category, where one of the six ethnicity codes will begin with a 6 eg Y , N
ethnicity_other_yn
cyf_soc_ethnic_pacifi c_ind
Varchar (1)
Yes/no flag for Statistics NZ level 1 classification for Pacific peoples ethnicity category, where one of the six ethnicity codes will begin with a 3 eg Y , N
ethnicity_pacific_p eople_yn
cyf_soc_ethnic_resid ual_ind
Varchar (1)
Yes/no flag for Statistics NZ level 1 classification for Māori ethnicity category, where one of the six ethnicity codes will begin with a 9. These are where ethnicity is not known or not stated. eg Y , N
ethnicity_residual_ yn
cyf_soc_extracted_da tetime
Datetime yyyy-mmddThh:mm:ss .ffffff
Data extraction datetime
39
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition
cyf_soc_ethnic1_snz _code
Varchar (6)
The primary ethnicity recorded for the person, using the Statistics NZ Standard Classification 2005 code.
cyf_soc_ethnic2_snz _code
Varchar (6)
The 2nd ethnicity code recorded for the person identity, using the Statistics NZ Standard Classification 2005 code.
cyf_soc_ethnic3_snz _code
Varchar (6)
The 3rd ethnicity code recorded for the person identity, using the Statistics NZ Standard Classification 2005 code.
cyf_soc_ethnic4_snz _code
Varchar (6)
The 4th ethnicity code recorded for the person identity, using the Statistics NZ Standard Classification 2005 code.
cyf_soc_ethnic5_snz _code
Varchar (6)
The 5th ethnicity code recorded for the person identity, using the Statistics NZ Standard Classification 2005 code.
cyf_soc_ethnic6_snz _code
Varchar (6)
The 6th ethnicity code recorded for the person identity, using the Statistics NZ Standard Classification 2005 code.
cyf_soc_ethnic_grp1_ snz_ind*
bit
An indicator created by Statistics NZ to categorise a person by their ethnicity group according to the Statistics NZ ethnicity classification. Where 1 appears in this column it indicates that the person has given their ethnicity as European. eg 1, 0
40
Source agency variable name
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition
cyf_soc_ethnic_grp2_ snz_ind*
bit
An indicator created by Statistics NZ to categorise a person by their ethnicity group according to the Statistics NZ ethnicity classification. Where 1 appears in this column it indicates that the person has given their ethnicity as Māori. eg 1, 0
cyf_soc_ethnic_grp3_ snz_ind*
bit
An indicator created by Statistics NZ to categorise a person by their ethnicity group according to the Statistics NZ ethnicity classification. Where 1 appears in this column it indicates that the person has given their ethnicity as Pacific eoples. eg 1, 0
cyf_soc_ethnic_grp4_ snz_ind*
bit
An indicator created by Statistics NZ to categorise a person by their ethnicity group according to the Statistics NZ ethnicity classification. Where 1 appears in this column it indicates that the person has given their ethnicity as Asian. eg 1, 0
cyf_soc_ethnic_grp5_ snz_ind*
bit
An indicator created by Statistics NZ to categorise a person by their ethnicity group according to the Statistics NZ ethnicity classification. Where 1 appears in this column it indicates that the person has given their ethnicity as Middle Eastern / Latin America / African (MELAA). eg 1, 0
41
Source agency variable name
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name cyf_soc_ethnic_grp6_ snz_ind*
Primary key
Format
Classification name
bit
Variable definition An indicator created by Statistics NZ to categorise a person by their ethnicity group according to the Statistics NZ ethnicity classification. Where 1 appears in this column it indicates that the person has given their ethnicity as Other ethnicity. eg 1, 0
* Mandatory
42
Source agency variable name
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
Ethnicity classification The Ethnicity New Zealand Standard Classification 2005 has been used by MSD and Statistics NZ to classify ethnicities. The ethnicity code data is recorded in the Statistics NZ level 3 format. The primary ethnicity for the person identity will be recorded in the cyf_soc_ethnic_1_code variable. The other non-primary ethnicity codes will be recorded in the cyf_soc_ethnic_2_code – cyf_soc_ethnic_6_code variables. The non-primary ethnicity codes will be sorted in ascending order. There is a maximum of six ethnicity codes reported per person identity. In this table, MSD has supplied a cyf_soc_ethnic_code as 541, which does not exist in the Ethnicity New Zealand Standard Classification 2005. This code has therefore been coded to 611 ‘Other ethnicity’ in the cyf_soc_ethnic_snz_code. While this means that this record will have cyf_soc_ethnic_grp6_snz_ind = 1 (‘Other’ group), the original grouping from CYF is cyf_soc_ethnic_melaa_ind = 1 (‘MELAA’ group). We cannot code this record to another cyf_soc_ethnic_snz_code because we cannot break the MELAA category down any further then level 3 without knowing which ethnicity the identity is associated with.
43
12 Data dictionary for date information and ‘from dates’ of events table Last updated on: 19 February 2015
Dataset description Contents of dataset: holds the date information and contains the ‘from dates’ of each event (CYF_FROM_DATE).
Dataset variables Variable group: cyf_from_date
44
IDI variable name
Primary key
Format
Classification name
Variable definition
Source agency variable name date_wid
cyf_frm_date_wid_dat e
Date yyyy-mm-dd
The from date of the event
cyf_frm_calendar_dat e
Date yyyy-mm-dd
The from date of the event
calendar_date_co de
cyf_frm_date_number _format_nbr
Integer
The from date of the event in number format (not a SAS numeric representation) eg 20140101 (YYYYMMDD)
date_in_number_f ormat
cyf_frm_day_in_week _nbr
Tinyint
Day of the week
day_number_in_w eek
cyf_frm_day_name_t ext
Varchar (10)
Day name eg Thursday
day_name
cyf_frm_month_name _text
Varchar (10)
Month name eg January
month_name
cyf_frm_calendar_ye ar_nbr
Smallint
Calendar year eg 2014
calendar_year
cyf_frm_calendar_mo nth_nbr
Tinyint
Calendar month eg 1
calendar_month_n umber
cyf_frm_calendar_qu arter_nbr
Tinyint
Calendar quarter eg 2014Q1
calendar_quarter_ number
45
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition
Source agency variable name
cyf_frm_year_quarter _text
Varchar(7)
Calendar year quarter code eg 2014Q1
year_quarter
cyf_frm_fiscal_year_t ext
Varchar(7)
Fiscal year eg 2014/15
fiscal_year
cyf_frm_fiscal_month _nbr
Tinyint
Month number in fiscal year ie 1–12
fiscal_month
cyf_frm_fiscal_quarte r_nbr
Tinyint
Quarter number in fiscal year ie 1–4
fiscal_quarter_nu mber
cyf_frm_fiscal_year_q uarter_text
Varchar(9)
Fiscal year quarter code eg2014/15Q1
fiscal_year_quarte r
cyf_frm_weekend_ind
Varchar(1)
Weekend indicator, when the date is on a weekend (Saturday or Sunday) Eg Y,N
weekend_yn
cyf_frm_holiday_ind
Varchar(1)
Public holiday indicator, when the date falls on a public holiday eg Y, N
holiday_yn
cyf_frm_extracted_da tetime
Datetime yyyy-mmddThh:mm:ss.fffff f
Data extraction datetime
data_extracted_da tetime
46
Unknown, not applicable, and invalid records These values apply to both the ‘from_date’ and ‘to_date’ tables.
Date_wid 12DEC9999
calendar_date_code day_name Unknown UNK
day_number_in_week 6
13DEC9999
Not applicable
XXX
7
14DEC9999
Invalid
INV
1
15DEC9999
Exceeds
EXC
2
31DEC9999
Null
NUL
4
01JAN1900
Default
DEF
2
47
13 Data dictionary for date information and ‘to dates’ of events table Last updated on: 19 February 2015
Dataset description Contents of dataset: holds the date information and contains the ‘to dates’ of each event (CYF_TO_DATE).
Dataset variables Variable group: cyf_to_date
48
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition
Source agency variable name date_wid
cyf_to_date_wid_date
Date yyyy-mm-dd
‘To date’ of the event
cyf_to_calendar_date
Date yyyy-mm-dd
‘To date’ of the event
calendar_date_co de
cyf_to_date_number_fo rmat_nbr
Integer
The ‘to date’ of the event number format (not a SAS numeric representation) eg 20140101 (YYYYMMDD)
date_in_number_f ormat
cyf_to_day_in_week_nb r
Tinyint
Day of the week
day_number_in_w eek
cyf_to_day_name_text
Varchar (10)
Day name eg Thursday
day_name
cyf_to_month_name_te xt
Varchar (10)
Month name eg January
month_name
cyf_to_calendar_year_n br
Smallint
Calendar year eg 2014
calendar_year
cyf_to_calendar_month _nbr
Tinyint
Calendar month eg 1
calendar_month_n umber
cyf_to_calendar_quarte r_nbr
Tinyint
Calendar quarter eg 2014Q1
calendar_quarter_ number
cyf_to_year_quarter_te xt
Varchar(7)
Calendar year quarter code eg 2014Q1
year_quarter
49
IDI Data Dictionary: Child, Youth and Family data (June 2015 edition)
IDI variable name
Primary key
Format
Classification name
Variable definition
Source agency variable name
cyf_to_fiscal_year_text
Varchar(7)
Fiscal year eg 2014/15
fiscal_year
cyf_to_fiscal_month_nb r
Tinyint
Month number in fiscal year ie 1–12
fiscal_month
cyf_to_fiscal_quarter_n br
Tinyint
Quarter number in fiscal year ie 1–4
fiscal_quarter_nu mber
cyf_to_fiscal_year_quar ter_text
Varchar(9)
Fiscal year quarter code eg 2014/15Q1
fiscal_year_quarte r
cyf_to_weekend_ind
Varchar(1)
Weekend indicator, when the date is on a weekend (Saturday or Sunday) eg Y
weekend_yn
cyf_to_holiday_ind
Varchar(1)
Public holiday indicator, when the date falls on a public holiday eg Y, N
holiday_yn
cyf_to_extracted_dateti me
Datetime yyyy-mmddThh:mm:ss.f fffff
Data extraction datetime
data_extracted_da tetime
50
Appendix The event_type table below shows which code or ID the source_uk_varN_text variable represents in each of the placements_event, intakes_event, and abuse_event tables.
These are event_type_description the source variables as we do not put this table into the clean environmen t.event_typ e_wid 12 Client abuse finding event sourced from CYRAS source system
service_line_descript ion
source_table_u k_var1_name
source_table_u k_var2_name
source_table_u k_var3_name
source_table_u k_var4_name
Child, Youth and Family
assessment_id
assessment_find ing_type_code
perpetrator_pers on_id
NULL
2
Client Intake Events sourced from CYRAS source system
Child, Youth and Family
notification_recor d_id
NULL
NULL
NULL
14
Client Placement Event sourced Child, Youth and from CYRAS source system Family
placement_id
NULL
NULL
NULL
51
