Prepare your data for import to hila

hila’s Conversational Finance application provides a standard schema for you to ingest your data. However, you can also ingest data with custom schemas and an external database.

• Standard hCF schema — prepare your data for ingestion according to the standard Conversational Finance schema, continue with the rest of this topic.
• Custom schema — prepare your data for ingestion with a custom schema, see Supporting a new domain and new schema.
• Custom schema from CSV or PARQUET files — prepare your data for ingestion with a custom schema from CSV or PARQUET files, see Ingest custom data from a file.
• Ingest data from an external database — prepare your data for ingestion from a database, see Ingest data from an external database.

Conversational Finance schema

Preparing your data for Conversational Finance involves two steps:

1. Prepare your data (customer task)
2. Configure hila to ingest the data (Professional Services or client IT task)

Prepare your data

You need to prepare your data for Conversational Finance in the form of CSV or PARQUET files that you give to your Professional Services or client IT contact for the next step.

To set up and populate Conversational Finance with pilot data for your organization, you need to prepare and provide the data in a standard format. You can do this by running a custom report in your financial system and exporting the data to a file with the expected columns and data. Or you can export data from your financial system in an Excel file and then transform this data to this standard format.

The Conversational Finance data model consists of fact tables (journal and plan) and dimension tables. The following tables show the relationships between these fact and dimension tables:

Fact tables

The fact tables are:

• Journal fact table
• Plan fact table

Journal fact table

Journal table contains transaction data from your general ledger in a normalized format. The following columns are available in the journal table:

Comments:

• Most of the columns are not required; however, the diversity of questions you can ask depends directly on the data being available in all or most of these columns.

Plan fact table

The plan table contains actual, budget, and forecast values at your desired aggregation level and combinations. Actual values may come from journal table or from other external sources. Budget and forecast values are expected to come from planning systems. The planning systems may also feed in actual values to plan table if these values are different from journal values. So the design supports “loose integration” of journal and plan tables. However, both journal and plan fact tables use the same dimension tables in the data model.

Dimension tables

The following dimension tables must be populated:

• Account table
• Channel table
• Company table
• Cost center table
• Customer table
• Department table
• Division table
• Fiscal calendar table
• Fiscal period table
• Material table
• Material group table
• Product table
• Product group table
• Profit center table
• Supplier table

Account table

Account table contains general ledger account information. The following columns must be populated in this table for each of the accounts that appear in the journal table:

Comments:

• account_type column is used to categorize accounts into different types like Revenue and Expense.
• account_category column is used to categorize accounts into different categories like Cost of Goods Sold, Operating Expenses, etc.

Channel table

Provides details about the sales channels used by your organization. This table is optional, but it can help you analyze sales performance across different channels.

Company table

Company provides details about your company or companies.

Cost center table

Provides details about the cost centers in your organization. Use to track expenses and revenues for specific departments or functions within your organization.

Customer table

Provides details about your customers. This table is optional, but it can help you analyze sales performance across different customer segments.

Department table

Provides details about the departments in your organization. Use to categorize costs and revenues for specific functions or areas within your organization.

Division table

Provides details about the divisions in your organization. Use to categorize costs and revenues for specific business units or divisions within your organization.

Fiscal calendar table

If your organization follows a fiscal year calendar that is aligned with a calendar year, then you just need to provide the start date of the fiscal year. For example, if your fiscal year starts on 1st January, the fiscal year and calendar year are expected to be identical. If your fiscal year starts on 1st of July for example, then Q1 of the fiscal year would be 1st July to 30th September, Q2 would be 1st October to 31st December, and so on. In such cases, Professional Services will be able to generate a fiscal calendar table for queries automatically. However, if your organization follows a non-standard fiscal year calendar, you will need to provide a fiscal_calendar.parquet file.

Comments:

• If your organization follows a fiscal year calendar that is aligned with a calendar year, then you just need to provide the start date of the fiscal year.

Fiscal period table

Provides details about the fiscal periods in your organization. This table is optional, but it can help you analyze financial data across different fiscal periods.

Material table

Provides details about the materials used in your organization. This table is optional, but it can help you analyze costs and revenues associated with specific materials.

Material group table

Provides details about the material groups in your organization. This table is optional, but it can help you analyze costs and revenues associated with specific material groups.

Product table

Provides details about the products in your organization. This table is optional, but it can help you analyze costs and revenues associated with specific products.

Product group table

Provides details about the product groups in your organization. This table is optional, but it can help you analyze costs and revenues associated with specific product groups.

Profit center table

Provides details about the profit centers in your organization. Use to categorize costs and revenues for specific business units or profit centers within your organization.

Supplier table

Provides details about the suppliers in your organization. Use to track costs and revenues associated with specific suppliers.