In BI Builder, you can combine datasets to display the required data on a dashboard. For example, if you need data on deals, products, and product properties, you can write an SQL query, create a new dataset, and add 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. This query will retrieve information about deals, product prices, and variant properties.
Use the following query as an example. Make sure to specify the deal ID in the crm_deal.ID line.
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';
Understanding the parts of an SQL query
SELECT: Chooses which data to extract. For example, the crm_deal.ID AS "Deal id" line pulls the ID field from the crm_deal table and labels it as "Deal id". This is how you get the deal ID and name the column.
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: Specifies the main table for the query. In this example, it's the crm_deal table that contains information about deals.
FROM crm_deal
JOIN: Connects different tables to combine their data. For example, crm_deal_product_row ON crm_deal.ID = crm_deal_product_row.DEAL_ID links the deals table with the products in deals table using their ID fields. This way, you can get a list of products in a specific 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: Filters the query results to select specific rows. For example, you can get data only about the deal with ID 325 and limit the product properties to color.
WHERE crm_deal.ID = 325 AND crm_product_property.NAME = 'Color';
Save the query as a dataset and create a chart for your dashboard.
Create a chart based on a virtual dataset
In brief
-
To blend data about deals, products, and product properties, use datasets and write an SQL query in BI Builder.
-
Save the query as a dataset and create a chart for your dashboard.