Finance General Ledger Analytics data model
Kimball warehouse for the GL: transaction-grain journal lines and a monthly trial-balance periodic snapshot over conformed account/entity/cost-center/period/currency dimensions. Dual functional and reporting currency amounts support consolidation; vendor tax IDs are classified restricted and masked.
Corporate finance general-ledger analytics covering journal-entry postings at line grain and monthly trial-balance snapshots across a multi-entity, multi-currency chart of accounts. A Kimball star schema with conformed account (SCD2 chart of accounts), legal-entity, cost-center, period, and currency dimensions supporting GAAP and IFRS statement classification and consolidation. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 11
- tables
- 106
- columns
- 14
- relationships
- 3
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Journal line grain for the transaction fact
Kimball recommends declaring the fact at the lowest atomic grain; a single journal line (one account, one debit or credit) is the natural atom of the ledger and supports drill-across to any rollup.
Separate monthly trial-balance periodic snapshot
Balances are semi-additive (sum across accounts, not across periods); a periodic snapshot at account/entity/period grain gives fast statement reporting independent of journal volume.
Dual functional and reporting currency measures
Multi-entity consolidation under IFRS/GAAP requires both the entity functional currency and the group reporting currency on every fact, with the exchange rate retained for audit.
SCD2 on the chart of accounts and legal entity
Restatements and reorganizations change account classification and consolidation method over time; type-2 history preserves the as-was mapping required for comparable period reporting under SOX.
Tables in the Finance General Ledger Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (2)
fct_gl_journal
Grain: one row per general-ledger journal line
Transaction-grain ledger fact — one row per journal line (debit or credit).
| Column | Type | Keys |
|---|---|---|
| gl_journal_key | bigint | PK |
| date_key | bigint | |
| period_key | bigint | |
| account_key | bigint | |
| entity_key | bigint | |
| cost_center_key | bigint | |
| currency_key | bigint | |
| department_key | bigint | |
| vendor_key | bigint | |
| project_key | bigint | |
| posting_date_key | bigint | |
| journal_id | varchar | |
| journal_line_number | integer | |
| source_module | varchar | |
| entry_type | varchar | |
| debit_amount | decimal | |
| credit_amount | decimal | |
| amount_functional | decimal | |
| amount_reporting | decimal | |
| exchange_rate | decimal | |
| is_posted | boolean | |
| is_reversal | boolean |
fct_trial_balance
Grain: one row per account per entity per fiscal period (monthly periodic snapshot)
Monthly periodic snapshot — balances are semi-additive across periods.
| Column | Type | Keys |
|---|---|---|
| period_key | bigint | |
| account_key | bigint | |
| entity_key | bigint | |
| cost_center_key | bigint | |
| currency_key | bigint | |
| opening_balance | decimal | |
| period_debits | decimal | |
| period_credits | decimal | |
| net_movement | decimal | |
| closing_balance | decimal | |
| closing_balance_reporting | decimal | |
| is_adjusted | boolean |
Dimension tables (8)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| month_number | smallint | |
| month_name | varchar | |
| quarter | smallint | |
| year | smallint | |
| fiscal_year | smallint | |
| fiscal_period | smallint | |
| is_business_day | boolean | |
| is_period_end | boolean |
dim_period
Grain: one row per fiscal accounting period
| Column | Type | Keys |
|---|---|---|
| period_key | bigint | PK |
| period_code | varchar | BK |
| fiscal_year | smallint | |
| fiscal_quarter | smallint | |
| fiscal_month | smallint | |
| period_start_date | date | |
| period_end_date | date | |
| period_status | varchar | |
| is_year_end | boolean |
dim_account
Grain: one row per GL account per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| account_key | bigint | PK |
| account_id | varchar | BK |
| account_number | varchar | |
| account_name | varchar | |
| account_type | varchar | |
| account_subtype | varchar | |
| normal_balance | varchar | |
| gaap_classification | varchar | |
| ifrs_classification | varchar | |
| statement_section | varchar | |
| parent_account_number | varchar | |
| is_intercompany | boolean | |
| is_active | boolean |
dim_entity
Grain: one row per legal entity per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| entity_key | bigint | PK |
| entity_id | varchar | BK |
| entity_name | varchar | |
| entity_type | varchar | |
| country | varchar | |
| functional_currency | varchar | |
| tax_jurisdiction | varchar | |
| consolidation_method | varchar | |
| parent_entity_id | varchar | |
| is_eliminating | boolean |
dim_cost_center
Grain: one row per cost center per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| cost_center_key | bigint | PK |
| cost_center_id | varchar | BK |
| cost_center_name | varchar | |
| cost_center_type | varchar | |
| manager_name | varchar | PII |
| region | varchar | |
| parent_cost_center_id | varchar |
dim_department
Grain: one row per department
| Column | Type | Keys |
|---|---|---|
| department_key | bigint | PK |
| department_id | varchar | BK |
| department_name | varchar | |
| function | varchar | |
| division | varchar |
dim_vendor
Grain: one row per vendor per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| vendor_key | bigint | PK |
| vendor_id | varchar | BK |
| vendor_name | varchar | |
| tax_id | varchar | PII |
| vendor_category | varchar | |
| country | varchar | |
| is_1099 | boolean |
dim_project
Grain: one row per project
| Column | Type | Keys |
|---|---|---|
| project_key | bigint | PK |
| project_id | varchar | BK |
| project_name | varchar | |
| project_status | varchar | |
| project_manager | varchar | PII |
| start_date | date | |
| end_date | date |
Lookup tables (1)
dim_currency
Grain: one row per ISO currency
| Column | Type | Keys |
|---|---|---|
| currency_key | bigint | PK |
| currency_code | varchar | BK |
| currency_name | varchar | |
| currency_symbol | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_gl_journal.date_key → dim_date (many-to-one)
- fct_gl_journal.period_key → dim_period (many-to-one)
- fct_gl_journal.account_key → dim_account (many-to-one)
- fct_gl_journal.entity_key → dim_entity (many-to-one)
- fct_gl_journal.cost_center_key → dim_cost_center (many-to-one)
- fct_gl_journal.currency_key → dim_currency (many-to-one)
- fct_gl_journal.department_key → dim_department (many-to-one)
- fct_gl_journal.vendor_key → dim_vendor (many-to-one)
- fct_gl_journal.project_key → dim_project (many-to-one)
- fct_trial_balance.period_key → dim_period (many-to-one)
- fct_trial_balance.account_key → dim_account (many-to-one)
- fct_trial_balance.entity_key → dim_entity (many-to-one)
- fct_trial_balance.cost_center_key → dim_cost_center (many-to-one)
- fct_trial_balance.currency_key → dim_currency (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.