OVERVIEW – ITX Data Warehouse
ITX Data warehouse overview video
The Datamart tables in the Primary Data Warehouse are designed to correspond to a base table in the Pro-Filer™ system. The same number of records that exist in the corresponding Pro-Filer™ table can be found in the datamart along with the denormalized data that relates to that table.
The goal of the datamart is to improve reporting in two ways.
- Speed. These are not transactional tables. They are read-only. The data has been denormalized so that the large numbers of joins in the transactional table are not required to write reports. Multiple joins can often slow down the return of data in a report.
- Simplification. Each datamart has a specific purpose. Report writers can link the datamart tables to each other in various fashions that will allow the generation of many of the reports with only one or two joins.
For example, if a report writer wanted to report on Recorded Services along with their scheduled appointments, the following join would be used:
JOIN ITX_PRIMARY_APPOINTMENT a ON r.Appointment_OID = a.Appointment_OID
This one join alone yields the necessary data from providers and coproviders for the appointment as well as all the layers of RECORDED_SERVICE -> CLIENT in one long record.
If a report writer wanted to write a census report, linking the ITX_PRIMARY_APPOINTMENT with ITX_PRIMARY_CENSUS on the BED_OID would return the information for the report.
Additionally, many of the columns found in the datamarts can be joined directly to the replicated Pro-Filer™ tables to return any additional details.
Use the replicated tables on the reporting server wherever possible, as cross database joins are unwieldy and perform slowly. Replication is close enough to real time for almost any circumstance (within seconds).