We've been integrating Bitrix24 and building analytical dashboards for over 8 years. In nearly every second project we hear: "Our analytics team compiles reports manually in Excel, exporting once a week, and the data is stale by Monday." Our solution is an ETL pipeline with a direct connection from Power BI to the Bitrix24 REST API. Dashboards refresh automatically up to 8 times a day. This cuts manual data assembly costs by up to 80% and saves dozens of hours monthly.
How to choose the right approach?
Option 1 — Power BI Dataflow + REST API. Configured in Power BI Service via the "Web" connector using a Bitrix24 webhook. Suitable for small volumes — up to 50,000 records. Downside: no error control and a limit on request count.
Option 2 — ETL process → intermediate database → Power BI. The intermediate layer is PostgreSQL or MariaDB. An ETL script (Python or Node.js) extracts data from the REST API, transforms it, and loads it into analytical tables. Power BI connects to the database via ODBC. This is a production-ready approach: more reliable, faster, and not dependent on API quirks during report refresh.
For most clients we recommend the second option. It provides flexible scheduling, denormalized slices, and easy scaling as data grows.
Production ETL architecture
Bitrix24 REST API → Python ETL → PostgreSQL (analytical schema) → Power BI The ETL script runs on cron every hour. The database schema uses denormalized tables optimized for analytical queries. Unlike the Bitrix24 OLTP schema, there are minimal JOINs: fact tables (deals, leads) and dimension tables (users, stages, funnels).
-- Fact table for deals CREATE TABLE b24_deals ( id BIGINT PRIMARY KEY, title TEXT, stage_id VARCHAR(50), amount NUMERIC(15,2), currency VARCHAR(3), assigned_id INT, contact_id INT, company_id INT, created_date TIMESTAMP, closed_date TIMESTAMP, pipeline_id INT ); -- Dimensions CREATE TABLE b24_users ( id INT PRIMARY KEY, full_name TEXT, department TEXT, email TEXT ); CREATE TABLE b24_stages ( id VARCHAR(50) PRIMARY KEY, name TEXT, pipeline_id INT, sort INT, is_final BOOL ); Data export via REST API in Python
The Bitrix24 REST API returns data in pages of 50 records. Iterative collection is a simple loop:
import requests import psycopg2 WEBHOOK = "https://your-domain.bitrix24.ru/rest/1/token/" PG_CONN = "postgresql://user:pass@localhost/analytics" def fetch_all(method, params=None): """Paginated fetch from B24 API""" items, start = [], 0 while True: r = requests.post(WEBHOOK + method, json={ **(params or {}), "start": start }).json() items.extend(r.get("result", [])) if r.get("next") is None: break start = r["next"] return items def sync_deals(): deals = fetch_all("crm.deal.list", { "select": ["ID","TITLE","STAGE_ID","OPPORTUNITY","CURRENCY_ID", "ASSIGNED_BY_ID","CONTACT_ID","COMPANY_ID", "DATE_CREATE","CLOSEDATE","CATEGORY_ID"], "filter": {">=DATE_MODIFY": last_sync_timestamp()}, }) conn = psycopg2.connect(PG_CONN) cur = conn.cursor() for d in deals: cur.execute(""" INSERT INTO b24_deals VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT (id) DO UPDATE SET stage_id=EXCLUDED.stage_id, amount=EXCLUDED.amount, closed_date=EXCLUDED.closed_date """, (d["ID"], d["TITLE"], d["STAGE_ID"], ...)) conn.commit() This approach ensures data is up-to-date: every hour the script fetches only records whose DATE_MODIFY field has changed. This field is supported for all CRM objects per the REST API documentation.
Key metrics for the dashboard
Power BI builds the dashboard on top of analytical tables. Typical visualizations:
| Metric | Source |
|---|---|
| Deal funnel by stage | b24_deals GROUP BY stage_id |
| Revenue by manager | b24_deals JOIN b24_users |
| Lead-to-deal-to-payment conversion | b24_leads JOIN b24_deals |
| Average time in stage | Calculated from stage transition timestamps |
| Manager workload (active deals) | Filter WHERE is_final = false |
Why choose ETL with an intermediate database?
Direct Dataflow connections often break during data refresh due to REST API limitations. An ETL layer solves this: errors are logged, missed batches are retried. Plus, you denormalize the data in advance, speeding up dashboard rendering by 3–5 times.
Data refresh in Power BI Service
In Power BI Service, you configure a refresh schedule for the dataset: connect to PostgreSQL via an On-premises data gateway (if the database is local) or directly to cloud PostgreSQL. Refresh frequency ranges from once per day (free plan) up to 8 times per day (Premium Per User).
For real-time sales monitoring, we set up DirectQuery instead of Import Mode. The downside: queries hit the database every time the report is opened, increasing database load.
How often are data refreshed and what does incremental load deliver?
With over 100,000 deals, a full reload every hour is wasteful. With Power BI Premium, you can set up Incremental Refresh: the system automatically determines the range of new data by date and loads only the changes. This reduces load on the CRM and speeds up report updates.
| Task | Effort |
|---|---|
| ETL script (Python + psycopg2) | 8–12 hours |
| Analytical database schema | 4–6 hours |
| Power BI dashboard (5–7 reports) | 8–16 hours |
| Schedule & monitoring setup | 3–4 hours |
What's included in the work
Each Bitrix24-to-Power BI integration project includes:
- Documentation of data schema and REST API limitations
- Development of an ETL script with incremental load
- Creation of an analytical database (PostgreSQL/MariaDB)
- Power BI dashboard with 5–7 reports (funnel, revenue, conversion, workload)
- Data refresh configuration and error monitoring
- Training for staff on dashboard usage
We guarantee support for 3 months after delivery. Over 8 years, we have completed more than 120 Bitrix24 integration projects with various systems.
We'll assess your project for free – write to us to discuss the details. Order a turnkey integration: from data audit to a Power BI dashboard.
Contact us for a consultation – we'll help you choose the best approach for your volumes.