Docstoc

General Data Mart - Conceptual Design v_3_0

Document Sample
General Data Mart - Conceptual Design v_3_0 Powered By Docstoc
					        National Data Warehouse


       General Data Mart
       Conceptual Design
       Version 3.0
       April 2005




                                                                       Department of Health and
                                                                              Human Services

                                                                                Indian Health Service

                                                                                  Office of Information
                                                                                     Technology (OIT)


U:\Data Warehouse\Contracting Issues\General Data Mart - Conceptual Design v_3_0.doc
         6/3/2005 10:24 AM
Contents
Version Control................................................................................................................ iii
Overview .............................................................................................................................1
Physical Environment........................................................................................................2
Customer Support..............................................................................................................2
Appendix A – NDW Schemas and Tables .......................................................................4
  Target Tables .................................................................................................................4
  Reference Tables............................................................................................................4
  Metadata Tables .............................................................................................................7




 National Data Warehouse                                                                                    General Data Mart
                                                                  ii                                        Conceptual Design
Version Control
Version              Date                         Notes

1.0       April 2005        First Draft

2.0       May 2005          Final

3.0       May 2005          Updated to describe requirements for FY 2006




National Data Warehouse                                        General Data Mart
                                    iii                        Conceptual Design
Overview
The government has requested that the NDW system provide a General Data Mart that will
provide authorized users with more advanced statistical and analytic skills direct access to the
data for individual ad hoc queries. We have been asked to:

 1. Complete the design and then build, implement, and support a General Data Mart.

 2. Design, build, and then administer access controls that allow users to only access data
    they are authorized to use.

 3. Design the system so that its availability to users is maximized (compared to the time it
    is shut down for data refresh, system maintenance, etc.).

 4. Design the system so that data marts could be loaded from this data mart.

 5. Monitor system resource use by individual queries so that if a query exceeds a set,
    reasonable threshold, it can be placed on hold and the user contacted.

The General Data Mart will contain, per customer request,
•   A complete set of the NDW target tables (i.e., current versions of the physical structures of
    the NDW database).
•   A complete set of the current snapshots of all registration records (i.e., data).
•   A subset of the current snapshots of all encounter records dated within 4 years of the current
    date (i.e., data).

The General Data Mart will be created with the following parameters:
•   The General Data Mart will exist on a separate server from the NDW Production
    Environment.
•   The General Data Mart will be implemented using the three new servers currently being
    installed. No migration will be necessary when the fourth server is installed.
•   The representative data in the General Data Mart database will be a copy of live production
    data. It will not include any scrambling, cleansing, or encryption; or use other methodologies
    to disguise patient identifiable data.
•   All data within the NDW General Data Mart will be refreshed monthly.
•   The maximum disk space allowed for the General Data Mart will be 140 GB.
•   The NDW system owner working in collaboration with the NDW data owner, will specify to
    the contractor those who will be granted access to which data in this data mart, as well as the
    time period during which they will be granted access.
•   Only authorized users will be allowed access to the General Data Mart.
•   Security controls commensurate with those in the production NDW database and adhering to
    IHS standards (as outlined in separate security documents), will be enforced.


 National Data Warehouse                                                           General Data Mart
                                                  1                                Conceptual Design
•   The General Data Mart will be enterprise compliant, to allow various environments to access
    the database, including ODBC, JDBC, OLE, CLI. These are some of the environments and
    protocols that may be used to access the General Data Mart depending on the user’s needs,
    access protocols, and environments.
•   SAS DB2 connect software will be installed on the server that houses the General Data Mart
    as provided by the IHS.


Physical Environment
Users of the General Data Mart will be granted READ ONLY access to the data. The database
will contain the following schemas and/or tables:

    REG              Current Patient Registration data

    ENCTR            Current Encounter data (4-years worth)

    REF              NDW reference tables

    META             Only DW_INFO and TABLES tables – data dictionary data

    SYSIBM           Only SYSCOLUMNS and SYSTABLES tables – data dictionary data

For a complete list of NDW schemas and tables, see Appendix A.

The General Data Mart will be populated with all current registration data and all current
encounter data within 4 years of the date the mart is being populated with the data.

Authorized users will be assigned one of the following data access categories:

 1. National Level access – these users will be allowed to see all data in the General Data
    Mart database.

 2. Area Level access – these users will be allowed to see all data within their specified
    Area only.


Customer Support
NPIRS/NDW-provided Customer Support will include the following:

 1. Data dictionary (DW_Info and Table tables from Meta schema)

 2. Training Guide, which will include information not readily apparent from viewing the
    model or data dictionary

The NPIRS/NDW staff will provide individual customer support to its users, including
responding to questions about table structure, relationships, content, indexes, data types, and any
specific features of Data Mart design that may affect the efficiency and accuracy of queries.


 National Data Warehouse                                                         General Data Mart
                                                 2                               Conceptual Design
They will also assist customers in connecting to the data mart by providing Data Mart-specific
information such as network addresses and other connection parameters and by addressing any
questions specific to Data Mart connection issues. The users of this Data Mart are expected to be
highly skilled in designing queries and so the NPIRS contractor will not be asked to provide
instruction on how to structure queries, perform table joins, etc.

The NPIRS/NDW staff will install SAS/ACCESS software to assist SAS users to query the
General Data Mart using SAS. The NPIRS contractor will provide basic customer support for
SAS/ACCESS (e.g., any special configuration settings required to connect to this Data Mart using
this application, help troubleshooting connection problems [level 1, generalist-type support, not
expert SAS/ACCESS application support], provide DB2’s ODBC driver for the user’s
workstation, etc.), but will not troubleshoot installation problems for DB2’s ODBC driver on the
client’s workstation, provide support once the data has been successfully downloaded to the
user’s SAS application, or provide support or training for SAS or any other SAS applications.

NPIRS/NDW staff will not provide COTS product-specific support (e.g., SAS, SPSS, EpiInfo,
etc. application support) for the General Data Mart, except as noted above and in other
documents. 1 Additional COTS product-specific support will be provided by the vendor.
However, it is assumed that clients (in the venue of user groups), vendors (in the role of customer
support for their products), and NPIRS/NDW staff may come to an agreement on the specific
roles and support requirements necessary for the successful deployment and use of the General
Data Mart.

 In order to comply with customer’s request to monitor system resource use by individual
 queries so that if a query exceeds a set, reasonable threshold, it can be placed on hold and
 the user contacted we will need IBM’s DB2 Query Patroller to
    •   stop runaway processes
    •   schedule queries (little ones can go before big ones)
    •   track which queries run when
    •   limit the number of queries that can be run at the same time.




            1
             For example, the contractor does provide certain COTS product-specific support for its implementations of IBM’s
            DB2 and related productions, Crystal Reports, and SAS/Access (as described in this document).


 National Data Warehouse                                                                           General Data Mart
                                                           3                                       Conceptual Design
Appendix A – NDW Schemas and Tables

Target Tables
                                    Included or
Schema       Table
                                    Excluded
REG          ALIAS                  Included
REG          CHART                  Included
REG          DEMOGR                 Included
REG          INSUR_ELIG             Included
REG          PAT_MED                Included
REG          PAT_REG                Included
REG          SSA_VERIF              Included
ENCTR        ADA_PROC               Included
ENCTR        CLIN_MEAS              Included
ENCTR        DX                     Included
ENCTR        ENCTRSS                Included
ENCTR        ENCTRSS_CONTRACT       Included
ENCTR        ENCTRSS_DENTAL         Included
ENCTR        ENCTRSS_INPAT          Included
ENCTR        ENCTRSS_MISC           Included
ENCTR        ENCTRSS_PHN            Included
ENCTR        EXAM                   Included
ENCTR        HCPCS_PROC             Included
ENCTR        HEALTH_FACTOR          Included
ENCTR        IMMUN                  Included
ENCTR        LAB_TEST               Included
ENCTR        MEDICATION             Included
ENCTR        PAT_EDUCATION          Included
ENCTR        PAT_SKIN_TEST          Included
ENCTR        PROCEDURE              Included
ENCTR        PROVIDER               Included
ENCTR        TEETH                  Included



Reference Tables
                                    Included or
Schema       Table
                                    Excluded
REF          ADA                    Included



National Data Warehouse                           General Data Mart
                                4                 Conceptual Design
                                        Included or
Schema       Table
                                        Excluded
REF          ADMISSION                  Included
REF          AFFILIATION                Included
REF          AREA                       Included
REF          BENEF_CLASS                Included
REF          BLOOD_QUANTUM              Included
REF          CHS_INP_DISPOS             Included
REF          CLINIC                     Included
REF          CLINICAL_MEASURE           Included
REF          COUNTY                     Included
REF          DAY_OF_WEEK                Included
REF          DEN_PATIENT_TYPE           Included
REF          DENTAL_DELIVERY            Included
REF          DENTAL_OPSITE              Included
REF          DISCHARGE_TYPE             Included
REF          DX_CAUSE                   Included
REF          DX_RECODE_CLASS_XREF       Included
REF          EDUC_UNDERSTAND            Included
REF          EDUCATION                  Included
REF          ER_DISPOS                  Included
REF          ERROR_CATEGORY             Included
REF          EXAM_IHS                   Included
REF          FAC_LOC_TYPE               Included
REF          FACILITY                   Included
REF          FACILITY_COPY              Included
REF          FACILITY_HIST              Included
REF          FACILITY_TYPE              Included
REF          GENDER                     Included
REF          HL7_IMMUN_CVX              Included
REF          HL7_IMMUN_MVX              Included
REF          HL7_IMMUNIZATION           Included
REF          ICD9_DISEASE_CLASS_XREF    Included
REF          ICD9_DX                    Included
REF          ICD9_EXT_INJ_CAUSE         Included
REF          ICD9_INP_XREF              Included
REF          ICD9_PROC                  Included
REF          ICD9_PROC_CLASS_XREF       Included
REF          IHS_IMMUNIZATION           Included
REF          INJURY_PLACE               Included
REF          INP_DISPOS                 Included



National Data Warehouse                               General Data Mart
                                    5                 Conceptual Design
                                         Included or
Schema       Table
                                         Excluded
REF          INP_RECODE                  Included
REF          INP_RECODE_CLASS_XREF       Included
REF          INSURANCE_CAT               Included
REF          LOINC                       Included
REF          MED_COND_CAT                Included
REF          OLD_INJ_CAUSE               Included
REF          OLD_INJ_PLACE               Included
REF          PAY                         Included
REF          PHN_ACTIVITY                Included
REF          PHN_INTERV_LEVEL            Included
REF          PHN_PROB_ICD9_XREF          Included
REF          PHN_PROBLEM                 Included
REF          PHN_PROG_AREA               Included
REF          PROV_X12_CLASS              Included
REF          PROV_X12_SPEC               Included
REF          PROV_X12_TYPE               Included
REF          PROVIDER_DISC               Included
REF          PROVIDER_TYPE               Included
REF          RECORD                      Included
REF          REGION                      Included
REF          RESERVATION                 Included
REF          SERVICE                     Included
REF          SERVICE_LEVEL               Included
REF          SERVICE_UNIT                Included
REF          SHALL_TEST                  Included
REF          SKIN_TEST                   Included
REF          SKIN_TEST_RESULT            Included
REF          SRC_SYSTEM                  Included
REF          SSN_LOCAL_VERIF             Included
REF          SSN_SSA_VERIF               Included
REF          STATE                       Included
REF          STATUS                      Included




National Data Warehouse                                General Data Mart
                                     6                 Conceptual Design
                                 Included or
Schema       Table
                                 Excluded
REF          SVC_CATEGORY        Included
REF          SVC_ELIG            Included
REF          SVC_TYPE            Included
REF          TRIBE               Included
REF          VA_DRUG_CLASS       Included




Metadata Tables
                                 Included or
Schema       Table
                                 Excluded
SYSIBM       SYSCOLUMNS          Included
SYSIBM       SYSTABLES           Included
ADMIN        EXPORT_INFO         Excluded
ADMIN        LOAD_ERRORS         Excluded
META         COLUMNS             Excluded
META         CONTACTS            Excluded
META         DATA_AUTH           Excluded
META         DW_INFO             Included
META         DW_MAP              Excluded
META         EXP_INIT_LOAD       Excluded
META         HL7_INFO            Excluded
META         ISSUES              Excluded
META         REF_TABLE           Excluded
META         REPORTS             Excluded
META         RPMS_NOTES          Excluded
META         TABLES              Included
META         TECH_NOTES          Excluded




National Data Warehouse                        General Data Mart
                             7                 Conceptual Design

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:5
posted:4/11/2010
language:English
pages:10