Data Foundation: The Common Finance Model

Data Foundation is the finance data foundation for your PlaidCloud projects. Its Common Finance Model brings together ERP data from multiple ERP systems, and multiple instances of each, into a single model: one chart of accounts, one set of entities and one reporting currency. Reports, metrics and AI assistants built on the model all read the same numbers, whichever ERP they came from.

Your ERP connects through its PlaidCloud connection, so the general ledger, trial balance, master data and budgets arrive directly from the source, with nothing to export. Files add data a system doesn’t hold, such as currency rates, allocation drivers or a budget kept outside the ERP, and can replace the ERP rows they cover. Load conforms every source into the model, translates it into the reporting currency and checks that it ties out, all from the project’s Foundation tab. Templates, such as Enterprise Profitability, build their reports and models on top of it.

Every ERP system and every instance lands in one model, so every report, metric and AI answer reads the same numbers.

Connect Your ERP

Your ERP connects to Data Foundation through its PlaidCloud connection. Data Foundation reads the general ledger, trial balance, master data, exchange rates and budgets directly from it, and only ever reads. You can pick an existing connection or create one on the tab; the connection guides cover each ERP’s access setup.

1. In Analyze, open Projects and click New Project. Enter a name, and under Start from, choose a template, such as Enterprise Profitability (template), instead of Blank project. Click Create.

   The project opens on its Foundation tab. It is private to you, and you are its only Architect, until you share it.

2. On the Foundation tab, pick an existing connection for your ERP, or choose Create a new connection. Each existing connection lists the workflows that already use it, or says Not used by any workflow yet.

3. For a new connection, enter a Connection name, choose the Environment, and fill in the credentials your ERP connection asks for.

   Don’t have the credentials yet? The form shows a ready-made access request for your ERP administrator. Click Copy request or Email request, send it, and enter the values they send back.

4. Click Test connection. PlaidCloud signs in and tries to read each dataset, then lists each as Readable, Missing permission or Error. When something is missing, it names the permissions to ask your ERP administrator to add.

5. Set Months of history: how many months back to load, from 1 to 120. The default is 24.

6. For an existing connection, tick the entities to load, or leave them all clear to load every entity. Inactive and elimination entities stay hidden until you choose to show them. Then click Connect.

7. For a new connection, click Connect to create it. Then tick the entities to load and click Connect again to save them. Until you do, every entity is loaded.

Once connected, the card says which connection, environment, entities and months of history the project loads.

Each load reads only what changed: open periods and the last closed period are read again, and closed periods already loaded are kept. Raising Months of history reads just the added months. A changed exchange rate, entity crosswalk or reporting currency reads again the months it affects.

Add Files for Data Outside Your ERP

Files supplement your ERP connection with data a system doesn’t hold, such as currency rates, allocation drivers, an Entity crosswalk or a budget kept outside the ERP. Each file belongs to one of the datasets listed under Common Finance Model Tables.

1. Under Files, choose the Document account to store the file in.

2. Drop a CSV or Excel file on Drop a CSV or Excel file here, or click Choose a file. The file is uploaded to the /foundation/ folder of the document account.

   For a file that is already in the document account, enter its File path, for example /foundation/budget.csv, and click Add file.

3. PlaidCloud reads the file and proposes a mapping:

   • Detected delimiter, decimal separator and dates. The delimiter (comma, semicolon, tab or pipe), the decimal separator and the date format are detected for you.
   • Dataset: the Common Finance Model table the file belongs to, matched from the file name and column headings. When the match isn’t clear, choose it from the list.
   • Decimal separator: Period (1,234.56) or Comma (1.234,56).
   • Date format: shown when the file has dates. Change it if the detected format is wrong: YYYY-MM-DD, DD.MM.YYYY, DD/MM/YYYY or MM/DD/YYYY.
   • A grid of Column in file, Type, Examples and Goes to. Change Goes to to send a column to a different field, or to Don’t load.

4. Check where each column goes, then click Confirm mapping.

When PlaidCloud can’t decide the dataset, the decimal separator or the date order on its own, for example a file whose dates could read as day/month or month/day, it asks you to choose before you confirm. Adding a second file for the same dataset replaces the first.

Accepted files are CSV, TSV and TXT text files, Excel .xlsx files (read from the first sheet) and Parquet files.

Note

A file that is missing or empty never stops a load. Adding it says there is nothing to map yet, and a load finishes with a warning that names the path, leaving that dataset empty. Add the file again once it exists.

Load Your Data

Click Load on the Load card or the Last load card. The load reads your connected ERP and your confirmed files, conforms them into the Common Finance Model, translates them into the reporting currency, runs the readiness checks and rebuilds the template’s models.

• Only one load runs for a project at a time. A second Load says a load is already running.
• If other workflows use the same ERP connection, the first load lists them and asks you to confirm, because a load reads from the ERP and can slow those workflows down. Click Load anyway to go ahead.
• A failed load shows red and never hides behind earlier results. Rows from an earlier load show From an earlier load. Load again. until a load succeeds.
• If one entity and period fails to read, the rest still load. Run Load again to fetch only what failed.

ERP Data and Files Together

A file replaces only what it covers, and the other rows from your ERP stay.

• Master files (accounts, departments, customers, products and Segment values) replace the ERP rows with the same code.
• Transaction files replace the ERP rows for the same entity and month. General ledger and trial balance files also match by ledger, budget files by scenario, and statistical driver files by scenario and driver.

If a file later stops covering a month, the ERP’s data for that month comes back on the next load.

Entity Crosswalk

Each source names its entities with its own codes. The Entity crosswalk maps those codes to the entities in your entity master, so lines from every source land on the same entity.

To use one, add a CSV or Excel file and choose the Entity crosswalk dataset:

Column	Holds
source_entity	The code as the source carries it, such as a company code.
entity	The entity it belongs to, as the entity master names it.

• A code that isn’t in the crosswalk is used as it is.
• Several codes can map to one entity. Their lines are combined, and a month counts as closed once it is closed for all of them.
• Codes in the crosswalk apply to every source, so each source’s entity codes must be unique across sources.
• An Entities file row replaces the ERP’s entity with the same entity code.

The Unmapped entities check flags any line whose entity is neither in the entity master nor mapped to one by the crosswalk. Until an entity master is loaded, it shows – Not applicable.

Currency Translation

Each load translates the general ledger, trial balance, sales, inventory and budget amounts from their local currency into one reporting currency. Reports and models read the translated amounts, so entities in different currencies, from any system, total correctly.

The Reporting Currency

To choose the reporting currency, set the project variable reporting_currency to a currency code such as USD. See Managing Project Variables. The template creates it blank. While it is blank, each load uses the first of these that names exactly one currency:

1. The group currency of the entities.
2. The local currency of the top-level entities, the ones with no parent.
3. The local currency of the files.

If none of them names exactly one currency, Currency translation shows a problem until you set reporting_currency.

Amounts already in the reporting currency stay as they are, so a project whose data is all in one currency needs no rates.

Sales, inventory and budget files can carry a currency too. Map a column named Currency or Local Currency and it is picked up automatically, and the file’s local amounts are translated to the reporting currency. A group or reporting amount that is already in the file is kept as it is. Product standard cost is not translated.

Exchange Rates

A rate converts one unit of a currency into the reporting currency for a month: the translated amount is the local amount times the rate.

• From your ERP, each load reads the exchange rates the ERP holds.
• From a file, add a CSV or Excel file and choose the Currency rates dataset. A rate in the file replaces the ERP’s rate for the same currencies and month. Use a file to report in a currency your ERPs don’t publish rates into, or to add planned rates for budget months.

Column	Holds
from_currency	The currency to translate from, for example EUR.
to_currency	The reporting currency, for example USD.
period	The month, as YYYY-MM.
average_rate	The average rate for the month.
closing_rate	The rate at the end of the month.
historical_rate	The historical rate, for equity and for accounts set to the historical rate.

Only rates into the reporting currency are used. A rate quoted the other way round, from the reporting currency into another currency, isn’t inverted.

Which Rate Applies

Each row is translated from its own currency, so a line in a different currency from its entity is translated from the currency it is in. The first rule that fits the row applies:

Row	Rate
Statistical and non-posting accounts	Not translated.
Already in the reporting currency	Not translated.
Account with a rate type	That rate: average, closing or historical. Historical falls back to closing when there is no historical rate. From an ERP, the rate type is the account’s own rate type. In a chart of accounts file, map a rate type column to fx_rate_type with average, closing or historical.
Sales line	The average rate for the period.
Inventory balance	The closing rate for the period.
Budget row	By account, like the general ledger: the average rate for profit and loss accounts and the closing rate for balance sheet accounts.
Profit and loss account	The average rate for the period.
Equity account	The historical rate, or the closing rate when there is no historical rate.
Any other balance sheet account	The closing rate for the period.

Translated amounts aren’t rounded, so totals don’t drift.

Note

The trial balance holds each period’s movements, so balance sheet amounts are each month’s movements translated at that month’s closing rate. They aren’t a balance sheet retranslated at the period-end rate, and there is no cumulative translation adjustment or intercompany elimination. Use the translated amounts for the profit and loss.

Hierarchies

Each load rebuilds seven dimensions from the master data of every source:

Dimension	Built from
Entity	Entities and their parents
Account	Chart of accounts and parent accounts
Department	Departments and their parents
Customer	Customers and their parent customers
Product	Products and their parent products
Segment 1	Segment values for segment_1
Segment 2	Segment values for segment_2

• A record with no parent, or one that names itself as its parent, loads as a top-level member.
• Because each load rebuilds the hierarchies, change a parent in the ERP or in your file, not in the dimension.
• A problem in one hierarchy affects only that dimension. The load and the template’s models still complete.
• You can rename Segment 1 and Segment 2 to what they hold, for example Class and Location.

To supply segment hierarchies from a file, choose the Segment values dataset:

Column	Holds
segment	Which segment the value belongs to: segment_1 to segment_5. Segment 1 and Segment 2 have dimensions.
segment_value	The value, as the general ledger, sales and budget lines carry it.
segment_value_name	Its name.
parent_segment_value	Its parent within the same segment.

Readiness Checks

After each load, the Readiness grid lists every check for every entity and period, with one of these statuses:

Status	Meaning
● Ready	The check passed.
▲ Needs attention	Worth a look, but the numbers are usable.
■ Problem	Fix this before you rely on the results.
– Not applicable	There is no data for this check yet.

The checks are:

Check	What it confirms
Data loaded	Every fiscal period has general ledger lines, and every ERP read succeeded. A closed period with no lines is a problem; an open period with none needs attention.
Trial balance ties to GL	The trial balance equals the general ledger for each entity, period and account. Where both the trial balance and the general ledger carry departments for an account, it also ties out each department.
Sales tie to GL	Sales lines equal the general ledger revenue and COGS accounts for each period.
Unknown products/customers	Every sales line names a product and customer that exist in the product and customer masters.
Accounts missing from chart	Every general ledger line names an account in the chart of accounts. Lines with an unknown account are left out of the profit and loss.
Currency translation	Every general ledger, trial balance, sales, inventory and budget row has an amount in the reporting currency. A profit and loss or sales row with no rate is a problem, because the profit and loss reads translated amounts. A balance sheet, inventory or budget row with no rate needs attention. A budget month with no rate yet shows amber, so load planned rates for budget months. See Currency Translation.
Unmapped entities	Every general ledger, trial balance and sales line names an entity in the entity master, directly or through the entity crosswalk. Not applicable until an entity master is loaded. See Entity Crosswalk.

Each row that isn’t ready says what to do in the What to do column, such as loading the chart of accounts, loading currency rates or running Load again for a period.

The grid also shows two setup checks, which appear only when they need attention:

Check	When it appears
Overhead allocation	Only one of the project variables overhead_department and overhead_driver is set, or no allocation driver matches overhead_driver, so the allocation would do nothing. Set both, or clear both to turn overhead allocation off.
Budget comparison	Budget rows are loaded, but none are in the version the project variable budget_version names, so the budget comparison would be empty.

Keep It Current

After a load from your ERP connection succeeds with no problems, Refresh nightly appears on the Last load card. Click it to run Load every night at 2:00 in your time zone. The card then shows Nightly refresh: Every night at 2:00 and who set it up.

The nightly load runs as the person who set it up. If that person can no longer use the ERP connection, the grid says the nightly load is skipped; set it up again. A project that has only sample data can’t be scheduled.

A file that is missing or empty on a nightly load finishes with a warning that names the path, not an error, and the rest of the model still loads.

Build on the Model With a Template

A template builds its reports and models on the Common Finance Model, so they work the same whichever ERPs feed it. When you start a project from a template, such as Enterprise Profitability, it creates:

• The Common Finance Model tables, empty or filled with sample data.
• Entity, Account, Department, Customer, Product, Segment 1 and Segment 2 dimensions built from those tables. See Hierarchies.
• The Load workflow and the workflows it runs to read your ERP and files, conform them, check readiness and build the template’s models.
• The template’s own reports and models. Enterprise Profitability adds a profit and loss table with revenue, COGS, gross margin, direct and overhead operating expenses, contribution and operating margin, plus margin bridges year over year and against budget.
• Metrics and an analysis path for asking questions about the results.

To explore before you connect your own ERP, tick Start with sample data when you create the project. The project runs a first Load straight away, so the Foundation tab shows a complete example, including a euro entity translated into US dollars. When you connect your own ERP or add your own files, the sample rows are cleared and your data takes their place.

The Foundation Tab

The Foundation tab is where you connect, load and check your data. It has these sections, top to bottom:

Section	What it shows
Last load	Whether the last load succeeded, failed or is still running, with the Load, Refresh and Refresh nightly buttons.
Setup	The setup checklist: Connect your data, Load your data, Check that totals tie out, Review your hierarchies and Share with your team. The current item is in bold.
Files	Each file you added, its dataset, such as General ledger or Currency rates, and whether its mapping is ✓ Confirmed or ○ Waiting for confirmation.
Data loaded	For each entity, the first and last period loaded and how many periods are ready.
Periods	Each entity’s fiscal periods and whether each is ✓ Closed or ○ Open.
Readiness	The readiness grid: one row per entity, period and check, with its status and What to do.
Connection	Connect your ERP, choose the entities and how many months of history to load.
Files (add)	Add a CSV, TSV, TXT, Excel or Parquet file and confirm how its columns map.
Load	Start a load.

While a load runs, the tab refreshes on its own.

The setup checklist tracks your progress:

• Connect your data and Load your data tick themselves once a source is attached and a load succeeds.
• Check that totals tie out ticks itself when the tie-out checks pass.
• Review your hierarchies: click Open dimensions to review the Entity, Account and Department hierarchies, then tick I have reviewed them. You confirm this one yourself.
• Share with your team: share the project when it is ready. See Managing Project Access.

Common Finance Model Tables

Data Foundation conforms every source, ERP or file, into the same fifteen tables, so reports, metrics and models built on them work the same whatever the source. Each row also records its source system and when it was read. The Dataset column is the name you choose when you add a file.

Dataset	Table	Holds
Entities	cfm_entity	Legal entities with parent, local and group currency, and country.
Entity crosswalk	cfm_entity_map	Source entity codes and the entity each belongs to. See Entity Crosswalk.
Currency rates	cfm_fx_rate	Monthly average, closing and historical rates by currency pair. See Exchange Rates.
Fiscal calendar	cfm_fiscal_calendar	One row per entity fiscal period, with start and end dates and whether it is closed.
Chart of accounts	cfm_account	Accounts with parent, type (Revenue, COGS, Opex, NonOperating, Asset, Liability, Equity), statement, normal balance and rate type.
Departments	cfm_department	Departments and cost centers with parent and profit center.
Products	cfm_product	Product master with parent, family, group, unit of measure and standard cost.
Customers	cfm_customer	Customer master with parent, group, region and country. Customer names are not masked.
Segment values	cfm_segment	Segment values with name and parent. See Hierarchies.
General ledger	cfm_gl_journal	Journal lines in transaction, local and reporting currency.
Trial balance	cfm_trial_balance	Period debits, credits and net by entity, account and department, in local and reporting currency.
Sales lines	cfm_sales_line	Invoiced sales lines with quantity, gross, discount, net and COGS amounts.
Inventory balances	cfm_inventory_balance	Period-end inventory by product and warehouse.
Budget	cfm_budget	Budget and forecast by scenario, version, account and department.
Allocation drivers	cfm_stat_driver	Statistical drivers such as headcount, square feet or units.

Periods are written YYYY-MM, and transactions are placed by posting period. Budget is optional: an ERP without budgets still loads everything else.

A trial balance file can carry debit and credit columns, a net column, or both. With only debit and credit, the net is debit minus credit, and a blank side counts as zero. With only a net, a positive net becomes the debit and a negative net becomes the credit, as a positive amount. A department column is optional.

Troubleshooting

Symptom	What to do
Test connection says the ERP refused the credentials	Check the connection’s credentials, and that the integration user can sign in to the ERP.
Test connection shows Missing permission for a dataset	Ask your ERP administrator to add the permissions it names. The connection guide for your ERP lists what the connection needs.
An ERP error message is shorter than the ERP’s own	PlaidCloud removes credentials, tokens and keys from provider errors before showing them, so some detail is left out. Run Test connection to see which dataset is affected.
Data loaded is a problem for a period	An ERP read for that period failed. Run Load again; periods that loaded are skipped.
Accounts missing from chart is a problem	Add the missing accounts to the chart of accounts in the ERP or your file, and run Load again.
Currency translation is a problem	An entity’s currency has no rate into the reporting currency for that period, or no single reporting currency was found. Add the rates to a Currency rates file, check that the connection can read the ERP’s exchange rates, or set the project variable reporting_currency. Then run Load again.
Currency translation needs attention	Balance sheet, inventory or budget rows have no closing, historical or planned rate for the period. The profit and loss is not affected. Add the rates, including planned rates for budget months, and run Load again.
Unmapped entities is a problem	Add the entity to the entity master, or map its code to an existing entity in the Entity crosswalk file, and run Load again.

Related

• Connections: set up the PlaidCloud connection for each ERP.
• Dimensions
• Managing Projects