A store owner on Bitrix faces scattered data: orders in b_sale_order, traffic in Yandex.Metrica, prices in a separate price list. Manually combining them in Excel takes hours and leads to errors. We connect Yandex DataLens directly to PostgreSQL, but without proper indexes and data marts, dashboards become slow. Recently, a client with a catalog of 500 000 products and 1 million orders complained about 30‑second queries. After creating a data mart and indexes, query time dropped to 0.2 seconds. In 70% of cases, a direct connection without indexes yields response times above 10 seconds. After optimization, query times are reduced by 85% on average.
Problems we solve
-
Direct connection — straightforward access to tables
b_sale_order,b_catalog_price,b_iblock_element. But every dashboard open triggers a query to the database. If a table contains more than 500 000 rows, speed drops. We solve this by creating indexes and limiting the selection. - Data mart — aggregated tables updated on a schedule. Queries execute in fractions of a second, even on 1 million orders. However, the update delay of up to 60 minutes is not acceptable for everyone.
- ClickHouse — stream processing of events: orders, views, carts. Data freshness is near real‑time, but additional infrastructure is required.
| Option | Query speed | Database load | Data freshness | Complexity |
|---|---|---|---|---|
| Direct connection | Depends on table size | High per query | Real‑time | Minimal |
| Data mart | Instant | Low | 1‑60 minute lag | Medium |
| ClickHouse + events | High | Low | Real‑time + buffering | High |
Direct connection is 3× faster in freshness than a data mart, but a data mart is 10× faster in query speed. The choice depends on priorities. If you are unsure which option fits, get a consultation from an engineer.
Which data schema to choose?
For a store with fewer than 100 000 orders per month, direct connection works if the database has indexes. For volumes exceeding 500 000 records, a data mart reduces database load by 8–10×. ClickHouse is justified for real‑time analytics with dozens of events per second. We help select the schema after auditing your database.
How to prepare the Bitrix database for DataLens?
The cleanest option is a direct PostgreSQL connector. The database must be reachable from the Yandex cloud (white IP or VPN). We create a read‑only user:
CREATE USER datalens_ro WITH PASSWORD 'strong_password'; GRANT CONNECT ON DATABASE bitrix TO datalens_ro; GRANT USAGE ON SCHEMA public TO datalens_ro; GRANT SELECT ON b_sale_order, b_sale_order_item, b_iblock_element, b_catalog_price, b_user TO datalens_ro; Details in the official 1C-Bitrix database optimization documentation.
If the database is large (millions of orders), it is better to use a data mart. Here is an example of a daily sales mart:
CREATE TABLE datalens_orders_daily AS SELECT DATE_TRUNC('day', DATE_INSERT)::DATE AS order_date, CURRENCY AS currency, COUNT(*) AS orders_count, SUM(PRICE) AS revenue, SUM(PRICE_DELIVERY) AS delivery_total, AVG(PRICE) AS avg_order_value FROM b_sale_order WHERE CANCELED = 'N' GROUP BY 1, 2; CREATE INDEX idx_datalens_od_date ON datalens_orders_daily(order_date); Complete example of a data mart updated via agent
function UpdateDataLensOrdersVitrine() { global $DB; $DB->Query(" INSERT INTO datalens_orders_daily SELECT DATE_TRUNC('day', DATE_INSERT)::DATE, CURRENCY, COUNT(*), SUM(PRICE), SUM(PRICE_DELIVERY), AVG(PRICE) FROM b_sale_order WHERE CANCELED = 'N' AND DATE_INSERT >= NOW() - INTERVAL '2 days' GROUP BY 1, 2 ON CONFLICT (order_date, currency) DO UPDATE SET orders_count = EXCLUDED.orders_count, revenue = EXCLUDED.revenue ", false, 'FILE', __LINE__); return \CAgent::DEFAULT_PERIOD; } What's included in the setup?
- Audit of the current database and performance.
- Selection of the connection method (direct, data mart, ClickHouse).
- Creation of a read‑only user and indexes.
- Writing agents to update data marts.
- Configuring the DataLens connection and creating datasets.
- Dashboard development tailored to your KPIs: sales funnel, cohort analysis, ABC product analysis.
- Documentation on the schema and update process.
- Employee training on using dashboards.
- 3‑month warranty support.
How to connect Yandex.Metrica as a second source?
DataLens connects directly to Metrica — you only need the counter number and an OAuth token. After creating two connections (PostgreSQL + Metrica), you can build a dataset with a JOIN on date: revenue and traffic on one chart. This shows which advertising campaigns actually generate money, not just clicks.
Typical mistakes when integrating DataLens with Bitrix
| Mistake | Solution |
|---|---|
| No indexes | Create indexes on DATE_INSERT, CANCELED, PRICE |
| No read‑only user | Create a separate user with SELECT privileges |
| Agent not configured | Set up an agent with a period of 3600 seconds |
| Too many sources | Consolidate into a data mart instead of direct queries |
Work process
- Analysis — we study your Bitrix structure, table composition, and required reports.
- Design — we choose the connection method (direct, data mart, or ClickHouse) and design the schema.
- Implementation — we configure the connection, create data marts or agents.
- Testing — we verify data correctness and dashboard speed.
- Deployment — we move to production and document.
- Training — we show employees how to work with the dashboards.
Timeline
Estimated timeline: 5 to 15 business days, depending on complexity (data volume, number of sources, freshness requirements). The exact timeline is determined after auditing your database. Our experience: 15+ successful integrations of DataLens with Bitrix. Average manager time savings: 40%. With over 5 years in the industry and over 30 BI projects completed, we deliver reliable results. Warranty support: 3 months. Contact us for a cost and timeline estimate.