= Вовед

Во рамки на првата фаза, фокусот е ставен на:
* генерирање на податоци
* креирање на база и табели
* пополнување на табелите со реалистични податоци.

== Синтетички податоци

Поради чувствителноста на здравствените податоци и регулативите за приватност, во проектот не се користат реални пациентски податоци.

Наместо тоа, се користат синтетички податоци кои
* ја задржуваат статистичката структура на реални податоци
* не содржат лични или доверливи информации
* се погодни за тестирање и анализа

== Synthea алатка

[https://github.com/synthetichealth/synthea Synthea] е open-source алатка за генерирање на синтетички здравствени податоци.

Алатката генеррира податоци за пациенти, прегледи, дијагнози, терапии и интервенции организирани во CSV формат.

Податоците се структурирани така што можат директно да се внесат во релациона база на податоци.

= Генерирање на податоци

Податоците се генерирани со користење на **Synthea**, при што е симулирана популација од 250 000 пациенти.

{{{#!cmd
./run_synthea.bat -p 250000
}}}

= Креирање на табели

По генерирањето на CSV датотеките, следен чекор е подготовка на релационата база на податоци.

Ова опфаќа:
* креирање на база на податоци
* дефинирање на табели
* избор на соодветни типови на податоци
* подготовка за ефикасен увоз на податоци

=== Креирање на база на податоци

{{{#!sql
create database HospitalSyntheaDB
on primary
(name = 'HospitalSyntheaDB', filename = 'C:\Path\To\Data\HospitalSyntheaDB.mdf', size = 1024MB, filegrowth = 256MB)
log on
(name = 'HospitalSyntheaDB_log', filename = 'C:\Path\To\Data\HospitalSyntheaDB_log.ldf', size = 512MB, filegrowth = 128MB);
go
}}}

=== Patients табела

{{{#!sql
create table patients (
    id uniqueidentifier primary key,
    birthdate date not null,
    deathdate date null,
    ssn varchar(20) null,
    drivers varchar(50) null,
    passport varchar(50) null,
    prefix varchar(20) null,
    first varchar(100) not null,
    middle varchar(100) null,
    last varchar(100) not null,
    suffix varchar(20) null,
    maiden varchar(100) null,
    marital varchar(20) null,
    race varchar(50) not null,
    ethnicity varchar(50) not null,
    gender char(1) not null,
    birthplace varchar(200) null,
    address varchar(200) not null,
    city varchar(100) not null,
    state varchar(50) not null,
    county varchar(100) null,
    fips varchar(10) null,
    zip varchar(20) null,
    lat decimal(9,6) null,
    lon decimal(9,6) null,
    healthcare_expenses decimal(18,2) null,
    healthcare_coverage decimal(18,2) null,
    income decimal(18,2) null
);
go
}}}

=== Encounters табела

{{{#!sql
create table encounters (
    id uniqueidentifier primary key,
    start datetime2 not null,
    stop datetime2 null,
    patient uniqueidentifier not null,
    organization uniqueidentifier not null,
    provider uniqueidentifier not null,
    payer uniqueidentifier not null,
    encounterclass varchar(50) not null,
    code varchar(50) not null,
    description varchar(500) not null,
    base_encounter_cost decimal(18,2) not null,
    total_claim_cost decimal(18,2) not null,
    payer_coverage decimal(18,2) not null,
    reasoncode varchar(50) null,
    reasondescription varchar(500) null
);
go
}}}

=== Conditions табела

{{{#!sql
create table conditions (
    start date not null,
    stop date null,
    patient uniqueidentifier not null,
    encounter uniqueidentifier not null,
    system varchar(100) not null,
    code varchar(50) not null,
    description varchar(500) not null
);
go
}}}

=== Observations

{{{#!sql
create table observations (
    date datetime2 not null,
    patient uniqueidentifier not null,
    encounter uniqueidentifier null,
    category varchar(100) null,
    code varchar(50) not null,
    description varchar(500) not null,
    value varchar(max) not null,
    units varchar(50) null,
    type varchar(50) not null
);
go
}}}

=== Medications

{{{#!sql
create table medications (
    start datetime2 not null,
    stop datetime2 null,
    patient uniqueidentifier not null,
    payer uniqueidentifier not null,
    encounter uniqueidentifier not null,
    code varchar(50) not null,
    description varchar(500) not null,
    base_cost decimal(18,2) not null,
    payer_coverage decimal(18,2) not null,
    dispenses int not null,
    totalcost decimal(18,2) not null,
    reasoncode varchar(50) null,
    reasondescription varchar(500) null
);
go
}}}

=== Allergies

{{{#!sql
create table allergies (
    start date null,
    stop date null,
    patient uniqueidentifier not null,
    encounter uniqueidentifier null,
    code varchar(50) not null,
    system varchar(100) not null,
    description varchar(500) not null,
    type varchar(50) null,
    category varchar(50) null,
    reaction1 varchar(100) null,
    description1 varchar(500) null,
    severity1 varchar(50) null,
    reaction2 varchar(100) null,
    description2 varchar(500) null,
    severity2 varchar(50) null
);
go
}}}

=== Procedures

{{{#!sql
create table procedures (
    start datetime2 not null,
    stop datetime2 null,
    patient uniqueidentifier not null,
    encounter uniqueidentifier not null,
    system varchar(100) null,
    code varchar(50) not null,
    description varchar(500) not null,
    base_cost decimal(18,2) null,
    reasoncode varchar(50) null,
    reasondescription varchar(500) null
);
go
}}}

=== Immunizations

{{{#!sql
create table immunizations (
    date datetime2 not null,
    patient uniqueidentifier not null,
    encounter uniqueidentifier null,
    code varchar(50) not null,
    description varchar(500) not null,
    base_cost decimal(18,2) not null
);
go
}}}

= Пополнување на табели

Податоците ги увезуваме од CSV датотеки преку staging табели, со цел:
* да се овозможи контрола и чистење на податоците
* да се избегнат грешки при директен внес на финалните табели
* да се овозможи безбедно кастирање на типови (dates, decimals, uniqueidentifier)

== Staging табели

За секоја финална табела е креирана соодветна staging табела. Во овие табели сите колони се дефинирани како `VARCHAR`, со што:
* се избегнуваат грешки при `BULK INSERT`
* се овозможува чистење на податоци (отстранување на наводници)
* се користи `TRY_CAST` за безбедна конверзија кон финалните типови. 

Staging табели:
* `patients_staging`
* `encounters_staging`
* `conditions_staging`
* `observations_staging`
* `medications_staging`
* `allergies_staging`
* `procedures_staging`
* `immunizations_staging`

== Процес на увоз на податоци

Процесот на пополнување за секоја табела се состои од следните чекори:
1. Празнење на staging табелата (truncate).
2. `BULK INSERT` од CSV датотека
3. Проверка на број на редови во staging табелата
4. `INSERT` во финалната табела со `CAST` / `TRY_CAST`
5. Проверка на број на редови во финалната табела.

=== Пример: Пополнување на табелата Patients

Податоците за пациенти се увезуваат од датотеката `patients.csv`.
При увозот:
* `ID` полињата се конвертираат во `UNIQUEIDENTIFIER`
* датумите се конвертираат во `DATE`
* нумеричките вредности се конвертираат во `DECIMAL`
* се користи `TRY_CAST` за справување со недостасувачки вредности

{{{#!sql
use HospitalSyntheaDB;
go

if object_id('dbo.patients_staging') is not null drop table dbo.patients_staging;
go

create table patients_staging(
    id varchar(100), birthdate varchar(50), deathdate varchar(50), ssn varchar(50), drivers varchar(50),
    passport varchar(50), prefix varchar(50), first varchar(100), middle varchar(100), last varchar(100),
    suffix varchar(50), maiden varchar(100), marital varchar(50), race varchar(50), ethnicity varchar(50),
    gender varchar(10), birthplace varchar(200), address varchar(200), city varchar(100), state varchar(50),
    county varchar(100), fips varchar(20), zip varchar(20), lat varchar(20), lon varchar(20),
    healthcare_expenses varchar(50), healthcare_coverage varchar(50), income varchar(50)
);
go

truncate patients_staging;
bulk insert patients_staging
from 'C:\Users\user\Documents\DBA\synthea\output\csv\patients.csv'
with (fieldterminator = ',', rowterminator = '\n', firstrow = 2, codepage = '65001', tablock);
go

print 'Patients staging row count:';
select count(*) as patients_staging_count from patients_staging;
go

insert into patients
select
    cast(replace(id,'"','') as uniqueidentifier),
    cast(replace(birthdate,'"','') as date),
    try_cast(replace(deathdate,'"','') as date),
    replace(ssn,'"',''),
    replace(drivers,'"',''),
    replace(passport,'"',''),
    replace(prefix,'"',''),
    replace(first,'"',''),
    replace(middle,'"',''),
    replace(last,'"',''),
    replace(suffix,'"',''),
    replace(maiden,'"',''),
    replace(marital,'"',''),
    replace(race,'"',''),
    replace(ethnicity,'"',''),
    upper(replace(gender,'"','')),
    replace(birthplace,'"',''),
    replace(address,'"',''),
    replace(city,'"',''),
    replace(state,'"',''),
    replace(county,'"',''),
    replace(fips,'"',''),
    replace(zip,'"',''),
    try_cast(replace(lat,'"','') as decimal(9,6)),
    try_cast(replace(lon,'"','') as decimal(9,6)),
    try_cast(replace(healthcare_expenses,'"','') as decimal(18,2)),
    try_cast(replace(healthcare_coverage,'"','') as decimal(18,2)),
    try_cast(replace(income,'"','') as decimal(18,2))
from patients_staging;
go

print 'Patients final row count:';
select count(*) as patients_count from patients;
go
}}}

== Чистење по увоз

По успешниот увоз на податоци, staging табелите е бришат.

Ова спречува непотребно користење простор и ја задржува базата чиста за понатамошни фази од проектот.

{{{#!sql
drop table patients_staging;
go
}}}