The PEARS data mart is a read-only nightly copy of the entire set of PEARS data for your organization(s), transformed into a more readable and usable database schema. You can connect directly to your data mart database using tools like TablePlus (
TablePlus | Modern, Native Tool for Database Management.) or MySQL Workbench (
MySQL :: MySQL Workbench). Or, you can use an Open Database Connectivity (ODBC) connector so that systems like PowerBI, Power Query, Tableau, or even Excel can access tables directly. Finally, you can access the data mart via application code in virtually any language. The database is hosted on Amazon Web Services (AWS) using an Aurora MySQL scalable Relational Database Service (RDS) instance.
Structure
Each PEARS module has a corresponding primary table in the data mart with a matching name. For example, Program Activities are stored in the program_activity table where each row is a single program activity. Sub-tables store related information where there may be more than one record for each record in the primary table. For example, a program activity may have multiple curricula. Each attached curriculum is stored as a separate row in program_activity_curriculum with a reference to the related program activity via the program_activity_id field. Following is a list of PEARS modules and the corresponding primary data mart table.
Primary Tables
| PEARS Module | Data Mart Table |
|---|---|
| Action Plans | action_plan |
| Coalitions | coalition |
| CRM | crm |
| Direct Contacts | direct_contact |
| Indirect Activities | indirect_activity |
| Partnerships | partnership |
| Professional Development | professional_development |
| Program Activities | program_activity |
| PSE Site Activities | pse |
| Quarterly Efforts | quarterly_effort |
| Social Marketing Campaigns | social_marketing |
| Success Stories | success_story |
| Surveys | survey |
Some tables contain data either used in multiple PEARS modules or that apply more broadly. All drop-down fields typically pull from a “list” table. For example, list_curriculum contains a list of all curricula options that show up in the primary and additional curricula fields in the program activities module. Similarly, list_gender contains a list of gender options used in various modules. Following is a list of supporting tables along with a brief description of each.
Supporting Tables
| Table | Description |
|---|---|
| action_log | Logs of every update and record creation by all organization users. This table may contain a large number of records |
| custom_form | Custom data form definitions |
| list_* | All the lists used throughout the site. Generally utilized in drop-downs or similar fields. |
| organization | All organizations included in this data mart. For most use cases this will contain a single organization. For state-wide data marts, this table would contain a list of all organizations included. |
| reporting_period | All reporting periods for this data mart. |
| site | Site list. Sites are connected to almost every module via the side_id key field. |
| user | All users in the organizations included |
Connectors
Applications such as PowerBI, Tableau, Excel, or Power Query require a connector to interface between the application and the data mart. The database is Aurora MySQL, but we have found the MariaDB connector to be more compatible. Be sure to download the connector matching your operating system and architecture. For most windows systems, this will be the MS Windows 64-bit connector. However, some applications utilize the 32-bit connector.
-
PowerBi or Power Query:
Download MariaDB Connectors for data access & streaming | MariaDB -
Tableau:
MariaDB