Avatar Data Warehouse Introduction

Last updated
Save as PDF

The myAvatar Data Warehouse (DW), also knows as a DSS (Decision Support System), allows you to run process-intensive reports without impacting the performance of the production server. The Data Warehouse is written in Java and extracts rows of data from within Cache and inserts these rows into an Oracle, SQL Server, or Informix database.

Difference between Full Data Warehouse and Incremental Data Warehouse

The Full Data Warehouse pushes (dumps) the entire data from a table to the SQL Server.

The Incremental Data Warehouse only pushes the additions and/or changed data to the table.

Installation

The Data Warehouse is installed as a separate module under the myAvatar PM namespace.

Note: It is very important to keep your Data Warehouse module up-to-date with the latest Data Warehouse middleware and the latest Data Warehouse updates.

Setup

Configure Oracle, SQL Server, or Informix Database

A system administrator must set up the Oracle, SQL Server, or Informix Database where the rows of data will be inserted. Normally, a database is set up for each application that is included with Data Warehouse (for example, one database for myAvatar PM, one database for myAvatar CWS, and so on). All Netsmart solutions are supported by the Data Warehouse.

Configure the myAvatar Data Warehouse user within myAvatar

You must create a new myAvatar user (RADplus Utilities > System Security > User Definition) for each application that is going to utilize the Data Warehouse.

The user does not need to have access to any forms.
The user needs to be granted access to the tables that should be included when the Data Warehouse is run.

In User Definition the following table needs to be selected for the Data Warehouse to run: dss_inc_full_load.

Log Files

Log files are created based on a schedule set up by Netsmart.

myAvatar Incremental Data Warehouse

What is the myAvatar Incremental Data Warehouse?

The myAvatar Incremental Data Warehouse is an ETL (Extract, Transform, Load) solution that allows SQL tables and row data to be extracted from a myAvatar database and loaded into a secondary database.

How does the myAvatar Incremental Data Warehouse work?

The myAvatar Incremental Data Warehouse extracts data from the source database (myAvatar), transforms the data by means of a comparison/validation process, and loads the data into the target database (Data Warehouse). Row data is loaded incrementally by transferring only the data that has been modified in the myAvatar database since the last run of a Data Warehouse instance.

The validation process compares SQL table data in myAvatar with tables in the Incremental Data Warehouse and reports any columns (fields) that are missing or mismatched in the Data Warehouse tables and automatically marks them for load into the target database.

What setup is required to use the Incremental Data Warehouse?

Refer to the myAvatar Incremental Data Warehouse installation guide for details.

What is not supported by the Incremental Data Warehouse?

Any views assigned to the myAvatar Data Warehouse user will be pushed to the Data Warehouse as a full drop-and-load on a nightly basis. In addition, the following tables are also full drop-and-loads for incremental nightly runs due to their unique table structures and will also have a full drop-and-load done on them on a nightly basis.

Avatar PM / myAvatar Cal-PM

view_episode_summary_discharge
billing_guar_order_cur_ep
billing_guar_order_current
billing_guar_subs_data

Avatar MSO

table_associated_cpt_codes
table_associated_diag_codes
table_associated_revenue_codes
table_parent_map_prov_pgm
table_parentmap_per_prov
table_parent_map_perf_pgm

RADplus

RADplus_user_tables
RADplus_current_users
RADplus_users

File import tables are not supported

The File import tables should never be selected for the Data Warehouse.

Any file format errors will need to be corrected before import. These tables hold the name of the file import and not the actual data.

Data Warehouse Forms

Data Warehouse Transient Comparison Configuration form

Use the Data Warehouse Transient Comparison Configuration form to select transient (calculated at run-time) columns for an SQL table already selected for Data Warehouse load and to compare the data for these columns between the myAvatar and Data Warehouse databases to determine if any of the values have changed, and then push any rows to the target database that contain changes in the selected fields.

The Data Warehouse Transient Comparison Configuration form contains all transient fields for selection.

Dictionary Value fields are excluded.
Upon installation, no transient fields are selected.
Data entered in the Data Warehouse Transient Comparison Configuration form will be filed in the SQL table 'DW.dw_transient_compare_info'.
This form will need to be modified if you want to
- Select transient field(s) from a table to be included in the transient field comparison for the Data Warehouse load.
- Remove unnecessary transient field(s) to decrease the total runtime of the comparison process.

The user can either select specific tables or mark all tables assigned to the myAvatar Data Warehouse user for a full load.

Notification Emails are sent daily

Points to remember

Self-hosted clients entering a Support case must include the log file.
Data Warehouse does not store the tables on the SQL Server – the tables are overridden every night.

Terminology

The following terminology applies to myAvatar Data Warehouse processing.

Daily Completion flag - The table dss_completion_flag is created in the target database with the columns 'table_name" and 'dss_status' which can be queried to determine the current load status of any table in the target database. (1 - Finished, 0 - Failed, I - Incomplete [running])
Journal file - A file containing a log of updates made to the database since the last backup.
Queue Manager - A tool that maintains a queue of items that need to be sent to myAvatar as hashes so that data comparisons can be done as processors allow.
SHA1 hash - A cryptographic hash function used to verify the integrity of data. An SHA1 hash is produced at a particular point in time and compared to a later version of the data to determine if changes have been made. In this case data from the Data Warehouse is hashed and sent to myAvatar to determine if changes have been made since the data was previously submitted to the Data Warehouse.
Snapshot - A view of the data that is frozen at a particular date and time.
Source database - myAvatar Cache database
Target database - Data Warehouse database
Transient field - A field with a value that is not stored statically in a table and is determined at the time a table is queried.
Validation Tool - A tool to validate the data in the target database against the source database to ensure data integrity.