BigQuery table field reference

General rules for working with views

All Bimp data is available as views in the “bimp_public” dataset of the “Bimpsoft” project. The view and field names in this reference match those you see and use in queries, Looker Studio and Power BI. For connection, see BigQuery and Looker Studio and Power BI.

Rule	What it means
Fields with the _guid suffix	A unique identifier for linking to the corresponding directory. For example, “item_guid” links a fact to “v_dim_nomenclature”
“company_id”	Present in every table; the company identifier (for companies with several databases or branches)
“exported_at”, “created_at”, “updated_at”	Technical dates of export or record change, not a business date. For reports use “document_date” or “movement_date”
End-of-month balances	The “opening_” columns are values at the start of the month, “closing_” at the end. The current month is not filled in the calculated views because it is not finished
Batches	Product balances are kept by batches. If the company does not track batches, “batch_guid” is empty in all rows. If it does, aggregate by batches when you do not need the batch
Two currencies	In the cash views, “amount” and “_balance” are in the company’s base currency, and “_currency” is in the currency of the account or cash desk. Do not mix them in one sum
Prices	Updated once a day, the rest of the data more often. If a report shows an “old” price, first check “exported_at”

Which views are available

The “bimp_public” dataset contains directories (dimension), facts (fact), cash views and calculated views.

View	What it contains	Group
v_dim_nomenclature	Products and services: goods, services, variants	Directory
v_dim_counterparties	Counterparties: customers, suppliers	Directory
v_dim_employees	Employees	Directory
v_dim_business_lines	Lines of business	Directory
v_dim_nomenclature_groups	Product groups	Directory
v_dim_projects	Projects	Directory
v_dim_warehouses	Warehouses	Directory
v_dim_organizations	Organizations (the company’s legal entities)	Directory
v_dim_financial_items	Income and expense items	Directory
v_dim_equipment	Equipment (customers’ equipment)	Directory
v_dim_equipment_groups	Equipment groups	Directory
v_cash_accounts	Accounts and cash desks	Directory
v_customer_price	Price lists for customers	Directory
v_fact_sales	Sales	Fact
v_fact_orders	Orders (invoices)	Fact
v_fact_income_expenses	Income and expenses	Fact
v_fact_repairs	Repairs	Fact
v_fact_additional_costs	Additional costs for sales	Fact
v_fact_service_costs	Cost price of services	Fact
v_fact_stock_movements	Product movements by warehouse	Fact
v_cash_movements	Cash movements	Fact
v_customer_prices	Prices for customers	Fact
v_dim_monthly_stock_balances	Monthly warehouse balances	Calculated
v_monthly_cash_balances	Monthly cash balances	Calculated

v_dim_nomenclature: products and services

The “v_dim_nomenclature” view contains goods, services and their variants (characteristics). It is this view’s “item_guid” that is used in the fact views.

Field	Description
company_id	Company
item_guid	Unique identifier of a product or service (used in the fact views)
product_guid	Identifier of the base product (if the row is a variant or characteristic)
variant_guid	Identifier of the product variant (characteristic)
product_name	Name of the base product
variant_name	Name of the variant or characteristic
full_name	Full name (product + variant)
is_variant	This is a variant (characteristic), not a main product
article	Item code
group_guid	Product group (link to v_dim_nomenclature_groups)
unit_name	Unit of measure
is_service	Service (not a physical product)
is_archived	Archived (inactive) product
is_kit	Kit (a set of several products)
barcode	Barcode
weight, length, width, height, volume	Weight, length, width, height, volume
country_of_origin	Country of origin
description	Product description
min_stock_level	Minimum stock level in the warehouse
is_batches_tracked	Batches are tracked
is_serial_tracked	Serial numbers are tracked
created_at, updated_at	Date the record was created and last changed

v_dim_counterparties: counterparties

The “v_dim_counterparties” view contains customers and suppliers. The key is “contractor_guid”.

Field	Description
contractor_guid	Unique identifier of the counterparty
company_id	Company
status	Counterparty status
name	Full name
print_name	Name for printing in documents
priority	Counterparty priority
parent_contractor_guid	Parent counterparty (if part of a group or holding)
owner_name	Person responsible for the counterparty
email, phone	Contact details
lead_source	Source the customer came from
is_customer	Is a customer
is_supplier	Is a supplier
is_individual	Individual (not a company)
country, region	Country, region
tax_id	Tax ID (taxpayer number)
reg_code	EDRPOU (company registration code)
author_name	Who created the record
manager_name	Manager responsible for the counterparty
is_archived	Archived (inactive) counterparty
created_at, updated_at	Date created and last changed
comment	Comment on the counterparty
contact_name	Name of the counterparty’s main contact

v_dim_employees: employees

Field	Description
employee_guid	Unique identifier of the employee
company_id	Company
name	Full name
position	Position
status_name	Status (employed / dismissed)
hire_date	Hire date
dismissal_date	Dismissal date
comment	Comment
is_archived	Archived record
created_at, updated_at	Date created and last changed

v_dim_business_lines: lines of business

Field	Description
business_line_guid	Unique identifier of the line of business
company_id	Company
name	Name of the line of business
comment	Comment
is_archived	Archived line of business
is_main	Main line of business
parent_business_line_guid	Parent line of business (if there is a hierarchy)
warehouse_guid	Linked warehouse
created_at, updated_at	Date created and last changed

v_dim_nomenclature_groups: product groups

Field	Description
nomenclature_group_guid	Unique identifier of the group
company_id	Company
name	Group name
comment	Comment
is_archived	Archived group
parent_nomenclature_group_guid	Parent group (hierarchy of product groups)
created_at, updated_at	Date created and last changed

v_dim_projects: projects

Field	Description
project_guid	Unique identifier of the project
company_id	Company
name	Project name
comment	Comment
is_archived	Archived project
manager_name	Project manager
parent_project_guid	Parent project (hierarchy)
created_at, updated_at	Date created and last changed

v_dim_warehouses: warehouses

Field	Description
warehouse_guid	Unique identifier of the warehouse
company_id	Company
name	Warehouse name
is_main	Main warehouse
is_archived	Archived warehouse
is_public	Public warehouse (available to everyone)
is_active	Active warehouse
is_group	Warehouse group (not a physical warehouse, but a grouping)
warehouse_kind	Warehouse type
comment	Comment
address	Address
is_counterparty_warehouse	Counterparty’s warehouse (for example, consignment)
parent_warehouse_guid	Parent warehouse (hierarchy)
created_at, updated_at	Date created and last changed

v_dim_organizations: organizations

The view contains the company’s legal entities.

Field	Description
organization_guid	Unique identifier of the organization
company_id	Company
name	Organization name
legal_name	Legal name
inn_code	Tax ID
edrpou_code	EDRPOU (company registration code)
legal_address	Legal address
director_name	Director
phone_number	Phone
organization_type	Organization type
is_main	Main organization
is_vat_payer	VAT payer
is_archived	Archived organization
created_at, updated_at	Date created and last changed

v_dim_financial_items: income and expense items

Field	Description
financial_item_guid	Unique identifier of the item
company_id	Company
item_type	Item type (income / expense)
name	Item name
group_name	Name of the item group
parent_guid	Parent item (hierarchy)
rgk_item	Linked cash flow statement item, if applicable
activity_kind_name	Kind of activity (operating / investing / financing)
is_archived	Archived item
exported_at	Date the record was exported

v_dim_equipment: equipment

The view contains customers’ equipment.

Field	Description
equipment_guid	Unique identifier of the equipment unit
company_id	Company
name	Equipment name
is_archived	Archived record
created_at, updated_at	Date created and last changed
equipment_group_guid	Equipment group (link to v_dim_equipment_groups)
model	Model
serial_number	Serial number
manufacture_year	Year of manufacture
counterparty_guid	Equipment owner (counterparty)
equipment_kind	Kind of equipment
comment	Comment
acceptance_date	Date of acceptance for accounting
warranty_months	Warranty period, months

v_dim_equipment_groups: equipment groups

Field	Description
equipment_group_guid	Unique identifier of the equipment group
company_id	Company
name	Group name
parent_group_guid	Parent group (hierarchy)
is_archived	Archived group
created_at, updated_at	Date created and last changed

v_cash_accounts: accounts and cash desks

The view contains the organizations’ own bank accounts and the company’s cash desks. Counterparties’ accounts are not included.

Field	Description
account_guid	Account or cash desk
company_id	Company
account_kind	bank_account (bank account) or cash_desk (cash desk)
name	Name
code	Code in the directory
currency_guid	Currency
currency_code	Currency code per ISO 4217
bank_name	Bank (accounts only)
account_number	Account number
mfo	Bank MFO code
is_main	Main account or cash desk
is_archived	Archived
is_deleted	Marked for deletion
comment	Comment
exported_at	Date the record was exported

v_customer_price: price lists for customers

The “v_customer_price” view is a directory of price lists. The prices themselves are in “v_customer_prices”.

Field	Description
price_list_guid	Unique identifier of the price list
company_id	Company
name	Price list name
comment	Comment
is_main	Main price list
is_archived	Archived price list
currency_code	Price list currency code per ISO 4217

v_fact_sales: sales

The “v_fact_sales” view contains the rows of sales of goods and services. One row corresponds to one item of a sales document.

Field	Description
sale_id	Unique identifier of the sales row
company_id	Company
movement_date	Date and time of the sale
item_guid	Product or service
batch_guid	Product batch
batch_name	Batch name
organization_guid	Selling organization
counterparty_guid	Customer
contract_guid	Contract
order_guid	Linked order (invoice)
business_line_guid	Line of business
project_guid	Project
warehouse_guid	Warehouse the sale was made from
responsible_employee_guid	Responsible employee
quantity	Quantity
amount_base	Sales amount in base currency, including VAT
amount_without_vat_base	Amount without VAT, in base currency
vat_amount_base	VAT amount, in base currency
amount_currency	Amount in the document currency
document_currency_code	Document currency code
goods_cost_amount	Cost price of the goods sold
operation_type	Sales operation type
sales_document_guid	Base document for the sale
document_type	Sales document type
document_number	Sales document number
document_date	Sales document date
order_number	Order (invoice) number
order_date	Order (invoice) date
order_type	Order document type
contract_number	Contract number
contract_name	Contract name
exported_at	Date the record was exported

v_fact_orders: customer orders

The “v_fact_orders” view contains orders (invoices) by rows.

Field	Description
row_id	Unique identifier of the order row (order + row number)
order_guid	Identifier of the order (document)
company_id	Company
order_date	Order date
order_number	Order number
organization_guid	Selling organization
counterparty_guid	Customer
project_guid	Project
business_line_guid	Line of business
comment	Order comment
responsible_employee_guid	Responsible employee
warehouse_guid	Shipping warehouse
planned_shipment_date	Planned shipment date
status_name	Order status
contract_name	Contract name
contract_number	Contract number
line_number	Row number in the order
item_guid	Product or service in the order row
quantity	Quantity
amount_without_vat	Row amount without VAT
vat_amount	Row VAT amount
amount_with_vat	Row amount including VAT
discount_amount	Discount amount
exported_at	Date the record was exported

v_fact_income_expenses: income and expenses

Field	Description
movement_id	Unique identifier of the cash movement or item
company_id	Company
movement_date	Operation date
document_guid	Base document
document_type	Document type
document_date	Document date
document_number	Document number
business_line_guid	Line of business
project_guid	Project
financial_item_guid	Income or expense item
counterparty_guid	Counterparty
organization_guid	Organization
income_amount	Income amount
expense_amount	Expense amount
exported_at	Date the record was exported

v_fact_repairs: repairs

Field	Description
movement_id	Unique identifier of the record
company_id	Company
movement_date	Operation date (handover or acceptance)
document_guid	“Acceptance and handover for repair” document
document_number	Document number
counterparty_guid	Customer
warehouse_guid	Warehouse
project_guid	Project
business_line_guid	Line of business
responsible_guid	Responsible employee
reason_for_service	Reason for the request or description of the fault
acceptance_date	Date the equipment was accepted for repair
completion_date	Repair completion date
return_date	Date the equipment was returned to the customer
item_guid	Product, spare part or service related to the repair
quantity	Quantity
order_guid	Linked order
order_date	Order date
order_number	Order number
order_type	Order document type
exported_at	Date the record was exported

v_fact_additional_costs: additional costs for sales

Field	Description
company_id	Company
document_guid	Additional costs document
item_guid	Product the cost is allocated to
batch_guid	Product batch
sales_document_guid	Sales document the cost is attributed to
movement_date	Operation date
cost_amount	Additional cost amount
exported_at	Date the record was exported

v_fact_service_costs: cost price of services

Field	Description
company_id	Company
document_guid	Base document
item_guid	Service or product
order_guid	Linked order
movement_date	Operation date
cost_amount	Cost price amount
exported_at	Date the record was exported

v_fact_stock_movements: product movements by warehouse

Field	Description
company_id	Company
movement_id	Unique identifier of the movement
document_guid	Base document of the movement
document_type	Document type
document_number	Document number
document_date	Document date
movement_date	Date and time of the product movement
warehouse_guid	Warehouse
item_guid	Product
batch_guid	Product batch
batch_name	Batch name
quantity	Movement quantity
amount	Movement amount
sign	Movement direction: receipt or write-off (+1 / −1)
exported_at	Date the record was exported

v_cash_movements: cash movements

The “v_cash_movements” view covers receipts and expenses on accounts and cash desks: payments, transfers between accounts and cash desks, receipt and repayment of loans and investments.

Field	Description
company_id	Company
movement_id	Row key: document_guid:row number
document_guid	Base document
document_type	Document type
document_number	Document number
document_date	Document date
movement_date	Movement date
account_guid	Account or cash desk
account_kind	bank_account or cash_desk
cash_type_guid	Cash type
cash_type_name	Cash type name
currency_guid	Currency
currency_code	Currency code per ISO 4217
amount	Amount in base currency
amount_currency	Amount in the currency of the account or cash desk
sign	Movement direction: receipt or expense (+1 / −1)
comment	Payment purpose
exported_at	Date the record was exported

v_customer_prices: prices for customers

The “v_customer_prices” view contains the current prices of products for each price list. It is a snapshot of current prices, not a history of changes: for each “product + price list” pair there is exactly one row with the latest price. The view is updated once a day at 02:00, so a price changed during the day appears the next night.

Field	Description
company_id	Company
item_guid	Product (for products with characteristics, the specific characteristic)
price_list_guid	Price list (link to v_customer_price)
price	Price
currency_code	Currency code per ISO 4217; taken from the price list
price_date	Date from which this price applies
exported_at	Moment the snapshot was taken, the same for all rows of one update

v_dim_monthly_stock_balances: monthly warehouse balances

Calculated views are not exported from the accounting system directly: the rows are calculated by accumulating movements. The “v_dim_monthly_stock_balances” view shows product balances by warehouse at the start and end of each completed month.

Field	Description
company_id	Company
warehouse_guid	Warehouse
item_guid	Product
batch_guid	Product batch (empty if the company does not track batches)
month_start	First day of the month the row belongs to
net_quantity	Net movement of product quantity for the month
opening_balance	Quantity balance at the start of the month
closing_balance	Quantity balance at the end of the month
net_amount	Net movement of value for the month
opening_amount	Value of the balance at the start of the month
closing_amount	Value of the balance at the end of the month
average_cost	Cost price of a unit of the balance: closing_amount / closing_balance
calculated_at	Date the record was calculated

v_monthly_cash_balances: monthly cash balances

The “v_monthly_cash_balances” view shows turnovers and balances for each account or cash desk at the start and end of a completed month, in the base currency and in the account currency.

Field	Description
company_id	Company
account_guid	Account or cash desk
account_kind	bank_account or cash_desk
currency_code	Currency code per ISO 4217
month_start	First day of the month the row belongs to
net_amount	Turnover for the month in base currency
opening_balance	Balance at the start of the month
closing_balance	Balance at the end of the month
net_amount_currency	Turnover for the month in the account currency
opening_balance_currency	Balance at the start of the month in the account currency
closing_balance_currency	Balance at the end of the month in the account currency
calculated_at	Date the record was calculated

Which views are not described in the reference

Two views in the dataset have no field descriptions.

View	Note
v_fact_sales_enriched	A derived view on top of sales; the set of fields depends on its definition
test_whoami	Service view for checking access; contains no business data