| Oracle® Healthcare Master Person Index User's Guide Release 4.0 E68430-01 |
|
|
PDF · Mobi · ePub |
| Oracle® Healthcare Master Person Index User's Guide Release 4.0 E68430-01 |
|
|
PDF · Mobi · ePub |
This chapter presents general information that will help you analyze your database requirements and what you should consider before you design an Oracle Healthcare Master Person Index database. It then provides a procedure that leads you through the process of creating an OHMPI database.
Note:
This is chapter is intended for users who need to set up a database for a project created using Oracle Healthcare Master Person Index.This chapter includes the following sections:
Before you create an OHMPI database, familiarize yourself with the database scripts and the database structure. Analyze your database requirements, including hardware considerations, startup data, indexing needs, and performance.
The following sections provide information to help you in your analysis:
Designing the Oracle Healthcare Master Person Index Database
Oracle Healthcare Master Person Index Database Table Description for sbyn_systems
Oracle Healthcare Master Person Index Database Table Description for sbyn_user_code
The wizard creates SQL scripts based on information you specified about code lists and external systems that you can use to define startup data for the Master Person Index application. When you generate the application, additional scripts are generated for creating or dropping database tables, creating and dropping tables' indexes. These scripts appear under the Database Script node of the Master Person Index project, and are named create.sql, systems.sql, codelist.sql, drop.sql, create-index.sql, and drop-index.sql. You can modify these scripts as needed to customize the tables, indexes, startup data, and database distribution. You can also create new database scripts if needed.
When configuring the master person index database, there are several factors to consider, including basic software requirements, operating systems, and disk space. This section provides a summary of requirements for the database. For more detailed information about designing and implementing the database, refer to the appropriate database platform documentation. The person responsible for the database configuration should be a database administrator familiar with the OHMPI database and with your data processing requirements.
The OHMPI database can be run on Oracle12c R1 (12.1.0.2.0 or higher) or Oracle 11gR2. You must have this software installed before beginning the database installation. Make sure you also install the latest patches for the version you are using.
The OHMPI application can be secured via Application Server provided SSL. The OHMPI application runs as an EJB application and as a WSDL-based web service. Please refer to the Oracle WebLogic Server security guides on how to configure applications using SSL.
The database can be installed on any operating system supported by the database platform you are using. See the documentation that came with your database server for more information.
This section describes the minimum recommended hardware configuration for a database installation. These requirements are based on the minimum requirements recommended by Oracle database for a typical installation. Depending on the size of the database and expected volume, you should increase these recommendations as needed. See the documentation for your database for more information and for supported operating systems.
For a Windows database server, the following configuration is recommended as a minimal installation:
Windows 2008, Windows 2008 R2 on 64 bits, or Windows 7
Pentium 266 or later
1 GB RAM (increase this based on the number of users, connections to the database, and volume)
Virtual memory should be double the amount of RAM
3 GB disk space plus an additional 2 KB for each system record to be stored in the database (note that this is a conservative estimate per system record, assuming that most records do not contain complete data). This depends on the Oracle environment you install. Enterprise Edition can take up to 5 GB.
256-color video
For a UNIX database server, the following configuration is recommended as a minimal installation:
256 MB RAM (increase this based on the number of users and connections to the database)
Swap space should be a minimum of twice the amount of RAM
2 GB disk space plus an additional 2 KB for each system record to be stored in the database (note that this is a conservative estimate per system record, assuming that most records do not contain complete data).
Note:
Disk space recommendations do not take into account the volume and processing requirements or the number of users. These are minimal requirements to install a generic database. At a minimum, the empty database and the database software will require 2.5 GB of disk space.The OHMPI database contains some common tables that are created for all implementations and some that are customized for each implementation. The common tables include standard database system tables and supporting tables, such as sbyn_seq_table, sbyn_common_header, and sbyn_common_detail. These tables do not store information about the enterprise object structure you defined. The names of the tables that store information about the enterprise object are customized based on the object structure.
Two tables store information about the primary, or parent, object you defined: sbyn_parent_object and sbyn_parent_objectsbr, where parent_object is the name you specified for the parent object in the object structure. The sbyn_parent_object table stores parent object data from each local system and the sbyn_parent_objectsbr table stores the parent object data contained in the SBRs. Similar tables are created for each child object you defined in the object structure.
For a complete description of the database tables, see Oracle Healthcare Master Person Index Message Processing Reference.
In designing the database, there are several factors to consider, such as the volume of data stored in the database and the number of transactions processed by the database daily. The OHMPI database should be created in its own tablespaces. The following sections describe some of the analyses to perform along with considerations to take into account when designing a database.
The Oracle database installation guide provides detailed information about installing the database software for optimal performance. The database platform includes guide containing information about monitoring and fine-tuning your database, including tuning memory, swap space, I/O, CPU usage, block and file size. You should be familiar with these concepts prior to creating a database.
Before defining the object structure, analyze the structure of the legacy data to help you define the object structure and the attributes of each field. You can use this data analysis to determine the amount of data that will be stored in the database, which will help you size the OHMPI database and decide how to best distribute the database. Knowing the volume of existing data plus the expected daily transaction volume will help you plan the requirements of the database server, such as networking needs, disk space, memory, and swap space.
The data structure analysis also helps you determine the processing codes and descriptions to enter in the common tables (described below), and should help you determine any default values that have been entered into certain fields that could skew the matching probability weights.
Common table data analysis involves gathering information about the abbreviations used for specific data elements in each sending system, such as system codes and codes for certain attributes of the objects in your database. For example, if you are indexing person objects, there might be processing codes for genders, such as F for female or M for male. The processing codes and their descriptions are stored in a set of database tables known as common maintenance tables. The wizard creates a script to help you load the processing codes into the database.
When an enterprise object appears on the MIDM, the master person index application translates the processing codes defined in the common tables into their descriptions so the user is not required to decipher each code. The data elements stored in the common maintenance tables are also used to populate the drop-down lists that appear for certain fields in the MIDM. Users can select from these options to populate the associated fields.
User code data analysis involves gathering information about the abbreviations used for specific data elements in each sending system for a field whose format or possible values are constrained by a separate field. For example, if you store credit card information, you might have a drop-down list in the Credit Card field for each credit card type. The format of the field that stores the credit card number is dependent on the type of credit card you select. You could also use user code data to validate cities with postal codes. The abbreviations and related constraint information are stored in the sbyn_user_code table.
When you create the master person index database, you need to consider several factors, such as sizing, distribution, indexes, and extents. By default, all of the OHMPI database tables for an Oracle database are installed in the system tablespace. You should install the OHMPI tables in different tablespaces, depending on the original size and expected volume of the database.
To begin the database installation, you first create a database instance using the provided configuration tools or command line functions. Use the tools provided by the database vendor to define the tablespace and extent sizing for the database.
When you create the database instance, you can define the distribution of your system tables, data tables, rollback logs, dump files, and control files. Use internal policies regarding relational database distribution to determine how to best distribute your OHMPI database.
The database index improves the performance of data retrieval and search database operations generically. By default, indexes are defined for the following system common tables:
sbyn_appl
sbyn_common_header
sbyn_common_detail
sbyn_enterprise
sbyn_transaction
sbyn_assumedmatch
sbyn_potentialduplicates
sbyn_audit
sbyn_merge
Also, composite indexes are defined automatically for all the combined fields used for blocking. For each query block defined in the blocking query, a composite index containing the fields in that block is created automatically. If a field in that block is standard field or phonetic-coded field, an index for its original field is also created. Indexes are optionally defined for all the fields used for matching. You can create additional indexes against the database to optimize the searching and matching processes.
The following indexes are automatically created to improve performance when running large reports from the command line or MIDM:
CREATE INDEX SBYN_POTENTIALDUPLICATES3 ON SBYN_POTENTIALDUPLICATES (TRANSACTIONNUMBER ASC);
CREATE INDEX SBYN_ASSUMEDMATCH2 ON SBYN_ASSUMEDMATCH (TRANSACTIONNUMBER ASC);
CREATE INDEX SBYN_TRANSACTION4 on SBYN_TRANSACTION (EUID2 ASC, TIMESTAMP ASC);
CREATE INDEX SBYN_TRANSACTION3 on SBYN_TRANSACTION (TIMESTAMP ASC, TRANSACTIONNUMBER ASC);
Note:
To improve performance, these four indexes should be dropped prior to performing an initial load or batch load of data. They can be recreated once the load is complete if you are running the provided reports.The OHMPI automatically creates four database tablespaces, assigns the OHMPI tables, and indexes to different tablespaces. Table 3-1 lists the database tablespaces created by OHMPI.
Table 3-1 Database Tablespaces
| Tablespace Name | Description |
|---|---|
|
<application_name>.dbf |
Application tablespace for all the OHMPI tables except the SBYN_TRANSACTION table |
|
<application_name>_transaction.dbf |
Application transaction tablespace for the SBYN_TRANSACTION table |
|
<application_name>_index.dbf |
Application indexes tablespace for OHMPI table indexes |
|
<application_name>_temp.dbf |
Application temporary tablespace for carrying out temporary database transactions, used by database system when required |
When you create an OHMPI application through the NetBeans OHMPI wizard, tablespaces are created and assigned automatically.
The commands for creating and assigning tablespaces are generated along with tables and indexes in the following scripts under the <project_home>/src/DatabaseScript directory of an OHMPI project:
create.sql
create-index.sql
By default, the tablespace is locale datafile storage with the maximum size of 100 MB. The default tablespaces are created at location specified by the Oracle database property DB_CREATE_FILE_DEST.
For example,
CREATE TABLESPACE PERSON40 DATAFILE 'PERSON40.DBF' SIZE 100M REUSE AUTOEXTEND ON;
CREATE TABLESPACE PERSON40_TRANSACTION DATAFILE 'PERSON40_TRANSACTION.DBF' SIZE 100M REUSE AUTOEXTEND ON;
CREATE TEMPORARY TABLESPACE PERSON40_TEMP TEMPFILE 'PERSON40_TEMP.DBF' SIZE 100M REUSE AUTOEXTEND ON;
CREATE TABLESPACE PERSON40_INDEX DATAFILE 'PERSON40_INDEX.DBF' SIZE 100M REUSE AUTOEXTEND ON;
When you drop OHMPI tables and indexes using the following scripts in the Database Scripts folder of an OHMPI project, the corresponding tablespaces are also dropped:
drop.sql
drop-index.sql
OHMPI automatically creates the default tablespace with the following settings when a tablespace is created:
SMALLFILE tablespace
Datafile is created at $ORACLE_HOME
Tablespace size is 100 MB and is auto-extendable
You can customize these settings according to the actual requirements and platforms. You must modify the create.sql script directly using any text editor.
Specify the BIGFILE keyword to modify the table creation statement. For example:
CREATE BIGFILE TABLESPACE PERSON40 DATAFILE 'PERSON40.DBF' SIZE 100M REUSE AUTOEXTEND ON;
Specify different DATAFILE path to modify the tablespace creation statement. For example:
For Linux:
CREATE TABLESPACE PERSON40 DATAFILE '/users/tablespace/PERSON40.DBF' SIZE 100M REUSE AUTOEXTEND ON;
For Windows:
CREATE TABLESPACE PERSON40 DATAFILE 'C:\users\tablespace\PERSON40.DBF' SIZE 100M REUSE AUTOEXTEND ON;
Change the default initial size of the tablespace to modify the tablespace creation statement. For example:
CREATE TABLESPACE PERSON40 DATAFILE 'PERSON40.DBF' SIZE 200M REUSE AUTOEXTEND ON;
For more advanced guidelines on how to manage and tune database tablespaces, see Oracle Database Administrator's Guide for 12c Release.
The following table lists and describes the columns in the sbyn_systems database table so you can create SQL statements to insert source system information into the master person index database.
| Column | Description |
|---|---|
| systemcode | The unique processing code of the system. This field accepts any of the following characters:
|
| description | A brief description of the system, or the system name. |
| status | The status of the system in the master person index system. Specify A for active or D for deactivated. |
| id_length | The length of the local identifiers assigned by the system. This length does not include any additional characters added by the input mask (see below).
Note: The default maximum length of the LID database columns is 25. If the system generates longer local IDs, be sure to increase the length of all LID columns in the database. |
| format | The required data pattern for the local IDs assigned by the system. For more information about possible values and using Java patterns, see "Patterns" in the class list for java.util.regex in the Javadocs provided with Java. Note that the pattern specified here might be further restricted by the input mask described below. |
| input_mask | A mask used by the MIDM to add punctuation to the local ID. For example, you can add an input mask to display the local IDs with hyphens or constant characters. To define an input mask, enter a character type for each character in the field and place any necessary punctuation between the types. For example, to insert a hyphen after the second and fifth characters in an 8-digit ID, the input mask would be DD-DDD-DDD. The following character types can be used; any other characters are treated as constants.
If you use an input mask, you should also define a value mask to remove the punctuation from the stored value. |
| value_mask | A mask used to strip any extra characters that were added by the input mask. This mask ensures that data is stored in the database in the correct format and matches the regex format for validation.
To specify a value mask, type the same value entered for the input mask, but type an "x" in place of each punctuation mark. Using the 8-digit input mask described above, you would specify a value mask of DDxDDDxDDD. This strips the hyphens before storing the ID in the database. |
| create_date | The date system information has inserted into the database. You can specify "sysdate" for this column, or use the variables defined in of the sample script. |
| create_userid | The logon ID of the user who inserted the system information into the database. You can enter the logon ID or use the variables defined in of the sample script. |
The following table lists and describes the columns in the sbyn_user_code database table so you can create SQL statements to insert user code data into the master person index database.
| Column Name | Description |
|---|---|
| code_list | The code list name of the user code type (using the credit card example above, this might be similar to "CREDCARD"). This column links the values for each list. |
| code | The processing code of each user code element. |
| description | A brief description or name for the user code element. This is the value that appears in the drop-down list. |
| format | The required data pattern for the field that is constrained by the user code. For more information about possible values and using Java patterns, see "Patterns" in the class list for java.util.regex in the Javadocs provided with Java. Note that the pattern might be further restricted by the value of the input mask described below. |
| input-mask | A mask used by the MIDM to add punctuation to the constrained field. For example, the input mask DD-DDD-DDD inserts a hyphen after the second and fifth characters in an 8-digit ID. If you use an input mask, you should also use a value mask to strip the punctuation for database storage (see below).
The following character types can be used.
|
| value-mask | A mask used to strip any extra characters that were added by the input mask for database storage. The value mask is the same as the input mask, but with an "x" in place of each punctuation mark. Using the input mask described above, the value mask is DDxDDDxDDD. This strips the hyphens before storing the ID. |
Once you have customized the configuration files and generated the Master Person Index application, you can create the OHMPI database. Before you begin, make sure you have Oracle installed on the database server.
During this process you can define custom startup data, such as code lists and source systems. The wizard defines SQL statements in the Code List and Systems SQL files (codelist.sql and system.sql) to insert startup data into the database based on information you specify, and you can customize the statements to insert the data relevant to your object structure. The code lists you define are used to translate processing codes from incoming messages into descriptions for the MIDM fields and to create drop-down lists for MIDM fields. System information is required in order to add records to the OHMPI application.
Follow these steps to create the database:
You can also delete the database for testing purposes using the supplied script. See Dropping Oracle Healthcare Master Person Index Database Tables for more information.
Before you begin to create the master person index database, perform an analysis of the structure of the legacy data to be stored in the database and determine the amount of data that will be processed daily. During the analysis, be sure to define the processing codes that need to be stored in the common maintenance tables and the systems that will share data with the master person index application. You should also know the length and format of the local IDs assigned by each system.
A database administrator who is familiar with your data and processing requirements should perform this task. After this task is complete, continue to Step 2: Create a Master Person Index Database and User.
For additional information and guidelines about how to set up your database, see Understanding Oracle Healthcare Master Person Index Database Scripts and Design.
Before beginning this step, complete Step 1: Analyze the OHMPI Database Requirements. After you create the database instance and user, continue to Step 3: Define Master Person Index Database Indexes if you want to define additional database indexes; otherwise skip to Step 4: Define Master Person Index External Systems.
For this step you need to create a database in which the master person index database instance will be created. Use the tools provided by your database vendor to create the database. Using these tools, you define tablespaces, including their sizes and locations; extents; and dump file, log file, and rollback file sizes and locations. Make sure these issues have been thoroughly analyzed and designed before creating the database.
Once you create the database, you can use standard SQL to create the master person index application user for the database. The user you create in this step will be used to create the database structure and to connect to the database through the application server.
For Oracle, assign the user to the "connect" and "resource" roles for the master person index tablespaces. For example:
create user <ohmpi_database_username> identified by <ohmpi_database_password>; grant connect, resource to <ohmpi_database_username>; grant create tablespace to <ohmpi_database_username>; grant drop tablespace to <ohmpi_database_username>; commit;
where username is the login ID of the administrator user and password is the login password of the administrator user.
To optimize data processing in the Master Person Index application, you can define additional indexes for the database tables that store object data. Oracle recommends that you define indexes for each field used for searching, blocking, or matching. You can define these indexes in create-index.sql or create.sql script. Oracle recommends that you use create-index.sql for any domain-relevant indexes and create.sql for any system-relevant common indexes. Before you begin this step, complete Step 2: Create a Master Person Index Database and User.
Perform one of the following:
Under the master person index project in the Projects window, expand Database Script and then open create-index.sql or create.sql in the text editor.
Navigate to project_home/src/DatabaseScript, where project_home is the location of the master person index project files. Open create-index.sql or create.sql in a text editor.
Perform one of the following:
Remove an existing index definition (not recommended).
Create new index definitions for the required fields.
Modify an existing index definition.
Save and close the file.
Continue to Step 4: Define Master Person Index External Systems.
A SQL script is automatically created to insert the systems you specified in the wizard. These statements are provided in systems.sql in the master person index project. Before you begin this step, complete Step 2: Create a Master Person Index Database and User and optionally, Step 3: Define Master Person Index Database Indexes.
Perform one of the following:
Under the master person index project in the Projects window, expand Database Script and then open systems.sql in the text editor.
On your computer, navigate to project_home/src/DatabaseScript, where project_home is the location of the master person index project files. Open systems.sql in a text editor.
For each system, create an INSERT statement using the column descriptions in Master Person Index Database Table Description for sbyn_systems to define the VALUES clause.
For example:
INSERT into sbyn_systems (systemcode, description, status, id_length,
format, input_mask, value_mask, create_date, create_userid) VALUES ('ARS',
'Automated Registration System', 'A', 8, '[0-9]{8}', 'DD-DDD-DDD',
'DD^DDD^DDD', sysdate, 'admin');
Delete any default INSERT statements you do not need.
Save and close the file.
Continue to Step 5: Define Master Person Index Code Lists.
You only need to perform this step if you defined any fields in the object structure to have a code module. The SQL script for entering processing codes and descriptions into the database is written in PL/SQL. The wizard creates a stanza in codelist.sql (located under the Database Script node of the project) for each code list you specified in the field properties. You need to customize the file by defining the entries for each code list. This script inserts data into two tables: sbyn_common_header, which lists the types of common table data, and sbyn_common_detail, which lists each common table data element. Before you begin this step, complete Step 4: Define Master Person Index External Systems.
Note:
The codes you specify in this file can be no longer than eight characters (the codes are the second value in the value list for each common table data type and data element).For information on the syntax, see To Customize Common Table Data for Oracle.
Perform one of the following:
Under the master person index project in the Projects pane, expand Database Scripts and then open codelist.sql in the text editor.
On your computer, navigate to project_home/src/DatabaseScript, where project_home is the location of the master person index project files. Open codelist.sql in a text editor.
Scroll to the following line.
codes tCodeList := tCodeList(
The statements following this line must be customized.
In the first code list stanza, change "module description" in the first line to a brief description of the code type.
For example:
-- **** PHONTYPE ****
tCode('L', 'PHONTYPE', 'TELEPHONE TYPE'),
Create the entries for the module using the following syntax:
tCode('V', 'code', 'code description'),
where "code" is the processing code of the data element and "code description" is the description of the element as you want it to appear on the Master Index Data Manager windows. For example:
-- **** PHONTYPE ****
tCode('L', 'PHONTYPE', 'TELEPHONE TYPE'),
tCode('V', 'H', 'HOME'),
tCode('V', 'C', 'CELL'),
tCode('V', 'F', 'FAX'),
tCode('V', 'O', 'OFFICE'),
tCode('V', 'HB', 'HOME BUSINESS'),
Repeat the previous two steps for each code list type defined in the file.
If you specified additional code list fields in object.xml and midm.xml after the database scripts were generated, add a new stanza for each new code type.
In the last code module stanza, make sure each line except the last contains a comma at the end.
For example:
-- **** ADDRTYPE ****
tCode('L', 'ADDRTYPE', 'ADDRESS TYPE'),
tCode('V', 'H', 'HOME'),
tCode('V', 'B', 'BUSINESS'),
tCode('V', 'M', 'MAILING')
Save and close the file.
Perform one of the following:
If you need to define user code lists, continue to Step 6: Define Master Person Index User Code Lists.
If you need to create a custom database script, skip to Step 7: Create Custom Master Person Index Database Scripts.
If you are ready to create the master person index database structure, skip to Step 8: Create the Master Person Index Database Structure.
If you specified a value for the Constrained By and User Code properties of a field, you must define the user code values for those fields. Below is a sample insert statement for the sbyn_user_code table.
insert into sbyn_user_code (code_list, code, descr, format, input_mask,
value_mask) values ('AUXIDDEF', 'CC', 'CREDIT CARD', '[0-9]{16}', 'DDDD-DDDD-DDDD-DDDD',
'DDDD^DDDD^DDDD^DDDD');
To learn more about the sbyn_user_code table, see Oracle Healthcare Master Person Index Database Table Description for sbyn_user_code. Complete Step 5: Define Master Person Index Code Lists before beginning this step.
Create a new SQL file to contain the user code SQL statements.
Use the above sample to define a value for the user code drop-down list and the required format for the dependent fields.
Repeat the above step for each drop-down list value and type (for example you might have one list for credit cards and another for postal codes and their corresponding cities).
Save and close the file.
Continue to Step 7: Create Custom Master Person Index Database Scripts or to Step 8: Create the Master Person Index Database Structure if you do not need to create any custom database scripts.
You can insert additional information into the database by creating a custom script. For information about the structure of the OHMPI database, see Oracle Healthcare Master Person Index Message Processing Reference.
Create a new text file to contain the SQL statements.
In the text editor, create the SQL script to insert the custom data.
Save and close the file.
Continue to Step 8: Create the Master Person Index Database Structure.
After you create the database instance and customize the database scripts, you can create the master person index tables and insert the custom data.
Open a standard SQL editor or SQL command line and connect to the master person index database using the user you created in Step 2: Create a Master Person Index Database and User.
Perform one of the following:
Note:
To customize the tablespaces, modify the create.sql script. For information, see Oracle Healthcare Master Person Index Database Tablespaces.Open create.sql in the NetBeans editor, copy and paste the entire text of the file into the SQL editor, and run the script against the database.
From the SQL editor or command line, run create.sql directly. The file is located at project_home/src/DatabaseScript.
Tip:
To run this script for Oracle, the command is@create.sql.Repeat the previous two steps for systems.sql, codelist.sql, create-index.sql, and any custom scripts you created.
By default, the EUIDs assigned by the master person index application start with "0", with padded zeroes added to the left to make the EUID number the correct length. For more information, see Oracle Healthcare Master Person Index Configuration Reference. You can modify this numbering format by changing the value of the seq_name column of the sbyn_seq_table database table where the sequence name is "EUID". For example:
update sbyn_seq_table set seq_count=1000000001 where seq_name='EUID';
|
 Copyright © 2011, 2016, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|