Finance module
Account groups
Data Hub's financial calculations and analytics rely on a list of pre-defined ‘Common Account Types’, which can be found in a Unified Layer enabled model’s ‘Common Chart Mapping’ data source. Once a Unified Layer enabled solution is deployed and the account types are correctly mapped, all Financial analytics and dashboards will report and work across companies and chart of accounts.
The Common Account Type (looked up from the embedded Common chart mapping spreadsheet) maps your ERP’s categorization of accounts (e.g. groups, categories, classes) to Common Account Types. These are then used as foreign keys in relevant pipelines, therefore remapping should not be required.
Common chart mapping spreadsheet
To view the Common Account Category mapping, or add mappings to a report, download and edit the ‘Common chart mapping’ spreadsheet from the model. The spreadsheet has two tabs, namely, ‘CommonCoA’ and ‘SourceMapping’.
The ‘CommonCoA’ tab contains a list of pre-defined account types that can be used to map your account groups or accounts. This sheet contains different generic categorizations of accounts in a typical enterprise application or accounting software, such as Classes, Categories, and Types. Each of these classifications has its own granularity which is useful for reporting.
The ‘SourceMapping’ tab by default contains the mapping of your ERP’s categorized accounts to the specified ‘CommonAccountType’ found on the CommonCoA tab. On this tab, you can map any additional groupings (under the ‘SourceAccountCategoryName’ header) to Common Account Types (under the ‘CommonAccountType’ header) by selecting a type from the drop-down box.
It may not be appropriate to use account categorizations such as groups, categories, classes as your ERP may not support this. In this case, it may be possible to map account ranges to Common Account Types instead:
In this case, account ranges are associated with Common Account Types. For example, ‘Cash and Cash Equivalents’ is represented by accounts between 10100 and 10999 inclusive.
Common account types
Outlined below are Common account types, descriptions, and the method to follow to map your chart of accounts.
Most of the ZAP calculated members in the analytics, have the same name as the Common Account Type. For more information on accounts mapped to each account category, refer to: Configure account categories for financial reporting
Accounts
This unified layer is applied to general ledger accounts, typically from a software’s chart of accounts. Accounts are categorized at various granularities, such as Class, Balance or Income, Category and Type. For more information on these categorizations, see the Common chart Unified Layer.
Columns table
Column Name | Data Type | Description | Key |
Account category name | Text (50) | The name of the account category or group that the software uses to categorize accounts for financial reporting purposes. | |
Account code | Text (50) | The account number or code to which general ledger transactions are posted to. | Primary key |
Account key | Text (100) | For use in Power BI relationship mappings. This is usually made up of the concatenation of key columns with the company column of a dimension pipeline. If the dimension is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Account-level | Text (10) | The level that is associated with an account in a top-down account hierarchy, normally represented as a number. This column is for software systems that have their own built-in parent-child hierarchies. For example, an account representing 'Current Assets' may be a level 2 account, and 'Inventory' would be level three. The parent account of 'Inventory' would be 'Current Assets' | |
Account name | Text (100) | The name that is associated with the account code. | |
Balance or Income | Text (50) | The name that is associated with the account code. Indicates whether the account is a Balance sheet account or Profit & Loss account. | |
Cash account | Text (1) | Indicates whether the account is a cash account or not. | |
Cash flow relevant | Text (1) | Flag that indicates whether the account is applicable in a cash flow statement or not. | |
Category | Text (50) | The number or code associated with an ERP or accounting software's own account category. Account categories (or groups) separate accounts into several categories for reporting purposes | |
Common Account Type | Text (50) | A way of grouping accounts based on codes in the 'Common Chart mapping' spreadsheet. The Common Account Type column is a unique identifier code that offers a granularity of categorization that is commonly used for reporting. Examples of these codes are 'CASH', 'AR', AP', 'SALES' and 'COGS' | |
Common account Type Name | Text (100) | The name associated with the Common Account Type, originating from the 'Common Chart mapping' spreadsheet. Examples include 'Cash and Cash Equivalents', 'Accounts Receivable', 'Accounts Payable', 'Sales' and 'Cost of Goods Sold'. | |
Common category | Integer | A way of grouping accounts based on codes in the 'Common Chart mapping' spreadsheet. The Common category column is an integer that offers a granularity of categorization that is commonly used for reporting. | |
Common category name | Text (50) | The name associated with the Common category, originating from the 'Common Chart mapping' spreadsheet. Examples include 'Current Assets', 'Non-Current Assets', 'Current Liabilities', 'Non-Current Liabilities', 'Equity', 'Revenue', and 'General Expenses'. | |
Common class | Integer | A way of grouping accounts based on codes in the 'Common Chart mapping' spreadsheet. The Common class column is an integer that offers a granularity of categorization that is commonly used for reporting. | |
Common class name | Text (25) | The name associated with the Common class, originating from the 'Common Chart mapping' spreadsheet. Examples include 'Assets', 'Liabilities', 'Equity', 'Revenue', 'Expenses', 'Other Income' and 'Other Expenses'. | |
Company | Text (50) | The column contains the unique code of the legal entity that it is necessary to report on. | Additional key |
Is retained earnings | Text (5) | Flag that indicates whether the account is a Retained earnings account or not. | |
Level 1 code | Text (50) | The code of the first level in the account hierarchy. | |
Level 2 code | Text (50) | The code of the second level in the account hierarchy. | |
Level 3 code | Text (50) | The code of the third level in the account hierarchy. | |
Level 4 code | Text (50) | The code of the fourth level in the account hierarchy. | |
Level 5 code | Text (50) | The code of the fifth level in the account hierarchy. | |
Parent Account | Text (50) | The account code that is one level directly above the account code in the hierarchy. | |
Segment 1 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 1 refers to the first segment in the list. | |
Segment 2 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 2 refers to the second segment in the list. | |
Segment 3 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 3 refers to the third segment in the list. | |
Segment 4 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 4 refers to the forth segment in the list. | |
Segment 5 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 5 refers to the fifth segment in the list. |
Accounts payable
This unified layer is applied to the Accounts Payable pipeline. Use this layer to map columns such as your vendors' AP balances, invoice due dates, and aged days.
Columns table
Column Name | Data Type | Description | Key |
Amount | Numeric | The column contains the total amount on the supplier's invoice. | |
AP balance Company | Numeric | The total amount still outstanding from a supplier's invoice. The unique identifier code of the company that has transactions with suppliers. | |
Company | Text (50) | The unique identifier code of the company that has transactions with suppliers. | Additional key |
Days past due | Numeric | For unsettled transactions, the number of days between the due date and today. | |
Days to pay | Numeric | For settled transactions, the number of days between the due date and the paid date. | |
Document count | Numeric | A column that counts the number of distinct AP invoices. | |
Due date | Date | The date that the AP invoice is due to be paid to the supplier. | |
Due date aged days | Integer | A column that shows the number of days past due for days between 150 days early and 150 days late. It can be used to relate to the aging buckets pipeline. | |
Is Closed | Integer | Flag to determine if an invoice has been closed or not. | |
Payment date | Date | For paid transactions, the date that payment has been made. | |
Posting date | Date | The invoice or posting date of the transaction. | |
Row number | Text (50) | The unique identifier line number within the accounts payable invoice. | Additional key |
Segment 1 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 1 refers to the first segment in the list. | |
Segment 2 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 2 refers to the second segment in the list. | |
Segment 3 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 3 refers to the third segment in the list. | |
Segment 4 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 4 refers to the forth segment in the list. | |
Segment 5 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 5 refers to the fifth segment in the list. | |
Transaction key | Text (50) | The unique identifier header code of the accounts payable invoice. | Primary key |
Transaction type | Text (50) | The document type responsible for the accounts payable open item. | |
Vendor | Text (50) | The unique identifier code of the vendor or supplier. | |
Vendor group | Text (50) | The unique identifier code of the vendor group or supplier group. | |
Vendor key | Text (100) | For use in Power BI relationship mappings. This is usually made up of the concatenation of key columns with the company column of a dimension pipeline. If the dimension is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Relationships
Name | Related layer | Mapping |
Company | Company | Company -> Company |
Vendors | Vendors | Vendor key -> Vendor key |
Note
It is assumed that all amount columns mapped to this unified layer are already currency converted to the Main currency. If amounts are not currency converted when they are mapped, cross-company reporting may not work as expected.
Accounts receivable
This unified layer is applied to the Accounts Receivable pipeline. Use this layer to map columns such as your customers' AR balances, invoice due dates, and aged days.
Columns table
Column Name | Data Type | Description | Key |
Amount | Numeric | The column that contains the total amount on the customer's invoice. | |
AR balance | Numeric | The total amount still outstanding from a customer's invoice. | |
AR paid after due month | Numeric | This is the amount of Account Receivables that is not due in a particular month and has been paid after its due month. Used for Collection Effectiveness calculations. | |
AR paid before due month but after posting | Numeric | This is the amount of Account Receivables that is not due in a particular month and has been paid before its due month but after it has been posted. Used for Collection Effectiveness calculations. | |
AR paid in the due month | Numeric | This is the amount of Account Receivables that is not due in a particular month and has been paid in the same month as its due date. Used for Collection Effectiveness calculations. | |
AR pre-paid or paid in the posted month | Numeric | This is the amount of Account Receivables that is not due in a particular month and has been pre-paid or paid in in the same month as it has been posted. Used for Collection Effectiveness calculations. | |
Company | Text (50) | The unique identifier code of the company that has transactions with suppliers. | |
Current not paid receivables | Numeric | This is the amount of Account Receivables that is not past due, and has not been paid to date. Used for Collection Effectiveness calculations. | |
Customer | Text (50) | The unique identifier code of the customer. | |
Customer group | Text (50) | The unique identifier code of the customer group. | |
Customer key | Text (100) | For use in Power BI relationship mappings. This is usually made up of the concatenation of key columns with the company column of a dimension pipeline. If the dimension is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Days past due | Numeric | Numeric For unsettled transactions, the number of days between the due date and today. | |
Days to pay | Numeric | Numeric For settled transactions, the number of days between the due date and the paid date. | |
Document count | Numeric | Numeric A column that counts the number of distinct AR invoices. | |
Due date | Date | The date that the AR invoice is due to be paid by the customer. | |
Due date aged days | Integer | A column that shows the number of days past due for days between 150 days early and 150 days late. It can be used to relate to the aging buckets pipeline. | |
Is Closed | Integer | Flag to determine if an invoice has been closed or not. | |
Is Paid | Integer | Flag to determine if an invoice has been paid or not. | |
Payment date | Date | For paid transactions, the date that payment has been made. | |
Payment vs Due date status | Integer | The status of a transaction based on its payment date, due date and document date. Used for Collection Effectiveness calculations. | |
Posting date | Date | The invoice or posting date of the transaction. | |
Row number | Text (50) | The unique identifier line number within the accounts payable invoice. | |
Sales | Numeric | Total amount on the invoice, including tax and freight in a particular month. Used for Collection Effectiveness calculations. | |
Segment 1 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 1 refers to the first segment in the list. | |
Segment 2 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 2 refers to the second segment in the list. | |
Segment 3 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 3 refers to the third segment in the list. | |
Segment 4 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 4 refers to the forth segment in the list. | |
Segment 5 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 5 refers to the fifth segment in the list. | |
Total AR for future payments | Numeric | Total Accounts Receivables that is past due, and has been paid after the context month. Used for Collection Effectiveness calculations. | |
Total AR for past payments | Numeric | Total Accounts Receivables that is past due, and has been paid on or before the context month. Used for Collection Effectiveness calculations. | |
Total AR for unpaid open items | Numeric | Total AR balance that is past due, and has not been paid to date. Used for Collection Effectiveness calculations. | |
Transaction key | Text (50) | The unique identifier header code of the accounts receivable invoice. | |
Transaction type | Text (50) | The document type responsible for the accounts receivable open item. |
Relationships
Name | Related layer | Mapping |
Company | Company | Company -> Company |
Customer groups | Customer groups | Customer group -> Customer group Company -> Company |
Customer | Customer | Customer -> Customer Company -> Company |
Note
In order to take advantage of Collection effectiveness metrics, it is important to map the following CEI columns in the unified layer: AR paid after due month, AR paid before due month but after posting, AR paid in the due month, AR pre-paid or paid in the posted month, Current not paid receivables, Sales, Total AR for future payments, Total AR for past payments, Total AR for unpaid open items.
It is assumed that all amount columns mapped to this unified layer are already currency converted to the Main currency. If amounts are not currency converted when they are mapped, cross-company reporting may not work as expected.
Budget scenario
This unified layer is applied to general ledger budget pipelines which provide the list of budget scenarios. Depending on the ERP or accounting software, these may be referred to as Budget Types, Codes, Versions or Models. The list of budget scenarios is used by the 'Ledger Actuals Budget' pipeline and can be used to compare actual with budget financial data.
Columns table
Column Name | Data Type | Description | Key |
Company | Text (50) | The unique identifier code of the company that the budget is applicable to. | Additional key |
Fiscal Year | Start Date | The start of the fiscal year for which the budget applies. | |
Main budget | Text (5) | This is a flag that identifies the company’s main budget. If it is the main budget, it will return the text ‘Main’. If not, it will return ‘Other’. | |
Scenario | Text (50) | The unique identifier code associated with the budget scenario (or budget type, code or version). | Primary key |
Scenario key | Text (100) | For use in Power BI relationship mappings. This is usually made up of the concatenation of key columns with the company column of a dimension pipeline. If the dimension is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Scenario name | Text (100) | The name of the budget scenario. |
Common Chart of Accounts
The purpose of this unified layer is to generically categorize the general ledger accounts of an enterprise application or accounting software into common classes, categories and types. Each of these classifications has its own granularity which is useful in reporting and can make up a hierarchy of Class->Category->Type.
Tip
An account may be mapped to one of the following 'Common Classes': Assets, Liabilities, Equity, Revenue, Expenses, Other Income, Other Expenses
An account may be mapped to a 'Common Category' such as: Current Assets, Non-Current Assets, Current Liabilities, Non-Current Liabilities, General Expenses.
An account may be mapped to a 'Common Account Type' such as: Cash, Accounts Receivable, Inventory, Intangible Assets, Interest Payable.
Columns table
Column Name | Data Type | Description | Key |
Account Type | Text | The unique identifier code associated with the Account Type, for example 'AP', 'AR', 'INV'. | Primary key |
Account Type ID | Integer | The unique identifier number associated with the Account Type in the spreadsheet. This column is useful for sorting as per standard financial statements. | |
Balance or Income | Text (20) | Flag column to indicate whether an account is a balance sheet account or profit & loss. | |
Cash Flow Category | Text (20) | For cash flow accounts, this column indicates the cash flow category it belongs to. Returns NULL for non-cash flow accounts. | |
Category | Integer | The category number associated with the account. | |
Category Name | Text (100) | The name of the category that the account belongs to. | |
Class | Integer | The class number associated with the account. | |
Class Name | Text (100) | The name of the class that the account belongs to. | |
Common Account Type Name | Text (150) | The name associated with the Account Type, for example 'Accounts Payable', 'Accounts Receivable', 'Inventory'. |
Debit/Credit
Flag indicating whether the transaction is a debit or a credit.
Columns table
Column Name | Data Type | Description | Key |
Debit/Credit | Text (5) | Flag indicating whether the transaction is a debit or a credit. | |
Debit/Credit - Label | Text (10) | Description of the flag indicating whether the transaction is a debit or a credit. |
General ledger
The General ledger unified layer is applied to the pipeline that combines general ledger actual and budget data (if available). Use of this unified layer will allow seamless reporting across GL actuals and budgets for financial reporting across companies, charts of accounts and segments, where applicable.
Columns table
Column Name | Data Type | Description | Key |
Account code | Text (50) | The unique identifier code associated with the account against which the general ledger transaction is recorded. | Additional key |
Account key | Account key Text (100) | For use in Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Accounting date | Date | The accounting or posting date of the general ledger transaction. | |
Actual amount | Numeric | The amount attached to the general ledger transaction, in transaction currency. | |
Actual amount (local) | Numeric | The amount attached to the general ledger transaction, in local currency. | Primary key |
Budget amount | Numeric | The budget amount attached to the general ledger transaction, in transaction currency. | |
Budget amount (local) | Numeric | The budget amount attached to the general ledger transaction, in local currency. | |
Budget scenario | Text (50) | The unique identifier code associated with the budget scenario (or budget type, code or version). | |
Budget scenario key | Text (100) | For use in Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Common account type | Text (50) | The unique identifier code associated with the Account Type, for example 'AP', 'AR', 'INV'. | |
Company | Text (50) | The unique identifier code associated with the company. | |
Credit Amount | Numeric | An amount that either increases a liability or equity account, or decreases an asset or expense account | |
Currency | Text (50) | The transaction currency. | |
Debit amount | Numeric | An amount that either increases an asset or expense account, or decreases a liability or equity account. | |
Debit/Credit | Text (1) | Flag indicating whether the transaction is a debit or a credit. | |
Description | Text (300) | A description of the general ledger transaction. | |
Fiscal Year | Text (35) | The fiscal year that the transaction is in. | |
Is Generated RE | Text (10) | Flag to indicate whether this is a data model generated retained earnings transaction or not. | |
Journal entry | Text (100) | The journal entry that this transaction belongs to. | |
Line number | Integer | The line number of the transaction. | |
Main budget | Text (100) | This is the budget that is classified by the company as the main budget. | |
Period | Text (50) | The fiscal period that the transaction is in. | |
Retained earnings amount | Numeric | The retained earnings amount for the period. Retained earnings are calculated in the data model at the month granularity. | |
Segment 1 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 1 refers to the first segment in the list. | |
Segment 2 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 2 refers to the second segment in the list. | |
Segment 3 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 3 refers to the third segment in the list. | |
Segment 4 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 4 refers to the forth segment in the list. | |
Segment 5 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 5 refers to the fifth segment in the list. | |
Site | Text (50) | The site to which this transaction relates to. | |
Transaction type | Text (50) | Unique identifier code for the general ledger transaction type or origin. | |
Transaction type Name | Text (100) | The name of the transaction type. |
Relationships
Name | Related layer | Mapping |
Accounts | Accounts | Account key-> Account key |
Budget Scenario | Budget Scenario | Budget Scenario key-> Scenario key |
Common Chart of Accounts | Common Chart | Common account type -> Account type |
Company | Company | Company -> Company |
Note
If Ledger Actual and Budget information is stored in different tables in the source, it is necessary to combine them into one table to make use of this unified layer. Ensure that the tables have at least the following columns in common before combining them (for example, using a union), to ensure the correct granularity: Account code, Common Account Type, Company, Date, Amount.
It is assumed that all amount columns mapped to this unified layer are already currency converted to the Main currency. If amounts are not currency converted when they are mapped, cross-company reporting may not work as expected.