In BI Builder, you can combine datasets to show the data you need on a dashboard. For example, you can combine data about deals, products, and product properties. To do this, write an SQL query, create a dataset, and build a chart.
Description of datasets and fields for BI Builder
Open BI Builder and go to SQL > SQL Lab.
Select the bitrix24 schema. Enter your SQL query on the right and click Run. The query returns data about deals, product prices, and product properties.
Use this example query. Replace the deal ID in the crm_deal.ID with the actual deal ID.
SELECT
crm_deal.ID AS "Deal id",
crm_deal_product_row.PRODUCT_ID AS "Product id",
crm_deal_product_row.PRODUCT_NAME AS "Product name",
crm_product_property.NAME AS "Product property name",
crm_product_property_value.VALUE AS "Property value",
crm_deal_product_row.PRICE AS "Price"
FROM
crm_deal
JOIN
crm_deal_product_row ON crm_deal.ID = crm_deal_product_row.DEAL_ID
JOIN
crm_product_property_value ON crm_deal_product_row.PRODUCT_ID = crm_product_property_value.PRODUCT_ID
JOIN
crm_product_property ON crm_product_property_value.PROPERTY_ID = crm_product_property.ID
WHERE
crm_deal.ID = Deal ID AND crm_product_property.NAME = 'Color';
SQL query structure
Use SELECT to choose which data to return. For example, crm_deal.ID AS "Deal id" gets the ID from the crm_deal table and labels the column as “Deal id”.
SELECT
crm_deal.ID AS "Deal id",
crm_deal_product_row.PRODUCT_ID AS "Product id",
crm_deal_product_row.PRODUCT_NAME AS "Product name",
crm_product_property.NAME AS "Product property name",
crm_product_property_value.VALUE AS "Property value",
crm_deal_product_row.PRICE AS "Price"
Use FROM to define the main table. In this example, the main table is crm_deal, which stores deal data.
FROM
crm_deal
Use JOIN to connect tables and combine their data. For example, crm_deal_product_row ON crm_deal.ID = crm_deal_product_row.DEAL_ID links deals with the products in those deals using their IDs. This lets you get the list of products for each deal.
JOIN
crm_deal_product_row ON crm_deal.ID = crm_deal_product_row.DEAL_ID
JOIN
crm_product_property_value ON crm_deal_product_row.PRODUCT_ID = crm_product_property_value.PRODUCT_ID
JOIN
crm_product_property ON crm_product_property_value.PROPERTY_ID = crm_product_property.ID
Use WHERE to filter results. For example, you can return data only for a specific deal and limit product properties to a specific value, such as color.
WHERE
crm_deal.ID = Deal ID AND crm_product_property.NAME = 'Color';
Save the query as a dataset. Then create a chart for your dashboard.
Create a chart based on a virtual dataset
In brief
-
Combine data about deals, products, and product properties by writing an SQL query in BI Builder.
-
Save the query as a dataset and use it to build a chart.