Zoho Books Connector to Power BI, Tableau, Looker Studio

Power BI Zoho Books connector by VIDI enables you to do the following:

Automatically connect your Zoho Books data to Power BI
Connect to multiple organization’s data and consolidate it in one place
Store the data in the database or data warehouse of your choice
Customize or enhance data based on your requirement

How Our Power BI Zoho Books Connector Works

Create a serverless job on the cloud of your choice to fetch the data from zoho books for all your accounts & organizations on a regular basis
Store the data in your database or data warehouse. You can either use your own database or we can create a new one for this. It can be any SQL database of warehouse for example (SQL Server, Postgresq, Mysql, BigQuery, etc.,)
Let us know the customizations you want to perform on your data, this includes any transformations that are to be performed to match with your organization’s standards
Tell us the list of tables you need in zoho books and we can fetch them
You can also tell us how your report should look like and we can store the data in a ready to consume format

This is a lightweight serverless solution which drastically reduces the cost with minimal monitoring and offers lots of customizations.

Power BI Zoho Books Connector Tables Documentation

Below are the list of tables we are extracting

Bank Accounts

Bank accounts table where you can track your transactions, have manual and automatic feeds imported for your bank and credit card accounts.

Column Name	Data Type	Description
account_id	string	ID of the Bank/Credit Card account
account_name	string	Name of the account
account_code	string	Code of the Account
currency_id	string	ID of the Currency associated with the Account
currency_code	string	Code of the currency associated with the Bank Account
currency_symbol	string	Symbol of the Currency associated with the Account
price_precision	integer	Precision of the Price Values
account_type	string	Type of the account
account_number	string	Number associated with the Bank Account
uncategorized_transactions	integer	Number of uncategorized transactions
total_unprinted_checks	integer	Number of unprinted checks.
is_active	boolean	Check if Account is Active
is_feeds_subscribed	boolean	Check if feeds are subscribed
is_feeds_active	boolean	Check if feeds are active
balance	double	Balance present in the account
bank_balance	integer	Balance present in the Bank
bcy_balance	double	Balance in Base Currency
bank_name	string	Name of the Bank
routing_number	string	Routing Number of the Account
is_primary_account	boolean	Check if the Account is Primary Account in Zoho Books
is_paypal_account	boolean	Check if the Account is Paypal Account
description	string	Description of the Account
refresh_status_code	string	Refresh Status Code of the Bank
feeds_last_refresh_date	string	Last Refreshed Date of the Feeds
service_id	string	Service ID of the Account
is_system_account	boolean	Check if the account is a system account
is_show_warning_for_feeds_refresh	boolean	Check if warning should be shown for refreshing Bank Feeds
organization_id	string	ID of the organization to distinguish
organization_name	string	Name of the organization

Bank Transactions

Bank Transactions Table where all the transactions involved in a account will be present

Column Name	Data Type	Description
transaction_id	string	Unique identifier for the transaction
date	date	Date of the transaction
amount	double	Amount of the transaction
transaction_type	string	Type of the transaction (e.g., purchase, deposit)
status	string	Status of the transaction (e.g., pending, completed)
source	string	Source of the transaction (e.g., online, manual entry)
account_id	string	ID of the associated account
account_name	string	Name of the associated account
rule_id	string	ID of the rule applied to the transaction
rule_name	string	Name of the rule applied to the transaction
is_auto_categorized	boolean	Indicates whether the transaction is automatically categorized
account_type	string	Type of the associated account
price_precision	integer	Precision of the price values
customer_id	string	ID of the customer associated with the transaction
payee	string	Payee of the transaction
is_paid_via_print_check	boolean	Indicates whether the transaction is paid via a printed check
payroll_tax_group_formatted	string	Formatted payroll tax group
description	string	Description of the transaction
currency_id	string	ID of the currency associated with the transaction
currency_code	string	Code of the currency associated with the transaction
currency_symbol	string	Symbol of the currency associated with the transaction
debit_or_credit	string	Indicates whether the transaction is a debit or credit
offset_account_code	string	Code of the offset account associated with the transaction
offset_account_name	string	Name of the offset account associated with the transaction
is_offsetaccount_matched	boolean	Indicates whether the offset account is matched with the transaction
reference_number	string	Reference number associated with the transaction
reconcile_status	string	Reconciliation status of the transaction
imported_transaction_id	string	ID of the imported transaction
is_rule_exist	boolean	Indicates whether a rule exists for the transaction
exclude_description	string	Description to exclude from categorization
is_excluded_by_system	boolean	Indicates whether the transaction is excluded by the system
is_advance_payment	boolean	Indicates whether the transaction is an advance payment
running_balance_formatted	string	Formatted running balance of the transaction
running_balance	double	Running balance of the transaction
organization_id	string	ID of the organization associated with the transaction
organization_name	string	Name of the organization associated with the transaction

Journals

Journals table used by accountants to work directly with the general ledger to create both debit and credit entries for unique financial transactions.

Column Name	Data Type	Description
journal_id	string	Unique identifier for the journal
journal_date	date	Date of the journal entry
entry_number	string	Entry number of the journal
reference_number	string	Reference number associated with the journal entry
currency_id	string	ID of the currency associated with the journal entry
status	string	Status of the journal entry (e.g., pending, completed)
notes	string	Notes or additional information about the journal entry
journal_type	string	Type of the journal entry (e.g., sales, purchase)
entity_type	string	Type of entity associated with the journal entry (e.g., customer, vendor)
total	double	Total amount of the journal entry
bcy_total	double	Total amount of the journal entry in base currency
created_by_id	string	ID of the user who created the journal entry
created_by_name	string	Name of the user who created the journal entry
organization_id	string	ID of the organization associated with the journal entry
organization_name	string	Name of the organization associated with the journal entry
cf_invoice_no	string	Custom field: Invoice number associated with the journal entry
cf_invoice_no_unformatted	string	Custom field: Unformatted invoice number associated with the journal entry
cf_invoice_date	string	Custom field: Invoice date associated with the journal entry (formatted)
cf_invoice_date_unformatted	string	Custom field: Unformatted invoice date associated with the journal entry

Opening Balances

Opening Balances table is required while migrating from existing accounting software to Zoho Books, you need to ensure that the transition is flawless, that all prevailing data such as journal entries, records, expense and income statements etc, have been recorded and continuity in financial statements is maintained. To ensure this, an opening balance needs to be calculated.

Column Name	Data Type	Description
journal_id	string	Unique identifier for the journal entry
journal_date	date	Date of the journal entry
entry_number	string	Entry number of the journal entry
reference_number	string	Reference number associated with the journal entry
currency_id	string	ID of the currency associated with the journal entry
status	string	Status of the journal entry (e.g., pending, completed)
notes	string	Notes or additional information about the journal entry
journal_type	string	Type of the journal entry (e.g., sales, purchase)
entity_type	string	Type of entity associated with the journal entry (e.g., customer, vendor)
total	double	Total amount of the journal entry
bcy_total	double	Total amount of the journal entry in base currency
created_by_id	string	ID of the user who created the journal entry
created_by_name	string	Name of the user who created the journal entry
organization_id	string	ID of the organization associated with the journal entry
organization_name	string	Name of the organization associated with the journal entry
cf_invoice_no	string	Custom field: Invoice number associated with the journal entry
cf_invoice_no_unformatted	string	Custom field: Unformatted invoice number associated with the journal entry
cf_invoice_date	string	Custom field: Invoice date associated with the journal entry (formatted)
cf_invoice_date_unformatted	string	Custom field: Unformatted invoice date associated with the journal entry
exchange_rate	double	Exchange rate for the currency
debit_or_credit	string	Indicates whether the transaction is a debit or credit
product_name	string	Name of the product
currency_code	string	Code of the currency
account_split_id	string	ID of the account split
account_id	string	ID of the account
bcy_amount	double	Amount in base currency
total_formatted	string	Formatted total amount
product_stock_rate_formatted	string	Formatted product stock rate
branch_id	string	ID of the branch
product_id	string	ID of the product
account_name	string	Name of the account
branch_name	string	Name of the branch
bcy_amount_formatted	string	Formatted amount in base currency
product_stock	double	Product stock
product_stock_rate	double	Product stock rate
account_type	string	Type of the account
account_type_formatted	string	Formatted type of the account
date	date	Date of the transaction
opening_balance_id	string	ID of the opening balance
date_formatted	string	Formatted date of the transaction

Chart Accounts

Chart Accounts table, where Chart of Accounts in Zoho Books consists of a wide range of accounts that are generally used with any type of business. The accounts are classified into different types such as Income, Expense, Equity, Liability & Assets.

Column Name	Data Type	Description
account_id	string	ID of the account
account_name	string	Name of the account
account_code	string	Code of the account
account_type	string	Type of the account
description	string	Description of the account
is_user_created	boolean	Indicates whether the account was created by a user
is_system_account	boolean	Indicates whether the account is a system account
is_active	boolean	Indicates whether the account is currently active
can_show_in_ze	boolean	Indicates whether the account can be shown in a specific application (e.g., Zoho)
parent_account_id	string	ID of the parent account
parent_account_name	string	Name of the parent account
depth	integer	Depth level of the account hierarchy
has_attachment	boolean	Indicates whether the account has attachments
is_child_present	boolean	Indicates whether the account has child accounts
child_count	integer	Number of child accounts
documents	string	Documents associated with the account
created_time	string	Time when the account was created
is_standalone_account	boolean	Indicates whether the account is a standalone account
last_modified_time	string	Time when the account was last modified
organization_id	string	ID of the organization associated with the account
organization_name	string	Name of the organization associated with the account

Chart Account Transactions

Chart accounts transactions table. Where chart account transactions related to the chart account will be present

Column Name	Data Type	Description
categorized_transaction_id	string	ID of the categorized transaction
transaction_type	string	Type of the transaction
transaction_id	string	ID of the transaction
transaction_date	date	Date of the transaction
transaction_type_formatted	string	Formatted type of the transaction
account_id	string	ID of the account associated with the transaction
customer_id	string	ID of the customer associated with the transaction
payee	string	Payee of the transaction
description	string	Description of the transaction
entry_number	string	Entry number of the transaction
currency_id	string	ID of the currency associated with the transaction
currency_code	string	Code of the currency associated with the transaction
debit_or_credit	string	Indicates whether the transaction is a debit or credit
offset_account_name	string	Name of the offset account associated with the transaction
reference_number	string	Reference number associated with the transaction
debit_amount	double	Amount debited from the account
credit_amount	double	Amount credited to the account
fcy_debit_amount	double	Foreign currency amount debited from the account
fcy_credit_amount	double	Foreign currency amount credited to the account
organization_id	string	ID of the organization associated with the transaction
organization_name	string	Name of the organization associated with the transaction
act_id	string	ID of the activity associated with the transaction

Above are some of the tables which are mainly used for report generation to get insights on what’s happening in your zoho books. We can fetch more tables based on the requirement of your report.