Data Warehouse Accelerator

With every iteration of the software development life cycle, the 1-Answer mission is getting the answer to these three big questions:

  • What are your ETL needs?
  • How can we better meet those needs?
  • How can we improve technical performance?

  • Our answer lies in the Data Warehouse Accelerator (DWA). The DWA improves each part of the systems development life cycle.  The process involves the following:

  • Pre-Built Data Models
  • Data Disovery & Mapping
  • SSIS Package Autogeneration
  • Unit Testing
  • Pre-Built Data Models

    What is a Data Model?
    Data models support data and systems by providing a single definition and format for data applications required to exchange and integrate data.  A data model represents the framework of what the relationships are in a database.  This framework will provide the structure which will support the analytical needs of the decision makers. The data itself will be stored within this framework on a database. This step requires your business people to engage in the process by participating in facilitated sessions with our data architects.

    Based on your company’s specific data requirements, 1-Answer is able to architect solutions by identifying gaps in pre-built data models and tailoring these models and reports to address your requirements. By combining 1-Answer industry expertise with Business Intelligence and our significant knowledge of data integration, we can help you achieve immediate business value while setting you up for long-term sustainability.

    Why use a Pre-Built Data Model?

    For starters, this model provides a starting point for asking requirements questions. Next,  Pre-Built Data Models are less likely to miss important components or make modeling errors due to not recognizing common possibilities. Finally, projects take less time and cost less because they can be quickly customized.  The 1-Answer Pre-Built Data Models establish a comprehensive data architecture supporting requirements for Providers, Members and Claims:

     

    Provider Pre-Built Data Models

    Provider Relational Data Model

    The Provider Relational Data Model, designed in the Third Normal Form Model (3NF) , holds all Transactional (OLTP) Elements.  Tables like Credentials for Providers are available in 3NF but not in Dimensional Layer unless needed.

    Relational Schemas:

    • The Relational Tables are Segmented by Schema Which Represents a Logical Grouping of Tables For Each Subject Area
    • Inheritance in Relational Model keeps data in Third Normal Form Contact_ID is same number as Person_ID for faster query results


    Provider Dimensional Data Model

    The 1-Answer Provider Dimensional Data Model provides operational capability, supports Patient Safety and Quality measurements.  It includes a Provider Network Search which enables trending of provider coverage. In addition,  it enables analysis by locations at different levels, such as Postal Code and Groupings like “neighborhood”.  Hospitals, insurance companies and other healthcare sites will benefit from this model to help improve clinical outcomes, reduce waste and prepare for new methods of delivering care required by health reform.

    Dimensions and Key Attributes Include:

    • Provider: Provider Demographics
    • Facility: Facilities operating at
    • Service Location: Service Locations operating at
    • Employer: Provider Employer details
    • Employee: Provider Employment details like hire date, etc.
    • Practice: Provider Practice details
    • Office Hours: Hours Open Week and Weekend
    • Panel: Accepting new patients
    • Credentialing: Provider Credentials
    • Language: Languages spoken
    • Ranking: Health Grades or other Ranking
    • Specialty: Standardized Specialty Dimension
    • Date: Hierarchies for Year, Month, Date (Calendar and Fiscal)

    Membership Pre-Built Data Model

    Membership Dimensional Data Model

    The 1-Answer Membership Dimensional  Data Model shows the relationship between database tables that contain information about enrollment, membership trends, lapse analysis and loyalty.

    Dimensions and Key Attributes Include

    • Membership Statistics:
      • Membership totals at end of month
      • New Members
      • Dropped Members
      • Rejoined Members
    • Member: Member Demographics, Employment, Medicare FCFA Number, etc
    • Member Enrollment: Programs Member is enrolled in
    • Line of Business: Hierarchy of Business Unit, Group Category, Program, Brand, etc.
    • Benefit Package: Plan Type, Is ACA, Metal Level, etc.
    • Geolocation: Address, County, Lat/Long, Congressional District, Customizable Groupings for neighborhood or other groups of postal codes, etc.
    • Statistic: Statistic name (New Member, Dropped Member, etc.)
    • Date: Hierarchies for Year, Month, Date (Calendar and Fiscal)

    Claim Pre-Built Data Model

    Claim Dimensional Data Model

    The 1-Answer Claim Dimensional  Data Model enables advanced claim analytical capabilities for:

    • Optimizing Loss Ratio
    • Highest Paid Claims
    • Claim Summary Analysis

    Concise policy information is detailed down to the coverage and risk parameter level. Claim information includes individual payments, historical reserve estimates and full tracking of claim status changes.  The Claim Data Model consists of 19 business areas including Diagnosis, DRG, Provider, Insurance Plan and so on:

    Dimensions and Key Attributes Include

    • Payer: Payer Type and Customizable Categorization
    • Provider Practice: Hierarchy of Business Unit, Group Category, Program, Brand, etc.
    • Provider: Provider Demographics
    • Specialty: Standardized Specialty Dimension
    • Date: Hierarchies for Year, Month, Date (Calendar and Fiscal)
    • Facility: Facility of Service
    • Service Location: Location of Service
    • Diagnosis: ICD10 and ICD9 Standard Dimension with Customizable Categorization
    • DRG: Diagnosis Related Group
    • Procedure: Procedure Standard Dimension
    • Patient: Patient demographics
    • Patient Group: Deidentified patient grouping
    • Insurance Contract Holder: Contract Holder Insurance Information
    • Insurance Plan: Insurance Plan Details
    • Benefit Package: Plan Type, Is ACA, Metal Level, etc.

    The Claim Fact and Dimensions tables contain details of the EDI 837 Transaction Set. The claim information included amounts to the following, for a single care encounter between patient and provider:

    • A description of the patient
    • The patient’s condition for which treatment was provided
    • The services provided
    • The cost of the treatment

    Data Mapping

    Mapping data from the data source to the destination is a core task of BI solutions, and often a very time-consuming element of the process. Some key characteristics that make the DWA Web App tool surpass the competition include fast data profiling, integrated data dictionary, and quick mapping between multiple layers. The DWA Web App integrates profiled data to aid data discovery and help the architect rapidly determine where the incoming data fits in the data model.

    When the model needs to be expanded upon, it is easy to add model elements within the Web App tool. The DWA map helper also allows the customer to customize numerous portions of the data mapping for each layer. Examples include Type 1, 2 columns, default values and derived column logic. DWA Excel templates are also available that effortlessly integrate the mappings in Excel. With these templates, users refresh data within excel without the need to export each time changes are made.

    1-Answer Data Mapping Example

    The entire DWA suite centers around data mapping. Once the data has been mapped, the magic of the Autogeneration begins by generating table creation scripts with consistent system columns. Then the auto-generation process uses these mappings to build SSIS packages based on various templates. Also, boundary test cases are derived from the profiled data and are integrated into the SSIS packages for testing various data without making any package changes.

    SSIS Package Automation

    Problem

    Within Microsoft SQL Server Integration Services (SSIS), your team can build robust packages to manage your ETL solutions. On the other hand, every ETL project has some repetitive tasks: you may need to import 10 different flat file exports from the HIS system, you need to load 15 dimensions into the data warehouse and so on. With standard SSIS functionality, your team is forced to create multiple individual packages. This can be very time consuming due to the lack of easy code reuse. In a typical BI project, the ETL implementation can easily take up 75% of the project time, so it is beneficial if the development time of similar SSIS packages can be cut down.

    Solution

    1-Answer offers your team a powerful, metadata driven solution – SSIS Package Autogeneration.  Metadata is the information that describes other data – in other words, ‘data about data’.  It is crucial to the efficiency of your data warehouse’s ability to classify and categorize data. With our SSIS Package Autogeneration you can swiftly generate SSIS packages based on metadata, allowing your team to effectively reduce the SDLC time by up to 75%.

    During SSIS Autogeneration, packages which moves data between the layers are built, modified, converted to XML, then converted back into a new package, based on data that was previously inserted into your mapping tables. Over 20 packages are auto generated in less than 1 minute.

    Layers:
    • Historical
    • API
    • Relational
    • Dimensional
    1-Answer Data Mapping

    The autogeneration process will check for errors by validating the XML as well as the resulting SSIS packages. For example, if a destination table used in a data flow does not already exist, an error will be thrown. Packages will not be generated if an error is found during compilation.

    Testing

    Automated ETL Testing

    ETL testing is done before data is moved in a Production Data Warehouse.  The main objective is to identify and mitigate data defects and general errors prior to processing of data for analytical reporting. This can be a challenging process because it often involves comparing large volumes of data between heterogeneous data sources. When ETL Testing is done manually, users are only able to test sample set of data because they compare data in excel. Since full volume of data is not compared, the ETL process might break in the production environment or incorrect data might get loaded because of limited test coverage. 1-Answer provides automated ETL testing:  the key to achieve 100% data validation and improved time to market.

    One of the key aspects of 1-Answer testing is the use of ETL Templates, which provide robust error handling.

    ETL Templates:
    • Log execution detail and row counts (inserted, updated, deleted)
    • Log error info
    • Log error rows to common table in XML
    • Unit Test Helper generates test data with full variations possible, based on business logic and test data.
    Testing Tables Enable Automated Testing:

    One of the benefits of automated ETL testing software is ability to do regression testing. 1-Answer enables users to perform regression testing via test templates which allows users to combine all the testing through 6 test sets.

    • Tests are organized into sets for more easily understanding test groups in ETL.Test_Set
    • Individual Tests described in ETL.Test
    • Test data can be described in ETL.Test_Row table
      • Input in xml format Input_Row_Xml
      • Expected results in xml Expected_Result_Row_Xml
    Test Sets
    1-Answer ETL Test Sets