Prepare your data for import to hila

hila’s Conversational Analytics application provides a standard schema for you to ingest your data. However, you can also ingest data with custom schemas.

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

Conversational Analytics schema

Preparing your data for Conversational Analytics involves two steps:

1. Prepare the 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 Analytics 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 Analytics 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 Analytics 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 is directly dependent on 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
• Config 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

Company table

Company provides details about your company or companies.

Config table

Cost center table

Customer table

Department table

Division table

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

Material table

Material group table

Product table

Product group table

Profit center table

Supplier table

Configure hila

After you have prepared the data, send the files to your Professional Services or client IT contact. They will configure hila to ingest the data and set up Conversational Analytics for your organization.