1. Overview

The Data Sources feature allows administrators to connect Business Intelligence (BI) to various data repositories. A data source serves as the connection point between the BI platform and business data stored in Perfex CRM or external databases.

Once a data source is configured and validated, it can be used for dataset creation, ETL processes, dashboard development, KPI tracking, and analytical reporting.

2. Creating a New Data Source

Step 1 – Open the Add Data Source Form

1. Go to Business Intelligence → Data Sources.

2. Click Add Data Source.

The Add Data Source dialog will appear.

Step 2 – General Information

• Source Name: Enter a unique and meaningful name for the data source. Examples: Perfex CRM Production,MySQL Branch (ERP),....
• Source Type: Select the source type: 
  • Perfex CRM: Connects directly to the current Perfex CRM instance
  • Database: Connects to an external database server
• Description: Provide a detailed explanation of the data source.
• Tags: Tags help organize and search data sources. Multiple tags can be assigned to a single data source.

Step 3 – Database Connection Settings

The following fields are displayed only when Source Type = Database.

• Connector:Select the database platform: 
  • MySQL
  • SQL Serve
  • PostgreSQL
• Host: Specify the database server hostname or IP address. Examples: 192.168.1.10, db.company.local, finance-db.company.local.... 
• Port: Enter the database service port. Common default ports: 3306 (MySQL), 5432 (PostgreSQL), 1433 (SQL Server). 
• Database: Enter the database name. Examples: finance_erp, customer360, sales_dw,...
• Schema: Specify the database schema. Common values: public, default (MySQL),dbo (SQL Server, public, analytics, reporting(PostgreSQL)
• User Name: Enter the database account used by BI
• Password: Enter the password for the selected database account

Step 4 – Save

After completing all required fields, click Save

3. Verifying and Exploring a Data Source

After a data source has been created successfully, open its detail page to confirm the connection works and to explore the underlying database before using it in a dataset, ETL job, or report.

4.1 Testing the Connection

On the data source detail page, click Test Connection in the top-right corner.

The system attempts to connect to the configured database or Perfex CRM instance using the saved credentials, and reports the result directly below the page title:

• Connection Status – shows Connected or Failed.

• Latency – the response time of the connection, in milliseconds.

4.2 Loading the Schema

Once the connection is confirmed, switch to the Schema tab and click Load Schema to retrieve the full list of tables from the connected database.

The tab then lists every table available in the database or Perfex CRM instance (for example tblacc_accounts, tblacc_bill_items, tblacc_budgets). Use this list to:

• Confirm the expected tables are visible and accessible.

• Identify which tables to use when building a dataset or ETL process.

• Check naming conventions before writing SQL in the Query tab.

4.3 Running Queries

To use it:

1. Open the Query tab on the data source detail page.

2. Type a SQL statement into the SQL Query editor.

3. Click Run Query to execute it and view the returned rows.

To use it:

1. Open the Query tab on the data source detail page.

2. Type a SQL statement into the SQL Query editor.

3. Click Run Query to execute it and view the returned rows.

Recommendation: Use read-only SELECT statements only. The connection normally runs on a read-only BI account, so statements that modify data (INSERT, UPDATE, DELETE, DROP) should not be used here.

Example Queries

Preview the first rows of a table:

SELECT * FROM tblacc_accounts LIMIT 10

Count the number of records in a table:

SELECT COUNT(*) AS total_accounts FROM tblacc_accounts

Filter records by a condition:

SELECT id, name, account_type_id FROM tblacc_accounts WHERE account_type_id = '1'

Aggregate values by group:

SELECT account_type_id, COUNT(*) AS total FROM tblacc_accounts GROUP BY account_type_id