# Data Mart {% embed url="" %} 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](https://tableplus.com/) or Power BI. You can also use an Open Database Connectivity (ODBC) connector so that other systems like 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 a PostgreSQL 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. ### Primary Tables | PEARS Module | Data Mart Table | | ----------------------------- | ----------------------------------------------------------------- | | Action Plans | `action_plan` | | Coalitions | `coalition` | | Community Events | `event` | | Community Relationships (CRM) | `crm_person`, `crm_interaction`, `crm_category` | | Direct Contacts | `direct_contact` | | Indirect Activities | `indirect_activity` | | Partnerships | `partnership` | | Professional Development | `professional_development_event`, `professional_development_plan` | | Program Activities | `program_activity` | | PSE Site Activities | `pse` | | Quarterly Efforts | `quarterly_effort` | | Social Marketing Campaigns | `social_marketing` | | Success Stories | `success_story` | | Surveys | `survey` | ### Supporting Tables 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. | 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 `site_id` key field. | | `user` | All users in the organizations included. | ## Data Dictionary If your organization has the data mart, you have access to the data dictionary. The data dictionary provides a set of pages within PEARS to browse all tables within the data mart, including their columns and relationships with other tables. For more information, see [Data Dictionary](/analyze/data-mart/data-dictionary.md). ## Data Tools Further instructions are provided for the common data tools of R and Power BI. If you plan to utilize one or both of these tools, reference the corresponding documentation to get started. * [Setting up Power BI](/analyze/data-mart/setting-up-power-bi.md) * [Setting up R](/analyze/data-mart/setting-up-r.md) ## Other Connectors Other applications such as Tableau or Excel may require a connector to interface between the application and the data mart. Be sure to download the connector matching your operating system and architecture. For most Windows systems, this will be the 64-bit connector. However, some applications utilize the 32-bit connector. * Tableau: [PostgreSQL Connector](https://www.tableau.com/support/drivers) * ODBC: [Driver Information](https://www.postgresql.org/docs/current/odbc.html) * [ODBC Downloads Site](https://www.postgresql.org/ftp/odbc/versions/msi/) ## Security Your data mart lives behind a firewall with access granted only to whitelisted IP addresses. As part of the setup, the PEARS team will request an IP address or range to use when configuring access. If this changes, or if you need access from a new machine or server, reach out to the team and we'll work with you to update the firewall rules. {% hint style="warning" %} **NOTE:** The fewer IP addresses we whitelist, the better. Although we will support a range of IP addresses, please minimize the range. {% endhint %} A unique user name with a strong password will be assigned to your organization and shared via 1Password. This will include the host name, port, user, and password. All connections enforce SSL using TLS 1.2 or greater. {% hint style="warning" %} **NOTE:** Store your credentials securely and do not share the host, port, or other information via email or other insecure communication methods. {% endhint %} ## Questions Have questions about the data or getting connected? Reach out to the PEARS Support team and we can help get you set up and building. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://support.pears.io/analyze/data-mart.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.