UETL_UserGuide.doc
UETL (Universal Extract, Transform, and Load)
UETL (Universal Extract, Transform, and Load)
2 UETL (Universal Extract, Transform, and Load) architecture
5.1 Generate bulk-load files for i2b2DemoData schema
5.2 Generate bulk-load files for i2b2DemoData schema
1 Installation
2 UETL ( Universal Extract, Transform, and Load ) architecture
3 UETL process
3.1 UETL Process flow diagram
3.2 Filters
Following filters are applied for Observation_Fact data.
ICD9 : It will generate concept_code as ICD9: ICD9 value
ICD10 : ICD10: ICD10 value
ProxyBirth: This is for PHI, if you don’t want to use original Date Of Birth. It will proxy every date of birth to Jan 1 st , for example 2010/05/25 will be 2010/01/01
Also, you apply this filter to any date filed, which you want to proxy
ProxyEncounter : If there is an encounter column in the selected columns and want to use ProxyEncounter, which ware previously generated using VisitDim UI and loaded in Proxy_Enounter table
ProxyMRN : If there is a MRN column in the selected columns and want to use ProxyMRN, which ware previously generated using PatientDim UI and loaded in Proxy_MRN table
ZipCodeFilter: To proxy the ZipCode. It masks last two digits of zipcode.
e.g. 95123 will be 951xx
i2b2 Dimension Filters
We will be using only for the PatientDim
The source table for this is PROXY_MRN_PATIENT_DIMENSION.
It will generate the demographics data in descriptive form.
It will substitute the appropriate text description for the demographics data.
e.g. Patient gender will have 1 for Male, 2 for F
Or Race is represented in numeric value 1,2 3, etc., It will add appropriate text for the race, 1 can be White, 2 can be Black
4 Database (DB) setup
4.1 Configure database
4.1.1 Source database
Got to dist folder e dit DBConnection.xml file to enter Database details
Eg. C:\UETL\dist
Following is the sample:
<ServerName NAME="SQL-Server">
<DatabaseType>MSSqlServer2005</DatabaseType>
<ServerIP>127.2.2.1</ServerIP>
<Port>1433</Port>
<Drivername>com.microsoft.sqlserver.jdbc.SQLServerDriver</Drivername>
<URL>jdbc:sqlserver</URL>
<DatabaseName>Enter Database name</DatabaseName>
<Username>Enter User name</Username>
<Password>Entere Password</Password>
</ServerName>
<ServerName NAME="XYZ">
<DatabaseType>Oracle</DatabaseType>
<ServerIP>127.2.2.2</ServerIP>
<Drivername>oracle.jdbc.driver.OracleDriver</Drivername>
<URL>jdbc:oracle:thin</URL>
<Port>1521</Port>
<DatabaseName>Enter Database name from oracle</DatabaseName>
<InstanceName>Enter Database instance name</InstanceName>
<Username>Eneter user id</Username>
<Password>Eneter Password</Password>
</ServerName>
4.1.2 Proxy database
Sample for the sql server
<ServerName NAME="ProxyMRN">
<DatabaseType>MSSqlServer2005</DatabaseType>
<ServerIP>127.2.2.2</ServerIP>
<Port>1433</Port>
<Drivername>com.microsoft.sqlserver.jdbc.SQLServerDriver</Drivername>
<URL>jdbc:sqlserver</URL>
<DatabaseName>ProxyMRNDb</DatabaseName>
<Username>Eneter User name for proxy DB</Username>
<Password>Eneter password</Password>
</ServerName>
4.2 Proxy database schema
Following tables are required for ProxyMRNs and ProxyEncounters
Table PROXY_MRN
CREATE TABLE [dbo] . [PROXY_MRN] (
[MRN] [bigint] NULL,
[ProxyMRN] [bigint] NULL,
[SourceSystem] [varchar] ( 30 ) NULL,
[CreatedDate] [datetime] NULL
) ON [PRIMARY]
Table PROXY_ ENCOUNTER
CREATE TABLE [dbo] . [PROXY_ENCOUNTER] (
[Encounter] [bigint] NULL,
[ProxyEncounter] [bigint] NULL,
[MRN] [bigint] NULL,
[ProxyMRN] [bigint] NULL,
[SourceSystem] [varchar] ( 30 ) NULL,
[CreatedDate] [datetime] NULL
) ON [PRIMARY]
Table PROXY_MRN_PATIENT_DIMENSION
CREATE TABLE [dbo] . [PROXY_MRN_PATIENT_DIMENSION] (
[PATIENT_NUM] [bigint] NULL,
[VITAL_STATUS_CD] [varchar] ( 10 ) NULL,
[BIRTH_DATE] [datetime] NULL,
[DEATH_DATE] [datetime] NULL,
[SEX_CD] [varchar] ( 1 ) NULL,
[AGE_IN_YEARS] [int] NULL,
[LANGUAGE_CD] [varchar] ( 20 ) NULL,
[RACE_CD] [varchar] ( 100 ) NULL,
[MARITAL_STATUS_CD] [varchar] ( 20 ) NULL,
[RELIGION_CD] [varchar] ( 20 ) NULL,
[ZIP_CD] [varchar] ( 20 ) NULL,
[STATECITYZIP_PATH] [varchar] ( 12 ) NULL,
[PATIENT_BLOB] [varchar] ( 100 ) NULL,
[UPDATE_DATE] [datetime] NULL,
[DOWNLOAD_DATE] [datetime] NULL,
[IMPORT_DATE] [datetime] NULL,
[SOURCESYSTEM_CD] [varchar] ( 20 ) NULL,
[UPLOAD_ID] [int] NULL
) ON [PRIMARY]
4.3 I2b2 database schema
Under construction
5 How to use UETL
Under construction
5.1 Generate bulk-load files for i2b2DemoData schema
Under construction
5.1.1 Patient_Dimension
Under construction
5.1.2 Visit_Dimension
Under construction
5.1.3 Concept_Dimension
Under construction
5.1.4 Patient_Dimension
Under construction
5.2 Generate bulk-load files for i2b2DemoData schema
Under construction
5.2.1 I2b2 table
Under construction
5.2.2 TableAccess table
Under construction
6 Load data into i2b2
Under construction
7 Known issues
Under construction
